Forum Discussion

MatM's avatar
MatM
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Appending 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 W...
  • AlexisOlson's avatar
    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.