VLOOKUP From Same Table - MUST BE IN POWER QUERY

[ruarijp]

Hi there,

I need to do a version of VLOOKUP for Power Query to look up a value from the same table from the previous period. THIS MUST NOT BE IN DAX.  Below is an example:

I have time series data with Role ID, Status and a time stamp. I need to create a new column that states what the status was for that Role ID in the previous period.

The output would need to look like this. I can then calculate who is new/ has left by doing a comparion between Status and Status_Last_period.

 I have tried claculated a Time_Stamp_Code i.e 2023-2 and then a new variable Time_Stamp_Code_Last_Period i.e 2023-1 and then doing a merge on this but it is generating a huge number of rows.

Any help would be greatly appreciated.

[latimeria]

Hi @ruarijp ,

You can give a try to this :

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRyslTCMgvLgGyDAz1gcjIwMhYKVYnWskILF2WmJycmIdF2hi/bmyGG6EbjlMaw3AjhHQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Time_Stamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Time_Stamp", type date}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Time_Stamp", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Rows", each _, type table [ID=nullable number, Status=nullable text, Time_Stamp=nullable date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom1", each 
        let
            AllLists = Table.ToColumns([Rows]),
            PreviousStatus = {null} & Table.RemoveLastN([Rows],1)[Status],
            CombinedLists = AllLists & {PreviousStatus},
            Columns = Table.ColumnNames([Rows])
        in
            Table.FromColumns(CombinedLists, Columns & {"Status_Last_Period"})
    ),
    Expanded2 = Table.Combine(#"Added Custom"[Custom1]),
    #"Changed Type2" = Table.TransformColumnTypes(Expanded2,{{"Status_Last_Period", type text}})
in
    #"Changed Type2"

or this way of working

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRyslTCMgvLgGyDAz1gcjIwMhYKVYnWskILF2WmJycmIdF2hi/bmyGG6EbjlMaw3AjhHQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Time_Stamp = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Time_Stamp", type date}}),
    #"Sorted Rows" = Table.Buffer(Table.Sort(#"Changed Type",{{"ID", Order.Ascending}, {"Time_Stamp", Order.Ascending}})),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"ID"}, {{"Rows", each _, type table [ID=nullable number, Status=nullable text, Time_Stamp=nullable date]}}),
    #"Added Index" = Table.TransformColumns(
        #"Grouped Rows",
        {
            {"Rows", each Table.AddIndexColumn(_,"Index", 0 )}
        }
    ),
    #"Added Previous status" = Table.TransformColumns(
        #"Added Index",
        {
            { "Rows", (r)=> Table.AddColumn(r, "Status_Last_Period", each try r[Status]{[Index]-1} otherwise null)}
        }
        ),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Added Previous status", "Rows", {"Status", "Time_Stamp", "Status_Last_Period"}, {"Status", "Time_Stamp", "Status_Last_Period"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Rows",{{"Status_Last_Period", type text}})
in
    #"Changed Type1"

[jbwtp · Accepted solution]

Or something like this (using self-Join)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwVNJRcgRiQ31DfSMjpVgdkKARUMAJXdAYKOCMLojQboSq3RldEKTdBUkwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Status = _t, Preiod = _t]),
    Main = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Status", type text}, {"Preiod", type date}}),
    AddMonth = Table.TransformColumns(Main,{{"Preiod", each Date.AddMonths(_,1), type date}}),
    #"Merged Queries" = Table.NestedJoin(Main, {"ID", "Preiod"}, AddMonth, {"ID", "Preiod"}, "AddMonth", JoinKind.LeftOuter),
    #"Expanded AddMonth" = Table.ExpandTableColumn(#"Merged Queries", "AddMonth", {"Status"}, {"Status_Last_Period"})
in
    #"Expanded AddMonth"

Cheers,

John

@ruarijp