Hierarchy help!

[H_insight]

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.

[KNP]

Here's a Power Query option that may work for you. See attached PBIX. 

[CNENFRNL · Accepted solution]

Excel worksheet formula is powerful enough to solve this simple question,

[H_insight]

Thanks for your help @CNENFRNL 

Agree, excel would have been easier. 👍

[CNENFRNL]

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"