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 🙂
Hi Imke,
fully agree with approach. I use very similar also for Excel modeling. Kind of “Design mode” for workbooks. Helps also to prevent publishing of workbooks with wrong data if something goes wrong during refresh.
Hi Ivan, good point!
So instead of filtering we could also “divert” to a different source with small sets of sample data.
you always have great ideas…this is good i was struggling with large models and everrytime you step through the query editor steps it refreshes/updates in the background (even if auto refresh is tuned off in the options >settings menu.
Another great timesaver from the BIAccountant. Thnaks David
Hi David,
thanks for the flowers – but I’m still not sure if I can claim them for myself 🙂
But as I’m treasuring this as a real timesaver, thought it’s time to share it.
Actually I often thought that the refresh in the query editor took longer than loading the query into the model/sheet.
Hi Imke,
What is the best way to set-up a central switch to control the DBFilterToggle value when you are using Power BI Desktop? I have used a parameter but wasn’t sure if there was a better way to control changing the toggle value from 0 to 1. Would changing the query properties option of include in report refresh to no, achieve the same effect as the toggle mentioned above?
Thank you Dave,
yes, I think all of your suggestions would work.
Cannot think of a better ways.
Forgive my ignorance but how is this different than inserting a filter to keep top 100 rows a the first step after connecting to data source? Then, after all mashing/modeling, you can just erase that first filter step.
Is it the toggle aspect that allows you to quickly turn it on and off?
Yes, you’re right: You could insert a filter and then erase it.
For me, the toggle has 2 main advantages:
I leave it in the workbook, also when shipping. Often these files go back and forth before the client is finally happy with it, so I don’t have to insert filters manually back again when I’m going to edit the code.
The other thing is that I can activate multiple filters in my workbook by switching just one toggle.
It’s not much, just a convenience I wouldn’t want to miss any more 🙂
Sorry i am a rookie here but when I put FilterToggle = if DBFilterToggle=1 then LastQueryStep else FilteredRowStep it said DBFilterToggle not found – any ideas?
Thanks
Peddie
Sorry, I should have made that clearer: You need a query named “DBFilterToggle” which you allocate the 1 or 0 to, like:
let
Source = 1
in
Source
After that you can reference it with the formula from above.
You should add this information to the main body of the page. Dave H. also called out using a parameter which would apparently work as well. It would be helpful to include that information in the main body as well.
Hey Imke,
that’s a nice one. It reminded me, that PQ only evaluates those lines of code, which are actually used in future steps. Thanks for that hint 🙂
Regards,
Lars
Happy you’ve enjoyed it Lars 🙂
I really like how this could benefit me. I wait often, 10 minutes between changes and updates to queries due to size and complexity. But, you do not describe above how to implement fully. What are all your steps to get this working?
Hey Imke,
I know this is an old post already, just thought I’d ask you anyway, since I love saving on waiting time whenever I can.
I’ve tried this too before, but doesn’t this break query folding when you leave it in the report (even when turned off)? I’m working with MySQL as a datasource, but certain operations (such as change type, but also filtering on (for instance) a startdate, or a toggle stored in a central parameter seems to break query folding for me. So it’s kind of a trade off in my opinion. Do you have any way around this?
Cheers,
Niels
Hi Niels,
yes good point. It might prevent further folding. So will have to find a compromise where to place the filter toggle.
Hi, we can also use Table.Buffer for this reason, right? Do you know if they are the same?
I like the approach. Do you have a way to pass in an external data source for the filter? I want to create a SharePoint list with one value, for the filter (TRUE/FALSE) I want to query that list and pass that value in, but I cannot figure it out.
It does not really speed up the query design process as stil the data are loaded from the whole file and it might take very long.
Is it possible to turn off any automatic refresh of incoming data, only when it is excplicitly asked to do via “Refresh button”? It eould be the best feature that could save a lot of time.
Imke I am loving this. I was already using Kept Rows to limit my recordset during design time, however adding/removing it manually and only ever in one query at a time. What a great idea to control this centrally!
hello, thanks for sharing, but I could not understand the issue of switching off and on from a central switch, would it be possible to upload a small bpix example?