Forum Discussion
Power Query - CPU usage very high (microsoft.mashup.Container.Loader.exe)
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
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
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"
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