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 Query Paul Turley ... Details You're Registered! DescriptionSpeakers 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 Examples: 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