cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bridley28
Regular Visitor

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?

 

IndexTaskStatus DateUser
11000101/01/2020J.Bloggs
21000101/06/2020T.Adams
11000205/06/2020B.Smith
11000305/06/2020B.Smith
21000310/06/2020T.Adams
31000315/06/2020T.Adams

 

Thanks all 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

1 REPLY 1
mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors