Forum Discussion
Helper VM-Query - List.Max with Condition/Filter
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
Microsoft EmployeeLooks like you have named two columns the same thing (MaxBaselineDate) and you are missing the ) to close the List.Max( ).
Pat
Helper VThanks Pat. I corrected my mistake and that seems to have corrected the problem. Unfortunately I am now seeing an error related to the query not being able to convert type List to type Number. I cannot quite identify what I should address in order to get the List column to appear.
If I excluded the ALL function entirely, 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]})
N.B – I changed the parameter from [Milestone]=”Yes” to [IsRolloutMilestone]=1
- mahoneypat
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
- imranamikhan
Thank you very much mahoneypat for taking time out of your Monday to promptly respond and support. This has saved me a significant amount of time. I have subscribed to your channel. Thanks again.
Final solution for anyone else who might come across this thread:
Original list max query:#"Grouped Rows Default" = Table.Group(#"Removed Columns", {"II___Project"}, {{"MaxBaseline", each List.Max([WE_Baseline_Finish_Date]), type nullable date}, {"MaxForecast", each List.Max([WE_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=date, WE_Baseline_Finish_Date=nullable date]}})
New list max query with condition:#"Grouped Rows Conditional List Max" = Table.Group(#"Removed Columns", {"II___Project"}, {{"MaxBaseline", 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]}})New list max query with condition but with an additional list max (now includes list max for the Forecast date column as well as the Baseline date column:
#"Grouped Rows Custom" = Table.Group(#"Removed Columns", {"II___Project"}, {{"MaxBaseline", each List.Max([WE_Baseline_Finish_Date]), type nullable date}, {"BaselineIsRollOut", each List.Max(Table.SelectRows(_, each [IsRolloutMilestone]=1)[WE_Baseline_Finish_Date]), type date}, {"MaxForecast", each List.Max([WE_Finish_Date]), type nullable date}, {"FcastIsRollout", each List.Max(Table.SelectRows(_, each [IsRolloutMilestone]=1)[WE_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, IsRolloutMilestone=nullable number, Milestone_ID=nullable text, Source.Name=nullable text, WE_Finish_Date=nullable date, WE_Baseline_Finish_Date=nullable date]}})Anonymous I have found very few examples online of List.MaxN being used. If you have any links to share that would be appreciated.
Best regards,