Getting a unique value of the latest date

[Petri]

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?

[v-jingzhang]

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.

[Petri]

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.

[v-jingzhang · Accepted 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.

[Petri]

Thanks a lot for your quick reply - this was exactly what I was after.