Max Date joined to itself

[MORDax]

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?

[tamerj1]

Hi @MORDax 

What do you mean by "max date for each row"? Do you have multiple date columns ans you want to calculate the max out of them? Can you share some sample data?

[MORDax]

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 date

[v-yanjiang-msft · Accepted solution]

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 _ kalyj

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

[amitchandak]

@MORDax , You can create a new column

max date = max(Table1[date])

or a new measure

max date = calculate(max(Table1[date]), all(Table1))

[MORDax]

In the Power Query Editor? How do I then join the table to itself for the query Table1[Date] - Table1[MaxDate]

[amitchandak]

@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/