Once the first reports are live, the requests get harder. Combine forty files from a folder. Compare this year with last. Show a running total. Make it load faster.
This course is for people who already build reports. It covers appending, merging and combining files, reading and writing M, parameters and templates, DirectQuery, designing models for accuracy and speed, calculated tables, navigation and time intelligence functions in DAX, and visuals that get the point across quickly.
By the end, participants can
- Understand the Power BI workflow and apply best practices from the start
- Combine, append and merge data from multiple queries and files
- Write basic M code and use parameters and templates to automate repeated work
- Transform data using DirectQuery and apply usability features to improve their queries
- Create and manage well-structured data models
- Build advanced measures, calculated columns and calculated tables using DAX
- Apply navigation and time intelligence functions for period-based analysis
- Enhance models with additional features to improve usability and performance
- Create advanced visualisations that communicate insights clearly
Course outline
Day 1 · Advanced Data Transformation and Modelling
01Getting Started
- Course introduction and Power BI refresher
02Transforming Data
- Appending queries: stack data from multiple tables into one
- Combine files: automatically merge many files from a folder
- Merging queries: join tables to bring related data together
- Writing basic M: read, edit and write Power Query code
- Parameters and templates: make queries flexible and reusable
- More query parameters: advanced parameter techniques
- Usability features: make queries easier to maintain and use
- Transforming data with DirectQuery: work with live data connections
03Creating the Data Model
- Designing relationships and model structure for accuracy and performance
Day 2 · Measures, model and visuals
04Creating Measures, Calculated Columns and Tables
- Navigation functions: move across relationships and context in your model
- Calculated tables: create new tables using DAX
- Time intelligence functions: year-to-date, previous period, year-on-year comparisons
05Additional Model Enhancements
- Refine the model to be more intuitive and efficient for report users
06Advanced Visual Options
- Build advanced visualisations and interactive report features that communicate insights clearly