Forum Discussion

ebecerra's avatar
ebecerra
Microsoft Employee
3 years ago
Solved

Recursively split string into columns

I want to create some sort of "tree map visual" from some data stored in a DB, let me explain a little bit. I have the following sample data:   MainPage MainPage/Page1 MainPage/Page1/SubPage Mai...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    ebecerra You can do a split and then Unpivot other rows in Power Query or you can use SUBSTITUE(MAX('Table'[Column]), "/", "|"). That will turn it into a path and you can use the PATH functions like PATHLENGTH.

CNENFRNL's avatar
CNENFRNL
Community Champion
3 years ago

You can easily map the path this way,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k3MzAtITE9VitVBcPRBhCEWIf3g0iTsqo0whYxBqgtAPP+SjNQiGMcxLyU43z8PQrvlF5Vk6IfmlWTm6LvkV6XmFfunhWTmphbjsLwAYlUsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Path = _t]),
    #"Split Path" = Table.TransformColumns(Source, {"Path", each let l1=Text.Split(_,"/"), l2={null} & List.RemoveLastN(l1) in Table.FromRows(List.Zip({l1,l2,{1..List.Count(l1)}}),{"Dir","Parent Dir","Depth"})}),
    #"Expanded Path" = Table.Distinct(Table.ExpandTableColumn(#"Split Path", "Path", {"Dir","Parent Dir","Depth"}))
in
    #"Expanded Path"