Forum Discussion

Helper III

6 years ago

Solved

Create hierarchy columns from one column

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 ...

Need Help

v-yingjl

6 years ago

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.

amitchandak

Super User

6 years ago

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

Helper III

6 years ago

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

Community Support

6 years ago

Hi kormosb ,

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 )
            )
        )
    )
)

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
Helper III
6 years ago
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)

Forum Discussion

Create hierarchy columns from one column

Related content

Create a column with hierarchy information

How to create hierarchy not in fct table or based on integer key columns?

Creating Hierarchy "Backwards"

Create columns group

Date hierarchy