How to optimize Power Query

[JustDavid]

In my PQ, I have lots of tables that I'm joining and referencing. Now I've reached at a point that it's unbearable.

Everytime I'm checking into a step, or adding new steps, at the bottom right of the screen, I'd see that it's looping through all the source files.

And it's looping the source file not once but multiple times. Am wondering if it's because of the number of times it's merging that it causes it to loop through the source file multiple times.

I've googled, and have tried to turn off Global and Local background refresh. I've added Table.Buffer and/or List.Buffer and seems like nothing helps.

Any suggestions?

[PwerQueryKees · Accepted solution]

Hey David,

Without more infoamrtion about the size and structure of your data and the queries that are causing trouble, we can only give you general advice.

• Filter early
• remove coolumns you don't need early
• If you have a connector supporting query folding, try to avoid anyuthing that breaks the folding.
• If you are merging a big table but only need a few columns, I may pay off to do a table.buffer of the few columns you need befor merging.
• If possible, DON'T merge (or join). 
  Load you data in the semantic model in stead. The columnar database is ordfers of magnnitude faster with merges. And as a bonus, the structure of you data gets documented in the process.
  This has its challenges and limitations though! But it's worth the investment to learn if you work with large volumes and join a lot of reference tables to one master file. More complex situations might work as well, but are more challenging.
• Do an internet search on optimization of power query, there has been written a lot on this subject.

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[v-ssriganesh]

Hi @JustDavid,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @JustDavid,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @JustDavid 
Thank you for posting your query in the Microsoft Fabric Community Forum. Thank you @ZhangKun and @PwerQueryKees for sharing your valuable insights.

Could you please confirm if your query has been resolved by the solution provided by @ZhangKun and @PwerQueryKees? If it has, kindly mark the helpful response and accept it as the solution. This will assist other community members in resolving similar issues more efficiently.

Thank you

[PwerQueryKees · Accepted solution]

Hey David,

Without more infoamrtion about the size and structure of your data and the queries that are causing trouble, we can only give you general advice.

• Filter early
• remove coolumns you don't need early
• If you have a connector supporting query folding, try to avoid anyuthing that breaks the folding.
• If you are merging a big table but only need a few columns, I may pay off to do a table.buffer of the few columns you need befor merging.
• If possible, DON'T merge (or join). 
  Load you data in the semantic model in stead. The columnar database is ordfers of magnnitude faster with merges. And as a bonus, the structure of you data gets documented in the process.
  This has its challenges and limitations though! But it's worth the investment to learn if you work with large volumes and join a lot of reference tables to one master file. More complex situations might work as well, but are more challenging.
• Do an internet search on optimization of power query, there has been written a lot on this subject.

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[ZhangKun]

Use partial data, not full data, unless your code is well tested.

At the same time you also need to note that the test data should contain all possible data.

[JustDavid]

Initially I did use partial data, but when I try to implement to the rest of the data that I had, that's when I realized that it's slowing things down dramatically.

Note too that, when I'm using partial data, I did see at the bottom right of the screen,  looping through all the source files whenever I'm looking at steps or inserting new steps.

As of speaking right now, it's taking 10 minutes and still have the refresh going on

[ZhangKun · Accepted solution]

common operations that slow down the program are sorting, indexing data from other tables(exclued Merge Queries), merging queries and summarizing(the data are loaded multiple times).