Slow data refresh due to merges? (Power Query optimisations)

[GilesFS]

Hi all,

We have a PowerBI Pro licence and the refresh of one of our PowerBI datasets is taking between 1hr and over 2 hours (at which point it fails due to timeout). The volumes of data are not huge but I believe it may be due to the way the PowerBI merges data from differnet sources.

As I understand, the way the merge operates is that, rather than load both sources tables initially (two single requests to the source tables) and then join to create the combined table, it starts with one table then makes individual calls to the other source table for every line.

I have tried creating separate queries in the Power Query window pointing to the individual source tables and then joining those queries in a separate query, but that hasn't helped (so I suspect it doesn't actually load the data in those initial queries, but just uses them as a reference).

I'm thinking about adding a couple of simple query steps to those initial queries (e.g. add a dummy row), to see if that forces an actual load of the table, which then gets used in the subsequent join query to reduce the number of requests going out to he source tables.

I was just wondering if anyone had come across or tried anything similar before?

Thanks

[mahoneypat]

Merges can get slow, but that sounds ways too long.  Can you share your M code for your queries from the Advanced Editor?

You may be interested in this 3-part series by Chris Webb.

Chris Webb's BI Blog: Optimising The Performance Of Power Query Merges In Power BI, Part 1: Removing...

Pat

[GilesFS]

Thanks Pat,

Hard to share all the M code as there are a total of 30 queries in the file, which pull data from 17 tables from 2 sources and then perform different transformations to get to the final tables for reporting. (Unless you know of a way of grabbing all the M-code in one go?).

I appreciate I'm probably using Power Query as more of an ETL tool than it is possibly intended for.

Thanks for the Chris article, very interesting, but not sure I can reduce the number of columns in the underlying tables (and they're not in databases where I could create a view with reduced columns). But useful insight nevertheless!

[mahoneypat]

You can do some serious ETL with M.  I didn't realize you were doing many merges.  If you suspect it is repeating steps with for each row (seeing the M would have highlighted that potentially), you can incorporate Table.Buffer() with your query to greatly speed things up.

Pat