Forum Discussion
Appending rows from one table to another based on condition
- 4 years ago
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!