Create hierarchy columns from one column

[kormosb]

Hi,

I would like to create a hierarchy from the below table in Power BI to be able to use it in a matrix visual:

Hierarchy	2017	2018
1. Current account, net (1.1.+1.2.+1.3.)	2 937	-21
1.1. Goods and Services, net	9 153	6 000
1.1.1. Goods, net	1 909	-1 688
1.1.1.A Export	85 555	88 659
1.1.1.B Import	83 646	90 346
1.1.2. Services, net	7 244	7 687
1.1.2.A Export	23 862	25 359
1.1.2.B Import	16 618	17 671
1.2. Primary income, net	-5 066	-5 195
1.2.1. Compensation of employees, net	2 624	2 322
1.2.2. Investment income, net	-8 911	-8 716
1.2.2.1. Direct investment income, net	-7 392	-7 402
1.2.2.2. Portfolio investment income, net	-1 469	-1 315
1.2.2.3. Other investment income, net	-238	-192
1.2.2.4. Reserve assets, net	187	192
1.2.3. Other primary income, net	1 221	1 200
1.2.3.1. of which: EU transfers	1 221	1 200
1.3. Secondary income, net	-1 151	-826
1.3.1. of which: EU transfers	103	487
2. Capital account, net	1 075	2 996
2.1. of which: EU transfers	1 353	2 188
3. Financial account (net assets) (3.1+3.2+3.3+3.4+3.5)	1 880	1 168
3.1. Direct investment (net assets)	-2 044	-2 781
3.1.1. Abroad (net assets)	1 079	4 267
3.1.1.1. Equity (net assets)	215	4 819
3.1.1.1.1. Equity other than reinvestment of earnings (net assets)	-855	4 015
3.1.1.1.2. Reinvestment of earnings (net assets)	1 070	804
3.1.1.2. Debt instruments (net assets)	864	-552
3.1.1.2.1. Assets	867	144
3.1.1.2.2. Liabilities	4	696
3.1.2 In Hungary (net liabilities)	3 123	7 048
3.1.2.1. Equity (net liabilities)	8 458	6 487
3.1.2.1.1. Equity other than reinvestment of earnings (net liabilities)	2 320	845
3.1.2.1.2. Reinvestment of earnings (net liabilities)	6 137	5 641
3.1.2.2. Debt instruments (net liabilities)	-5 335	561
3.1.2.2.1. Assets	3 927	1 078
3.1.2.2.2. Liabilies	-1 408	1 639
3.2. Portfolio investment (net assets)	3 774	-185
3.2.k Assets	1 916	-140
3.2.t Liabilities	-1 857	44
3.3. Financial derivatives (other than reserves), net assets	-1 050	-933
3.3.k Assets	-4 616	-4 432
3.3.t Liabilities	-3 567	-3 499
3.4. Other investment  (net assets)	1 172	1 469
3.4.k Assets	1 753	2 437
3.4.t  Liabilities	580	969
3.5. Reserve assets	28	3 598

I think I have to divide the hierarchies to different columns, so I have the following:

First hierarchy	Second hierarchy	Third hierarchy	Fourth hierarchy
1. Current account, net	1.1. Goods and Services, net	1.1.1. Goods, net	1.1.1.A Export
1. Current account, net	1.1. Goods and Services, net	1.1.1. Goods, net	1.1.1.B Import
1. Current account, net	1.1. Goods and Services, net	1.1.2. Services, net	1.1.2.A Export
1. Current account, net	1.1. Goods and Services, net	1.1.2. Services, net	1.1.2.B Import
...	...	...	...

I wonder if I can do it in Power BI with DAX, or should I do something in the Power Query/Excel?

Thanks for in advance,

Benjamin

[amitchandak]

@kormosb , is it display of matrix or table or hierarchy ?

For matrix, display refer

https://www.burningsuit.co.uk/blog/2019/04/7-secrets-of-the-matrix-visual/

for Hierarchy refer
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://community.powerbi.com/t5/Desktop/How-to-create-Hierarchy/td-p/479913
https://stoneridgesoftware.com/creating-hierarchies-in-power-bi/
https://guyinacube.com/2019/08/07/how-to-create-and-use-a-power-bi-hierarchy/

[kormosb]

Hi,

Thanks for the advices. I think I need to solve it with parent-child hierarchy with PATH and PATHITEM functions, can you help with this?

Link to the PBI file.

Ben

[v-yingjl · Accepted solution]

Hi @kormosb ,

Sorry for replying late. You should note that PATH() and PATHITEM() function need two differend columns as parameters. In you sample file, you only have a single column which needs a hierarchy so these functions are not suitable.

I have created an index column to mark each hierarchy from 0 to 5:

Index = 
IF (
    CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 1 ), "M" ),
    0,
    IF (
        CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 3 ), " " ),
        1,
        IF (
            CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 5 ), " " ),
            2,
            IF (
                CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 7 ), " " ),
                3,
                IF ( CONTAINSSTRING ( LEFT ( 'Table'[Hierarchy], 9 ), " " ), 4, 5 )
            )
        )
    )
)

Then created four hierarchies column using containstring() and left functions with the index column to lookup strings in the column. Under this logic, if the row just has three or less hierarchies, it will show the previous hierarchy in the next hierarchy column:

