Return value based on highest number in index column

[Jays_D]

Hi All,

New to PBI and could really use some help working this one out.

I have a table which has multiple rows, which can be the same account repeated a few times. A column which has a Status value and an index column (which has a value assigned 1-21 for each Status in the Status value column). Looks something like this:

Account	Status	Index	Last Status
ABC123	Waiting	1
ABC123	Commenced	2
DEF456	Waiting	1
GHI789	Complete	3

What I am trying to do is have a new column (Last Status) which will look at all rows for (use ABC123 as an example) and based on the largest index, return the Status. Using the sample above, the result would be "Commenced"

Apologies, I hope I am making sense. Any guidance would be greatly appreciated.

[Vijay_A_Verma · Accepted solution]

Below is the sample code for this problem

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRCk/MLMnMSweyDJVidZAknPNzc1PzklNTgGwjsJSLq5uJqRkWPe4enuYWlhA9BTmpJalApjEuLaaoEkGleXkQCaAlsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Status = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Status", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, {{"All", each Table.AddColumn(_, "Last Status", (x)=> _[Status]{List.PositionOf(_[Index],List.Max(_[Index]))})}})[[All]],
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Account", "Status", "Index", "Last Status"}, {"Account", "Status", "Index", "Last Status"})
in
    #"Expanded All"

[dufoq3 · Accepted solution]

Hi @Jays_D,

2 similar versions of code:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRCk/MLMnMSweyDJVidZAknPNzc1PzklNTgGwjsJSLq5uJqRkWPe4enuYWlhA9BTmpJalwGSxa0MwKKs3Lg0gYK8XGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Status = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}}),
    v1_GroupedRows = Table.Group(#"Changed Type", {"Account"}, {{"All", each _, type table}, {"Last Status", each Table.Max(_, "Index")[Status], type table}}),
    v1_StepBack = #"Changed Type",
    v1_Ad_LastStatus = Table.AddColumn(v1_StepBack, "Last Status", each v1_GroupedRows{[Account = [Account]]}[Last Status], type text),
    v1_Ad_PrevStatus = Table.AddColumn(v1_Ad_LastStatus, "Previous Status", each try v1_GroupedRows{[Account = [Account]]}[All]{[Index = [Index]-1]}[Status] otherwise null,type text),
    v2_SingleStep = Table.Combine(Table.Group(#"Changed Type", {"Account"},
    {{"Added Statuses", each Table.AddColumn(Table.AddColumn(_, "Last Status", (x)=> Table.Max(_, "Index")[Status], type text), "Previous Status", (x)=> try _{[Index = x[Index]-1]}[Status] otherwise null, type text), type table}})[Added Statuses])
in
    v2_SingleStep

[Vijay_A_Verma · Accepted solution]

Below is the sample code for this problem

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyNjQyVtJRCk/MLMnMSweyDJVidZAknPNzc1PzklNTgGwjsJSLq5uJqRkWPe4enuYWlhA9BTmpJalApjEuLaaoEkGleXkQCaAlsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Account = _t, Status = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account", type text}, {"Status", type text}, {"Index", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Account"}, {{"All", each Table.AddColumn(_, "Last Status", (x)=> _[Status]{List.PositionOf(_[Index],List.Max(_[Index]))})}})[[All]],
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Account", "Status", "Index", "Last Status"}, {"Account", "Status", "Index", "Last Status"})
in
    #"Expanded All"