Forum Discussion
Column with accumulated values
Hello all,
I am working on a task where I need to create a column that acumen the value in the last 2 months. For a specific reason, I cannot use a measure to acumen this number because I only need to accumulate those numbers in a specific context, so from my humble knowledge I intend to solve my issue this way.
Start of Month | SKU | Quantity | Sales Order Invoiced Qty | Sales Order Qty | ERROR | ERROR 2M |
01 October 2022 | Product 01 | 641 | 223 | 223 | 418 | 418 |
01 November 2022 | Product 01 | 1923 | 1277 | 1277 | 646 | 1064 |
01 December 2022 | Product 01 | 1313 | 700 | 700 | 613 | 1259 |
01 June 2022 | Product 013 | 400 | 400 | 400 | ||
01 August 2022 | Product 013 | 641 | 641 | 1041 | ||
01 July 2022 | Product 013 | 629 | 629 | 1270 |
The result I need to achieve is the last column "ERROR 2M".
Basically, I need this accumulation on the last 2 months (based on the Start of the Month) but considering the distinction of different products (Column SKU).
I know this can be easy for you guys, but for me, it has been I big challenge for days.
It seems you are defining "last two months" as relating solely to the row position. Otherwise, why is the last row "1270" and not "1029"? Or are the dates in "Start of Month" in the wrong order?
To obtain the output you show from the input you provide, try the code below:
let Source = Excel.CurrentWorkbook(){[Name="Table32"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Start of Month", type date}, {"SKU", type text}, {"Quantity", Int64.Type}, {"Sales Order Invoiced Qty", Int64.Type}, {"Sales Order Qty", Int64.Type}, {"ERROR", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, { {"Error 2m", (t)=> let #"2MRT" = List.Generate( ()=>[rt=t[ERROR]{0}, idx=0], each [idx] < Table.RowCount(t), each [rt=t[ERROR]{[idx]} + t[ERROR]{[idx]+1}, idx=[idx]+1], each [rt]), #"add 2MRT" = Table.FromColumns( Table.ToColumns(t) & {#"2MRT"}, Table.ColumnNames(#"Changed Type") & {"ERROR 2M"} ) in #"add 2MRT", type table [Start of Month=nullable date, SKU=nullable text, Quantity=nullable number, Sales Order Invoiced Qty=nullable number, Sales Order Qty=nullable number, ERROR=nullable number, ERROR 2M=nullable number]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"SKU"}), #"Expanded Error 2m" = Table.ExpandTableColumn(#"Removed Columns", "Error 2m", {"Start of Month", "SKU", "Quantity", "Sales Order Invoiced Qty", "Sales Order Qty", "ERROR", "ERROR 2M"}) in #"Expanded Error 2m"
6 Replies
- ronrsnfldSuper User
It seems you are defining "last two months" as relating solely to the row position. Otherwise, why is the last row "1270" and not "1029"? Or are the dates in "Start of Month" in the wrong order?
To obtain the output you show from the input you provide, try the code below:
let Source = Excel.CurrentWorkbook(){[Name="Table32"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Start of Month", type date}, {"SKU", type text}, {"Quantity", Int64.Type}, {"Sales Order Invoiced Qty", Int64.Type}, {"Sales Order Qty", Int64.Type}, {"ERROR", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"SKU"}, { {"Error 2m", (t)=> let #"2MRT" = List.Generate( ()=>[rt=t[ERROR]{0}, idx=0], each [idx] < Table.RowCount(t), each [rt=t[ERROR]{[idx]} + t[ERROR]{[idx]+1}, idx=[idx]+1], each [rt]), #"add 2MRT" = Table.FromColumns( Table.ToColumns(t) & {#"2MRT"}, Table.ColumnNames(#"Changed Type") & {"ERROR 2M"} ) in #"add 2MRT", type table [Start of Month=nullable date, SKU=nullable text, Quantity=nullable number, Sales Order Invoiced Qty=nullable number, Sales Order Qty=nullable number, ERROR=nullable number, ERROR 2M=nullable number]}}), #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"SKU"}), #"Expanded Error 2m" = Table.ExpandTableColumn(#"Removed Columns", "Error 2m", {"Start of Month", "SKU", "Quantity", "Sales Order Invoiced Qty", "Sales Order Qty", "ERROR", "ERROR 2M"}) in #"Expanded Error 2m"- AnonymousNot applicable
Before anything thanks so much for your help! It is much valuable for me!
Sorry! My bad! I typed the wrong month in the table. The last value 1270 is the sum of 641 + 629 (July and August) respectively. I put the table corrected below!:
Start of Month
SKU
Quantity
Sales Order Invoiced Qty
Sales Order Qty
ERROR
ERROR 2M
01 October 2022
Product 01
641
223
223
418
418
01 November 2022
Product 01
1923
1277
1277
646
1064
01 December 2022
Product 01
1313
700
700
613
1259
01 June 2022
Product 013
400
400
400
01 July 2022
Product 013
641
641
1041
01 August 2022
Product 013
629
629
1270
Do you think your code above still can solve my issue?
- ronrsnfldSuper User
Yes
- AnonymousNot applicable
Hi ronrsnfld , before anything else, thanks for all your support!
It may be a mistake on my end, but it is not working for me.I followed your instructions:
1 - Made the table as in the above picture,
2 - Got data from this new table (what was automatically named as "table"),
3 - In the Power Query editor - Pasted your Code (only changing the table reference name to "table"),
4 - I got the error in the screenshot below:
Sincerely,
Jorge Lacerda
- ronrsnfldSuper User
Did you include the "ERROR 2M" column in your original table?