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:
Thanks in advance.
1 ACCEPTED SOLUTION
Excel worksheet formula is powerful enough to solve this simple question,
Here's a Power Query option that may work for you. See attached PBIX.
| Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
| If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. |
Proud to be a Super User!
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"
