Call us at 1-800-413-0939
Hands On Technical Training

Analyzing Data with Power BI, DAX, and Power Query M

Attend face-to-face, remote-live, on-demand or on site at your facility.

Travel Packages Discount Programs
Top

Analyzing Data with Power BI, DAX, and Power Query M

Duration: 5 Days

Price: $2795 *California residents and government employees call for pricing.

Discounts: We offer multiple discount options. Click here for more info.

Delivery Options: Attend face-to-face in the classroom, remote-live or on-demand training.

Registration: Click here to register for face-to-face or remote-live sessions, or click here to register for on-demand training.

Students Will Learn

  • Performing desktop Power BI data transformations
  • Describing desktop Power BI modelling
  • Creating Power BI desktop visualizations
  • Implementing the Power BI service
  • How to connect to Excel data
  • How to collaborate with Power BI data
  • Connecting directly to data stores
  • Describing the Power BI developer API
  • Describing the Power BI mobile app
  • Using DAX to add new Calculations to an existing data model
  • Simulating a Star data Schema from an OLTP source with DAX
  • Fixing Data Granularity issues with DAX Calculations
  • Providing Row Level security using Row Filters in DAX
  • Performing Transformations while loading data with Power Query and M
  • Using M language to write more powerful queries than the standard Power Query user interface allows

Course Description

This course gives students a solid understanding of data analysis with Power BI, DAX, and Power Query M. Attending this course will prepare students with in-depth language skills necessary to tackle even the toughest Data Analysis needs in Power BI.

Topics include creating visualizations, connecting to Excel files, creating a Power BI dashboard, developing reports using the Power BI desktop App, using DAX syntax, accessing data in Azure SQL Data Warehouses, and using the Power BI Mobile App and the Developer API.

Students will learn beginner and intermediate techniques for adding calculations to their Power BI Data models using DAX. Our Hands-On labs provide practical exercises that cover some of the common data modelling problems that users face, and how DAX can be used to solve those. Students will experience flattening out an OLTP database into a star schema by using DAX, resolving common granularity issues with data models, and learn how Measures can be used to solve advanced calculation problems that languages like SQL are unable to tackle.

Students will also learn about the M language that Power Query uses behind its graphical user interface, and how writing M directly can enable advanced queries against the data-sources that populate a model with its data.

Students who want Power BI training but do not need coverage of DAX and Power Query M should instead attend the shorter Analyzing and Presenting Data with Power BI course rather than this course.

Course Prerequisites

Before attending this course, students must have:

  • Some prior experience working with data in Microsoft Excel, Microsoft Access, or SQL Databases
  • Some exposure to basic programming constructs (such as looping and branching)
  • An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable
  • Familiarity with Microsoft Office applications - particularly Excel

Course Overview

Introduction to Self-Service BI Solutions
  • Introduction to business intelligence
  • Introduction to data analysis
  • Introduction to data visualization
  • Overview of self-service BI
  • Considerations for self-service BI
  • Microsoft tools for self-service BI
Introducing Power BI
  • The Power BI service
  • Developing reports using the Power BI Desktop app
  • Using report items to create dashboards on the Power BI portal.
  • The Power BI data model
  • Using Excel as a data source for Power BI
  • Using databases as a data source for Power BI
Shaping and Combining Data
  • Power BI desktop queries
  • Shaping data
  • Combining data from different tables in your dataset
Modeling Data
  • Relationships between data tables
  • DAX syntax and queries
  • Calculations and measures
Interactive Data Visualizations
  • Creating Power BI reports
  • Creating a Power BI dashboard
  • Managing a Power BI solution
Direct Connectivity
  • Cloud data
  • Using Power BI direct connectivity to Azure SQL data warehouses, and big data sources such as Hadoop
  • Using Power BI with SQL Server Analysis Services data, including Analysis services models
The Developer API
  • The developer API
  • Using custom visuals
Power BI Mobile App
  • Using the Power BI mobile app
  • Power BI embedded
DAX Fundamentals
  • DAX Capabilities and Common DAX Use Cases
  • DAX Formula Syntax Fundamentals
  • Formula Evaluation Order
  • DAX Operators
  • DAX Operator Precedence
  • DAX Data Types
  • DAX Type Conversions
  • DAX Functions
  • Nested Function Execution Order
  • Naming Requirements
Basic DAX Calculations
  • Calculated Columns
  • Evaluation Context and Row Context
  • Working with Relationships
  • Using RELATED() and RELATEDTABLE()
  • Using Ad-hoc Relationships with USERELATIONSHIP()
  • Flattening Models with Calculated Columns
  • Calculated Tables
Advanced DAX Calculations
  • Measure Use Cases
  • Implicit Measures
  • Calculated Measures
  • Evaluation Context and Filter Context
  • Implicit Filter Context
  • Explicit Filter Context
  • Filtering with Calculate() and Filter()
  • Undoing Filters with All()
  • Iterator Fundamentals
  • Working with built-in Iterator Functions
  • Using Nested Iterators
DAX and Modeling
  • Resolving Model Granularity Issues
  • Flattening Models
  • Row Level Security with Row Filters
  • Avoiding Ambiguous Relationships with Role Playing Tables
M and Power Query
  • Power Query Basics
  • Using the Formula Bar in Power Query
  • Using the Advanced Editor in Power Query
  • PowerQuery's Standard Function Library
  • M Language
  • Case Sensitivity in Power Query and M
  • Single Literal Values
  • Intrinsic Values
  • Structured Values: Lists, Records, and Tables
  • M Language Operators
  • Commenting Code in M
  • Using the Let Statement
  • Variable Naming in M
  • M's Each Statement
  • Creating Columns in M
  • Conditional Constructs in M
  • M Functions

Related Courses

Course Benefits

  • Course materials include student guide, hands-on lab manual and USB flash drive for examples and lab work
  • Students receive a certificate of completion at the end of class
  • Students can retake any portion of a live class that has been completed, within 12 months at no extra cost
  • There are no registration fees or cancellation fees

Course Schedule

Choose a city below to view a schedule. If you have any questions, please call us at 978-250-4299 between 9:00 am - 5:00 pm EST.