Max Date from Power query list

[trev1234]

I am looking to find out how to add an extra column in my power query to let me know the max date of each site, so in all the rows with one they would say 10/11/22 and rows with 2 would say 05/11/22. I hope that makes sense and someone can help
thanks

Site	Date
1	09/11/2022
1	10/11/2022
1	09/11/2022
2	29/10/2022
2	05/11/2022
2	22/10/2022
2	15/10/2022
2	05/11/2022

 

[ValuationMan]

There might be a more efficient way coding in M directly, but I would duplicate the query and then GroupBy Site and then use the maximum operation. You will then have a table of a single site and the latest date. You could then merge the query with your original data using the Site as the identifier.

 

[RoryA]

You can use something like:

Table.AddColumn(#"Changed Type", "Custom", each List.Max(Table.SelectRows(Source, (row)=> [Site] = row[Site])[Date]))

where Source is the previous step name.

 

[trev1234]

Thanks ValuationMan that worked great, also thanks RoryA for you solution too, can always rely on this forum to help me out