question

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 asked ·

PQ - iterate over transactons and use aggregrate sum as target

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

power-query-desktoppower-query-m
· 7
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @JanPedersen-0386
1/ How is the initial/source table setup? Like the blue one? Like the grey one? Something different?

65142-demo.png

2/ What should happen/is expected if i.e. there's a Sales order in Week1 then another one in Week2...?
3/ Re. Condition 2, what should happen/is expected if the Sales order takes place in Week 4?

(not sure I'm up to the task but will give it a try - this won't be before tomorrow though)


0 Votes 0 ·
demo.png (20.0 KiB)

Thanks for your reply to my challenge

my feedback to your questions

  1. Current data is like the blue table, but I can pivot the data if it's easier to handle.

Below you will find the logic.

  1. The accumulated sum of current forecast qty. is the target, EXCEPT if the accumulated sum of Sales orders is greater than the accumulated sum of current forecast. (See case 3)

  2. Sales orders must consume a forecast in the same week and the previous weeks. (Case 4)

  3. If Sales order qty. is less than Forecast qty. then move the remaining forecast to the next week or to the following weeks. (case 2)

.

65401-image.png








0 Votes 0 ·
image.png (35.4 KiB)
Lz-3068 avatar image Lz-3068 JanPedersen-0386 ·

Hi @JanPedersen-0386. I've been thinking about your challenge for the last hours and I'm stuck with it. Sorry :(

0 Votes 0 ·

Hi Lz-3068

That's understandable.

I have discussed with my team, and they ask if it is possible to come up with a solution on more simple logic like the below.

67358-image.png


0 Votes 0 ·
image.png (48.9 KiB)
Show more comments
Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

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


· 2 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Now I only have to optimize performance as the custom function have to iterate more than 100K rows
Any proposals ??

0 Votes 0 ·

Can the function run inside nested tables ??

69918-2021-02-19-12-54-16.jpg


0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @JanPedersen-0386

Inside nested tables is no problem (sample available here) and I thought this was going to be your approach given your initial request

Regarding optimization with a 100k table not sure what to suggest w/o having the complete picture (Source table # columns, what your whole query should do - might not only be what we discussed - ...) + I'm not a PQ guru :(

As a general rule you should get rid of the columns and rows you don't need as early as possible. In other words, always "use" the smallest possible object (table, list...). If you look at the above sample I get rid of the FakeWeek column I created earlier, before calling the function inside each nested table

With your 100k table buffering might not be an option - see Curt Hagenlocher's explanation below the chart in this article - but who knows ???
I've also seen a number of posts where Ehren from the dev. team has recommended using a 64-bit version of Excel - this doesn't change anything with regard to the 256K RAM limit discussed by Curt though

Hope this helps a bit


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@JanPedersen-0386

I guess you saw it already but just in case... I focused so much on the BuildCorrection function that I overlooked the formula for the [Adjusted FC] that should simply say:

     AdjustedFC = Table.AddColumn(<PreviousStepName>, "Adjusted FC", each
         [Current FC]+[Correction], Int64.Type
     )
1 Vote 1 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

@JanPedersen-0386. According to your Case #3 you expect some nested tables where the Incoming Sales Order will be >= to the SUM([Current FC]), in which case the correction will be -[Current FC] for every row. The below revised version of the function avoids the iteration in this case:

 (SourceTable as table) as table =>
 let
     Source = SourceTable,
     Forecast = Table.Column(Source, "Current FC"),
     Sales = Table.First(Source)[Sales Order],
     Correction = List.Generate(
     ()=>
         [
             i = 0,
             forecast = Forecast{i},
             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]
     ),
     ToColumns = Table.ToColumns(Source) & {Correction},
     ToTable = Table.FromColumns(ToColumns, Table.ColumnNames(Source) & {"Correction"}),
     TypedCorrection = Table.TransformColumnTypes(ToTable, {
                 {"Correction", Int64.Type}}),
    
     NoIteration = Table.AddColumn(Source, "Correction", each -[Current FC], Int64.Type)
 in
     if Sales >= List.Sum(Forecast) then NoIteration else TypedCorrection


·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 answered ·

Thank you, but then I have to replace the null values with 0 (Zero) as the type logical cannot convert a null value.

correction = if Sales > 0 then -forecast else 0,
rest = if Sales = 0 then 0 else forecast-Sales

Below will exclude all the Forecast rows for the items where Sales = 0 in the first week.
I cannot do that as the number of rows in the Source(input) file must be the same number of rows as the in the Adjusted Forecast (Output)

NoIteration = Table.AddColumn(Source, "Correction", each -[Current FC], Int64.Type)
in
if Sales >= List.Sum(Forecast) then NoIteration else TypedCorrection

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 answered ·

Maybe your solution is okay, but then I must filter out the FC row where Sales qty = 0 in the first week and then combine Source and Adjusted FC at the end.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 answered ·

Hi @Lz-3068
I get some error with the below custom function:

Preview.Error: The current preview value is too complex to display.
Details:

How can I debug the custom function??



 (SourceTable as table) as number =>
 let
     Source = Table.Buffer(SourceTable),
     Forecast = Table.Column(Source, "FC"),
     Sales = Table.Buffer(Table.First(Source)[SO]),
     Correction = List.Generate(
         ()=>
          [
              i = 0,
              forecast = Forecast{i},
              correction =   if Sales = null or Sales = 0 then 0
                             else -forecast ,
              rest =         if Sales = null or Sales = 0 then 0 
                             else List.Sum({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 List.Sum({[rest],forecast})
                         else List.Max( {[rest],-forecast}, 0)
             ],
          each [correction]
      ),
      ToColumns = Table.ToColumns(Source) & {Correction},
      ToTable = Table.FromColumns(ToColumns, Table.ColumnNames(Source) & {"Correction"})
    
   in 
     Table.TransformColumnTypes(ToTable, {
                 {"Correction", Int64.Type}})
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @JanPedersen-0386

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




· 3 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Lz-3068
Thanks for the support. :-)
The issues has been resolved.

0 Votes 0 ·
Lz-3068 avatar image Lz-3068 JanPedersen-0386 ·

The issues has been resolved => Great :) You did the hard work. Thanks

0 Votes 0 ·

@Lz-3068 Final code. The if statements have been adjusted

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

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

@JanPedersen-0386

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.:

75077-demo.png

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://docs.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...


demo.png (15.5 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JanPedersen-0386 avatar image
0 Votes"
JanPedersen-0386 answered ·
     (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}})
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered ·

Hi @JanPedersen-0386

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



·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.