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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ebecerra
Employee
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.


@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
ebecerra
Employee
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.


@ 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors