Is there a way to make a Hierarchy with One Column

[MichaelaMul]

Hi,

Is it possible to make a hierarchy within one column? I have a separate column that indicates the Data Type but I'm not sure if there's a way to make turn the data type into a hierarchy. If possible, I would like to make a hierarchy based on the data type and it be as follows: Data Type: Category -> Brand -> Size -> UPC. See sample data below.

Data Type	Product	Volume Sales
Category	Flavored Milk Category	100,000.00
Brand	Brand A	45,000.00
Size	Brand A 32oz	15,750.00
UPC	Brand A Extra-Chocolate Milk 32oz - UPC 123	8,662.50
UPC	Brand A Strawberry Milk 32oz - UPC 345	7,087.50
Size	Brand A 64oz	20,475.00
UPC	Brand A Low-Fat Milk 64oz - UPC 098
UPC	Brand A Regular Milk 64oz - UPC 099
UPC	Brand A Chocolate Milk 64oz - UPC 078
Size	Brand A 96oz	8,775.00
UPC	Brand A Regular Milk 96 oz -UPC 778
Brand	Brand B	60,000.00
Size	Brand B 52oz	38,000.00
UPC	Brand B Regular Milk 52oz - UPC 456	24,700.00
UPC	Brand B Chocolate Milk 52oz - UPC 652	13,300.00
Size	Brand B 64oz	22,000.00
UPC	Brand B Chocolate Protein 64oz - UPC 852	6,600.00
UPC	Brand B Skim Milk 64oz UPC 951	15,400.00

[sivarajan21]

Hi @MichaelaMul 

Is this the expected output you need?

So you can create a table to achieve this hierarchy and yes you can display the null data as well.

Did it work? ✔ Give a Kudo • Mark as Solution – help others too!

[MichaelaMul]

No I would like to be able to make a hierarchy so if I click on Brand A I could see the sizes below it and then the UPCs under that.

[sivarajan21]

Hi @MichaelaMul 

Apologise I misunderstood your query!

Is this the expected output?

The data i used here is dummy one and not exactly matches yours

Please let me know if it doesn't meet your expectations!

Best Regards,

[MichaelaMul]

Yes! that's what I'm looking to do

[V-yubandi-msft]

If @sivarajan21 , guidance has resolved your issue, please mark the response as the Accepted Solution. This helps others in the community find useful answers and makes similar discussions easier to navigate.

Thank You.

[V-yubandi-msft]

Thank you for confirming, @MichaelaMul .
@sivarajan21 , we appreciate you sharing your insights. Your input will be helpful to others facing similar issues in the community.

Regards,
CST_Team.

[sivarajan21 · Accepted solution]

Hi @MichaelaMul 

Glad it worked! 

You need to do transformation in Power query to separate the columns as below:

sample m code:

let
    Source = YourSourceTable,
    
    // Filter only UPC rows (detail level)
    UPCRows = Table.SelectRows(Source, each [Data Type] = "UPC"),
    
    // Extract Brand
    AddBrand = Table.AddColumn(UPCRows, "Brand", each 
        if Text.StartsWith([Product], "Brand A") then "Brand A"
        else if Text.StartsWith([Product], "Brand B") then "Brand B"
        else null
    ),
    
    // Extract Size
    AddSize = Table.AddColumn(AddBrand, "Size", each 
        let
            ProductText = [Product],
            SizePattern1 = Text.PositionOf(ProductText, "32oz"),
            SizePattern2 = Text.PositionOf(ProductText, "52oz"),
            SizePattern3 = Text.PositionOf(ProductText, "64oz"),
            SizePattern4 = Text.PositionOf(ProductText, "96oz")
        in
            if SizePattern1 >= 0 then "32oz"
            else if SizePattern2 >= 0 then "52oz"
            else if SizePattern3 >= 0 then "64oz"
            else if SizePattern4 >= 0 then "96oz"
            else null
    ),
    
    // Extract UPC
    AddUPC = Table.AddColumn(AddSize, "UPC", each 
        let
            ProductText = [Product],
            UCPPosition = Text.PositionOf(ProductText, "UPC")
        in
            if UCPPosition >= 0 then
                Text.Trim(Text.Middle(ProductText, UCPPosition, 7))
            else null
    ),
    
    // Rename Product column
    RenameProduct = Table.RenameColumns(AddUPC, {{"Product", "Product Name"}}),
    
    // Select final columns
    SelectColumns = Table.SelectColumns(RenameProduct, {"Brand", "Size", "UPC", "Product Name", "Volume Sales"})
    
in
    SelectColumns

✔ Give a Kudo • Mark as Solution – help others too!

Thanks!

[V-yubandi-msft]

Hi @MichaelaMul ,
Could you let us know if your issue has been resolved or if you are still experiencing difficulties? Your feedback is valuable to the community and can help others facing similar problems.

[V-yubandi-msft]

Hi @MichaelaMul ,

Thanks for reaching out to the Fabric community. As others have mentioned, Power BI doesn't directly support creating a true hierarchy from a single column. As mentioned @Shahid12523  to use Power Query duplicate the column into separate fields like Category, Brand, Size, and UPC, assign values based on their data type, and apply Fill Down.

After restructuring, you can easily create a hierarchy in Power BI and use it in your visuals.

Thank you for sharing your insights in the community @Shahid12523 @lbendlin .

Regrads
Yugandhar

[Shahid12523]

You can’t make a true hierarchy from a single column directly.

Use Power Query:

1. Duplicate Product into 4 columns: Category, Brand, Size, UPC.
2. Put values based on Data Type.
3. Use Fill Down to populate levels.
4. You’ll get clean columns → build hierarchy in Power BI (Category → Brand → Size → UPC).

      This is the simplest and best approach.

[lbendlin]

While you may be able to coerce a hierarchy out of this column it won't help you in the next step as some of the rows are missing their values.

Better discuss with the data owners and ask them for a better, more structured data feed.