Power Query: Flat Out

Welcome to our Power Query blog. This week, I look at ways to improve the memory usage of a query involving flat files.

have a query which is running slowly. For the purposes of this blog, the queries are assumed to be coming from flat files which are not subject to query folding. If you have no idea what query folding is, don’t worry – more on query folding in a future blog!

This week, I will look at a couple of ways to improve the memory used processing the query in the Power Query engine.  Next time, I will also look at how to speed up loading this query to the Data Model in Power BI Desktop.

There are two areas of this query which are greedy when it comes to memory.  The first is right at the beginning, in the first step:

I am merging two queries to get this query.  I covered how to improve table merges in Power Query: Merging Matters. I can add a Table.Buffer() step to store a joined table in memory.  Although I normally start with the source step, this is not a rule, so I can add a step before it in the Advanced Editor:

I can then refer to this instead of the table Items in the join:

This should improve the merge.  The next area I can improve is the Added Custom step.

This is reading all the values in a list and adding them up.  I describe this process and using List.Buffer() to speed things up in Power Query: Keep on Running.  Instead of using #”Added Index”[Amount], I can buffer this value.  I insert a step before Added Custom (though it can be after it, Power Query doesn’t care!).

I must also refer to this in Added Custom:

Next time, I will consider how to make this query more efficient when loading to the Data Model in Power BI Desktop.

Come back next time for more ways to use Power Query!