Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
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.
Solved! Go to Solution.
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.
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 ?
@lbendlin Thanks for the suggestion. I've not heard of or tried List.Contains so I'll research that function and see if I can get it to work. Thx
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
70 | |
63 | |
40 | |
28 | |
16 |