Register to attend this class or follow the instructions below to include registration for the Power BI World Tour.
Please Note: Academy workshops are half-day, full-day, or two-day training sessions that will build your skills through hands-on, deep dive education. Our Academy Courses do require a separate registration. If you are registering for the
Power BI World Tour, you will have the option to add on Academy classes during the registration process.
If you have already registered, it's not to late to add on a class!!
- To edit your registration, please click here and follow these steps:
- This will bring you to the Registration page – you will see a message stating "You are currently Registered." Click on Edit Registration (right side of screen).
- When you see your registration, click Edit.
- On the next screen, click Add Activities.
- Check the box next to the class title you're interested in.
- Follow the prompts to complete the process of adding this class to your registration.
Mastering Power Query in a Day The foundations of a Business Intelligence solution are data transformations, data wrangling, data cleansing and ETL. A well-crafted Power BI project rests on Power Query and the queries that define the data model, calculations and report visuals. This full-day session will teach you how to lay the foundation for a Power BI solution with simple and advanced Power Query techniques.
Learn from Paul Turley, ten-year Microsoft Data Platform MVP and veteran BI Solution Architect. You will learn best practice design patterns, tricks, shortcuts and proven techniques to improve your skills and add immediate value to your projects. Power Query is everywhere - and growing. The skills and techniques taught in this workshop apply to Power BI Desktop, the "Get Data" feature in Excel 2016+, SQL Server Analysis Services 2017+ (SSAS), Azure Analysis Services (AAS) and Data Flows in the Power BI Common Data Service (CDS).
You will learn through exercises and instructor-led hands-on demos.
Bring your laptop with the latest version of Power BI Desktop installed. The rest will be provided. We will cover material from basics through advanced. Each exercise is separate so you can absorb only what you need to learn, based on your prior experience, needs and skill level.
Power Query BasicsQuick tour of the Power Query interface & essentials
Creating and managing queries
Adding and editing steps
Recovery and project management
Essential best practices
Managing data sources
Working with folder paths, web URIs & database connections
Referencing & Duplicating queries
Consolidating queries, building base queries & dependency chains
Loading queries into data model tables
Basic error handling & debugging
Data Sources & StructuresFlat CSV files
Irregular text files (headings & totals)
JSON (simple)
JSON (complex, with nested & ragged hierarchies)
SQL server
Excel (single sheet/table, multiple sheets/tables)
Folders & file collections
Web pages a page tables
Web APIs & web service endpoints
Essential Query TechniquesManaging data types
Applying correct naming conventions
Working with Date & Time values
Splitting & formatting columns
De-duplicating & grouping
Pivot, Unpivot & Transpose
Custom columns & expression basics
Extracting tables from a data sources to supporting essential modeling for Power BI report design:
o Fact tables
o Dimension tables
o Bridge tables
o Slicer & calculation-driver tables
o Role-playing dimensions
Advanced Power Query TechniquesWorking with M: The Data Mashup language
M function essentials
Prioritized learning (what's most important)
Using & managing parameters
Using the #shared object for internal documentation, examples & code syntax
Understanding M objects (values, tables, lists & records)
Number, Date, Time & Text manipulation M functions
Create a Date lookup/dimension table using M & Power Query
Create a Time series lookup/dimension table using M & Power Query
o Why do I need a Date dimension in Power BI?
o Standard date parts & hierarchies
o Columns to support time-intelligence calculations
o Working with fiscal & special-purpose calendars (e.g. 4-4-5, ISO)
Working with query functions
Parameterized queries, API endpoints & user-defined functions
Putting it TogetherExamples:
o Queries to support data model construction
o Queries used to support report visuals
o Deploy a report, configure the on-premises gateway
o Use query parameters to schedule refresh in a deployed Power BI solution