The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |