Avi Singh walks through a usability scenario in Power Pivot and Power BI:
Here is how we can go about combining the Products and the newly created SubCategory table for a more usable and elegant solution. I have highlighted the key steps, you can examine the solution file to go step by step in the Power BI Query.
Note: Don’t be scared of the M code, 99% of these steps were generated using the Query ribbon – I have included those screenshots as well.
When developing BI models, it’s important to keep things as simple as possible. Your desired end user likely does not have the necessary skill level to wade through normalized table designs, so make it easy for them to get their jobs done.