Forum Discussion
Max Date joined to itself
I basically have a table that has a date column, I am trying to find the max date for each row and then join this table to itslef on the query
Table1[date] joined with Table1[MaxDate]
So that I can remove duplicates/dedup them. How do I do that?
Hi MORDax ,
According to your description, here's my solution.
In Power Query, select Asset column, then click Group By tab under Transform ribbon.
Set it like this.
Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
MORDax , You can create a new column
max date = max(Table1[date])
or a new measure
max date = calculate(max(Table1[date]), all(Table1))
In the Power Query Editor? How do I then join the table to itself for the query Table1[Date] - Table1[MaxDate]
MORDax , Power query if you simply want max
Max of column in same table, but you need the table name in last step
List.Max("#last Step Table name"[Date])
Power Query - List.Max and List.MaxN : https://youtu.be/22a8fq2UeBw
If you want to join the table with self you have to duplicate
https://www.myonlinetraininghub.com/excel-power-query-vlookup
https://eriksvensen.wordpress.com/2019/02/28/powerquery-replicate-doing-an-excel-vlookup-in-m/
I have one date column, I want to duplicate it, make the date column max and then join it to itself. The underlying rows have multiple entries for a single asset if it was inspected twice, I just want one entry for each asset. That is why.
Asset Date
1 4/25/2022
2 4/24/2022
1 4/22/2022
3 4/21/2022
My final output should contain assets for their latest dateHi MORDax ,
According to your description, here's my solution.
In Power Query, select Asset column, then click Group By tab under Transform ribbon.
Set it like this.
Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
