How to get maximum date in Power Query for column comparison

[Anonymous]

Hi,

I have dates in 6 columns, I need to compare and find maximum date among them. How to achieve it in PowerQuery editor?

The above image shows 3 dates colum, I have to compare row by row and find the maximum among them.

Thanks,

Karthik

[v-henryk-mstf]

Hi @Anonymous ,

According to your description, currently in the latest version of powerbi desktop, it is not possible to directly obtain the column name corresponding to the maximum date through the formula.

But you can try the following formula, the parameter to get the maximum date in each column, and then use the switch function to compare in turn, manually enter the column name corresponding to the maximum date, refer to the following:

Max_data_name = 
var a = MAX('Table (3)'[data1])
var b = MAX('Table (3)'[data2])
var c = MAX('Table (3)'[data3])
return
SWITCH(TRUE(),
a>b&&a>c,"data1",
b>a&&b>c,"data2",
c>a&&c>b,"data3")

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Anonymous]

@v-henryk-mstf Thanks for the reply but I was trying to achieve this in PowerQuery and I did it based on Switch case statement and order of precedence concept. But your solution is also nice one for DAX.

[amitchandak]

@Anonymous , check if this can work

List.Max({[Col1],[Col2],[Col3], [Col4],[Col5], [Col6]} )

[Anonymous]

Thanks @amitchandak . 

It worked, is there any possibility in a new column I can extract the column name as well.

For example In row1 if column 5 is having maximum date and the date is extracted based on above formula. Now Can I get the column 5 name in a new column.

Thanks,

Karthik

[Anonymous · Accepted solution]

For anyone who is looking for the solution,

This one I resolved it based on switch case statement with order of precedence.

But you can also resolve with,

List.Max({[Col1],[Col2],[Col3], [Col4],[Col5], [Col6]} )

but I am not sure how to extract the column names so went for switch case statement and order of precendence concept.