Power Query Refresh Speed is very slow

[AshaZeng]

Below applied step in my power query is normal.

However, after I inserted below two steps before above step, the refresh speed becomes very slow

Not sure what happens, anyone knows what I should do to speed up the refresh?

By the way, I only 40 rows in Power Query.

Below is my whole M code, hope it could help to find the main issue~

[Anonymous]

I would use List.Buffer(ListName) to buffer the list. That will speed things up.

--Nate

[m_dekorte]

Hi @AshaZeng 

Store the table you are referencing, for each row in the table, into a separate variable; Lookup

Test if buffering that Lookup table helps to speed things up, as illustrated below.

Lookup = Table.Buffer(Table.Distinct(Table.SelectColumns(#"Reordered Columns2",{"CBL Number","FCR Number"}))),
YourStep = Table.AddColumn(#"Reordered Columns2", "FCR Quantity", each List.Count(Table.SelectRows( Lookup,(x)=>x[CBL Number]=[CBL Number])[FCR Number]))

Alternatively, consider a Merge that is often more performant than selecting rows since you have a shared key, the CBL Number column in both tables. Then count rows in the table that's returned...

I hope this is helpful.

[AshaZeng]

Thanks so much for your reply, but it does not work, above is my whole M Code, can you find the key issue which resulted in the slowness performance?

[m_dekorte]

Hi @AshaZeng,

Sure, I'd be happy to help you improve that query. Here are a few suggestions to get started:

- Remove redundant steps (such as reorders and renames)
- Consolidate value replacement steps
- Combine new field creation steps

If you need further assistance, please share a link to an excel file with representative dummy data, including the current state of your query. You can upload it to OneDrive or Google Drive, ensure it is accessible for anyone to download.

I hope this is helpful