First hierarchy = 
IF(
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1"),
    "1. Current account,net",
    IF(
        CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2"),
        "2. Capital account, net",
        IF(
            CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3"),
            "3. Financial account (net assets)",
            "Memo item"
        )
    )
)

Second hierarchy = 
SWITCH(
    TRUE(),
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "1. Current account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.1"), "1.1 Goods and Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.2"), "1.2 Primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.3"), "1.3 Secondary income, net",
    //end of 1

    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "2. Captital account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"2.1"), "2.1 of which: EU transfers",
    //end of 2
    
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "3. Financial account (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.1"), "3.1 Direct investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.2"), "3.2 Portfolio investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.3"), "3.3 Financial derivatives (other than reserves) investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.4"), "3.4 Other investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.5"), "3.5 Reserve assets",
    //end of 3

    "Memo item"
)

Third hierarchy = 
SWITCH(
    TRUE(),
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "1. Current account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.1") && [Index] = 2, "1.1 Goods and Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.1.1"), "1.1.1 Goods, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.1.2"), "1.1.2 Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.2") && [Index] = 2, "1.2 Primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.2.1"), "1.2.1 Compensation of employees, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.2.2"), "1.2.2 Investment income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.2.3"), "1.2.3 Other primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.3") && [Index] = 2, "1.3 Secondary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.3.1"), "1.3.1 of which:EU transfers",
    //end of 1


    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "2. Captital account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"2.1") && [Index] = 2, "2.1 of which:EU transfers",
    //end of 2


    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "3. Financial account (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.1") && [Index] = 2, "3.1 Direct investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.1.1"), "3.1.1 Abroad (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.1.2"), "3.1.2 In Hungary (net liabilities)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.2") && [Index] = 2, "3.2 Portfolio investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.k"), "3.2.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.t"), "3.2.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.3") && [Index] = 2, "3.3 Financial derivatives (other than reserves) investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.k"), "3.3.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.t"), "3.3.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.4") && [Index] = 2, "3.4 Other investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.k"), "3.4.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.t"), "3.4.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.5") && [Index] = 2, "3.5 Reserve assets",
    //end of 3

    "Memo item"
)

Fourth hierarchy = 
SWITCH(
    TRUE(),
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && [Index] = 1, "1. Current account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.1") && [Index] = 2, "1.1 Goods and Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.1.1") && [Index] = 3, "1.1.1 Goods, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.1.A"), "1.1.1.A Export",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.1.B"), "1.1.1.B Import",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.1.2") && [Index] = 3, "1.1.2 Services, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.2.A"), "1.1.2.A Export",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.1.2.B"), "1.1.2.B Import",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.2") && [Index] = 2, "1.2 Primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.2.1") && [Index] = 3, "1.2.1 Compensation of employees, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.2.2") && [Index] = 3, "1.2.2 Investment income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.1"), "1.2.2.1 Direct investment income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.2"), "1.2.2.2 Portfolio investment income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.3"), "1.2.2.3 Other investment income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.2.4"), "1.2.2.4 Reserve assets, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"1.2.3") && [Index] = 3, "1.2.3 Other primary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"1.2.3.1"), "1.2.3.1. of which: EU transfers",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"1.3") && [Index] = 2, "1.3 Secondary income, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"1") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"1.3.1"), "1.3.1 of which:EU transfers",
    //end of 1

    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"2") && CONTAINSSTRING(LEFT('Table'[Hierarchy],3)," "), "2. Captital account, net",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"2.1") && [Index] = 2, "2.1 of which:EU transfers",
    //end of 2


    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && [Index] = 1, "3. Financial account (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.1") && [Index] = 2, "3.1 Direct investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.1.1") && [Index] = 3, "3.1.1 Abroad (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],5),"3.1.2") && [Index] = 3, "3.1.2 In Hungary (net liabilities)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.1.1"), "3.1.1.1. Equity (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.1.2"), "3.1.1.2. Debt instruments (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.2.1"), "3.1.2.1. Equity (net liabilities)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],9),"3.1.2.2"), "3.1.2.2. Debt instruments (net liabilities)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.2") && [Index] = 2, "3.2 Portfolio investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.k"), "3.2.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.2.t"), "3.2.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.3") && [Index] = 2, "3.3 Financial derivatives (other than reserves) investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.k"), "3.3.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.3.t"), "3.3.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.4") && [Index] = 2, "3.4 Other investment (net assets)",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.k"), "3.4.k Assets",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],1),"3") && CONTAINSSTRING(LEFT('Table'[Hierarchy],7),"3.4.t"), "3.4.t Liabilities",
    CONTAINSSTRING(LEFT('Table'[Hierarchy],3),"3.5") && [Index] = 2, "3.5 Reserve assets",
    //end of 3

    "Memo item"
)

The final result will be like this:

Attached my sample fille that hopes to help you: create hierarchy columns from one column.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[kormosb]

Hi,

thanks for the solution. Generally it works fine, although when I want to put them in a matrix with the numbers it doesn't aggregate correctly, even if I remove aggregating the subtotals. (It's basically a p'n'l statement)