Filter to keep latest date for unique ID values in another column

[Anonymous]

I am trying to figure out a way within Power Query to filter for the latest date in my LoadDate column but also looking at the JobID column to only filter out old dates for each JobID. In my example I would want to keep the 8/27/20 rows for JobID 173 as well as the 9/2/20 rows for JobID 250. The StaffID and/or ServiceCode data could change each time new data is loaded so there could be more or less rows of data for each JobID every time the data is updated.

[Jimmy801 · Accepted solution]

Hello @Anonymous 

use Table.Group with a special function like this

{{"AllRows", (group)=> Table.SelectRows(group, each [LoadDate]= List.Max(group[LoadDate]))}}

here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3VtJRMjLXM7DQMzJQitUhUcgMv5CRqQFQyMBIz8CSoJCRiZ6BOUEhvBpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JOBID = _t, LoadDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOBID", Int64.Type}, {"LoadDate",type date,"de-DE"}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JOBID"}, {{"AllRows", (group)=> Table.SelectRows(group, each [LoadDate]= List.Max(group[LoadDate]))}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"LoadDate"}, {"LoadDate"})
in
    #"Expanded AllRows"

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

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create add a new step with following m codes.

Custom1= Table.SelectRows(#"Changed Type",each 
let jobid=[JobID],loaddate=[LoadDate],
tab = Table.SelectRows(#"Changed Type",each [JobID]=jobid),
maxdate = List.Max(tab[LoadDate])
in 
loaddate=maxdate
)

Here are the codes in 'Advanced Editor'.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZFLDsMgDAXvwjoKtsF8zhLl/tcoIS2lxgR1AxJi9J49x2EQM6QAZjMY3XUCYiw3IMH1mixFS0D1hzm3kciYcALQziqCFHOqTB4Y2KMOucB3MT8W05t9Z0EcY2jSrUFMbyg8L6CLCf8SDgYCVsisFnHgVNfOoJjMlpoXFRAiu//u41FGCI99RNuvYITGDvGzFKGxQ1CfRDos1n1D/CokSIRggdwWfxC3QoZiVIqdLw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ClientID = _t, JobID = _t, StaffID = _t, ServiceCode = _t, LoadDate = _t, ActualHoursAtETC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ClientID", Int64.Type}, {"JobID", Int64.Type}, {"StaffID", Int64.Type}, {"ServiceCode", Int64.Type}, {"LoadDate", type date}, {"ActualHoursAtETC", type number}}),
    Custom1 = Table.SelectRows(#"Changed Type",each 
let jobid=[JobID],loaddate=[LoadDate],
tab = Table.SelectRows(#"Changed Type",each [JobID]=jobid),
maxdate = List.Max(tab[LoadDate])
in 
loaddate=maxdate
)
in
    Custom1

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for all the inputs. This one got me exactly what I needed that I was able to understand best.

[Crystal_YW]

hi, you could try as follows: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Sorted Rows" = Table.Sort(Source,{{"JobID", Order.Ascending}}), Custom1 = Table.AddColumn(#"Sorted Rows","test",each List.Max( List.Range(#"Sorted Rows"[LoadDate], List.Min(List.PositionOf(#"Sorted Rows"[JobID],[JobID],Occurrence.All)), List.Max(List.PositionOf(#"Sorted Rows"[JobID],[JobID],Occurrence.All))+1 ) ) ), Custom2 = Table.SelectRows(Custom1, each [LoadDate]=[test]) in Custom2

[Jimmy801 · Accepted solution]

Hello @Anonymous 

use Table.Group with a special function like this

{{"AllRows", (group)=> Table.SelectRows(group, each [LoadDate]= List.Max(group[LoadDate]))}}

here the complete code

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQ3VtJRMjLXM7DQMzJQitUhUcgMv5CRqQFQyMBIz8CSoJCRiZ6BOUEhvBpjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [JOBID = _t, LoadDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"JOBID", Int64.Type}, {"LoadDate",type date,"de-DE"}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"JOBID"}, {{"AllRows", (group)=> Table.SelectRows(group, each [LoadDate]= List.Max(group[LoadDate]))}}),
    #"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"LoadDate"}, {"LoadDate"})
in
    #"Expanded AllRows"

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

[Anonymous]

Now I undestand this one as well and it works as a solution for my needs.

[Anonymous]

Hello @Jimmy801 

Thanks for the feedback. I couldn't get your solution to fully work, so not sure if it is exactly what I need. I have copied the full test data I am working with on this solution into a spreadsheet (link). The data I am actually working with is Direct Query from a SQL table. In the example data there are two JobIDs, so my goal is to retain only those rows for each JobID that reflect the most recet LoadDate associated with that JobID. In the example scenario I want to keep all the rows for JobID 1732051 with a LoadDate of 8/27/2020 and all the rows for JobID 1735603 with a LoadDate of 9/3/2020. Once active there will be hundreds of different JobIDs coming in with new rows of data associated with an updated LoadDate on that JobID. Ideally the solution would be accomplished in Power Query, but would settle for a DAX solution as well if that isn't possible.

https://docs.google.com/spreadsheets/d/14rhX4ZG_EmSkuu_PfW5VwjAerOPVtgdUtsKLNWiXGlk/edit?usp=sharing

[Jimmy801]

Hello @Anonymous 

My code does exactly what are you again writing. It groupes by JobID and keeps only row with the latest date. Just put your datasource instead of mine and in case adapt column names in my code. Then it should work

If you have any troubles, post your outcome and show me what is not working

All the best

Jimmy

[Anonymous]

I came across some similar disucssion and was able to come up with a DAX solution for each of the four data columns I need to sum for. This worked for me to get the sum of the most recent ActualHoursAtETC and when pulling it into a Power BI table by JobID it provided the sum I expected for only the most recent LoadDate.

Actual Hours = CALCULATE(SUM('EMD_Update_History'[ActualHoursAtETC]),FILTER('EMD_Update_History','EMD_Update_History'[LoadDate]=MAX('EMD_Update_History'[LoadDate])))

Would still love a solution within Power Query if possible, so I can just pull in the sum for each data column instead of having to have 4 different DAX solutions, but this works for now.

Thanks,

Aaron

[mussaenda]

Hi @Anonymous ,

I have attached a sample pbix for you to try.

Just change the source on power query.

This may no be the best solution but it achieves what you need.

Hope this helps!

[Greg_Deckler]

@Anonymous - Can you post your sample data as text in a table so that it can be easily copied? Then @ImkeF or @edhans or @Jimmy801 might have better luck coming up with a Power Query solution.

[amitchandak]

@Anonymous ,

refer  if these can help

https://www.youtube.com/watch?v=rqDdnNxSgHQ

https://www.youtube.com/watch?v=qUmTxQHr6nY

[Greg_Deckler]

@Anonymous Seems like a Grou By operation to me. In DAX, it would be a SUMMARIZE or GROUPBY with a max.