Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm trying to manipulate data from Azure DevOps Boards in PowerBI. I'm used Power Query to load one table of Features from ADO and one table of Stories. Stories are children of Features (Parent Work Item ID on a story matches the Work Item ID on a feature). But some features don't have any child stories.
To make subsequent manipulation easier (because of our specific use case) after loading the tables I would like to automatically add a row to the Stories table for each Feature that doesn't have any matching Stories. (This would be a placeholder story for the feature, and the estimate would be taken from the feature estimate).
Can anyone tell me if this is possible please?
E.g.
Feature table as queried from ADO
Work Item ID | Title | Original Estimate | Other fields specific to features |
1 | Some feature | 100 |
|
2 | Some other feature | 200 |
|
3 | Another feature | 150 |
|
User Story table as queried from ADO
Work Item ID | Parent Work Item ID | Title | Original Estimate | Other fields specific to user stories |
101 | 1 | […] | 16 |
|
102 | 1 | […] | 67 |
|
103 | 2 | […] | 17 |
|
104 | 2 | […] | 81 |
|
105 | 2 | […] | 102 |
|
What I want the user story table to look like after automatically adding placeholder stories...
Work Item ID | Parent Work Item ID | Title | Original Estimate |
101 | 1 | […] | 16 |
102 | 1 | […] | 67 |
103 | 2 | […] | 17 |
104 | 2 | […] | 81 |
105 | 2 | […] | 102 |
106 | 3 | Placeholder | 150 |
Solved! Go to Solution.
Take a copy of the Feature table and do a left anti join with the UserStory table matching [Work Item ID] with [Parent Work Item ID]. Then rename [Work Item ID] to [Parent Work Item ID], add an index column named [Work Item ID] starting at Max(UserStory[Work Item ID]) + 1, and set the Title to "Placeholder".
let
Source = Feature,
#"Merged Queries" = Table.NestedJoin(Source, {"Work Item ID"}, UserStory, {"Parent Work Item ID"}, "UserStory", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Other", "UserStory"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Work Item ID", "Parent Work Item ID"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Work Item ID", List.Max(Story[Work Item ID]) + 1, 1, Int64.Type),
#"Renamed Title" = Table.TransformColumns(#"Added Index",{{"Title", each "Placholder", type text}})
in
#"Renamed Title"
You can now append this new table with the existing UserStory table to create a new combined table.
Take a copy of the Feature table and do a left anti join with the UserStory table matching [Work Item ID] with [Parent Work Item ID]. Then rename [Work Item ID] to [Parent Work Item ID], add an index column named [Work Item ID] starting at Max(UserStory[Work Item ID]) + 1, and set the Title to "Placeholder".
let
Source = Feature,
#"Merged Queries" = Table.NestedJoin(Source, {"Work Item ID"}, UserStory, {"Parent Work Item ID"}, "UserStory", JoinKind.LeftAnti),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"Other", "UserStory"}),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Work Item ID", "Parent Work Item ID"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns1", "Work Item ID", List.Max(Story[Work Item ID]) + 1, 1, Int64.Type),
#"Renamed Title" = Table.TransformColumns(#"Added Index",{{"Title", each "Placholder", type text}})
in
#"Renamed Title"
You can now append this new table with the existing UserStory table to create a new combined table.
That's great -- thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
101 | |
45 | |
19 | |
13 | |
11 |