Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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).
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”.
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,
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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]})
@mahoneypat - Not sure why my post was removed and then reposted from "Syndicate Admin" - this post is from me just to avoid any confusion
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},
This is the error in Advanced Editor mode - highlighting the Right Brace symbol:
Looks like you have named two columns the same thing (MaxBaselineDate) and you are missing the ) to close the List.Max( ).
Pat
To learn more about Power BI, follow me on Twitter or subscribe 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]})
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
To learn more about Power BI, follow me on Twitter or subscribe 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,
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
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
To learn more about Power BI, follow me on Twitter or subscribe 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:
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]}})