Power Query - CPU usage very high (microsoft.mashup.Container.Loader.exe)

[Anonymous]

I am running a relatively simple power query on 3 CSV files. Every time I load I see 3 or 4 instances of Microsoft.Mashup.Container.Loader.exe in my task window. Usually there is a spike in CPU usage to 100% and the RAM usage is very high. CPU usage will drop IF the query is not actively refreshing. I have already disabled the "Allow data previews to download in the background" option. This option was turned off before opening the table. How do I fix this? Are there other settings that I need to change?

[smpa01]

@Anonymous 

try known hacks ,

Also use server-side language for db tables, i.e. don't use pq to transform sql tables after bringing into pq; write sql to query tables from sql -server before bringing into pq.

If you happen to use datmart, you can take advantge of AZURE SQL db as well, in case you happen to use a non-db large data source, that is killing the pc.

[BA_Pete]

Hi @Anonymous ,

Power Query is an absolute beast for CPU and RAM and it's not something you can really switch on/off via settings.

I know you don't want to hear it, but I solved this issue with an Intel i9 and 48GB RAM.

Pete

[BA_Pete]

Flippancy aside, if you can share your query code here we can have a look to see if there's any items that can be made more efficient. Just copy everything in Advanced Editor and paste it into a code window here ( </> button ). Make sure to obscure file/server paths using 'XXX' or similar, but leave the actual code structure intact please.

Pete

[Anonymous]

Pete,

Thanks for the response.  Unfortunately, I am stuck with my current setup for the time being.  One thing that I cannot figure out is what is causing the multiple instance of "microsoft.mashup.Container.Loader.exe".   Below is my code.

let
    Source = Table.NestedJoin(VT_Raw, {"VT", "DNM", "Quality", "Date"}, #"Com History", {"VT", "DNM Name", "Quality", "Date"}, "Com History", JoinKind.LeftOuter),
    
#"Expanded Com History" = Table.ExpandTableColumn(Source, "Com History", {"DP", "WDs"}, {"Com History.DP", "Com History.WDs"}),
    
#"Replace Null DP" = Table.ReplaceValue(#"Expanded Com History", each [Com History.DP], each if [VT_TYPE] = "MACHINE" and [Com History.DP] = null then 0 else [Com History.DP],Replacer.ReplaceValue,{"Com History.DP"}),
    
#"Replace Null WDs" = Table.ReplaceValue(#"Replace Null DP", each [Com History.WDs], each if [VT_TYPE] = "MACHINE" and [Com History.WDs] = null then 0 else [Com History.WDs],Replacer.ReplaceValue,{"Com History.WDs"}),
    
#"Grouped Rows" = Table.Group(#"Replace Null WDs", {"Date", "VT_TYPE"}, {{"Amount", each List.Sum([Amount]), type nullable number}, {"Central Delivery", each List.Sum([Central Delivery]), type nullable number}, {"Central Return", each List.Sum([Central Return]), type nullable number}, {"VT Delivery", each List.Sum([VT Delivery]), type nullable number}, {"VT Return", each List.Sum([VT Return]), type nullable number}, {"Store Order", each List.Sum([Store Order]), type nullable number}, {"Store Clearance", each List.Sum([Store Clearance]), type nullable number}, {"MACHINE Order", each List.Sum([MACHINE Order]), type nullable number}, {"MACHINE Return", each List.Sum([MACHINE Return]), type nullable number}, {"Com DP", each List.Sum([Com History.DP]), type number}, {"Com Wdls", each List.Sum([Com History.WDs]), type number}}),
    
#"Buffer" = Table.Buffer(#"Grouped Rows"),
    
#"Changed Type" = Table.TransformColumnTypes(#"Buffer",{{"Amount", Currency.Type}, {"Central Delivery", Currency.Type}, {"Central Return", Currency.Type}, {"VT Delivery", Currency.Type}, {"VT Return", Currency.Type}, {"Store Order", Currency.Type}, {"Store Clearance", Currency.Type}, {"MACHINE Order", Currency.Type}, {"MACHINE Return", Currency.Type}, {"Com DP", Currency.Type}, {"Com Withdrawls", Currency.Type}}),
    
#"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Date]), type text)
in
    
#"Inserted Month Name"

[BA_Pete · Accepted solution]

Hi @Anonymous ,

OK, I think there's a few things to look at.

1) The multiple instances of the Mashup Container is totally normal, and is not necessarily a fault in the code. It's just how PQ works behind the scenes.

2) Merges are very expensive in PQ as they are essentially 'whole table' operations. You can speed this up by either:

    2a) Don't do the merge in PQ at all - send both tables to the data model and relate them instead. This may not work for you in this scenario due to the grouping requirement later in the query, but very sound advice for any future queries regardless.

    2b) Create the combined columns that you want to merge on in each table before the merge to avoid PQ trying to match four columns in each table i.e. create a column in VT_RAW that is [VT], [DNM], [Quality], and [Date] all merged into one column with a '-' delimiter. Do the same in #"Com History" with the four relevant columns there so you can just merge on a single column from each.

3) You can remove your 'Replace Null DP' and 'Replace Null WDs' steps. As your later grouping aggregates are all List.Sum, these replace steps are irrelevant.

4) Remove your 'Buffer' step. It's not helping you in any way with your query and just stuffing the entire table into memory for no advantage.

Make these changes and let me know how it goes.

Pete