Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Power Query previous row value based on criteria!! :O

Dear Power BI community: I would really appreciate if you could help me out with the following issue. I have a table with Customer ID and current month sales, but I would like to have another column with previous month sales for every ID. When I try to do it using an index column, it always calculates the sales from previous month, but it doesn't take into account the customer ID. I'll paste an example of what I'm trying to do above.

 

 

Example.pngI'm really new to Power BI and I have read about this topic many times, but I haven't still figured out how to deal with clustered indexes or maybe there's an easier way to achieve this. Thank you all!!.

1 ACCEPTED SOLUTION
MarcelBeug
Community Champion
Community Champion

If you are looking for a Power Query solution, I have 2 alternatives.

In both cases I created an Excel workbook with your data; the step "Typed" is the actual starting point.

 

Alternative 1 is based on this blog by @MattAllington using indices and merge tables.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table  = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(Typed, "Index1", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index0", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index0"},#"Added Index1",{"Index1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Customer", "Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index1", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    #"Removed Other Columns"

 

 

Alternative 2 is an alternative from myself.
A bit more complicated but I just like to share it as I think it might be interesting.

I created a function PreviousRecords that adds a null record as first row to a table, removes the last row of that table and turns the result into a list of records.

In the query I turn the original table into a list of records and create a table from this list and the list from the function.

 

Function PreviousRecords:

