I have a table with parent/child hierarchy where the values in the children in a column (BinNum) needs to be propagated or copied over to the parent. Is there a way to do this in Power Query or DAX?
Input and Result Table:
| Level | Sequence | BinNum | Level | Sequence | BinNum | |
|---|---|---|---|---|---|---|
| A10 | 1 | 0 | A10 | 1 | 50 | |
| A10 | 2 | 0 | A10 | 2 | 243 | |
| A10 | 3 | 0 | A10 | 3 | 100 | |
| A10 | 4 | 0 | A10 | 4 | 67 | |
| A10 | 5 | 0 | A10 | 5 | 98 | |
| A10-05 | 1 | 0 | A10-05 | 1 | 50 | |
| A10-05 | 2 | 0 | A10-05 | 2 | 243 | |
| A10-05 | 3 | 0 | A10-05 | 3 | 100 | |
| A10-05 | 4 | 0 | A10-05 | 4 | 67 | |
| A10-05 | 5 | 0 | A10-05 | 5 | 98 | |
| A10-05-01 | 1 | 50 | A10-05-01 | 1 | 50 | |
| A10-05-01 | 2 | 243 | A10-05-01 | 2 | 243 | |
| A10-05-01 | 3 | 100 | A10-05-01 | 3 | 100 | |
| A10-05-01 | 4 | 67 | A10-05-01 | 4 | 67 | |
| A10-05-01 | 5 | 98 | A10-05-01 | 5 | 98 | |
| A10-06 | 1 | 0 | A10-06 | 1 | 50 | |
| A10-06 | 2 | 0 | A10-06 | 2 | 243 | |
| A10-06 | 3 | 0 | A10-06 | 3 | 100 | |
| A10-06 | 4 | 0 | A10-06 | 4 | 67 | |
| A10-06 | 5 | 0 | A10-06 | 5 | 98 | |
| A10-06-01 | 1 | 50 | A10-06-01 | 1 | 50 | |
| A10-06-01 | 2 | 243 | A10-06-01 | 2 | 243 | |
| A10-06-01 | 3 | 100 | A10-06-01 | 3 | 100 | |
| A10-06-01 | 4 | 67 | A10-06-01 | 4 | 67 | |
| A10-06-01 | 5 | 98 | A10-06-01 | 5 | 98 |
You can try this, which is a simple single step merge. It assumes both Level and Sequence are part of the match
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Text.BeforeDelimiter([Level],"-")),
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([BinNum] <> 0)),
#"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Sequence", "Custom"}),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BinNum"}),
#"Merged Queries" = Table.NestedJoin(#"Removed Columns", {"Custom", "Sequence"}, #"Removed Duplicates", {"Custom", "Sequence"}, "Table1 (3)", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(#"Merged Queries", "Table1 (3)", {"BinNum"}, {"BinNum"}),
#"Sorted Rows" = Table.Sort(Expanded,{{"Index", Order.Ascending}}),
#"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Custom"})
in #"Removed Columns1"