Forum Discussion

kent-culpepper's avatar
kent-culpepper
Frequent Visitor
2 years ago

List.Max function @ Power Query M (Inline Subquery)

I am trying to Add a Column ("Max_Quote_Date") that returns the Max Date of another Column ("Quote_Date") with 2 conditions ("Investment_Key","Month_Date"), using the following expression;     #"...
Need Help
Ahmedx's avatar
Ahmedx
Super User
2 years ago

firstly, the conditions are unclear, try to explain in words what you want and what conditions must be met
secondly, share data that will help you

kent-culpepper's avatar
kent-culpepper
Frequent Visitor
2 years ago

Where 

[Investment_Key] = #"Filtered Rows1"[Investment_Key] and [Month_Date] < #"Filtered Rows1"[Month_Date]

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    [Investment_Key]  this is Value
     #"Filtered Rows1"[Investment_Key]  this is List
    why do you want to compare the value with the List🙄

    so I ask what do you need?

    • kent-culpepper's avatar
      kent-culpepper
      Frequent Visitor
      2 years ago

      If we reference the visual, I am indicating that I want to return (in a new column) the yellow-shaded value where it is currently null.

       

      Which means I want to find the Max(Quote_Date) Where the Investment_Key = the Prior Step Investment Key and the Month_Date < the Prior Step Month_Date.

       

      9/30/23 = Max(Quote_Date) Where Investment_Key = Aeva Tech And Month_Date < 10/31/23.