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

     

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
5 years ago

Thanks Pat. Unfortunately I am seeing the an error related to the query not being able to convert type List to type Number. I cannot quite identify what I should address to resolve in order to get the List column to appear.

 

If I excluded the ALL function, this does work but obviously this is not ideal.

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



Image5.PNG

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

    mahoneypat - Not sure why my post was removed and then reposted from "Syndicate Admin" - this post is from me just to avoid any confusion