Forum Discussion
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
MainPage/Page2
MainPage/Page3/Subpage/OtherSubpage/AndSoOn/AndSoForth/Until/DozensOfTimes
MainPage/Page1/Subpage2
It is a list of "Paths" of dynamic length and I have no way of knowing the max levels that a path can have. All of them start with MainPage. I would like to visualize those paths in a way in PBI (ideally in hierarchical way) to kind of view a map of the paths to see where things are more clustered than others. I have split text in PBI before into columns, but this problem would require me to keep splitting an unknown number of times until the longest path has been completely processed. Ideally I would like to get something like this:
What would be the best way to parse those paths to create some useful visual for how those pages are organized? What would be a good visualization to aid with this? Or does this sounds like a problem that PBI can't solve and I should look into some other programatic way to get that visual?
Thanks in advance!
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.
3 Replies
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.
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"Thanks Greg_Deckler that seemed to do the trick to get me going.
