Power Query and Power BI Data Modelling That Adds Up

What's inside

1. Level 1: Applied Steps and Query FoldingFree
   Learn how Power Query records each change as an applied step that runs again on every refresh, and how query folding sends those steps back to the source so the work happens there.
2. Level 2: Data Types and LocaleFree
   Fix wrong totals that come from text-typed numbers and dates or decimals read in the wrong regional format, by setting data types on purpose and using the right locale.
3. Level 3: Removing Errors and Duplicates
   Find out where error values and duplicate rows come from, then remove or replace them in a way that does not quietly lose real data.
4. Level 4: Splitting, Merging and Extracting Columns
   Break packed text fields apart, join fields together and pull out codes or values so each column holds exactly one thing.
5. Level 5: Unpivot and Pivot
   Reshape a wide, spreadsheet-style table with months across the top into a tall table that a model can total, and know when pivoting back is the right choice.
6. Level 6: Appending Queries
   Stack tables with the same shape, such as yearly or regional extracts, into one table, and deal with column names that don't match.
7. Level 7: Merging Queries and Join Kinds
   Look up values from another table by merging queries, choose the right join kind for each job, and avoid the duplicate rows that inflate totals.
8. Level 8: Combining Files from a Folder with Parameters
   Load every file in a folder into one table with the Combine Files pattern, and control the folder path with a parameter so the query is easy to move between machines.
9. Level 9: Reading and Editing Simple M
   Open the Advanced Editor and read a query as a let...in chain of named steps, then make small, safe edits by hand.
10. Level 10: Staging and Reference Queries
    Organise your queries into staging, transformation and output layers using references, so the logic is written once and only the queries you need load into the model.
11. Level 11: Facts, Dimensions, Grain and Star Schema
    Design a star schema by splitting a flat sales table into a fact table at a stated grain and the dimension tables around it, and compare it with a snowflake.
12. Level 12: Relationships: Cardinality and Filter Direction
    Set up relationships with the right cardinality and cross-filter direction, and use active and inactive relationships, so filters flow the way the totals need them to.
13. Level 13: Many-to-Many and Bridge Tables
    Model real many-to-many situations, such as customers with several accounts, with a bridge table, and understand what Power BI's many-to-many cardinality setting does differently.
14. Level 14: Building and Marking a Date Table
    Create a proper date table that covers every date in your data with no gaps, mark it as the date table, and turn off auto date/time.
15. Level 15: Role-Playing Dimensions and Hierarchies
    Handle a dimension that plays several roles, such as order date, ship date and due date, and build hierarchies that report builders can drill through.
16. Level 16: A Tidy Model for Report Builders
    Hide keys and technical columns, put measures in clear homes and display folders, and name everything in business terms so report builders can't pick the wrong field.
17. Level 17: Storage Modes and Composite Models
    Choose between Import, DirectQuery and Dual for each table, understand Direct Lake in outline, and combine modes in a composite model, checking current Microsoft documentation for limits and licence needs.
18. Level 18: Model Size and VertiPaq Analyzer
    Shrink a bloated model by finding its most expensive columns with VertiPaq Analyzer and lowering their cardinality.
19. Level 19: Documenting the Model and Copilot Readiness
    Document the model with descriptions and a data dictionary, then use Power BI's prep-data-for-AI tools so Copilot gives answers that add up, checking preview status and licence needs in current docs.
20. Level 20: Final Challenge: Messy Sources to a Sound Model Exam
    Take a messy multi-source dataset all the way to a documented, fast, Copilot-ready star-schema model whose totals are right, using everything from the course.

Access

The first 2 levels are free with a free account. Every level, the podcast edition and the AI tutor come with All Access at £4.99/month or any Creator plan — see pricing.

Start free →