Forum Discussion
Solved
Return value based on highest number in index column
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.
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"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
2 Replies
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"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