(SourceTable as table) as list =>
let
    Shifted = Table.Combine({#table(Value.Type(SourceTable),{List.Repeat({null},Table.ColumnCount(SourceTable))}),Table.RemoveLastN(SourceTable,1)}),
    Renamed = Table.TransformColumnNames(Shifted, each "Previous."&_),
    Records = Table.ToRecords(Renamed)
in
    Records

Query:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    Records = Table.FromColumns({Table.ToRecords(Typed),PreviousRecords(Typed)}),
    ExpandedCurrent = Table.ExpandRecordColumn(Records, "Column1", {"Customer", "Month", "Current Month Sales"}, {"Customer", "Month", "Current Month Sales"}),
    ExpandedPrevious = Table.ExpandRecordColumn(ExpandedCurrent, "Column2", {"Previous.Customer", "Previous.Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    AddedConditionalColumn = Table.AddColumn(ExpandedPrevious, "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    RemovedOtherColumns = Table.SelectColumns(AddedConditionalColumn,{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    RemovedOtherColumns
Specializing in Power Query Formula Language (M)

View solution in original post

11 REPLIES 11
MarcelBeug
Community Champion
Community Champion

If you are looking for a Power Query solution, I have 2 alternatives.

In both cases I created an Excel workbook with your data; the step "Typed" is the actual starting point.

 

Alternative 1 is based on this blog by @MattAllington using indices and merge tables.

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table  = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(Typed, "Index1", 1, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index0", 0, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index0"},#"Added Index1",{"Index1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Customer", "Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index1", Order.Ascending}}),
    #"Added Conditional Column" = Table.AddColumn(#"Sorted Rows", "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column",{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    #"Removed Other Columns"

 

 

Alternative 2 is an alternative from myself.
A bit more complicated but I just like to share it as I think it might be interesting.

I created a function PreviousRecords that adds a null record as first row to a table, removes the last row of that table and turns the result into a list of records.

In the query I turn the original table into a list of records and create a table from this list and the list from the function.

 

Function PreviousRecords:

(SourceTable as table) as list =>
let
    Shifted = Table.Combine({#table(Value.Type(SourceTable),{List.Repeat({null},Table.ColumnCount(SourceTable))}),Table.RemoveLastN(SourceTable,1)}),
    Renamed = Table.TransformColumnNames(Shifted, each "Previous."&_),
    Records = Table.ToRecords(Renamed)
in
    Records

Query:

 

let
    Source = Excel.Workbook(File.Contents("C:\Users\Marcel\Documents\Forum bijdragen\Power BI Community\Power Query previous row value based on criteria.xlsx"), null, true),
    SalesByCustomer_Table = Source{[Item="SalesByCustomer",Kind="Table"]}[Data],
    Typed = Table.TransformColumnTypes(SalesByCustomer_Table,{{"Customer", type text}, {"Month", type text}, {"Current Month Sales", Int64.Type}}),
    Records = Table.FromColumns({Table.ToRecords(Typed),PreviousRecords(Typed)}),
    ExpandedCurrent = Table.ExpandRecordColumn(Records, "Column1", {"Customer", "Month", "Current Month Sales"}, {"Customer", "Month", "Current Month Sales"}),
    ExpandedPrevious = Table.ExpandRecordColumn(ExpandedCurrent, "Column2", {"Previous.Customer", "Previous.Current Month Sales"}, {"Previous.Customer", "Previous.Current Month Sales"}),
    AddedConditionalColumn = Table.AddColumn(ExpandedPrevious, "Previous Month Sales", each if [Customer] = [Previous.Customer] then [Previous.Current Month Sales] else null ),
    RemovedOtherColumns = Table.SelectColumns(AddedConditionalColumn,{"Customer", "Month", "Current Month Sales", "Previous Month Sales"})
in
    RemovedOtherColumns
Specializing in Power Query Formula Language (M)

Hi @MarcelBeug 

 

I have tried applying Alternative 1 to my advanced query, but get stuck on the first line:

 

I am VERY new to Power BI. 

 

This is my query so far, but I want to add your PreviousRecords function to what I have done already. I don't know how to get past the "(SourceTable as Table) as list =>" part because I keep getting "Token Identifier Expected" error.

 

Here is where I am, just removed a couple of columns and added your function, then get that error.

 

let
Source = avaLoadAndHaulDetail,
#"Removed Columns" = Table.RemoveColumns(Source,{"unique_id", "MineAreaName", "Asset", "LoadingTimeStampZA"}),
(SourceTable as table) as list => Shifted = Table.Combine({#table(Value.Type(SourceTable),{List.Repeat({null},Table.ColumnCount(SourceTable))}),Table.RemoveLastN(SourceTable,1)}), Renamed = Table.TransformColumnNames(Shifted, each "Previous."&_), Records = Table.ToRecords(Renamed) in Records

 

What am I doing wrong?

 

Thanks! 

MarcelBeug, thanks a lot sir! You saved my life!. That's what I was trying to achieve. I'd rather use alternative No.1 since it seems easier and I'm not yet able with custom functions!!!. Thanks for your kind answer!.

DoubleJ
Solution Supplier
Solution Supplier

Thanks for answering DoubleJ, but that's not what I'm looking for, in that post they are trying to calculate a running total, instead, I'm trying to retrieve a previous row value based on a customer ID.

 

Thanks anyway.

hi @juansgutierrezu

 

To solve this please follow these sequence:

 

1. Add a Index Column (Using Query Editor)

 

2. Create a calculated Column

 

Rank =
VAR Customer = Table1[Customer]
RETURN
    RANKX (
        FILTER ( ALL ( Table1 ), Table1[Customer] = Customer ),
        Table1[Index],
        ,
        ASC
    )

3. Create The PreviousRow Column Or in a Measure (Replace VAR Index = Table1[RANK]-1 with VAR Index=min(Table1[Rank])-1)  

 

PreviousRowSales =
VAR Index = Table1[Rank] - 1
RETURN
    CALCULATE (
        SUM ( Table1[Current Month Sales] ),
        FILTER ( ALLEXCEPT ( Table1, Table1[Customer] ), Table1[Rank] = Index )
    )

  




Lima - Peru

Thanks for the solution info.  I used the Merge Query approach from Matt Arlington and works great.  Also tried another PowerQuery approach with individual row calculations (using index number) and had significant performance issue, and the Merge Query approach works much faster.  Also could achieve the same in DAX but I prefer to do in PQ.

Vvelarde, thanks for your model! It looks pretty cool!! unfortunately I'm planning to do this using Power Query, instead of doing this in eather a measure or in a calculated column, since I don't want to increase the size of the data model by creating more calculated columns and as far as I'm concerned it is better to perform this kind of calculations and custom columns in power query than in power pivot.

 

Thanks anyway, coo DAX patterns.

This DAX Pattern has more detail on another approach

 

http://www.daxpatterns.com/cumulative-total/


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks Phil_Seamark but in that post they are trying to create a cumulative total, what I'm trying to perform is different. Thanks anyway!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.