Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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~
I would use List.Buffer(ListName) to buffer the list. That will speed things up.
--Nate
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.
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?
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