Forum Discussion
Microsoft EmployeeAppending rows from one table to another based on condition
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 |
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.
2 Replies
- AlexisOlson
Super User
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.
MatMThat's great -- thanks!