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
- mahoneypat
Microsoft Employee
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