Power BI – M & DAX Languages (Virtual)

The Key to Complex
Data Modeling

(2 days)

 

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

$1,100.00

Clear
Focus on advanced functionalities in Power BI Desktop Application to build more complex data models. Learn key concepts to define relationships between data tables. Use M language to transform data in Power Query. Create yourself a reference tool on most common DAX functions and their application.
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 a maximum of 4 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