When querying large databases with Power BI or Power Query, the design of the query itself sometimes causes long waiting periods because of background loading activities. In order to speed up this process, I’m adding a filter in my tables as one of the first steps, which I can toggle on or off using a central switch. The toggle is a query that will be referenced by an additional line of code:
FilterToggle = if DBFilterToggle=1 then LastQueryStep else FilteredRowStep
Where DBFilterToggle is a query itself with just one value: 0 if I want to have the filter active or 1 if I want to deactivate the filter and see all the data instead.
The cool thing about this method is that the following code will work seamlessly and we can use this toggle in multiple queries at the same time: Just filter your other long tables to your desire and refer them to DBFilterToggle.
This enables me to work with a very few data which will be delivered blazingly fast (due to query folding) and move on with my design very quickly or (often more importantly) during debugging where you need to move through multiple steps quickly in order to find the errors.
I’m not sure, if this is my “invention” or I have seen this before, but so far couldn’t find the source. So please post the source in the comments if you find it.
Enjoy & stay queryious 🙂