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]

As far as I can tell from your M, you can get what you want, along with a huge performance improvement, by grouping, which you seem to basically be implementing yourself with your joins, custom function, etc.

Example, given the below test data:

Numéro de produit	Désignation du produit	Stock movement qty
1	A	12
1	A	32
1	B	543
2	A	123
2	A	421
2	B	51
2	B	36
3	A	8
3	A	21
3	A	43

Through UI:

M:

let
    Source = base,
    GroupRows = Table.Group(
        Source, 
        {"Numéro de produit", "Désignation du produit"}, 
        {
            {"Min qty", each List.Min([Stock movement qty]), type nullable number}
        }
    )
in
    GroupRows

FYI in case you are trying to do something a little more nuanced than get the minimum by the given grouping, you actually have the whole original table (with only the rows applicable to the grouping for each distict values combo) to manipulate. You'll see if you select All Rows through the UI or add something like to the third argument list of lists in M: { "Grouped Original Table", each _, Value.Type( Source ) }

[Syndicate_Admin]

No, it's exactly the same.

But I'm already using Table.Buffer on codeTable before calling the function, and this has been a huge performance improvement (around 10 times faster with Table.Buffer(codeTable) than without).

[Syndicate_Admin]

Hello @MarkLaf , thank you for looking for a solution!

Unfortunately, your code doesn't meet my need.

Here is an example of what I want to achieve. The example table down here could be one of the rows from your column "Grouped Original Table".

The result I want to get is in red, and the result of your code is in blue.

Columns in italic aren't in the native table, and are only here to illustrate the logic of the red result.

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

To achieve this, I need to execute a running total to get the minimum cumulated stock over an ascending list of dates. Your code would have been perfect if I had the "Current qty" column in my native table, which is sadly not the case.

[Syndicate_Admin]

I think I understand your requirements now. I'm pretty sure you can achieve some performnce improvements by leveraging Grouop, still, but there are a couple extra steps. To test performance better, I switched to randomly generated data to test 10k and 100k rows in the structure you specified above for your testing.

The below works pretty well, just takes 1-2 seconds to load. Approach is to merge grouped rows (when grouped on a column, that column becomes primary key, which improve join performance), filter merged grouped rows as needed, sum values for running total, then do a second group to get the min:

let
    Source = PerfTest_10k,
    MaterialGroups = Table.Group(
        Source, 
        {"Material"}, 
        {{
            "Current qty", 
            each _, 
            type table [Material=nullable text, Date=nullable date, Stock movement qty=nullable number]
        }}
    ),
    MergeGroups = Table.NestedJoin( 
        Source, 
        "Material", 
        MaterialGroups, 
        "Material", 
        "Groups", 
        JoinKind.Inner 
    ),
    ExpandGroups = Table.ExpandTableColumn(MergeGroups, "Groups", {"Current qty"}, {"Current qty"}),
    GetCurQtyRows = 
    Table.TransformRows(  
        ExpandGroups, 
        (row)=> Record.TransformFields( 
            row, 
            {
                "Current qty", 
                each let 
                    _t = Table.SelectRows( row[Current qty], each [Date] <= row[Date] ) 
                in 
                    List.Sum( Table.Column(_t, "Stock movement qty") ) 
            } 
        ) 
    ),
    GetCurQty = Table.FromRecords( 
        GetCurQtyRows, 
        type table [Material=text, Date=date, Stock movement qty=number, Current qty=number] 
    ),
    GetMinQty = Table.Group(
        GetCurQty, 
        {"Material"}, 
        {
            { "Min qty", each List.Min([Current qty]), type number }
        }
    )
in
    GetMinQty

Output:

The above doesn't work so great when you up the rows to 100k, though. For that I think you have to turn to DAX. This takes about 2 sec to work over 100k rows (probably there are ways to improve performance further on this). Note that [Running Total] and [Min Running Total] are measures:

Running Total = 
VAR _thisDt = MAX( PerfTest_100k[Date] )
VAR _matGroup = CALCULATETABLE( PerfTest_100k, REMOVEFILTERS( PerfTest_100k ), VALUES( PerfTest_100k[Material] ) )
VAR _curPrevRows = FILTER( _matGroup, PerfTest_100k[Date] <= _thisDt )
RETURN
CALCULATE( SUM( PerfTest_100k[Stock movement qty] ), _curPrevRows )


Min Running Total = 
MINX( SUMMARIZE( PerfTest_100k, PerfTest_100k[Material], PerfTest_100k[Date] ), [Running Total] )

Output (note it's all randomly generated which is why these numbers don't match output above):

In case interested and to show my work, here is the M for the test data. Below generates 10k rows for

PerfTest_10k. It's ame code, but 10000 replaced with 100000 in line 4, for PerfTest_100k:

let
    Source = List.Generate( 
        ()=>0,
        each _ < 10000, 
        each _ + 1, 
        each [
            Material = Character.FromNumber( 
                List.Min( { 
                    Int32.From( Number.RandomBetween(65, 91) ), // A-Z
                    90 
                } ) 
            ), 
            Date = Date.AddDays( 
                #date(2022,1,1), 
                List.Min({ 
                    Int32.From( Number.RandomBetween( 0, 365 ) ), // 1/1/2022-12/31/2022
                    364 } ) 
                ),
            Stock movement qty = Int64.From( Number.RandomBetween( -100, 100 ) ) // -100 - +100
        ]
    ),
    Ouput = Table.FromRecords( Source, type table [Material=text,Date=date,Stock movement qty=number] )
in
    Ouput