Query design suggestions, evaluating projects and potential missing milestones

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.