31 Oct

Academy - Mastering Power Query in a Day (Seattle, WA)

Academy - Mastering Power Query in a Day (Seattle, WA)

Wednesday, October 31, 2018 (11:00 AM) to Wednesday, October 31, 2018 (8:00 PM) (Eastern Time)

Microsoft ADVANTA Building B 3009 160th Ave SE Bellevue, WA 98008 Power BI, Power Query
Paul Turley


You're Registered!
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 Basics

Quick 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 & Structures

Flat 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 Techniques
Managing 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 Techniques

Working 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 Together
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

Paul Turley