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
I think I understand your requirements now. I'm pretty sure you can achieve some performnce improvements by leveraging Group, 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 same 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
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 ) }
I think I understand your requirements now. I'm pretty sure you can achieve some performnce improvements by leveraging Group, 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 same 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
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}[#"Stock movement qty"],
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
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?
Helpful resources
Power BI T-Shirt Design Challenge 2023
Vote for your favorite t-shirt design now through March 28.
March 2023 Events
Find out more about the online and in person events happening in March!
