Forum Discussion
How to pull through latest index group in Power Query
So I have a table like the below, where some Tasks have more than one status (As highlighted by the Index Number).
What I need to accomplish is to show a single row by Task, where it shows the latest Status Date and associated User only.
is there a quick way to do this?
| Index | Task | Status Date | User |
| 1 | 10001 | 01/01/2020 | J.Bloggs |
| 2 | 10001 | 01/06/2020 | T.Adams |
| 1 | 10002 | 05/06/2020 | B.Smith |
| 1 | 10003 | 05/06/2020 | B.Smith |
| 2 | 10003 | 10/06/2020 | T.Adams |
| 3 | 10003 | 15/06/2020 | T.Adams |
Thanks all
Here is one way to do that by first Grouping and then using the Table.Max function to keep the latest row. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAA0QaG+kBkZGBkAOR46Tnl5KenFyvF6kQrGaGpMoOpCtFzTEnMhSiCGQVSbGCKpMhJLzg3syQDRZExPkVGSIoMDXBYZ4ysyBSbolgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Task = _t, #"Status Date" = _t, User = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Status Date", type date}, {"Index", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Task"}, {{"AllRows", each _, type table [Index=nullable number, Task=nullable text, Status Date=nullable date, User=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([AllRows], "Index")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Index", "Status Date", "User"}, {"Index", "Status Date", "User"})
in
#"Expanded Custom"Pat
1 Reply
Here is one way to do that by first Grouping and then using the Table.Max function to keep the latest row. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0MDAA0QaG+kBkZGBkAOR46Tnl5KenFyvF6kQrGaGpMoOpCtFzTEnMhSiCGQVSbGCKpMhJLzg3syQDRZExPkVGSIoMDXBYZ4ysyBSbolgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Task = _t, #"Status Date" = _t, User = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Status Date", type date}, {"Index", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Task"}, {{"AllRows", each _, type table [Index=nullable number, Task=nullable text, Status Date=nullable date, User=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max([AllRows], "Index")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"AllRows"}),
#"Expanded Custom" = Table.ExpandRecordColumn(#"Removed Columns", "Custom", {"Index", "Status Date", "User"}, {"Index", "Status Date", "User"})
in
#"Expanded Custom"Pat