New Member
January 18, 2020
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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
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"
1 Guest(s)