Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to get maximum date in Power Query for column comparison

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

 

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

     

5 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , check if this can work

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        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.