Hi,
can you help me with a solution for the following problem:
My data source contains a product categories hierarchy with the following structure:
Category ID | Category Name | Parent Category ID | Level
Level could even be omitted and calculated, but it’s easier to understand with the level column.
Currently there is level 1 = category and level 2 = subcategory in use and each product is assigned to one subcategory, and I could create two columns “category” and “subcategory” from that in Power Query and add them to my products table and create a hierarchy with category, subcategory, and product from it.
But since this is dynamic levels in the data source, the owner of the data source could restructure the product categories and introduce another level so that with the next data refresh in power BI I suddenly have a level 3 in addition and products are assigned to level 3 “subsubcategories”.
Is there a way in Power BI to keep the number of hierarchy levels dynamic so that I can still show the product categories hierarchy as a hierarchy in the fields pane and it adds and removes levels automatically? If this is not possible, what would be your suggestion to get closests to this behaviour, that especially allows me to drill down in bar charts and create hierarchical pivot tables?
Thanky you very much!
Best regards,
Martin
