Description

For analysts and business leaders looking to master Power BI

Learn the end-to-end process of connecting to and transforming your data, creating visualizations, and publishing dynamic reports and dashboards using Microsoft Power BI

About the Course

The Microsoft Power BI Hands-on Training course is a 2-day workshop that provides a hands-on approach to learning everything you need to know to begin creating and sharing interactive reports and dashboards.

In this 2-day workshop, you will familiarize yourself with the Power BI Desktop app and Power BI Service, learn how to connect to different data sources, prepare data for analysis, create and format standard and AI visuals, work through key DAX functions to create calculated columns and measures, publish and share reports, and create dashboards in the Power BI Service.

Throughout the course, our Microsoft Certified instructor will offer tips & tricks, industry best practices, and opportunities to practice the techniques and concepts learned.

The course features

• Live sessions (hands-on approach)
• Challenging exercises
• Power BI report templates
• Coding (DAX and M) cheat sheets
• Additional resources

Course details

• Duration: 2 days (15 hours)
• When: 9:00 AM – 4:30 PM (dates to be determined with the organizer)

Course outline

• Introduction to Microsoft Power BI
• Data Transformations 101: An Introduction to Power Query
• Advanced Power Query: Creating Calculated Columns
• Understanding Data Models in Power BI
• Creating Measures: An Introduction to DAX Programming
• Data Visualization 101: Creating Interactive Visualizations
• Advanced Data Visualization: Tips & Tricks
• Publishing and Sharing Reports and Dashboards

 

About the Trainer

Our Microsoft Certified instructor, Klayton Gonçalves, has 10 years of experience working with data analytics. He has a master’s degree in economics and loves “tinkering” with a variety of different datasets.

Klayton has delivered one-on-one and group workshops in for-profit and not-for-profit organizations in the construction, manufacturing, healthcare, consulting, and education industries.

 

What you will learn

Day 1 – Connect to and Transform Data

Day 1 of the Microsoft Power BI Hands-On Training is all about connecting to and transforming data in preparation for analysis. This includes an introduction to what Power BI is and how it can benefit your organization.

The course begins with an overview of the Power BI Desktop application and Power Query, showcasing what is possible with the tools at hand. Working with real data, you will learn about connecting to common data sources, such as Excel spreadsheets, CSV files, structured databases (i.e. SQL Server), and online services (i.e., SharePoint, Google Sheets, Salesforce, Quickbooks, and more).

Upon mastering data connection, you will learn about key data transformations required to get data ready for analysis. Working with Power Query, Power BI’s native data transformation engine, you will learn to transform and reshape datasets. This includes learning about data types, filtering datasets, replacing column values and renaming columns, splitting and merging columns, and creating custom (or calculated) columns using M programming language.

Day 1 is concluded with discussions and hands-on training in creating and analyzing data models, including understanding schemas and why they are important in Power BI. By the end of the day, you will begin creating your first DAX measures using key DAX functions.

Day 1 is divided into 4 blocks:

• Block 1: Introduction to Microsoft Power BI
• Block 2: Data Transformations 101: An Introduction to Power Query
• Block 3: Advanced Power Query: Creating Calculated Columns
• Block 4: Understanding Data Models in Power BI

Block 1 – Introduction to Microsoft Power BI

Block 1 provides an introduction to what is Power BI and how it can benefit your organization. We will work together by downloading the Power BI Desktop application and doing a complete walkthrough of the tool. Upon becoming familiar with the Power BI Desktop application, we will launch the Power Query Editor – Power BI’s built-in data transformation engine – for a complete overview. We will then work together to connect to common data sources, such as Excel, CSV, and Google Sheets. We will also discuss how to manage data source settings and handle common connection errors.

Learning Outcomes

By the end of Block 1, you will:

• LO1: Understand what Power BI is and how the tool can help our organization
• LO2: Become familiar with the Power BI Desktop application
• LO3: Become familiar with Power Query – the data transformation engine behind Power BI – and understand how it can be used for data transformation
• LO4: Be able to connect to common data sources
• LO5: Be able to change the location of data sources that have moved

Block 2 – Data Transformations 101: An Introduction to Power Query

Block 2 will be dedicated to data cleaning, transformations, and restructuring. In this block, we walk you through key data transformations that are often required prior to visualizing data in Power BI. 

Learning Outcomes

By the end of Block 2, you will be familiar with performing the following tasks in Power Query:

• LO1: Inspect and change data types
• LO2: Remove unnecessary columns from queries
• LO3: Replace values in a column
• LO4: Rename columns
• LO5: Reduce queries by keeping only some rows or removing rows (filtering datasets)
• LO6: Split and merge column values
• LO7: Pivot/unpivot data in queries
• LO8: Use Group By function to reduce rows in queries

Block 3 – Advanced Power Query: Creating Calculated Columns

In Block 3, we dive deeper into Power Query by exploring how to create calculated columns to enhance data queries. Working with real data, we explore all the different ways of creating calculated columns in Power Query, including writing basic and advanced M language code.

Learning Outcomes

By the end of Block 3, you will:

• LO1: Be familiar with all the different ways of adding calculated columns to queries in Power Query Editor
• LO2: Be able to merge the values of two or more columns to create a new calculated column in Power Query Editor
• LO3: Understand how to create columns by examples (without using code) in Power Query Editor
• LO4: Become familiar with key M language functions used to create custom columns in Power Query Editor
• LO5: Be able to create new columns by applying built-in mathematical formulas to existing columns
• LO6: Be able to create new columns by extracting text values from existing columns
• LO7: Be able to create new columns by extracting date or time components from existing columns

