Previous Transaction values when change occurs

[rmeng]

Hello community,

I want to add 2 new columns with previous values (Date and Tier) when there is a change of Tier:

I want to be  like this:

Thanks in advance

[edhans · Accepted solution]

See the following code @rmeng - it returns this data:

The code and method may seem more complex than you would expect, but there is no good way to get a previous row in Power Query. There are methods that will create indexes then do an offset and merge with each other, but those are only useful on small datasets. They breakdown on large data sets. 

This method turns the first 3 columns into a set of lists, then it creates 2 more lists offset by one row, for a total of 5 lists. Then it puts them back together into one table, then does some if/then/else logic to get the values or nulls as applicable.

If you need more help, please post usable data per the links below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYX1DfSMDIwOlWB1kQSNUQSOwoDGqoDFY0ASboCk2QTNUQROwoDk2QQuoYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Tier = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    ShiftedList1 = {null} & List.RemoveLastN(Table.Column(#"Changed Type", "Tier"), 1),
    ShiftedList2 = {null} & List.RemoveLastN(Table.Column(#"Changed Type", "Date"), 1),
    AllLists = Table.ToColumns(#"Changed Type") & {ShiftedList1} & {ShiftedList2},
    NewTable =
        Table.FromColumns(
            AllLists,
            Table.ColumnNames(#"Changed Type") & {"Temp Tier", "Temp Date"}
        ),
    #"Added Previous Account" = Table.AddColumn(NewTable, "Previous Account", each if [Temp Tier] <> null and [Temp Tier] <> [Tier] then [Temp Tier] else null, Text.Type),
    #"Added Previous Date" = Table.AddColumn(#"Added Previous Account", "Previous Date", each if [Temp Tier] <> null and [Temp Tier] <> [Tier] then [Temp Date] else null, Date.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Previous Date",{"Account", "Tier", "Date", "Previous Account", "Previous Date"})
in
    #"Removed Other Columns"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[edhans · Accepted solution]

See the following code @rmeng - it returns this data:

The code and method may seem more complex than you would expect, but there is no good way to get a previous row in Power Query. There are methods that will create indexes then do an offset and merge with each other, but those are only useful on small datasets. They breakdown on large data sets. 

This method turns the first 3 columns into a set of lists, then it creates 2 more lists offset by one row, for a total of 5 lists. Then it puts them back together into one table, then does some if/then/else logic to get the values or nulls as applicable.

If you need more help, please post usable data per the links below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIEYX1DfSMDIwOlWB1kQSNUQSOwoDGqoDFY0ASboCk2QTNUQROwoDk2QQuoYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Tier = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    ShiftedList1 = {null} & List.RemoveLastN(Table.Column(#"Changed Type", "Tier"), 1),
    ShiftedList2 = {null} & List.RemoveLastN(Table.Column(#"Changed Type", "Date"), 1),
    AllLists = Table.ToColumns(#"Changed Type") & {ShiftedList1} & {ShiftedList2},
    NewTable =
        Table.FromColumns(
            AllLists,
            Table.ColumnNames(#"Changed Type") & {"Temp Tier", "Temp Date"}
        ),
    #"Added Previous Account" = Table.AddColumn(NewTable, "Previous Account", each if [Temp Tier] <> null and [Temp Tier] <> [Tier] then [Temp Tier] else null, Text.Type),
    #"Added Previous Date" = Table.AddColumn(#"Added Previous Account", "Previous Date", each if [Temp Tier] <> null and [Temp Tier] <> [Tier] then [Temp Date] else null, Date.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Previous Date",{"Account", "Tier", "Date", "Previous Account", "Previous Date"})
in
    #"Removed Other Columns"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[rmeng · Accepted solution]

Brilliant @edhans . Thank you

[edhans]

Glad I was able to assist with your project @rmeng 👍