I have two tables that i need to merge using powerquery. I have two parent/child columns in each table which i can use to merge the tables
However i need to create an index number re-starting the count for each new parent so i can create a unique concatenation
Parent Child Index needed
1234 AAA 1
1234 AAA 2
1234 BBB 1
1234 BBB 2
2345 CCC 1
2345 CCC 2
2345 CCC 3
Hi Percy,
Steps:
1. Grouping by Parent and Child, return All Rows (no aggregation)
2. Add a new column with AddIndexColumn formula, referring to the newly added column tables.
3. Remove other columns except last one.
4. Expand.
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Parent", Int64.Type}, {"Child", type text}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Parent", "Child"}, {{"All", each _, type table [Parent=number, Child=text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Parent", "Child", "Index"}, {"Parent", "Child", "Index"})
in
#"Expanded Custom"
