cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
imranamikhan
Helper V
Helper V

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 (with repeated values per grouping).

 

Image1.PNG

 

Is there a way to add an additional grouping but filter that grouping where it meets a specific condition? I still want to expand and display all data without the filter, but have the MAX DATE repeated.

 

The illustrate - in the example below the blue columns are returning the MAX Forecast and MAX Baseline date from each Project. The yellow columns (what I want to do) are also returning the MAX date by Project, but also where the Milestone column = “Yes”.

 

Image2.PNG

 

I was thinking something along the lines of this syntax but I do not know if this is possible:

 

= Table.Group(#"Added Custom7", {"II___Project", "Milestone"}, {{"MaxDate", each List.Max(Table.SelectRows(#"Added Custom7", each [Milestone] = "Yes"), [WE_Finish_Date]), type date})

 

 

I could create two duplicate queries, then in one of the queries have the Milestone column filtered to "Yes", then run the Group by function to get the MAX date, and then merge it back to the other unfiltered query, but I want to avoid building multiple queries if there is a more efficient solution.


Best regards,

1 ACCEPTED SOLUTION

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

10 REPLIES 10
Syndicate_Admin
Administrator
Administrator

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

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

 
imranamikhan
Helper V
Helper V

Thanks for the help too @watkinnc . Unfortunately I am seeing the same error.

 

I thought perhaps this is to do with my existing M-Query. So I created a "clean" version by recreating the Group By function:

 

= Table.Group(#"Added Custom7", {"II___Project"}, {{"MaxBaselineDate", each List.Max([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=nullable text, Milestone_ID=nullable text, Milestone_Level=nullable text, II___Project=nullable text, DIM_II_Project_Data_Direct_Extract.Percent_Complete=nullable number, IsRolloutMilestone=nullable number, WE_Finish_Date=date, WE_Baseline_Finish_Date=nullable date]}})

 

 

However, when I edit this by including the following syntax, I recieve the expected comma error.

 

 

{"MaxBaselineDate", each List.Max(Table.SelectRows(_, each [Milestone]="Yes")[WE_Baseline_Finish_Date], type nullable date},

 

 

Image4.PNG

 

This is the error in Advanced Editor mode - highlighting the Right Brace symbol:

 

imranamikhan_1-1629056147923.png

 

 

Looks like you have named two columns the same thing (MaxBaselineDate) and you are missing the ) to close the List.Max( ).

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

 



Image5.PNG

N.B – I changed the parameter from [Milestone]=”Yes” to [IsRolloutMilestone]=1

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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

 

@watkinnc 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,

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
mahoneypat
Microsoft
Microsoft

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

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


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:

 

Image3.PNG

 

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

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors