Forum Discussion
Helper I% of difference from previous rows in a group for large dataset for Power query
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"
Helper IAnd if I need (assuming its available in the initial table) to retain 2 columns - "PO date" and "LP change date" for checking purposes in the final table, where should I add these in your code?
Super UserYou can just add those columns within the List.Generate function as I did for the Price on PO column (adjusting the subsequent steps to account for those extra columns):
let
Source = myTable,
//Group by Item
#"Grouped Rows" = Table.Group(Source, {"Item"}, {
{"Change", (t)=>
[a=List.Generate(
()=>[w=t{0}[Price on PO], ww=t{0}[PO Date], www=t{0}[LP Change Date], x=0, y=0, idx=0],
each [idx] < Table.RowCount(t),
each [idx=[idx]+1, w=t{idx}[Price on PO],
ww=t{idx}[PO Date],
www=t{idx}[LP Change Date],
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],[ww],[www],[x],[y]}),
b=List.Transform(a, each Record.FromList(_,{"Price on PO","PO Date","LP Change Date","Change from last PO","Cumulative Change"} ))
][b], type table[Item=Int64.Type, Price on PO=number, PO Date=date, LP Change Date=date,
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","PO Date","LP Change Date", "Change from last PO", "Cumulative Change"})
in
#"Expanded Change1"
If you have more than a few columns to add, it may be more performative to do a Join operation, but you'd have to test that.
- PolinaOr
Do I need it pre-sorted? And if so, before grouping?
- ronrsnfld
There is no need to sort by item, unless you decide to use GroupKind.Local for performance reasons.
How else would you want to sort the data? It is not apparent from your originally posted data set.
Table.Group is not guaranteed to always maintain the original row order, and this is especially true if your data is coming from an SQL Query.
If the data is originally properly sorted row by row by whatever criteria you choose, and especially if you want to maintain that order, the safest method would be to
- Add an Index column before grouping.
- Within the Table.Group function, sort each group by that index column as an initial step before doing the calculations.
- After re-expanding the data, again sort by the Index column.
If there is some column you haven't shown which could be used for sorting, you may be able to sort on that instead of adding an Index column.