Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Min Dates (mutiple Columns) Power Query - possible blank values

Hi, 

 

Below i have a DAX function giving me the min. value of mutiple date values (columns). Does anyone know how to do this in PowerQuery? 

 

SuccessDateToUse =
VAR _remoteRental = IF(ISBLANK([Remote rental date]),DATE(9999,12,31),[Remote rental date])
VAR _remoteSales = IF(ISBLANK([Remote sales date]),DATE(9999,12,31),[Remote sales date])
VAR _PhysicalRental = IF(ISBLANK([Physical rental date]),DATE(9999,12,31),[Physical rental date])
VAR _PhysicalSale = IF(ISBLANK([Physical sales date]),DATE(9999,12,31),[Physical sales date])

VAR _calc1 =
IF(_remoteRental < _remoteSales,_remoteRental,_remoteSales)
VAR _calc2 =
IF(_calc1 < _PhysicalRental,_calc1,_PhysicalRental)
VAR _calc3 =
IF(_calc2 < _PhysicalSale,_calc2,_PhysicalSale)
RETURN
_calc3
Tips and Tricks
  • HotChilli's avatar
    HotChilli
    5 years ago

    A slight adjustment to AIB's formula should do it.  Sub in your column names (should all be Date type)

    Table.AddColumn(#"Changed Type", "Custom", each List.Min({[rSales] , [rRental] , [PRental] , [PSale]}))

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    Hi Anonymous 

    You could make one list of all four columns and then extract the minimum. Something like 

    List.Min(Table1[Col1] & Table1[Col2] & Table1[Col3] & Table1[Col4]))

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi AlB , 

       

      When i do this i get the following error

      --> I think because for some values are "null"

       

      Expression.Error: We cannot convert Type to List type.
      Details:
      Value=[Type]
      Type=[Type]

      • HotChilli's avatar
        HotChilli
        Community Champion
        5 years ago

        A slight adjustment to AIB's formula should do it.  Sub in your column names (should all be Date type)

        Table.AddColumn(#"Changed Type", "Custom", each List.Min({[rSales] , [rRental] , [PRental] , [PSale]}))