Forum Discussion
% of difference from previous rows in a group for large dataset for Power query
- 5 months ago
You can add a shifted column within the Item Group to do the math:
let Source = myTable, //Group by Item #"Grouped Rows" = Table.Group(Source, {"Item"}, { {"Change", (t)=> //within each Item, add a shifted column to do the math [a=Table.FromColumns( Table.ToColumns(t) & {{null} & List.RemoveLastN(t[Price on PO],1)}), b=Table.AddColumn(a,"Change from last PO", each ([Column2]-[Column3])/[Column3]??0, Percentage.Type), c=Table.RemoveColumns(b,{"Column3"}) ][c], type table[Column1=number, Column2=number, Change from last PO=Percentage.Type]} }), //clean up and expand #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Item"}), #"Expanded Change" = Table.ExpandTableColumn(#"Removed Columns", "Change", {"Column1", "Column2", "Change from last PO"}, {"Item", "Price on PO", "Change from last PO"}) in #"Expanded Change"By the way, for your cumulative change it seems you are computing a running total of the row by row changes. Are you sure that is what you want to do? I would think it more appropriate to calculate the cumulative change as the percentage change between the current row and the first row in each item group.
If that is what you really want, it may be easier to use List.Generate to create both of those columns as shown below:
let Source = myTable, //Group by Item #"Grouped Rows" = Table.Group(Source, {"Item"}, { {"Change", (t)=> [a=List.Generate( ()=>[w=t{0}[Price on PO], x=0, y=0, idx=0], each [idx] < Table.RowCount(t), each [idx=[idx]+1, w=t{idx}[Price on PO], x=(t{idx}[Price on PO]-t{idx-1}[Price on PO]) / t{idx-1}[Price on PO], y=(t{idx}[Price on PO] - t{0}[Price on PO]) / t{0}[Price on PO]], each {[w],[x],[y]}), b=List.Transform(a, each Record.FromList(_,{"Price on PO","Change from last PO","Cumulative Change"} )) ][b], type table[Item=Int64.Type, Price on PO=number, Change from last PO=Percentage.Type, Cumulative Change=Percentage.Type] }}), #"Expanded Change" = Table.ExpandListColumn(#"Grouped Rows", "Change"), #"Expanded Change1" = Table.ExpandRecordColumn(#"Expanded Change", "Change", {"Price on PO", "Change from last PO", "Cumulative Change"}) in #"Expanded Change1"
You can add a shifted column within the Item Group to do the math:
let
Source = myTable,
//Group by Item
#"Grouped Rows" = Table.Group(Source, {"Item"}, {
{"Change", (t)=>
//within each Item, add a shifted column to do the math
[a=Table.FromColumns(
Table.ToColumns(t) &
{{null} & List.RemoveLastN(t[Price on PO],1)}),
b=Table.AddColumn(a,"Change from last PO", each ([Column2]-[Column3])/[Column3]??0, Percentage.Type),
c=Table.RemoveColumns(b,{"Column3"})
][c],
type table[Column1=number, Column2=number, Change from last PO=Percentage.Type]}
}),
//clean up and expand
#"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Item"}),
#"Expanded Change" = Table.ExpandTableColumn(#"Removed Columns", "Change", {"Column1", "Column2", "Change from last PO"}, {"Item", "Price on PO", "Change from last PO"})
in
#"Expanded Change"
By the way, for your cumulative change it seems you are computing a running total of the row by row changes. Are you sure that is what you want to do? I would think it more appropriate to calculate the cumulative change as the percentage change between the current row and the first row in each item group.
If that is what you really want, it may be easier to use List.Generate to create both of those columns as shown below:
let
Source = myTable,
//Group by Item
#"Grouped Rows" = Table.Group(Source, {"Item"}, {
{"Change", (t)=>
[a=List.Generate(
()=>[w=t{0}[Price on PO], x=0, y=0, idx=0],
each [idx] < Table.RowCount(t),
each [idx=[idx]+1, w=t{idx}[Price on PO],
x=(t{idx}[Price on PO]-t{idx-1}[Price on PO]) / t{idx-1}[Price on PO],
y=(t{idx}[Price on PO] - t{0}[Price on PO]) / t{0}[Price on PO]],
each {[w],[x],[y]}),
b=List.Transform(a, each Record.FromList(_,{"Price on PO","Change from last PO","Cumulative Change"} ))
][b], type table[Item=Int64.Type, Price on PO=number, Change from last PO=Percentage.Type, Cumulative Change=Percentage.Type]
}}),
#"Expanded Change" = Table.ExpandListColumn(#"Grouped Rows", "Change"),
#"Expanded Change1" = Table.ExpandRecordColumn(#"Expanded Change", "Change", {"Price on PO", "Change from last PO", "Cumulative Change"})
in
#"Expanded Change1"
- PolinaOr5 months ago
Helper I
"By the way, for your cumulative change it seems you are computing a running total of the row by row changes. Are you sure that is what you want to do? I would think it more appropriate to calculate the cumulative change as the percentage change between the current row and the first row in each item group"
This is cool! So essentially the second code is shorter than the first one, even though we have more columns added?- ronrsnfld5 months ago
Super User
My answer demonstrates two different methods of accomplishing a similar task. The first responds to your exact question. But I think the second method may be better for your use case since both columns can be calculated in the same step.
And I would not focus so much on the length of the code but rather on performance. There are diagnostic tools in Power BI desktop that you can use to time queries as well as steps within the query.
In any event, if the code I provided meets your needs, I would appreciate it if you could Accept it as Solution.
- PolinaOr5 months ago
Helper I
I used second one, and it works great!