Good day, everyone. As posts, related to this question, are old or require essentially duplicating the dataset and re-merging it, which i think will greatly impact the productivity due to the size of the dataset, I am searching for a modern PQ solution to it. I need % of "Price on PO" difference between current and previous rows within one item code group.ItemPrice on POChange from last POCumulative change12.550.0%0.0%12.51-1.56%-1.56%12.645.17%3.61%12.19-17.04%-13.43%210%0%21.550%50%23100%150% I will then create Cumulative change column, for which I think I have found solution, and add a condition to select specific columns for automatic sending out through POWER BI Service dataflow and pipeline.

Forum Discussion

PolinaOr

Helper I

5 months ago

Solved

% of difference from previous rows in a group for large dataset for Power query

Good day, everyone.

As posts, related to this question, are old or require essentially duplicating the dataset and re-merging it, which i think will greatly impact the productivity due to the size of the dataset, I am searching for a modern PQ solution to it.

I need % of "Price on PO" difference between current and previous rows within one item code group.

Item	Price on PO	Change from last PO	Cumulative change
1	2.55	0.0%	0.0%
1	2.51	-1.56%	-1.56%
1	2.64	5.17%	3.61%
1	2.19	-17.04%	-13.43%
2	1	0%	0%
2	1.5	50%	50%
2	3	100%	150%

I will then create Cumulative change column, for which I think I have found solution, and add a condition to select specific columns for automatic sending out through POWER BI Service dataflow and pipeline.

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"

12 Replies

ronrsnfld

Super User

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"

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"

PolinaOr
Helper I
5 months ago
"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?
- ronrsnfld
  Super User
  5 months ago
  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.

oussamahaimoud
Memorable Member
5 months ago
Hi PolinaOr,
Hope you are doing well!

Here's the modern Power Query (M) solution, no dataset duplication needed :

let
Source = YourTableHere,

// Group by Item, then within each group calculate row-over-row % change
GroupedByItem = Table.Group(
Source,
{"Item"},
{
{"AllRows", (group) =>
let
// Sort by the natural order (or add a date/PO number column to sort by if available)
Sorted = Table.AddIndexColumn(group, "_idx", 0, 1, Int64.Type),

// Add "Change from last PO" column
WithChange = Table.AddColumn(Sorted, "Change from last PO", (row) =>
let
idx = row[_idx],
prevRows = Table.SelectRows(Sorted, each [_idx] = idx - 1),
prevPrice = if Table.IsEmpty(prevRows) then null
else Table.First(prevRows)[Price on PO]
in
if prevPrice = null then 0
else (row[Price on PO] - prevPrice) / prevPrice
, type number),

// Add "Cumulative change" column
WithCumulative = Table.AddColumn(WithChange, "Cumulative change", (row) =>
let
idx = row[_idx],
firstPrice = Table.First(Sorted)[Price on PO]
in
if idx = 0 then 0
else (row[Price on PO] - firstPrice) / firstPrice
, type number),

// Clean up helper column
Cleaned = Table.RemoveColumns(WithCumulative, {"_idx"})
in
Cleaned
, type table}
}
),

// Expand back to flat table
Expanded = Table.Combine(GroupedByItem[AllRows])

in
Expanded

Keep in mind that Power Query processes rows in load order. If your source doesn't guarantee row order within an item group, add a sort step on a PO Date or PO Number column inside the group lambda before the AddIndexColumn line:

Sorted = Table.Sort(
Table.AddIndexColumn(group, "_idx", 0, 1, Int64.Type),
{{"PO Date", Order.Ascending}}
)

For formatting as % in the report layer, keep the values as decimals (0.05 = 5%) and apply percentage formatting in Power BI's column format settings, avoids storing redundant strings in the datamodel.

Hope this helps! Don't forget to mark as solution and thumbs up that's motivate me to keep helping you and others 🙂
- PolinaOr
  Helper I
  5 months ago
  Hi, could you please tell me if you used AI for this answer?
Hans-Georg_Puls
Super User
5 months ago
Hi PolinaOr ,

this Power Query Code should do what you want:

let
Source = YourSourceTable
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", Int64.Type}, {"Price", type number}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
Custom1 = Table.AddColumn(
#"Added Index", "Change from last PO",
each if [Index] > 0 then let
LastItem = #"Added Index"[Item]{[Index]-1},
CurrentItem = #"Added Index"[Item]{[Index]},
LastPrice = #"Added Index"[Price]{[Index]-1},
CurrentPrice = #"Added Index"[Price]{[Index]},
value = if LastItem = CurrentItem

then Value.Divide((CurrentPrice - LastPrice), LastPrice)

else 0
in
value
else 0
)
in
Custom1

You should change the first line to reference your source table.

Starting with the source table:

You will get:
- PolinaOr
  Helper I
  5 months ago
  Hi, as it takes very long time to test even slightest changes, as the database is over 10mln rows. I have used a different solution, but thank you very much as well.

Forum Discussion

% of difference from previous rows in a group for large dataset for Power query

12 Replies

Related content

Calculate difference from previous month

Create a report per Worker ID with different datasets feeding different tablix

calculate % difference from previous quarter

difference between dataflows and datasets?

Refresh dataset api