Power Query Editor: performance loading & viewing the data

[jasetheace]

I have such a love/hate relationship with Power BI. The hate part is performance. What am I doing wrong?

Just today I load up my Power BI report, open the Query Editor, and I'm sitting here waiting 5+ minutes for this data preview to load:

This is not complicated stuff: these are locally stored .csv files. 12 of them. Total file size about 50Mb. They are Appended together.

My transformations (Applied Steps) are minimal: change the type of a field and make some values lowercase. There's no complex calculations.

I've got 10+ various Power BI reports and I swear for every join / calculation / analysis I perform, I spend 10% of my time writing the code and 90% of my time waiting for these **bleep** little dots to cross the screen and present the data to me...

I'm about to abort Power BI and import all data into a database and start writing querys in SQL, as that's substantially quicker and more efficient use of my time. This is just ludicrous. 

But it can't be just me???

[ManuelBolz]

Hello @jasetheace,

do you basically know the concept of dataflows? Maybe this is a solution for you.

For example, you store your CSV files on SharePoint/Teams/OneDrive. You access this with your dataflows. For log files, daily updating of the data should be sufficient.

The key advantage is that Power BI Desktop no longer has to manage file access.

I've got 10+ various Power BI reports and I swear for every join / calculation / analysis I perform, I spend 10% of my time writing the code and 90% of my time waiting for these **bleep** little dots to cross the screen and present the data to me...

Do you need a separate dataset for each of the 10+ reports. Or do you have 1 dataset for 10+ reports?

[lbendlin]

What's your question?  Can you show sample code and maybe provide some sample files?

[jasetheace]

For a question, how about: how to improve the efficiency of either a) my .pbix file or b) Power BI in general so I don't wait so long for the Power Query editor to present the results to me?

Are there some general settings that I should implement that I haven't so far? Are there some magic checkboxes that make all the difference that I should tick / may have unticked?

Is it a caching thing? Should I clear the cache more often? Never clear the cache? Have a bigger cache? Allow previews to load in background? Never allow them to load in background? Is there some indexing setting that increases performance?

This is also just a general gripe / "how do you manage your workload with this kind of performance?" question. I don't understand how anyone can be using this tool to achieve anything *quickly* because the slightest of changes to my queries can take me an entire day to code, test, and publish... I can't be the only person struggling with this?

[lbendlin]

how to improve the efficiency of either a) my .pbix file or b) Power BI in general so I don't wait so long for the Power Query editor to present the results to me?

You can choose to invest some time into learning the inner workings of Power Query. I am not demanding you should do that - it is your choice.  There are some excellent resources for that, mainly the Power Query Primer by Ben Gribaudo.  If you can work through all the installments of that primer you will have mastered the subject and will have (maybe) gained an appreciation for the elegance of that language. 

In the short term you can use the Query Diagnostics options to pinpoint which steps in your query perform badly.

Are there some general settings that I should implement that I haven't so far? Are there some magic checkboxes that make all the difference that I should tick / may have unticked?

You an choose to load previews in the background, or only do that when you need a refresh. This is similar to the "Apply filters"  feature in Power BI and Excel Pivot tables.  Switching the preview off will dramatically improve your development speed (especially if you modify the code heavily) at the risk that the sample data is no longer representative of what you are trying to achieve.  So - switch it off for furious development and switch it back on for the final checks.

Is there some indexing setting that increases performance?

Yes, there are actually two concepts.  One is specifying key columns in your existing data via Table.AddKey or by adding an Index column which automatically adds a primary key.  The other is to consider if certain steps in your code will be referenced more than once. If that is the case then you will want to use Table.Buffer or List.Buffer to force loading that step into memory.

This actually exposes the main weakness of Power Query. By default all processing is done on disk. In contrast, all DAX is done in memory.  By using buffers you can shift some of the burden of the Power Query process into memory which in some cases leads to dramatic performance improvements (at the cost of having to load the data into memory first, and at the risk of exhausting the memory)

I can't be the only person struggling with this?

The struggle is real, but it is very different for each person.  Once you get to know Power Query you will understand that it is overly paranoid about meta data changes, and is adding a lot (A LOT) of seemingly unnecessary steps to double and triple check that the meta data is still the same.  The tiniest change in meta data throws it into a tizzy, and leads to the dreaded "Evaluating..."  stage.  The "solution"  to this is to sit on your hands and not change the structure of your query every five minutes.

Finally, a personal opinion on merges: They are evil and harmful.  Whenever you can, don't do them. Instead, let the data model do the work for you.