Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
allan_t
New Member

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

ProjectNameMilestoneName
AM1
AM2
AM3
BM1
BM3
CM2
CM3
... 

 

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

ProjectNameMissingMilestone
AM4
AM5
AM6
etc 

 

Thanks in advance for any advice.  

1 ACCEPTED SOLUTION
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_1-1726194622497.png

 

 

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.

 

View solution in original post

3 REPLIES 3
v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_1-1726194622497.png

 

 

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
Super User
Super User

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  

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors