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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Please try this syntax from the bold section.

 

= Table.Group(#"Added Custom7", {"Name"}, {{"Max", each List.Max([Forecast]), type nullable date}, {"MaxYes", each List.Max(Table.SelectRows(_, each [Milestone]="Yes")[Forecast]), type date}})

 

Pat

 

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

Thanks Pat.

 

I am recieving the below syntax error but it is not obvious to me what I need to correct. I aded a comma between each [Milestone]="Yes") , [Forecast]) but that was not the issue.

 

Any thoughts?

 

Screenshot:

 

 

M Query:

 

 

= 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 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]}})