Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
ebecerra
Microsoft Employee
Microsoft Employee

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:

 

ebecerra_0-1671055109288.png

 

 

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!

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
ebecerra
Microsoft Employee
Microsoft Employee

Thanks @Greg_Deckler that seemed to do the trick to get me going. 

CNENFRNL
Community Champion
Community Champion

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"

CNENFRNL_0-1671071320933.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Greg_Deckler
Super User
Super User

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.