let
// The worksheet table name is Data
Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
PhraseSub = "Sub Assbly",
PhrasePart = "Part",
ColumnNames = Table.ColumnNames(Source),
ChangeType = Table.TransformColumnTypes(Source, List.Transform(ColumnNames, each {_, type text})),
Root = Record.Field(ChangeType{0}, ColumnNames{1}),
Subs = Table.RenameColumns(Table.SelectRows(ChangeType, each Text.StartsWith(Record.Field(_, ColumnNames{0}), PhraseSub) and Record.Field(_, ColumnNames{1}) <> null), {ColumnNames{0}, "ID"}),
Tops = Table.TransformColumns(Subs, {"ID", each Root, type text}),
Parts = Table.SelectRows(ChangeType, each Text.StartsWith(Record.Field(_, ColumnNames{0}), "Part") or (Text.StartsWith(Record.Field(_, ColumnNames{0}), PhraseSub) and Record.Field(_, ColumnNames{1}) = null)),
GroupSubs = Table.FillDown(Table.AddColumn(Parts, "Col", each if Record.Field(_, ColumnNames{0}) = PhrasePart then null else Record.Field(_, ColumnNames{0})), {"Col"}),
RemoveNulls = Table.SelectRows(GroupSubs, each (Record.Field(_, ColumnNames{1}) <> null)),
SubIDs = Table.SelectColumns(Table.AddColumn(RemoveNulls, "ID", each Text.Replace([Col], PhraseSub & " ", ""), type text),{"ID", ColumnNames{1}}),
AddTops = Table.Combine({Tops, SubIDs}),
ReorderColumns = Table.InsertRows(Table.ReorderColumns(Table.RenameColumns(AddTops,{{ColumnNames{1}, "Children"}}), {"Children", "ID"}), 0, {[Children = Root, ID = null]}),
fnAddParent = (Table as table, ColumnName as text, i as number) =>
let
ForeignKey = "Parent" & Number.ToText(i),
TableWithParent = Table.ExpandTableColumn(Table.NestedJoin(Table, ColumnName, Table, "Children", "Join", JoinKind.LeftOuter), "Join", {"ID"}, {ForeignKey}),
Result = if Table.RowCount(Table.SelectRows(TableWithParent, each (Record.Field(_, ForeignKey) <> null))) = 0 then
Table
else
@fnAddParent(TableWithParent, ForeignKey, i + 1)
in
Result,
ParentChild = fnAddParent((ReorderColumns), "ID", 0),
FinalColumnNames = List.Sort(Table.ColumnNames(ParentChild), Order.Descending),
ReverseColumns = Table.ReorderColumns(ParentChild, FinalColumnNames),
AlignNulls = Table.FromRecords(List.Transform(List.Transform(Table.ToRows(ReverseColumns), List.RemoveNulls), each Record.FromList(_, List.FirstN(Table.ColumnNames(ReverseColumns), List.Count(_)))), FinalColumnNames, MissingField.UseNull),
Sort = Table.Sort(AlignNulls, List.Transform(FinalColumnNames, each {_, Order.Ascending})),
EmptyDups = List.Accumulate(
{1..(List.Count(FinalColumnNames) - 1)},
Sort,
(state, current) =>
let
Result = Table.FromRecords(Table.TransformRows(state,
(r) => Record.TransformFields(r,
{FinalColumnNames{current - 1}, each if Record.Field(r, FinalColumnNames{current}) = null then _ else null})))
in
Result
),
Result = Table.RenameColumns(EmptyDups, List.Zip({FinalColumnNames, List.Transform({1..Table.ColumnCount(EmptyDups)}, each "Assembly" & Number.ToText(_))}))
in
Result
