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 would like power query to return just one row: the latest date. I have always used a workaround (select the latest date, then group by date column) but I wonder if one could do this more effectively by List.Distinct.
Attempts trying to nest it around List.Max did not work:
#"Filtered Rows" = Table.SelectRows(ATable, List.Distinct(let latest = List.Max(ATable[DateCharac]) in each [DateCharac] = latest))
#"Filtered Rows" = Table.SelectRows(ATable, let latest = List.Distinct(List.Max(ATable[DateCharac])) in each [DateCharac] = latest)
This one returned everything:
#"Filtered Rows" = Table.SelectRows(ATable, let latest = List.Max(ATable[DateCharac]) in each [DateCharac] = latest),
#"Distinct" = List.Distinct(#"Filtered Rows"[DateCharac])
Please let me know what goes wrong above - what would be the best way to get the latest date in one row?
Solved! Go to Solution.
Hi @Petri
Do you want to put custom1, custom2, etc. in different columns of a single row? Something like below?
custom1 = #table({"Latest Date 1", "Latest Date 2"}, {{List.Max(ATable[Date]), List.Max(BTable[Date])}})
in
custom1
If it's not what you want, can you provide the expected table you want to get with some sample data?
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @Petri
Is this what you want?
= #table({"Latest Date"},{{List.Max(ATable[Date])}})
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thank you! This indeed makes it easier and avoids navigating to the table and field first. I even managed to get the values from several tables:
custom1 = #table({"Latest Date"},{{List.Max(ATable[Date])}}),
custom2 = #table({"Latest Date"},{{List.Max(BTable[Date])}})
in
custom1
...althoug I still struggle how to get the custom1, custom2, etc. in different columns.
Hi @Petri
Do you want to put custom1, custom2, etc. in different columns of a single row? Something like below?
custom1 = #table({"Latest Date 1", "Latest Date 2"}, {{List.Max(ATable[Date]), List.Max(BTable[Date])}})
in
custom1
If it's not what you want, can you provide the expected table you want to get with some sample data?
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Thanks a lot for your quick reply - this was exactly what I was after.
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.