How to map data to categories and sub categories (appr. 500 unique rows)

[katina]

Hi

I attach here an example of my current task; I must map one list of items to categories and their subcategories. 

My challenge right now is that what would be the most efficient way to implement this mapping? Data comes from the data platform and the categories are sent me in an Excel sheet. 

I created an illustration to explain this in a more conrete way; yellow "side" shows the data structure. Combination of the three separate fields defines the category and subcategory (blue "side")

Is the bridge table the most clever way to solve this? If I create if clauses in Power Query - I think my report will collapse in a seconds and the data consists of about 500 unique rows that must be mapped to categories and subcategories. I have 4-5 main categories and 3 - 12 subcategories for each main category.

Thank you - every idea is welcome 🙂!

-katina

[katina]

Thank you for your comments!

Could I somehow benefit from the data platform where I get the main data? I mean do you find any solution to at least partially maintain the mapping table? 

I try to summarize the data sources here:

- Main data; it updates hourly and comes from the data platform

- Categories and subcategories comes from an external supplier in Excel file

Now I export the main data to excel and create manually the mapping table. Is there any way to avoid manual steps?

[rajendraongole1]

Hi @katina  - A bridge table (mapping table) is indeed the best approach for handling this type of categorization efficiently in Power BI. you can convert the Excel category mapping (right-side of your image) into a structured mapping table.
This table should have three key fields from your data (Product Category, Product Subcategory, Department) and the corresponding Main Category and Subcategory.Import both your main dataset and this mapping table into Power BI.

Connect the main dataset to the mapping table using three fields:
Product Category
Product Subcategory
Department
This ensures that each row in the main dataset gets automatically assigned the correct Main Category and Subcategory from the mapping table. Hope these details helps .

[katina]

Thank you! The bridge table is a quite new thing to me - I understand the idea. How do I create the relationship; 3 separate relations from the main table to the mapping table? 

[bhanu_gautam · Accepted solution]

@katina , Yes bridge table would be good for this '

Create a Bridge Table: This table will map the combination of fields from your data to the corresponding categories and subcategories. The bridge table should have columns for each of the fields that define the category and subcategory, as well as columns for the main category and subcategory.

Load your main data table.
Load the bridge table.'

In the Power BI model view, create relationships between your main data table and the bridge table based on the fields that define the category and subcategory.

Use the fields from the bridge table to categorize your data in reports. This way, you avoid complex if clauses and ensure your report remains performant.