Back To Schedule
Wednesday, October 31 • 8:00am - 5:00pm
Academy Course - Mastering Power Query in a Day ($799)

Sign up or log in to save this to your schedule, view media, leave feedback and see who's attending!

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

avatar for Paul Turley

Paul Turley

Paul (Blog | LinkedIn | Twitter) is Principal Consultant for Intelligent Business LLC, a Mentor and Microsoft Data Platform MVP. He consults, writes, speaks, teaches & blogs about business intelligence and reporting solutions. He works with companies around the world to model data, visualize and deliver critical infor... Read More →

Wednesday October 31, 2018 8:00am - 5:00pm PDT
Microsoft ADVANTA Building B 3009 160th Ave SE Bellevue, WA 98008

Attendees (2)