Forum Discussion

H_insight's avatar
H_insight
Helper V
4 years ago
Solved

Hierarchy help!

Hi All,

 

My dataset has two columns (Divison & Department). The normal sequence is from Division to Department (so each Div have multi Dep). I am trying to get a sub-index for the department in power query but with no luck. The expected outcome is below:

 

 

Sample pbix file.

 

Thanks in advance.

Need Help

4 Replies

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        4 years ago

        For fun only, a "Russian nesting doll" solution in PQ,

         

        let
    NestingDoll = (tbl as table, keyCol as text) => Table.AddIndexColumn(Table.Group(tbl, keyCol, {"ar", each _}), keyCol & "-ID", 1, 1),

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXI0VIrVwWQagZlOQKaTISGmETrTGch0NiTERFJrrBQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Division = _t, Department = _t]),

    Layer1 = NestingDoll(Source, "Division"),
    Layer2 = Table.TransformColumns(Layer1, {"ar", each NestingDoll(_, "Department")}),
    #"Expanded ar" = Table.ExpandTableColumn(Layer2, "ar", {"ar", "Department-ID"}),
    #"Expanded ar.1" = Table.ExpandTableColumn(#"Expanded ar", "ar", {"Department"})
in
    #"Expanded ar.1"