Power Query Hierarchy Question

[cr731]

I'm importing a hierarchy table in Power Query and it looks like this,



[table="width: 500, class: grid"]
[tr]
[td]Lowest Level[/td]
[td]Level 1[/td]
[td]Level 2[/td]
[td]Level 3[/td]
[td]Level 4[/td]
[td]Level 5[/td]
[/tr]
[tr]
[td]ABC[/td]
[td]D[/td]
[td]E[/td]
[td]F[/td]
[td]ABC[/td]
[td][/td]
[/tr]
[tr]
[td]DEF[/td]
[td]T[/td]
[td]Y[/td]
[td]DEF[/td]
[td][/td]
[td][/td]
[/tr]
[/table]

So basically where the "Lowest Level" column is the leaf member, and then the hierarchy appears to the right, each level as a column.

I'm looking to do two things with this,

1. I need to add a column showing what level the leaf is. In the example above, ABC is a level 4, DEF is a level 3.

2. I want to fill all of the blanks in the levels after the leaf with whatever the leaf member is, so there are no blanks. For instance, above, the level 5 cell in the row would say 'ABC' and the levels 4 and 5 cells of the DEF row would be DEF.

What would be the most efficient way to do this?

Thanks

 

[ImkeF]

This code should do:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    DemoteHeaders = Table.DemoteHeaders(Source),
    Transpose = Table.Transpose(DemoteHeaders),
    FillDown = Table.FillDown(Transpose,Table.ColumnNames(Transpose)),
    UnpivotOthers = Table.UnpivotOtherColumns(Transpose, {"Column1"}, "Attribute", "Value"),
    Lookup = Table.SelectRows(UnpivotOthers, each ([Column1] <> "Lowest Level")),
    TransposeBack = Table.Transpose(FillDown),
    Data = Table.PromoteHeaders(TransposeBack),
    MergeDataLookup = Table.NestedJoin(Data,{"Lowest Level"},Lookup,{"Value"},"NewColumn",JoinKind.LeftOuter),
    Expand = Table.ExpandTableColumn(MergeDataLookup, "NewColumn", {"Column1"}, {"Level"})
in
    Expand

But there probably are more elegant ways to do this. So let's cross our fingers that someone else steps in as well on this

 

[billszysz]

Not better...just different

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    Level = Table.AddColumn(Source, "Level", (x) => Table.ColumnNames(Source){List.Count(List.RemoveNulls(Record.ToList(x)))-1}),
    Headers = Table.ColumnNames(Level),
    Tbl = Table.AddColumn(Level, "Custom", (x) => Table.Pivot(Table.FillDown(Record.ToTable(x),{"Value"}), Headers , "Name", "Value")),
    RemoveOthCol = Table.SelectColumns(Tbl,{"Custom"}),
    Expand = Table.ExpandTableColumn(RemoveOthCol, "Custom", Headers, Headers)
in
    Expand

Cheers