Block 4 – Understanding Data Models in Power BI

In Block 4, we work through augmenting clean datasets by creating a data model and calculations. This block covers topics on creating and understanding data models in Power BI, including an introduction to schemas and why the “Star Schema” is often the best schema type for data analysis in Power BI. You will also learn how to link data tables together and use some basic DAX functions to create simple calculated measures.

Learning Outcomes

By the end of Block 4, you will:

• LO1: Understand what a schema is
• LO2: Understand what a star schema is and why it is the optimal schema for working with data inside of Power BI
• LO3: Be familiar with key functionalities of the data model view in Power BI Desktop
• LO4: Be able to link data tables together
• LO5: Understand relationship directions and cardinality and its impact on data visualizations
• LO6: Be able to create groups from a column
• LO7: Be able to create a hierarchy of columns
• LO8: Be familiar with basic DAX functions, including COUNT, SUM, AVERAGE, and DIVIDE, and how they can be used to create calculated measures in Power BI

Day 2 – Visualizing Data and Publishing & Sharing Reports/Dashboards

On Day 2 of the Microsoft Power BI Hands-On Training, we dive deeper into writing DAX code to create calculated measures to be used for data visualization. Together, we work through creating more complex calculations using time-intelligence functions and learn how to work with variables in Power BI.

Through hands-on practice, we work through key data visualization concepts as we develop a Power BI report together. You will learn how to set up report pages and import custom colour themes. Together, we create several visualizations such as charts, maps, images, slicers, text, shapes, and more.

Upon mastering the basics of creating data visualizations, we dive into more advanced data visualizations through a tips-and-tricks approach. Through mini projects, we explore how to use bookmarks, create dynamic visual titles, apply conditional formatting, and create custom tooltips.

Day 2 wraps up with an overview of Power BI Service and how to publish and share reports across the organization, how to set up auto-refresh for reports, and how to set up role-level security.

Day 2 is divided into 4 blocks:

• Block 1: Creating Measures: An Introduction to DAX Programming
• Block 2: Data Visualization 101: Creating Interactive Visualizations
• Block 3: Advanced Data Visualization: Tips & Tricks
• Block 4: Publishing and Sharing Reports and Dashboards

Block 1 – Creating Measures: An Introduction to DAX Programming

In Block 1, we dive deeper into writing DAX code to create calculated measures. We create more complex calculations using time-intelligence functions. You will also learn how to create and work with variables. Finally, we explain filter context inside of Power BI and create more advanced measures.

Learning Outcomes

By the end of Block 1, you will:

• LO1: Be familiar with key time-intelligence DAX functions and how they can be used to create year-over-year, month-over-month, year-to-date, and other similar time-dependent calculations
• LO2: Understand why a date table is a key table in any data model and be familiar with how to create one from scratch in Power Query
• LO3: Understand how to use the USERELATIONSHIP function to make calculations on inactive relationships in the data model
• LO4: Be familiar with filter context and how it impacts visuals in Power BI
• LO5: Gain hands-on experience creating advanced calculations using more advanced DAX functions, such as CALCULATE, ALL, IF, and more

Block 2 – Data Visualization 101: Creating Interactive Visualizations

In Blocks 2 and 3 we explore techniques to work with visualizations in Power BI. In Block 2, we explore how to set up report pages and import custom colour themes. Together, we work through building a report, which exposes you to several visualizations such as charts, maps, images, slicers, text, shapes, and more.

Learning Outcomes

By the end of Block 2, you will :

• LO1: Be familiar with how to resize report pages, add report backgrounds, and import custom colour themes into Power BI
• LO2: Understand key principles of data visualization, such as best practices of visualization positioning and design
• LO3: Understand how wireframing and prototyping can be useful techniques to work more efficiently when designing reports in Power BI
• LO4: Be familiar with how to create key visualization artifacts, including charts, images, slicers, buttons, shapes, text, and more
• LO5: Be familiar with how to customize the look and feel of visuals in Power BI

Block 3 – Advanced Data Visualization: Tips & Tricks

In Block 3, we work through more advanced visualization techniques in Power BI. Through mini projects, we explore how to use bookmarks, create dynamic visual titles, apply conditional formatting, and create custom tooltips.

Learning Outcomes

By the end of Block 3, you will:

• LO1: Understand how to use bookmarks to hide and show visuals on a report page in Power BI
• LO2: Be able to create DAX measures and apply these to create dynamic titles for report visuals
• LO3: Be able to apply conditional formatting to visuals in Power BI
• LO4: Be familiar with creating custom tooltips and how to apply them to charts in Power BI
• LO5: Be familiar with how to use custom tooltips for providing additional chart details without cluttering report pages

Block 4 – Publishing and Sharing Reports and Dashboards

In Block 4, we explore the Power BI Service (web-based platform). In this block, we create a workspace and publish reports to this workspace. We also go through all the features available in the Power BI Service, including how to create dashboards, how to publish and distribute workspace apps, how to set up auto-refreshes for reports, and how to set up role-level security.

Learning Outcomes

By the end of Block 4, you will:

• LO1: Be familiar with the Power BI Service
• LO2: Be able to create a workspace in Power BI Service
• LO3: Be able to publish a report to the Power BI Service
• LO4: Be familiar with creating dashboards in Power BI Service
• LO5: Understand how to share reports with colleagues
• LO6: Be familiar with all the settings involved in publishing a workspace app in Power BI Service