Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Dear Power Bi Community,
I’m trying to build a report with a hierarchy slicer that allows for filtering and aggregating values based on different levels of a ragged hierarchy.
The hierarchy that I want to use for slicing is the German Classification of Economic Activities issue 2008 (WZ 2008) but there are similar International (ISIC) and European (NACE) Classification Standards that work the same way. The WZ 2008 classification looks like this:
Ultimately, I’d like to have a slicer in my report that looks similar and allows to filter and aggregate values from a corresponding fact table (containing numbers of employees in the selected industries).
The classification hierarchy consists of five levels with the length of Classification IDs corresponding to the different levels:
Level | Level description | Classification code |
1 | Section | A - U |
2 | Division | 01 - 99 |
3 | Group | 01.1 - 99.0 |
4 | Class | 01.11 - 99.00 |
5 | Subclass | 01.11.0 - 99.00.0 |
It can be viewed interactively under the following link. One can also download the classification in different file formats (XML, CSV, XLS) under the following link.
For instance the downloadable XLS file show the classification codes from all different hierarchy levels of the classification in one single column as well as the corresponding levels and title the adjacent columns like the following:
Code WZ 2008 | Level | Title |
A | 1 | Agriculture, forestry and fishing |
01 | 2 | Crop and animal production, hunting and related service activities |
01.1 | 3 | Growing of non-perennial crops |
01.11 | 4 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 5 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 4 | Growing of rice |
01.12.0 | 5 | Growing of rice |
01.13 | 4 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 5 | Growing of vegetables and melons |
01.13.2 | 5 | Growing of potatoes and other roots and tubers |
Now, as I understand from my research so far, I need to create a table that has a parent-child relationship to set up a hierarchy slicer like in the following example table (that I created manually):
Code | ParentCode | Title |
A | Agriculture, forestry and fishing | |
01 | A | Crop and animal production, hunting and related service activities |
01.1 | 01 | Growing of non-perennial crops |
01.11 | 01.1 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 01.11 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 01.1 | Growing of rice |
01.12.0 | 01.12 | Growing of rice |
01.13 | 01.1 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 01.13 | Growing of vegetables and melons |
01.13.2 | 01.13 | Growing of potatoes and other roots and tubers |
However, I didn’t manage to create a parent-child data table in an automated way that I can use as dimension table and all posts that I’ve read so far start with the assumption that one already has a parent-child table available. Yet, there are 1835 row items in my classification, so obviously I don’t want to do this manually.
Thus my question is if anyone does have a good Power BI(ish) way of doing this?
I’ve found some inspiration on StackOverflow using R that I didn’t try, yet.
With a parent-child relationship table in hand, I could then use DAX with PATH() and PATHITEM() to build a hierarchy as described in Kasper de Jonge's blog post.
Many thanks in advance for your help!
If you are still looking for a way to do this. I had luck with this as my starting place in power bi.
You could also go the python route and use networkx to make node and edge lists which you then ingest into power bi using your prefered method.
Sorry I merely skimmed your post but was saw you mention parent child and so I figured I would throw this out there. SOrry if it is not close enough to your use case.
Hi @SurfingData,
many thanks for your reply!
That's a very interesting blog post and very helpful in my downstream analysis once I've created my parent-chield hierarchy correctly. Unfortunately, at the moment I'm still unable to programmatically convert my hierarchically structured data from an input like this:
Key | HierarchyLevel | Title |
A | 1 | Agriculture, forestry and fishing |
01 | 2 | Crop and animal production, hunting and related service activities |
01.1 | 3 | Growing of non-perennial crops |
01.11 | 4 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 5 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 4 | Growing of rice |
01.12.0 | 5 | Growing of rice |
01.13 | 4 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 5 | Growing of vegetables and melons |
01.13.2 | 5 | Growing of potatoes and other roots and tubers |
To a parent-child relationship table of something like the following:
Key | ParentKey | Title |
A | Agriculture, forestry and fishing | |
01 | A | Crop and animal production, hunting and related service activities |
01.1 | 01 | Growing of non-perennial crops |
01.11 | 01.1 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 01.11 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 01.1 | Growing of rice |
01.12.0 | 01.12 | Growing of rice |
01.13 | 01.1 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 01.13 | Growing of vegetables and melons |
01.13.2 | 01.13 | Growing of potatoes and other roots and tubers |
I appreciate any feedback to solve this!
P.S.: The example input data can be downloaded as .csv file from my GitHub at ReprexDimWZ08structure.csv
Hi @some_bih,
thanks for the hint to the Curbal's video on connecting Eurostat data with Power BI!
That's really helpful and great to know but unfortunately doesn't help with my issue.
Just to clarify again what my problem is.
I've got the following input table:
Code | Level | Title |
A | 1 | Agriculture, forestry and fishing |
01 | 2 | Crop and animal production, hunting and related service activities |
01.1 | 3 | Growing of non-perennial crops |
01.11 | 4 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 5 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 4 | Growing of rice |
01.12.0 | 5 | Growing of rice |
01.13 | 4 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 5 | Growing of vegetables and melons |
01.13.2 | 5 | Growing of potatoes and other roots and tubers |
To this table I'd like to add a ParentCode column containing information on the parent. This can be done using information in the Code column using length and order of code digits. The final output that I'm after is the following:
Code | ParentCode | Title |
A | Agriculture, forestry and fishing | |
01 | A | Crop and animal production, hunting and related service activities |
01.1 | 01 | Growing of non-perennial crops |
01.11 | 01.1 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 01.11 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 01.1 | Growing of rice |
01.12.0 | 01.12 | Growing of rice |
01.13 | 01.1 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 01.13 | Growing of vegetables and melons |
01.13.2 | 01.13 | Growing of potatoes and other roots and tubers |
You should be able to find all example files at https://github.com/janstrauss1/PowerBI_data including an example PowerBI report.
Hi @janstrauss1
In PowerQuery Bi use code below for file WZ_2008-EN-2023-11-09-Structure.csv, change location and output should be fine
let
Source = Csv.Document(File.Contents("<Your_Location_of_file>\WZ_2008-EN-2023-11-09-Structure.csv"),[Delimiter=";", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> "")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code WZ 2008", type text}, {"Level", Int64.Type}, {"Title", type text}, {"Unit(s) of measure", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "ParentCode", each try #"Added Index" [Code WZ 2008] {[Index]-1} otherwise null, type text),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Code WZ 2008", "ParentCode", "Level", "Title", "Unit(s) of measure", "Index"})
in
#"Reordered Columns"
PQ output
Proud to be a Super User!
Hi @some_bih,
Sorry, I think we got excited too soon!
I just noticed a major bug in our solution when building my ragged hierarchy slicer!
Using your approach via index creates wrong parent codes in the higher levels as I've marked in your output image that you send earlier:
I think one of the main problems is that we do not take into account that the it's an uneven (ragged) hierarchy...
Do you have any idea how to solve this?
I've updated the reproducible example (ReprEx) and you can find a .pbix file and associated data tables in my github at https://github.com/janstrauss1/PowerBI_data/tree/main/PowerBI_ReprEx.
As shown in the ReprEx, I'd like to achieve some filtering via a hierarchy slicer like the following:
Hi @janstrauss1 I see the issue. I will take a look and share findings.
Proud to be a Super User!
Hi @some_bih,
That's works great, many thanks!
However, there remains a small problem with the sections of the classification system (letters A - U) that don't have any parent. Using the index as you do, it only works for the first section A.
However, I can simply solve this by adding a some else if statements in my Power Query to replace the section letters:
let
Source = Csv.Document(File.Contents("<Your_Location_of_file>\WZ2008\WZ_2008-EN-2023-11-09-Structure.csv"),[Delimiter=";", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column2] <> "")),
#"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Code WZ 2008", type text}, {"Level", Int64.Type}, {"Title", type text}, {"Unit(s) of measure", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type1", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "ParentCode", each try #"Added Index" [Code WZ 2008] {[Index]-1} otherwise null, type text),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "ParentCode2",
each if Text.Contains([Code WZ 2008], "A") then null
else if Text.Contains([Code WZ 2008], "B") then null
else if Text.Contains([Code WZ 2008], "C") then null
else if Text.Contains([Code WZ 2008], "D") then null
else if Text.Contains([Code WZ 2008], "E") then null
else if Text.Contains([Code WZ 2008], "F") then null
else if Text.Contains([Code WZ 2008], "G") then null
else if Text.Contains([Code WZ 2008], "H") then null
else if Text.Contains([Code WZ 2008], "I") then null
else if Text.Contains([Code WZ 2008], "J") then null
else if Text.Contains([Code WZ 2008], "K") then null
else if Text.Contains([Code WZ 2008], "L") then null
else if Text.Contains([Code WZ 2008], "M") then null
else if Text.Contains([Code WZ 2008], "N") then null
else if Text.Contains([Code WZ 2008], "O") then null
else if Text.Contains([Code WZ 2008], "P") then null
else if Text.Contains([Code WZ 2008], "Q") then null
else if Text.Contains([Code WZ 2008], "R") then null
else if Text.Contains([Code WZ 2008], "S") then null
else if Text.Contains([Code WZ 2008], "T") then null
else if Text.Contains([Code WZ 2008], "U") then null
else [ParentCode]),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Index", "Code WZ 2008", "ParentCode", "ParentCode2", "Level", "Title", "Unit(s) of measure"})
in
#"Reordered Columns"
Hi @janstrauss1 I really like you contribution - it is great 🙂
Proud to be a Super User!
Hi @janstrauss1 I glad we cleared up something today 🙂
I will take a look git as currenty I am not available and let you know.
Proud to be a Super User!
Hi @janstrauss1 sessions expaired, so not so familiar what you want to do.
I am not sure are you familiar with drill mode in Power BI, please check link
https://learn.microsoft.com/en-us/power-bi/consumer/end-user-drill
Proud to be a Super User!
Hi @some_bih,
Thanks for your reply! I'm familiar with drill mode but my issue is further upstream during data preparation.
Just to clarify what I want to do and where my issue is.
I've got some hierarchical classification data where parent and child relationships are organised in a single column like in the following example table:
Code | Level | Title |
A | 1 | Agriculture, forestry and fishing |
01 | 2 | Crop and animal production, hunting and related service activities |
01.1 | 3 | Growing of non-perennial crops |
01.11 | 4 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 5 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 4 | Growing of rice |
01.12.0 | 5 | Growing of rice |
01.13 | 4 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 5 | Growing of vegetables and melons |
01.13.2 | 5 | Growing of potatoes and other roots and tubers |
Now, I'd like convert this table into a parent-child relationship table like the following example in an automated way (to be ably apply this on a large table with 999+ rows):
Code | ParentCode | Title |
A | Agriculture, forestry and fishing | |
01 | A | Crop and animal production, hunting and related service activities |
01.1 | 01 | Growing of non-perennial crops |
01.11 | 01.1 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.11.0 | 01.11 | Growing of cereals (except rice), leguminous crops and oil seeds |
01.12 | 01.1 | Growing of rice |
01.12.0 | 01.12 | Growing of rice |
01.13 | 01.1 | Growing of vegetables and melons, roots and tubers |
01.13.1 | 01.13 | Growing of vegetables and melons |
01.13.2 | 01.13 | Growing of potatoes and other roots and tubers |
Thus, I'd be glad for any help to accomplish this.
Hi @janstrauss1 I deal in my work with ESA and NACE so I am bit familiar with your issues.
In your case there should be some official documentation to show/ somehow explained structure of details like, with two digit / four digits means
like, A means Agriculture, forestry and fishing
What I still do not understand, what is your issue in part
I've got some hierarchical classification data where parent and child relationships are organised in a single column like in the following example table
Question: what is your input - pure pdf file or there is some csv ... (I could not see from your link as session expaired)
Proud to be a Super User!
Hi @some_bih,
I understand the structure of the classification (single letter = level 1, two digits = level 2, 01.1 child of 01...)
Hope the following link to the classification server works: https://www.klassifikationsserver.de.
You should be able to click yourself through to different classifications (including the Classification of Economic Activities, issue 2008 (WZ2008) I'm currently working with).
On these pages you can download official documentation and input files in different formats (.pdf, .csv, .xml, .xls). I'm usually working with csv or xls files.
Yet, the example data that I provide give a fully reproducible example.
To provide you with the full data tables, you can also now download them in different file formats from my github
Hi @janstrauss1
I could not see github (currently), I will check it later. Still, I am not sure did you have chance to see how Curbal use API Eurostat to connect it in Power BI.
In google print "curbal api eurostat" for idea / video
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |