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