In our latest Power BI workshop, the team dove deep into data modeling essentialsโexploring how to transform raw CSV files into meaningful insights. Using the classic Northwind Traders dataset as a case study, participants got hands-on experience with loading, cleansing, merging, and modeling data for effective reporting and dashboard creation.
๐ Key Highlights from the Workshop
๐ Data Loading & Transformation
Learned how to load and prepare multiple CSV/Excel sheets.
Practiced column splitting, data cleansing, and promoting headers for cleaner datasets.
Explored merging and appending data in Power Query to create structured models.
๐ฐ Sales Calculations in Excel & Power Query
Calculated total sales using unit price ร quantity.
Applied discounts by converting numerical values into percentages.
Used formulas like Total Sales ร (1 โ Discount) to calculate post-discount sales.
Troubleshot common errors when inputting formulas with brackets.
โ๏ธ Power Query vs. Data Modeling
Understood the limitations of Power Queryโwhile it can merge queries, it isnโt built for cross-table calculations.
Highlighted why parent-child relationships (like orders and order details) should be modeled, not merged.
A blog resource on when to append, merge, or relate data in Power BI was recommended for further clarity.
๐ Fact & Dimension Tables
Explored the distinction between fact tables (transactions) and dimension tables (descriptive attributes).
Practiced building many-to-one relationships using IDs like customer, shipper, and employee.
Learned why the fact table should always remain at the center of the model for clarity and scalability.
๐ Managing Date Tables
Created a custom date table using DAX formulas.
Linked it to dataset columns for accurate time-series analysis.
Learned how to rename and maintain date tables to cover the relevant data range.
๐ฏ Whatโs Next?
๐ Upcoming Assignment: Work on project management datasets with resource assignment tables.
๐ Next Class Focus: DAX formulas, KPIs, and performance measurement.
๐ Final Session: Automatic data refresh, handling large datasets, and scaling models (note: refresh requires a paid subscription).
๐ Takeaway
This session emphasized the power of proper data modeling in Power BI. By understanding relationships, fact-dimension structures, and the role of date tables, learners gained the foundation to build scalable and insightful dashboards.
๐ Ready to level up your Power BI skills?
๐ Explore Now
๐ก Enroll Now