Forum Discussion

BA11501Banderso's avatar
BA11501Banderso
Frequent Visitor
3 years ago
Solved

Create Max Date Columns based on multiple columns (some with blanks)

Hello,  I am trying to create a column populated with the max date from a series of columns.  Some of the columns have blanks in them.  For example Notification "1" would have a date of 9/16/2022 an...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi  BA11501Banderso ,

    Here are the steps you can follow：

    1. Enter PowerQuery and copy the Table to form Table2.

    2. Select all columns in Table2 except [Notification] - Transform - Unpivot Columns.

    Result:

    3. Create calculated column.

    Table2:

    max_date =
 MAXX(FILTER(ALL('Table2'),'Table2'[Notification]=EARLIER('Table2'[Notification])),[Value])

    Table:

    Max Date Columns =
MINX(FILTER(ALL('Table2'),'Table2'[Notification]=EARLIER('Table'[Notification])),[max_date])

    4. Result:

     

    Best Regards,

    Liu Yang

    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
3 years ago

Hi  BA11501Banderso ,

Here are the steps you can follow：

1. Enter PowerQuery and copy the Table to form Table2.

2. Select all columns in Table2 except [Notification] - Transform - Unpivot Columns.

Result:

3. Create calculated column.

Table2:

max_date =
 MAXX(FILTER(ALL('Table2'),'Table2'[Notification]=EARLIER('Table2'[Notification])),[Value])

Table:

Max Date Columns =
MINX(FILTER(ALL('Table2'),'Table2'[Notification]=EARLIER('Table'[Notification])),[max_date])

4. Result:

 

Best Regards,

Liu Yang

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

  • BA11501Banderso's avatar
    BA11501Banderso
    Frequent Visitor
    3 years ago

    Thank you Liu!  You made it very easy to follow.