Microsoft Power BI Intermediate – Data Modelling with Power Query

Course Times

• 9am to 4pm

Course Content

Overview

This course is designed to build on the knowledge gained in the Power BI Essential course perform complex data modelling from multiple data sources with related tables, and use advanced transformation techniques to prepare the data for use in creating Power BI reports.

This is best for:

Technical users who are involved in connecting to complex data sources with multiple related tables, who need to transform them into better, simpler models for creating Power BI Reports.

Connecting to Data Sources

• Relational databases
• Web pages
• PDF documents
• Folders
• SharePoint lists
• Power BI datasets
• Using ODBC

Column Transformations

• Duplicating, reordering and renaming columns
• Splitting columns
• Transforming text, number and date-time columns
• Adding custom columns
• Creating conditional columns
• Creating index columns

Advanced Transformation Techniques

• Grouping tables
• Renaming query steps
• Using parameters
• Removing rows from tables
• Filtering data
• Filling null area with values

Advanced Transformations

• Grouping tables
• Renaming query steps
• Using parameters
• Removing rows from tables
• Filtering data
• Filling null area with values

Incorporate Unrelated Data Sources

• Incorporating data from the web
• Merge using fuzzy matching

Converting Flat Files to Related Tables

• Why would we want to do this?
• Overview of techniques
• Removing duplicates
• Adding an index column
• Using merge to link tables

Adding a Source at a Higher Level

• Comparing against a budget
• Unpivoting tables
• Duplicating Vs referencing other queries
• Adding parent tables
• Linking to the model
• Creating visualisations

Basic M Scripting

• Basic script structure and data types
• Working with lists, records and tables
• Conditionals using IF / ELSE IF
• Looping using EACH
• Custom functions
• Creating a date table

Further development

Power BI Advanced – Creating Performance Measures using DAX – This training is aimed at Power BI users who are looking to get even more out of this popular tool. Learn how to write code in DAX (Data Analysis Expressions) to enhance the data models, create custom performance measures, create complex calculations. Incorporate these results into practical stunning reports, to make decision making easier.

• Understand how to connect to a wide range of data sources
• Develop the skills to performs number, text and date-time column transformations
• Develop the skills to add custom columns to existing data source tables
• Learn and use a range of advanced transformation techniques, including grouping tables, transforming rows and replacing values
• Understand how to incorporate date from unrelated data sources into our model
• Learn how to convert a flat file into multiple related tables (normalisation)
• Learn how to add a data source to the model, which is summarised at a different level, and perform comparisons
• Learn and try some basic M scripting techniques

Participants must have completed the Power BI Essentials course, as well knowledge of Excel formulas at an Intermediate level or higher.  Knowledge of DAX or a similar programming language is recommended, as well as a basic understanding of database theory is advantageous.