PQ - iterate over transactons and use aggregrate sum as target

Question

Hi guys

I have some forecast transactions that I need to adjust if there is coming a Sales Order in the same week as forecast, based on the Sales order qty. and the aggregate sum

There is general 4 conditions for the iteration

Condition 1

Sales order in WK02 of 10 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 40 (Forecast)
W02 : 10 (Sales order)
W03 : 50
W04 : 50
Aggregate sum 200
——————————————
Condition 2

Sales order in WK02 of 60 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 0 (Forecast)
W02 : 60 (Sales order)
W03 : 40 (Forecast)
W04 : 50
Aggregate sum 200

——————————————
Condition 3

Sales order in WK02 of 110 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 0 (Forecast)
W02 : 110 (Sales order)
W03 : 0 (Forecast)
W04 : 40
Aggregate sum 200

——————————————
Condition 4

Sales order in WK02 of 50 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 0 (Forecast)
W02 : 50 (Sales order)
W03 : 50
W04 : 50
Aggregate sum 200

Answer 1

Hi @Djpejsen
Could you play with this workbook and let me know? With the hope this is good this time as I run out of aspirin :-)

Answer 2

@Djpejsen

Something I should have mentioned earlier. If your nested tables results of a Grouping (Table.Group) operation and the column you GroupBy (the key) is already ordered before grouping, i.e.:

you can speed up the grouping operation using the 4th optional parameter (groupKind) set to GroupKind.Local, i.e.:

= Table.Group(InputTable, {"Acc ID"}, {{myAggregations}}, GroupKind.Local)

The doc. of the 4th param. "disappeared" when they moved the documention to https://learn.microsoft.com/ a few years ago. It used to say:

• A local group is formed from a consecutive sequence of rows from an input table with the same key value
• A global group is formed from all rows in an input table with the same key value

Also check ImkeF's Speed/Performance aspects article, there's probably a few things for you
Hope this helps...

Answer 3

Hi @Djpejsen

To debug a function no choice:

• Take one of your nested table as a source (myNestedTable below)
• Adjust your code as below
• In the PQ Editor identify the step that raises the error

Couple of comments:

• Not a good idea to buffer inside a function. Each time it's called what you buffer is added to what's already in memory (ram) until...the query evaluation completes
• You already have an issue with step Sales as it returns a scalar value, not a Table //(SourceTable as table) as number =>
  let
  //Source = Table.Buffer(SourceTable),
  Source = myNestedTable,
  Forecast = Table.Column(Source, "FC"),
  ...
  ...
  ToTable = Table.FromColumns(ToColumns, Table.ColumnNames(Source) & {"Correction"}),
  Typed = Table.TransformColumnTypes(ToTable, {
  {"Correction", Int64.Type}})
  in
  Typed

Answer 4

Hi @Djpejsen

Is it possible to have both [Correction] and [Rest] in the output? ToColumns = Table.ToColumns(Source) & {Correction} & {Rest}

(a never ending story :) I don't know where you are (cf. The if statements have been adjusted + our previous exchanges) so I kept the same example you Accepted as answer and revised the corresponding BuildCorrection function - Workbook avail. here

(SourceTable as table) as table =>  
let  
    Source = SourceTable,  
    Forecast = Table.Column(Source, "Current FC"),  
    CorrectionRest = List.Generate(  
    ()=>  
        [  
            i = 0,  
            forecast = Forecast{i},  
            sales = Table.First(Source)[Sales Order],  
            Correction = if sales > 0 then -forecast else 0,  
            Rest = if sales = 0 then 0  
            else forecast-sales  
        ],  
        each [i] < List.Count(Forecast),  
        each  
            [  
                i = [i] + 1,  
                forecast = Forecast{i},  
                Correction = if [Rest] = 0 then 0  
                    else if [Rest] > 0 then List.Min({forecast, [Rest]})  
                    else List.Max({-forecast, [Rest]}),  
                Rest = if [Rest] < 0 then  
                    if [Rest] > -forecast then 0 else [Rest]+forecast  
                    else List.Max({[Rest]-forecast, 0})  
            ],  
        each [ [Correction], [Rest] ]  
    ),  
    ToTable = Table.FromColumns(  
        Table.ToColumns(SourceTable) &  
        Table.ToColumns(Table.FromRecords(CorrectionRest, type table [Correction=Int64.Type, Rest=Int64.Type])),  
        Table.ColumnNames(SourceTable) & {"Correction", "Rest"}  
    )  
in  
    ToTable  

Answer 5

    (SourceTable as table) as table =>
    let
        Source = SourceTable,
        Forecast = Table.Column(Source, "FC Qty"),
        Correction = List.Generate(
        ()=>
            [
                i = 0,
                forecast = Forecast{i},           
                sales = Table.First(Source)[SO Qty],
                correction = if forecast  = null or forecast = 0 then 0 else -forecast,
                rest =       if forecast  = null or forecast = 0 then 0 else List.Sum({sales,-forecast})
            ],
            each [i] < List.Count(Forecast),
            each
                [
                    i = [i] + 1,
                    forecast = Forecast{i},
                    correction = if [rest] = 0 then 0
                            else if [rest] > 0 then if [rest] < forecast  then -[rest] else  -forecast
                            else  -[rest]
    ,
                    rest =       if [rest] <= 0 then 0
                            else if [rest] >  0 then if [rest] < forecast  then 0 else  [rest]-forecast
                            else  -[rest]
                ],
            each [correction]
        ),
        ToColumns = Table.ToColumns(Source) & {Correction},
        ToTable = Table.FromColumns(ToColumns, Table.ColumnNames(Source) & {"Correction"})
    in
        Table.TransformColumnTypes(ToTable, {{"Correction", Int64.Type}})