Query design suggestions, evaluating projects and potential missing milestones

[allan_t]

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.  

[v-xinruzhu-msft · Accepted 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.

[lbendlin]

Sounds good.  What have you tried and where are you stuck?  Have you used List.Contains  ?

[allan_t]

@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