Forum Discussion
Query design suggestions, evaluating projects and potential missing milestones
I have a use case I'm sure Power Query can help me with, but I'm not sure how to do it. I've done some YouTube searching with no luck. I'm hoping an expert can share some advice on how to do this in Power Query.
Use Case - identify projects that are missing required milestones
- I have a table that contains a list of milestones for each project.
- Each project is supposed to have 10 milestones (M1, M2, M3...M10)
- Some projects are missing 1 or more milestones (eg. Project A only has M1, M2, M3 and is missing M4 to M10)
Sample Input Table
| ProjectName | MilestoneName |
| A | M1 |
| A | M2 |
| A | M3 |
| B | M1 |
| B | M3 |
| C | M2 |
| C | M3 |
| ... |
In Power Query, I've been able to use Group By to count the number of unique milestones for each project. This helps me identify which project is missing required milestones (eg. Project has 3 milestones which is less than 10), but it doesn't tell me which specific milestones are missing.
Ideally, I'd like the query to generate a table that lists the ProjectName and the MissingMilestone(s).
Sample Output Table
| ProjectName | MissingMilestone |
| A | M4 |
| A | M5 |
| A | M6 |
| etc |
Thanks in advance for any advice.
Hi,
Thanks for the solution lbendlin offered and i want to offer some more information for user to refer to.
hello allan_t
You can create a vblank query and input the following code to advanved editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfI1VIrVgTKNEExjMNMJocAJIeqMUOsMFY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, MilestoneName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectName", type text}, {"MilestoneName", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectName"}, {{"Data", each _, type table [ProjectName=nullable text, MilestoneName=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Difference", each let a=List.Transform(List.Numbers(1,10,1),each "M"&Text.From(_)), b=List.Difference(a,[Data][MilestoneName]) in b), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded Difference" = Table.ExpandListColumn(#"Removed Columns", "Difference") in #"Expanded Difference"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi,
Thanks for the solution lbendlin offered and i want to offer some more information for user to refer to.
hello allan_t
You can create a vblank query and input the following code to advanved editor in power query.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfI1VIrVgTKNEExjMNMJocAJIeqMUOsMFY0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectName = _t, MilestoneName = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ProjectName", type text}, {"MilestoneName", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ProjectName"}, {{"Data", each _, type table [ProjectName=nullable text, MilestoneName=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Difference", each let a=List.Transform(List.Numbers(1,10,1),each "M"&Text.From(_)), b=List.Difference(a,[Data][MilestoneName]) in b), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}), #"Expanded Difference" = Table.ExpandListColumn(#"Removed Columns", "Difference") in #"Expanded Difference"Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Sounds good. What have you tried and where are you stuck? Have you used List.Contains ?
