Any possible combination in path column to create a hierachy

[fellin]

Hey all,

i have following problem and can't figure it out how to do this: I have a column with different paths, which include different Departments: E.g. || Department 1 || Dep. 2 || Dep. 3

My goal ist to create a hierachy. Thereore i would need 3 rows in a table:

Level 0	Level 1	Level 2
Department 1
Department 1	Dep. 2
Department 1	Dep. 2	Dep. 3

Unfortunately, i don't have every level as sperate path. So I want to create them out of a longer path. Is there a convenient to this in PowerBI?

Thanks in advance

[v-jingzhang · Accepted solution]

Hi @fellin 

Please try the following M codes. You can download the attached PBIX file to see the detailed steps in Power Query Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqqlRcEktSCwqyU3NK1EwVIDw9RSM4CxjOMtEKVYHXYcpXNYMzjLHos4CLmupFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Path = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Path", type text}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Levels", each List.Count(Text.PositionOf([Path],"||",Occurrence.All))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "LevelOrder", each List.Generate(() => [Levels], each _ > 0, each _ - 1)),
    #"Expanded Level" = Table.ExpandListColumn(#"Added Custom2", "LevelOrder"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Level", "Path", Splitter.SplitTextByDelimiter("||", QuoteStyle.Csv), {"Path.1", "Path.2", "Path.3", "Path.4", "Path.5"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Path.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Path.2", "Level 0"},{"Path.3", "Level 1"},{"Path.4", "Level 2"},{"Path.5", "Level 3"}}),
    Custom2 = Table.ReplaceValue(#"Renamed Columns",each [Level 1],each if [LevelOrder] <= 1 and [Levels] > 1 then null else [Level 1],Replacer.ReplaceValue,{"Level 1"}),
    Custom1 = Table.ReplaceValue(Custom2,each [Level 2],each if [LevelOrder] <= 2 and [Levels] > 2 then null else [Level 2],Replacer.ReplaceValue,{"Level 2"}),
    Custom3 = Table.ReplaceValue(Custom1,each [Level 3],each if [LevelOrder] <= 3 and [Levels] > 3 then null else [Level 3],Replacer.ReplaceValue,{"Level 3"})
in
    Custom3

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.