The Key to Complex
Data Modeling
(2 days – 9AM to 4PM)
Hands-On Workshop
- Participants interact with the instructor, in a virtual classroom, to complete hands on activities.
Learning Outcomes
- Use advanced functionalities in Power Query Editor and M language
- Understand the different relationship types in data modelling
- Build a DAX Reference Tool: understand and use DAX most common functions
- Develop strategies to pursue M and DAX learning
Technical Requirements
- Power BI Desktop
- Zoom
Prerequisite
- Power BI Foundations course (1 Day) is a prerequisite.
- Participants must master the Power BI Service functionalities to properly use the Desktop application.
- Participants must master navigation in Power BI Desktop, including the Power Query Editor.
Target Audience
Analysts, Accountants, Report Builders, data analytics professionals
Technical Requirements
Participant must have access to 2 screens: using a laptop and a monitor is adequate.
Participant must install free apps on his/her computer: Zoom and Power BI Desktop.
Microsoft Certified Instructor
Our Ottawa-based instructor is certified by Microsoft, as a skilled professional in Business Intelligence Reporting.
Quality Service
Workshops are limited to 10 attendees to address each participant’s questions and requests.
Interact with instructor using a web-conferencing interface: share screen and mouse control to resolve issues.
Training Schedule
Training takes place from 9 a.m. to 4 p.m.
Learning Outcomes
- Use advanced functionalities in Power Query Editor and M language
- Understand the different relationship types in data modelling
- Build a DAX Reference Tool: understand and use DAX most common functions
- Develop strategies to pursue M and DAX learning
Topics
1. Power Query and M language
- Connect to a folder and combine files
- Explore Power Query Editor environment: Menus, Applied Steps, Advanced Editor
- Explore M language syntax
- Edit a query: remove columns, extract text after delimiter, add a conditional column, filter, unpivot columns, group by, drill down
- Manage an error message
- Edit an Applied Step using M language
- Create Values, Lists and tables from blank queries
- Use M functions: List.Dates, #date, Duration.Days, #duration, DateTime.LocalNow(), Date.AddYears(), Date.EndOfYear(), Date.From()
- Create a dynamic date list
- Search the web to resolve issues
- Explore recommended web resources
2. Understand relationship types in data modelling
- Explore data model and table relationships concept
- Visualize the impact of wrong relationships
- Create relationships: edit type and filter direction
- Use and understand the impact of Many-to-Many relationships
- Edit model queries to eliminate Many-to-Many relationship
3. Build a DAX reference tool
- Understand DAX basic concepts and syntax
- Differentiate measures and calculated columns
- Use DAX functions: TODAY, YEAR, MONTH, DAY, FORMAT, COMBINEVALUES, COUNTA, COUNTBLANK, COUNTROWS, DISTINCTCOUNT, SUM, SUMX, FILTER, CALCULATE, ALL, ALLEXCEPT, DIVIDE, USERELATIONSHIP, IF, AND, OR, TOTALYTD, SAMEPERIODLASTYEAR
- Use CALCULATE function to override relationship
- Use Variables in DAX formulas
- Use the What If Parameter
- Deal with an inactive relationship
- Search the web to resolve issues
- Explore recommended web resources