How to create parent-child data structure to build a ragged hierarchy?

[janstrauss1]

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!

[SurfingData]

https://www.thebiccountant.com/2017/02/14/dynamically-flatten-parent-child-hierarchies-in-dax-and-po...

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.

[janstrauss1]

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

[janstrauss1]

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.

[some_bih]

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

[janstrauss1]

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:

[some_bih]

Hi @janstrauss1  I see the issue. I will take a look and share findings.

[janstrauss1]

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"

[some_bih]

Hi @janstrauss1 I really like you contribution - it is great 🙂

[some_bih]

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.

[some_bih]

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 

[janstrauss1]

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.

[some_bih]

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)

[janstrauss1]

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

[some_bih]

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