Forum Discussion

H_insight's avatar
H_insight
Icon for Helper V rankHelper 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 po...
Need Help
H_insight's avatar
H_insight
Icon for Helper V rankHelper V
4 years ago

Thanks for your help CNENFRNL 

Agree, excel would have been easier. 👍

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity 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"