Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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.
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?
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
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.
@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/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |