Group by date but retain one column

[Daviejoe]

Hi Power Query Experts

I need to group the W Number by the most recent Processed Time Stamp, so I'm fine grouping the W Number by MAX Processed Time Stamp, however I need to retain the Status column and this drops off once I group on Processed Time Stamp.

Any help would be great

Status ID	Number	W Number	Status	Processed Time Stamp
1	9400	370052		2020-09-08T15:00:07.8100000
2	9400	370059		2020-09-08T15:00:07.8270000
3	9400	370091		2020-09-08T15:00:07.8430000
4	9400	370099		2020-09-08T15:00:07.8570000
5	9400	370127		2020-09-08T15:00:07.8570000

[Jimmy801 · Accepted solution]

Hello @Daviejoe 

if you only think  you did it 😉 here a possible solution

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jc1BCsAgDATAr5SctWyiIcZ39Cb+/xu1F0s9SBfC5jJsa8QUyDMwKhmgMp5jnEAQ4RHlYq1AhZ2F8YR6aCSL850Tmy59nfPO5TRdXtx2T989/TgW++P6DQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Status ID" = _t, Number = _t, #"W Number" = _t, Status = _t, #"Processed Time Stamp" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Status ID", Int64.Type}, {"Number", Int64.Type}, {"W Number", Int64.Type}, {"Status", type text}, {"Processed Time Stamp", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"W Number"}, {{"AllRows", (tbl)=> Table.SelectRows(tbl, each [Processed Time Stamp]=List.Max(tbl[Processed Time Stamp])){0}[Status]}})
in
    #"Grouped Rows"

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Daviejoe]

I'm getting the below error

'Expression.Error: The name 'Changed Type' wasn't recognized. Make sure it's spelled correctly'

[Jimmy801]

Hello @Daviejoe 

when I copy paste my code to a new blank query, the result is this

you have to change my soure-step with your data source or your already created query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Daviejoe]

Hey @Jimmy801 

I think I was half asleep when I first tried to use your code, I'd entered an incorrect column name.

All is good and it's working, thank you!

[Daviejoe]

Thanks Jimmy, I'll check that out.

DJ

[AlB]

Hi @Daviejoe 

But the Status column is empty? Also, you want to group by W number but all W numbers in the sample provided are different.

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Daviejoe]

Hey @AlB 

I posted a snippet of the information, Status will not always be empty.  It will often contain a one or two letter code which will refer to another dimension table.  If the Status is blank then it means everything is fine.

So I will have multiple rows for the W Number but I only want to retain the latest record.

[Daviejoe]

Think I've solved it 🙂