Power Query doesn't use 100% of the processor

[Syndicate_Admin]

Hello,

In this example I'm using Power Query in Excel, but I gess it whould be the same on PowerBI.

I have currently a very complex query taking an average of 30 minuts to execute, while my processor (Intel I3 10th gen) is at 20% and my memory at 600Mo (8Go in total, 2Go still available).

I am wondering if there is a way to tell Power Query to use all the capacity it needs? Currently it's running really slowly, while I have a lot of unused capacity on my computer.

I don't have any recursive tasks in my query, all rows could theorically be processed at the same time (that's why I don't think it's a logical bottleneck).

The screenshot of my task manager (columns are: process name, processor, memory, hard disk, network):

Thank you for your help.

Alexandre

[Syndicate_Admin]

Hi @_AlexandreRM_ ,

Really hard to give a definitive answer here, but a few things to think about:

- Some of it depends on whether the work you're asking Excel/Power Query to do is serial or parallelable i.e. whether the work has to be done one part after another, or whether it can be threaded across different processing cores. Please don't ask me how to find out if this is the case, I haven't got a clue. It would probably be a question for someone like Ehren.

- If you're using a 32-bit version of Excel, then there's a hard RAM cap of 2GB per Windows process instance which, I believe, would then be subdivided across your evaluation containers that handle your query transformations. In this instance, you could consider upgrading to Excel 64-bit to avoid the cap.

- If you would consider using Power Query within Power BI Desktop, then you could get both the RAM cap gains by using the 64-bit version, as well as super-charge the RAM usage on the evaluation containers by updating the app MaxEvaluationWorkingSetInMB registry entry. More details on that from Chris Webb here:

https://blog.crossjoin.co.uk/2021/06/06/speed-up-power-query-in-power-bi-desktop-by-allocating-more-... 

Pete

[Syndicate_Admin]

Hi @BA_Pete , thank you very much for your tips.

- In my opinion all calculations could be paralell. @Ehren , I mention you, if you have a clue, here is my query code (function f is a custom function taking a very long time to complete):

let
    base = Table.Buffer(#"RRP1 + RRP4"),
    baseMaterials = Table.Distinct(base, {"Numéro de produit", "Désignation du produit"}),
    baseMaterialsJoinBase = Table.NestedJoin(baseMaterials, {"Numéro de produit"}, base, "Numéro de produit", "join", JoinKind.LeftOuter),

    bufferJoinTable = Table.TransformColumns(baseMaterialsJoinBase, {"join", Table.Buffer}),

    addMin = Table.AddColumn(bufferJoinTable, "Qty", each f([join], 0, 9999999999, 0))

in
    addMin

- I just checked in my account data, I am already on a 64bits version,

- This is incredibly powerful, thank you for sharing!!! I defined the parameter in my PowerBI desktop, but I didn't found it in Excel Power Query, it seems Microsoft still didn't pushed this change, sadly.

Alexandre

[Syndicate_Admin]

What is the CPU/memory utilization of the Microsoft.Mashup.Container*.exe processes? They're the ones doing the bulk of the PQ work.

Also, it's possible the processing is slow because of data source access, which wouldn't show up in CPU/memory.

If you remove the addMin step, is everything faster? If so, it would be important to share what the f function does.

[Syndicate_Admin]

Hello Ehren,

The CPU/memory utilization of all Excel sub-processes is as follows:

(columns: process name, CPU, memory, hard disk, network)

Note that the CPU utilization is far higher than on my previous screenshot, even if I didn't changed anything. Still, PQ has a good romm for improvement.

The data sources are 2 Excel files on the Sharepoint, previously mergued in the #"RRP1 + RRP4" table.

Here is the code of function f, which is the reason of the query slowness (a recursive function applying to the [join] table). Its goal is to calculate the minimum value of forecasted stock, using all stocks entries and exits:

    f = (codeTable as table, previousQt as number, minQt as number, i as number) => 
    let
        currentQt = previousQt + codeTable{i}[#"Qté entrées/besoins"],
        currentMinQt = if currentQt < minQt then currentQt else minQt,

        //former method, the new one with error handling seems more efficient:
        //result = if i = iMax then minQt else @f(codeTable, currentQt, currentMinQt, i + 1, iMax)
        result = try @f(codeTable, currentQt, currentMinQt, i + 1) otherwise currentMinQt
    in
        result,

If you have an idea to improve this function, I would be stronly interested!

Alexandre

[Syndicate_Admin]

I'm not entirely sure what the purpose of the minQt calculation is, but other than that the recursion seems unnecessary. Buffering each and every table in the join column independently also seems like it could be detrimental perf-wise.

After performing your join, have you tried just summing the "Stock movement qty" column in the nested tables?

[Syndicate_Admin]

Hello @Ehren , I can't use the aggregate option.

Here is an example of how works the function. The final result of the function is in red :

Date	Stock movement qty	Current qty	Min qty
01/01/2023	+30	30	30
01/02/2023	+10	40	30
01/03/2023	-15	25	25
01/04/2023	-5	20	20
01/05/2023	+40	60	20

[Syndicate_Admin]

There are a few ways to do this without recursion, but some of them involve repeatedly summing all the previous rows, which can be inefficient.

Here's an approach that only scans the data once.

let
    Source = ...,
    #"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    RunningTotal = List.Generate(
    () => [Index = 0, Total = #"Added Index"{0}[Stock movement qty], Min = Total],
    each [Index] < Table.RowCount(#"Added Index"),
    (previous) =>
        let
            newIndex = previous[Index] + 1
        in
            [
                Index = newIndex,
                Total = previous[Total] + #"Added Index"{newIndex}[Stock movement qty],
                Min = List.Min({previous[Min], Total})
            ]),
    Custom1 = Table.AddColumn(#"Added Index", "Current qty", each RunningTotal{[Index]}[Total], type number),
    Custom2 = Table.AddColumn(Custom1, "Min qty", each RunningTotal{[Index]}[Min], type number)
in
    Custom2

That said, these kinds of cumulative calculations are probably better done in DAX, after loading the data to the (Power Pivot) Data Model.

[Syndicate_Admin]

Hello @Ehren , sorry for the very late reply, I was in vacation.

I think the List.Generate is a good alternative, I never tried to use it with records to store multiple values at each iteration!

After tests, it seems that both solutions (recursivity and list generation) have almost the same (low) performance. I think it's just the fact to iterate manually over each row of a table which is time-consuming. For those who are interested in, here is the formula using List.Generate:

    f = (codeTable as table) => 
        let
            iMax = Table.RowCount(codeTable),
            qtList = List.Buffer(codeTable[#"Qté entrées/besoins"]),

            cumulativeStockList = List.Generate(
                () => 
                    [
                        i = 1, 
                        qt = qtList{0}, 
                        minQt = qtList{0}
                    ], 
                each [i] <= iMax, 
                each 
                    [
                        i = [i] + 1,
                        qt = [qt] + qtList{[i]},
                        minQt = List.Min({[qt] + qtList{[i]}, [minQt]})
                    ])
        in
            List.Last(cumulativeStockList)[minQt],

So, except if Microsoft add a way to access more efficiently rows content for custom formulas, I don't think this problem will be solved!

I will also take a look at DAX, but its interface in Excel isn't... user-friendly, let's say.

[Syndicate_Admin]

Just curious: do you see a perf difference if you omit the call to List.Buffer?