Forum Discussion
Flat Excel File to Data Model Help
Relatively new to PowerBI and want to pick the brain of some experts. How would you go about cleaning up an excel file that looks like the below (With the query editor or Excel) for a solid data model. It consists of quarterly data between 2005 and 2020 with distinguishments made between Total Market, Direct, Overall which is then broken down between Total Market CBD and non-CBD. I'd like to be able to filter between total market (cbd + non-cbd), cbd, and non-cbd by quarter.
Ignore the Total Market columns, you get those for free.
Have a Calendar table, a dimension table for your data subject hierarchy, and a fact table for the numbers.
2 Replies
Ignore the Total Market columns, you get those for free.
Have a Calendar table, a dimension table for your data subject hierarchy, and a fact table for the numbers.
Additionally, you will also need to use the "Unpivot Other Columns" feature in the Query Editor.
