Forum Discussion

juansgutierrezu's avatar
juansgutierrezu
Icon for Helper I rankHelper I
9 years ago
Solved

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 ...
Need Help
Tips and Tricks
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    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
  • juansgutierrezu's avatar
    juansgutierrezu
    Icon for Helper I rankHelper I
    9 years ago

    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.

    • Vvelarde's avatar
      Vvelarde
      Icon for Community Champion rankCommunity Champion
      9 years ago

      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 )
    )