Forum Discussion

imranamikhan's avatar
imranamikhan
Icon for Helper V rankHelper V
5 years ago
Solved

M-Query - List.Max with Condition/Filter

Hi everyone,   Using the Group By function, I am returning the MAX date value from two date columns, then using the All operator to return all data but with the MAX dates included as new columns (w...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    It looks like you have an extra } in the middle, and one missing at the end.  Here it is fixed (I think).

     

     

    = Table.Group(#"Removed Columns", {"II___Project"}, {{"Max", each List.Max([WE_Baseline_Finish_Date]), type nullable date}, {"MaxYes", each List.Max(Table.SelectRows(_, each [IsRolloutMilestone]=1)[WE_Baseline_Finish_Date]), type date}, {"All", each _, type table [Index=nullable number, Task_Name=nullable text, II___Project=nullable text, DIM_II_Project_Data_Direct_Extract.Percent_Complete=nullable number, Milestone Type=nullable text, IsRolloutMilestone=nullable number, WE_Finish_Date=nullable date, WE_Baseline_Finish_Date=nullable date]}})

     

     

    Here is the line from my local copy that is working.

     

    = Table.Group(#"Changed Type", {"Name"}, {{"Max", each List.Max([Forecast]), type nullable date}, {"MaxYes", each List.Max(Table.SelectRows(_, each [Milestone]="Yes")[Forecast]), type date}, {"AllRows", each _, type table [Name=nullable text, Milestone=nullable text, Forecast=nullable date]}})

     

    Pat

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Here is the syntax:

 

= Table.Group(#"Added Custom7", {"II___Project"}, {{"MaxBaselineDate", each List.Max([WE_Baseline_Finish_Date]), type nullable date}, {"MaxBaselineDate", each List.Max(Table.SelectRows(_, each [Milestone]="Yes")[WE_Baseline_Finish_Date], type nullable date}, {"All", each _, type table [Index=nullable number, Source.Name=nullable text, Task_Name=nullable text, Finish_Date=nullable date, Baseline_Finish=nullable date, Milestone_ID=nullable text, Milestone_Level=nullable text, II___Project=nullable text, DIM_II_Project_Data_Direct_Extract.Percent_Complete=nullable number, Milestone=nullable text, IsRolloutMilestone=nullable number, WE_Finish_Date=date, WE_Baseline_Finish_Date=nullable date]}})

 

But instead of all this, have you tried just using each List.MaxN, and just specifying [Milestone] = "Yes" as the comparison criteria? Might be cleaner.

 

--Nate