Speed up the PowerBI and Power Query design process

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

filtercode2

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 🙂

Comments (12) Write a comment

  1. 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.

    Reply

    • Hi Ivan, good point!
      So instead of filtering we could also “divert” to a different source with small sets of sample data.

      Reply

  2. 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

    Reply

    • 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.

      Reply

  3. 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?

    Reply

    • Thank you Dave,
      yes, I think all of your suggestions would work.
      Cannot think of a better ways.

      Reply

  4. 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?

    Reply

    • 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 🙂

      Reply

  5. 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

    Reply

    • 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.

      Reply

  6. 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

    Reply

Leave a Reply