The following reference materials were provided as context. Use them as guidance only -- always prefer the latest, most current information over any potentially outdated content in these files.
--- Reference: 00 Overview.pptx ---
Overview
Lesson 1: Getting started
Introducing Power BI
A quick look at Power BI Desktop
A quick look at the Power BI service
Lesson 2: Power BI and Excel
Introduction to using Excel data in Power BI
Upload Excel data to the Power BI service
Using Excel as a Data Source
Import Power View and Power Pivot to Power BI
Lesson 3: Power BI and Other Data
Data Sources in Power BI Desktop
Connect to data sources in Power BI Desktop
More advanced data sources and transformation
Connect to SQL Server in Power BI Desktop
Connect to CSV files in Power BI Desktop
Lesson 4: Clean, Transform, and Load Data
Introduction to the Query Editor
Clean and transform your data with the Query Editor
Cleaning irregularly formatted data
Lesson 5: Modeling Data
Introduction to modeling your data
How to manage your data relationships
Optimizing data models
Introduction to DAX
DAX functions
Calculated columns in DAX
Calculated measures in DAX
Create calculated tables
Explore your time-based data
Lesson 6: Visualizations
Introduction to visuals in Power BI
Create and customize simple visualizations
Combination Charts
Modify colors in charts and visuals
Text boxes, shapes, and smart narratives
Page layout and formatting
Group interactions among visualizations
Visual hierarchies and drill-down
Using custom visualizations
R integration in Power BI Desktop
Lesson 7: Publishing and Sharing
Introduction to the Power BI service
Publish Power BI Desktop Reports
Create and configure a dashboard
Share dashboards with your organization
Quick insights in Power BI
Ask questions of your data with natural language
Create custom Q&A suggestions
Create workspaces in Power BI
Manually republish and refresh your data
Install and configure a personal gateway
Export dashboards and reports to PDF
Printing from the Power BI Service
Publish to web from Power BI
Course Outline
Power BI Intermediate
Power BI Tools: DAX Introduction
Power BI Tools: Get and Transform Data (with M Programming)
PL-300: Microsoft Power BI Data Analyst
Go to www.onlc.com for more information.
Related Classes
--- Reference: 01 Getting Started.pptx ---
Lesson 1: Getting Started
Overview
Introducing Power BI
A quick look at Power BI Desktop
A quick look at Power BI service
Setup the Lab Environment
Refer to Get Started Building with Power BI
Lesson 1: Getting Started
What is Power BI?
The Parts of Power BI
How Power BI Matches your role
The Power BI Workflow
Overview
Microsoft’s business analytics service
Allows creation of visually rich data analysis
Semantic Models (Datasets)
Reports
Visualizations
Dashboards
Tiles
Introducing Power BI
A few pieces working together
Power BI Desktop
Power BI service
Power BI Mobile
Developer API
Introducing Power BI
Free to download and install
Primary tool for importing, shaping, and modeling data
Three main views
Report view
Table View
Model View
Also includes Power Query (from Excel Get & Transform Data)
A quick look at Power BI Desktop
Primary environment for sharing Power BI solutions
Several primary objects
Dashboards
Reports
Workbooks
Semantic Models (Datasets)
Requires Office 365 or a standalone license
Free trial is available
A quick look at the Power BI Service
Exercise 1: Download and install Power BI Desktop
Exercise 2: Download Class Files
Exercise 3: (Optional) Download and install Visual Studio Code
Exercise 4: Receive your Power BI account information from your instructor and record that information for use in the class
Lab 1: Setup the Lab Environment
Exercise 4: Receive your Power BI account information from your instructor and record that information for use in the class
OneNote Notebook > Lab 1 - Getting Started > Exercise 4 only!
OneNote Notebook: bit.ly/pbixl
Lab 1: Setup the Lab Environment
--- Reference: 02 Power BI and Excel.pptx ---
Lesson 2: Power BI and Excel
Power BI and Excel
Introduction to using Excel data in Power BI
Upload Excel data to the Power BI service
Using Excel as a Data Source
OneDrive for Business with Excel
Import Excel workbooks into Power BI Desktop
Refer to Get Data in Power BI
Lesson 2: Power BI and Excel
Excel and Power BI are closely related
Excel and Power BI share many common features
Power Query (“Get & Transform Data” in Excel)
PowerPivot/Power BI Data model =xVelocity engine
DAX (Data Analysis eXpressions)
PowerViews <> Power BI visuals, but similar
Power BI and Excel
Make sure your data is formatted as a table
From the browser, click New > Semantic Model > Excel >Browse this device
Limited to a single table, so NOT VERY USEFUL
No way to clean, shape, or transform the data
Good for quick reporting when away from Power BI Desktop
Upload Excel data to the Power BI service
In Power BI Desktop, click Get Data > Excel
Select table or tables
Load to model or Transform via Power Query
Using Excel as a Data Source
Allows for scheduled updates
Dataset can remain in OneDrive and be consumed by Power BI
OneDrive for Business with Excel
For workbooks with a data model, calculated measures/KPIs, power views
Entire workbook is converted to a Power BI solution
Import Excel Workbooks into Power BI Desktop
OneNote Notebook > Lab 2 - Power BI and Excel > Exercises 1 and 2
In this lab, you learn how to import a table of data from an Excel file and create a report to visualize this data. You will also import an Excel Data Model into Power BI.
Exercise 1: Use Excel as a Data Source
Exercise 2: Import an Excel Data Model and Power Views into Power BI
Duration: 30 minutes
OneNote URL: https://bit.ly/pbixl
Lab 2: Power BI and Excel
--- Reference: 03 Power BI and Other Data.pptx ---
Lesson 3: Power BI and Other Data
The Power BI Workflow
Data Sources in Power BI Desktop
Connect to data sources in Power BI Desktop
More advanced data sources and transformation
Connecting to a Database
Connecting to a CSV file
Refer to Get Data in Power BI
Lesson 3: Power BI and Other Data
The Power BI Workflow
All
File
Database
Power Platform
Azure
Online Services
Other
Data Sources in Power BI Desktop
Select data source type
Fill in connection info, e.g. file path, database name, etc.
Shape data if necessary
Connect to data sources in Power BI Desktop
XML
JSON
Folder
Web
OData Feed
R Script
ODBC
OLE DB
Blank Query
More advanced data sources and transformation
Get Data SQL Server
Server name, Database name, Credentials
Choose tables
Connecting to a Database
Get Data Text/CSV
Choose file
Select options
Connecting to a CSV file
OneNote Notebook > Lab 3 - Power BI and Other Data > Exercises 1 - 3
In this lab, you learn how to import a table of data from a Web page and create a report to visualize this data. As part of this process, you navigate across tables available on a web page, and apply data transformation steps to bring the table into a new shape.
Exercise 1: Analyzing Web page data using Power BI Desktop
Exercise 2: Shape data in the subject table
Exercise 3: Create visualizations using the Report view
Duration: 30 minutes
OneNote URL: bit.ly/pbixl
Lab 3: Power BI and Other Data
--- Reference: 04 Clean, Transform, and Load Data.pptx ---
Lesson 4: Clean, Transform, and Load Data
The Power BI Workflow
Introduction to the Query Editor
Clean and transform your data with the Query Editor
Cleaning irregularly formatted data
Refer to Clean, Transform, and Load Data in Power BI
Lesson 4: Clean, Transform, and Load Data
The Power BI Workflow
Query Editor
Power BI: “Power Query”
Excel: “Get & Transform”
M scripting language
“Advanced Editor”
Introduction to the Query Editor
Get Data
Remove unwanted data
Remove columns
Filter rows
Fix unwanted values
Remove nulls
Replace codes, etc.
Shape data
Unpivot columns
Generate calculated columns
Merge queries
Append queries
Clean and transform your data with the Query Editor
Data can be wrong or poorly formatted
Text data
UPPERCASE, lowercase, miXeD CaSE
Dates
Locale formatting: M/D/Y, D/M/Y
Numbers
Too many/too few decimal places
Thousands separators
Invalid values
Cleaning irregularly formatted data
OneNote Notebook > Lab 4A - Clean, Load, and Transform Data > Exercises 1 - 3
In this lab, you import data from Excel (it includes product information) and from an OData feed (which contains orders data). You'll perform transformation and aggregation steps, and combine data from both sources to produce a Total Sales per Product and Year report that includes interactive visualizations.
Exercise 1: Analyzing sales data from Excel and an OData feed
Exercise 2: Combine the Products and Total Sales queries
Exercise 3: Build visuals using your data
Duration: 45 minutes
OneNote URL: bit.ly/pbixl
Lab 4A: Clean, Transform, and Load Data
OneNote Notebook > Lab 4A - Clean, Load, and Transform Data > Exercises 1 - 3
In this lab, you import data from Excel (it includes product information) and from an OData feed (which contains orders data). You'll perform transformation and aggregation steps, and combine data from both sources to produce a Total Sales per Product and Year report that includes interactive visualizations.
Exercise 1: Analyzing sales data from Excel and an OData feed
Exercise 2: Combine the Products and Total Sales queries
Exercise 3: Build visuals using your data
OneNote URL: bit.ly/ONLCBPBEX1REF
Lab 4A: Clean, Transform, and Load Data
Lab 4B: Review
OneNote Notebook > Lab 4B - Review > Exercises 1 - 5
In this review, we put our new skills to use.
Exercise 1: Get data from a web site
Exercise 2: Shape data
Exercise 3: Combine data
Exercise 4: Build reports
Exercise 5: Share your work
Duration: 45 minutes
OneNote URL: bit.ly/pbixl
--- Reference: 05 Modeling Data.pptx ---
Lesson 5: Modeling Data
The Power BI Workflow
How to manage your data relationships
Optimizing data models
Introduction to DAX
Explore your time-based data
s Refer to Configure a semantic model, Write DAX formulas for semantic models, Create DAX calculations in semantic models, and Use DAX time intelligence functions in semantic models
Lesson 5: Modeling Data
The Power BI Workflow
Depends on table data
Primary key Foreign key
Cardinality:
One-to-Many: Primary Key (unique) to non-Primary Key (non-unique)
One-to-One: Primary Key (unique) to Primary Key (unique)
May be auto-detected
Can be created manually
Must be the same data type (but not name)
How to manage your data relationships
Remove unneeded columns
Filter unneeded rows
Hide fields
Sort by a different field
Optimizing data models
“Data Analysis Expressions”
Query and expression language for Power BI, Power Pivot, and SSAS tabular models
Excel-like syntax
Used to create calculated measures, columns, and tables
General syntax: {object name} = {DAX expression}
Introduction to DAX
Date and Time
Time Intelligence
Information
Logical
Mathematical
Statistical
Text
Parent/Child
Other
DAX Function Reference: https://msdn.microsoft.com/query-bi/dax/dax-function-reference
DAX functions
Row Context
The values in the new column appear on each row
Calculated Columns in DAX
Filter Context
Aggregated values that respond to filters
Calculated Measures in DAX
Create a new table from existing data
Calculated Tables in DAX
Automatic date hierarchies
DAX Time Intelligence Functions:
CLOSINGBALANCEMONTH, CLOSINGBALANCEQUARTER, CLOSINGBALANCEYEAR, DATEADD, DATESBETWEEN, DATESINPERIOD, DATESMTD, DATESQTD, DATESYTD, ENDOFMONTH, ENDOFQUARTER, ENDOFYEAR, FIRSTDATE, FIRSTNONBLANK, LASTDATE, LASTNONBLANK, NEXTDAY, NEXTMONTH, NEXTQUARTER, NEXTYEAR, OPENINGBALANCEMONTH, OPENINGBALANCEQUARTER, OPENINGBALANCEYEAR, PARALLELPERIOD, PREVIOUSDAY, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR, SAMEPERIODLASTYEAR, STARTOFMONTH, STARTOFQUARTER, STARTOFYEAR, TOTALMTD, TOTALQTD, TOTALYTD
Explore your time-based data
YTD Total Cost:=CALCULATE([Total Cost],DATESYTD('Date'[Datekey]))
Previous Year Total Sales Amount:=CALCULATE([Total Sales Amount], SAMEPERIODLASTYEAR('Date'[Datekey]) )
OneNote Notebook > Lab 5 - Modeling Data > Exercises 1 & 2
In this lab, we’ll understand the benefits of creating measures and calculated columns to assist in data analysis.
Create your own measures in Power BI Desktop
Create calculated columns in Power BI Desktop
NOTE: There is only data for the years 2011-2013.
BONUS: Create a page/report filter to limit options to only those years.
Duration: 45 minutes
OneNote URL: bit.ly/pbixl
Lab 5: Modeling Data
--- Reference: 06 Visualizations.pptx ---
Lesson 6: Visualizations
The Power BI Workflow
Introduction to Visuals in Power BI
Create and Customize Simple Visualizations
Combination Charts
Modify Colors in Charts and Visuals
Text Boxes, Shapes and Smart Narratives
Page Layout and Formatting
Group Interactions Among Visualizations
Visual Hierarchies and Drill-down
Using Custom Visualizations
R Integration in Power BI Desktop
Lesson 6: Visualizations
Refer to Design Power BI Reports
The Power BI Workflow
Introduction to Visuals in Power BI
Combine data with two types of values (e.g. profit and quantity)
Combination Charts
Vary with the visual
Colors, fonts, trendlines, etc.
Create and Customize Simple Visualizations
Lines
Bars
Individual Data Elements
Modify Colors in Charts and Visuals
Additional formatting
Decorative elements
Additional information
Text Boxes, Shapes, and Smart Narratives
Page size
Background color & image
Page Layout and Formatting
Cross highlighting
Cross filtering
Group Interactions Among Visualizations
Date hierarchies
Natural hierarchies
Analytical hierarchies
Visual Hierarchies and Drill-down
Custom visuals marketplace
Community-made
Microsoft-made
Types of custom visuals
“Normal”
R-based
Using Custom Visualizations
Requires install of R environment
https://cran.r-project.org/
R: A statistics application and language
Not part of Power BI
Open Source (free)
Rich, flexible, extensible
Very Popular
R Integration in Power BI Desktop
OneNote Notebook > Lab 6 - Visualizations > Exercise 1
In this activity you add a couple of custom visuals from the marketplace and use them to visualize your data.
Exercise 1: Add Custom Visuals
Duration: 10 minutes
OneNote URL: bit.ly/pbixl
Lab 6: Visualizations
--- Reference: 07 Publishing and Sharing.pptx ---
Lesson 7: Publishing and Sharing
The Power BI Workflow
Introduction to the Power BI service
Publish Power BI Desktop reports
Create and configure a dashboard
Share dashboards with your organization
Quick insights in Power BI
Ask questions of your data with natural language
Create custom Q&A suggestions
Create Workspaces in Power BI
Manually republish and refresh your data
Install and configure a personal gateway
Print and export dashboards and reports
Publish to web
Lesson 7: Publishing and Sharing
Refer to Manage workspaces in Power BI service, Manage semantic models in Power BI, and Create dashboards in Power BI
The Power BI Workflow
Powerbi.com
Online repository
Reports
Semantic models (Datasets)
Dashboards
Workbooks
Share your work
Introduction to the Power BI service
Create Reports in Power BI Desktop
Publish to Power BI service
Publish Power BI Desktop reports
Dashboards are easy to create
Composed of tiles
Create and configure a dashboard
Share dashboards with your organization
Machine learning
Analyzes data for patterns
Quick insights in Power BI
Ask questions of your data with natural language
Setting for dataset
Create custom Q&A suggestions
Collaborate with colleagues
Create Workspaces in Power BI
Easiest way of updating data
Manually republish and refresh your data
Allows automatic data refresh
Install and configure a personal gateway
Limited but functional
Export and Print from the Power BI Service
Makes a report publicly available
Publish to web
OneNote Notebook > Lab 7 - Publishing and Sharing > Exercises 1 & 2
In this lab, use the Power BI Service to publish and share your work with colleagues.
Exercise 1: Explore Dashboards and Reports with the Retail Analysis Sample
Exercise 2: Publish a Power BI Desktop solution to the Power BI Service
Duration: 30 minutes
OneNote URL: bit.ly/pbixl
Lab 7: Publishing and Sharing
--- Reference: BPBEX1 - Power BI Reference.pdf ---
## Power BI for Excel Users - Labs and Reference
Lesson 1: Getting started
Introducing Power BI
A quick look at Power BI Desktop
A quick look at the Power BI service
Lab 1: Set Up the Lab Environment
Lesson 2: Power BI and Excel
Introduction to using Excel data in Power BI
Upload Excel data to the Power BI service
Using Excel as a Data Source
Import Power View and Power Pivot to Power BI
Lab 2: Power BI and Excel
Lesson 3: Power BI and Other Data
Data Sources in Power BI Desktop
Connect to data sources in Power BI Desktop
More advanced data sources and transformation
Connect to SQL Server in Power BI Desktop
Connect to CSV files in Power BI Desktop
Lab 3: Getting Data
Lesson 4: Clean, Transform, and Load Data
Introduction to the Query Editor
Clean and transform your data with the Query Editor
Cleaning irregularly formatted data
Lab 4A: Shaping and Transforming Data
Lab 4B: More Shaping and Transforming Data
Lesson 5: Modeling Data
Introduction to modeling your data
How to manage your data relationships
Optimizing data models
Introduction to DAX
DAX functions
Calculated columns in DAX
Calculated measures in DAX
Create calculated tables
Explore your time-based data
Lab 5: Modeling Data
Lesson 6: Visualizations
Introduction to visuals in Power BI
Create and customize simple visualizations
Combination Charts
Modify colors in charts and visuals
Text boxes, shapes, and smart narratives
Page layout and formatting
Group interactions among visualizations
Overview Page 1
## Group interactions among visualizations Visual hierarchies and drill-down Using custom visualizations R integration in Power BI Desktop Lab 6: Visualizations Lesson 7: Publishing and Sharing Introduction to the Power BI service Publish Power BI Desktop Reports Create and configure a dashboard Share dashboards with your organization Quick insights in Power BI Ask questions of your data with natural language Create custom Q&A suggestions Create workspaces in Power BI Manually republish and refresh your data Install and configure a personal gateway Export dashboards and reports to PDF Printing from the Power BI Service Publish to web from Power BI Lab 7: Publishing and Sharing
## Overview Page 2
## Material Reference
Overview Page 3
## Overview Page 4
## Overview Page 5
## Overview Page 6
## Comparison: Power BI and Excel
Power BI Excel
Power BI is an advanced analytics applicationwith many Excel is atraditional spreadsheet applicationthat has been
powerful features to workwith and analyze data. available for many years.
Analytics More powerful analytical capabilities than Excel. Excel offers simple analytics.
Collaboration Sharing documents and working with others is easy but Sharing data and reports is easy with Excel.
requires licenses.
Collaboration Reports can be worked on by many users, whether they Excel is everywhere and available to most people.
are experts or not.
Dashboards Power BI Dashboards allow report creators to capture Excel has slicers to make the dashboards interactive.
slicers and filters. Pinning a live page allows for direct
interaction with all visuals.
Dashboards More advanced features are available for creating custom Users have limited dashboard creation features.
dashboards.
Data Model You can access and analyzedata from mostplatforms, Limited connectivity with other applications and systems.
software, and applications.
Data Model Individual data tables can be easily related. It may be difficult to relateseparate data tables.
Data Model Power BI can handle enormous amounts of data with the Exceloften crasheswhen working withlarger datasets.
Power Pivot engine model.
Data Model Power BI allows users to work withsimple and structured Excel is ideal for building complex data models.
data models.
Data Model Capable of handling large datasets. Capable of handling large datasets, but it is limited by the
smaller capacity of Excel.
Data Model Power BI has Power Query andcan acquiredata from Excel has Power Query andcan acquiredata from many available
many available sources. sources.
Data Security Power BI supports static and dynamic row-level security When you share the dashboard with external stakeholders, it also
which means that consumers will only have access to the requires sharing of the underlying data, which does not
data that they have been given permission to see. guarantee security.
Difficulty Power BI is a newer technology and might require some Excel has been around for a long time and most users have
time to master. Users can start building reports with a enough basic skills to build reports.
minimal amount of learning.
Formulas Power BI uses the DAX language for formulas. Excel uses the MDX language for formulas.
Licensing Power BI Desktop is free, but users must be licensed to Excel is included in all Microsoft 365 subscriptions.
use Power BI Service.
Performance Faster processing than Excel. Slower processing than Power BI.
Tabular Reports Power BI is not well-suited for tabular style reports. Excel handles tabular style reports much more effectively.
Usability Power BI can be easier to work with than Excel for many Creating visually rich reports can be more difficultthan Power
common reporting tasks. BI.
Visual Reports Power BI allows cross-filtering/cross-highlighting between Excel does not offercross-filtering between charts.
visualizations.
Visual Reports Visually beautiful, interactive reports. Visually simple and less complexreports.
Visuals Power BI has a wide variety of built-in visualizations as Excel has fewer built-in charts.
well as the ability to import many other visuals from the
Marketplace.
Overview Page 7
## Course Information
Saturday, August 10, 2024 12:24 PM
Power BI Version
This courseware has been prepared using Power BI Desktop Version: 2.130.930.0 64-bit (June 2024). All
pbix files and supporting files were created using that version.
Overview Page 8
## Lab 1: Set Up the Lab Environment
Duration: 15 minutes
In this lab, we'll prepare the environment for training by installing the latest version of Power BI
Desktop, downloading all course files and samples, and installing the Visual Studio Code
(optional).
Exercise 1: Download and Install Power BI Desktop
Exercise 2: Download the Class Files
Exercise 3: (Optional) Download and Install the Visual Studio Code
Exercise 4: Receive your Power BI account information from your
instructor and record that information for use in the class
Lab 1 -Set Up the Lab Environment Page 9
## Exercise 1: Download and Install Power BI Desktop
Sunday, July 28, 2024 4:05 PM
Note: This does NOT apply to the instructor-led courses.
Exercise 1: Download and install Power BI Desktop
The Power BI team updates the product every month. To ensure that your experience is
as current as possible, we will have you install the current version of Power BI Desktop
from the Microsoft download site.
1. Open a browser and navigate to the download URL: http://aka.ms/pbiSingleInstaller
(This redirects to https://www.microsoft.com/en-us/download/details.aspx?id=58494.)
2. On the download page click on the blue button labeled “Download”.
3. On the next page, select the option labeled “PBIDesktopSetup_x64.exe” and then
click the “Download” button.
4. Once the installer is done downloading, double-click it to launch.
5.
5. Follow the prompts on the installer, answering each in the affirmative.
Lab 1 -Set Up the Lab Environment Page 10
## 6.
## Lab 1 -Set Up the Lab Environment Page 11
## When you click “Finish”, Power BI Desktop launches.
## Lab 1 -Set Up the Lab Environment Page 12
6. You have now installed Power BI Desktop.
End of Exercise 1
## Proceed to Exercise 2
Lab 1 -Set Up the Lab Environment Page 13
## Exercise 2: Download the Class Files
Note: This does NOT apply to the instructor-led courses.
The files we'll be working with in the labs are available for you to download whenever you wish. To match the lab
instructions, it's recommended that you place them on your desktop.
1. Open a browser and enter the URL: http://bit.ly/ONLCBPBEX1FILES
2. This brings you to the download location. Click the Download button at the top left of the page.
Note: If you're using Edge and it asks for confirmation, click "Save".
3. In Windows Explorer, go to the Downloads directory and locate the compressed file called "BPBEX1ClassFiles". Double-
click it to open it.
Lab 1 -Set Up the Lab Environment Page 14
4. In the Downloads folder, once the extract operation is completed, there will be a folder called BPBEX1ClassFiles. Rename
that folder to BPBEX1 and drag it to the Desktop.
5. You have now downloaded the course lab files and copied them to the desktop.
## Proceed to Exercise 3
Lab 1 -Set Up the Lab Environment Page 15
## Exercise 3: (Optional) Download and Install Visual Studio Code
Note: This does NOT apply to the instructor-led courses.
Some of the labs involve editing various kinds of source code, so a good code editor may be useful.
Visual Studio Code is free, lightweight, and very, very good.
1. Open a browser and go to the URL: https://code.visualstudio.com/download
2. On the download page, click the "Windows" link.
3. If the browser asks, click "Run".
4. After downloading, the install wizard starts. Proceed through the wizard, accepting all defaults and
answering everything in the affirmative.
5. Accept the licensing agreement.
Lab 1 -Set Up the Lab Environment Page 16
## 6. Keep the default start menu folder. 7. Keep the defaults. 8. Once the wizard has completed, click "Finish".
## Lab 1 -Set Up the Lab Environment Page 17
9. You have now set up Visual Studio Code.
## End of Lab 0
Lab 1 -Set Up the Lab Environment Page 18
## Exercise 4: Receive your Power BI account information from your instructor and record that information for use in the class
Note: This only applies to instructor-led courses.
Your instructor has created accounts for your use in the classroom. These accounts will be presented to
you at this time. It is recommended that you login to the Power BI Desktop and the Power BI Service with
the same account for ease of Publishing content later. Please ensure you are using the identical account in
both places.
In most cases, these logins may be used for a period of time after class has ended. Please record the login
information and password your instructor provides you now so you can use it throughout class.
Lab 1 -Set Up the Lab Environment Page 19
## Lab 2: Power BI and Excel
Before you begin this lab, close any existing Power BI Desktop solutions and exit Power BI Desktop.
Duration: 30 minutes
In this lab, you learn how to import a table of data from an Excel file and create a report to
visualize this data. You will also import an Excel Data Model into Power BI.
Exercise 1: Use Excel as a Data Source
Task 1: Explore the contents of the workbook.
Task 2: Import data from the workbook into Power BI Desktop
Exercise 2: Import an Excel Data Model and Power Views
Task 1: Explore the contents of the workbook.
Task 2: Import the workbook into Power BI Desktop
Lab 2 -Power BI and Excel Page 20
## Exercise 1: Use Excel as a Data Source
If you've used the Power BI Desktop prior to this lab, be sure to close the Power BI Desktop and re-
open it to start with a "clean slate".
Note:This exercise uses files that are located in a folder called "BPBEX1", which is on your lab machine's
desktop.
In this exercise, you learn how to use tables in an Excel workbook as a data source. This activity uses the
workbook entitled "Financial Sample.xlsx". It is available in the student lab files directory, and can also be
directly downloaded via this link: https://go.microsoft.com/fwlink/?LinkID=521962
In this exercise:
• Task 1: Explore the contents of the workbook.
• Task 2:Import data from the workbook into Power BI Desktop
Task 1: Explore the contents of the workbook
1. On the desktop of your lab machine, double-click the BPBEX1folder:
Note: If you downloaded the files to your machine manually, the BPBEX1 folder might be
somewhere else on your machine, e.g., the Downloads directory. But, if you're using the machine
that ONLC provided you, the directory should be right there on the desktop.
2. Browse to the "Power BI Samples" folder and double-click the "Financial Sample.xlsx" workbook to
open it in Excel. (Note:If you downloaded the file using the above link, it's probably in your
"Downloads" folder instead -open it from there.)
Lab 2 -Power BI and Excel Page 21
## 3. Note that it contains a single table of data. 3. Close the workbook.
## Task 2: Import data from the workbook into Power BI Desktop
## 1. Start Power BI Desktop
a. Double-click the Power BI Desktop shortcut on the PC's desktop, or
b. Click the Start Button, type "Power BI Desktop", and click the Power BI Desktop icon
Lab 2 -Power BI and Excel Page 22
## 2. When Power BI Desktop launches it displays the Home Screen. At the top of the Home Screen, click the tile labeled "Blank report" 3. A new report is created. Optional:Save it with an appropriate name.
## Lab 2 -Power BI and Excel Page 23
## 4. On the Home ribbon, click the downward-pointing arrow on the "Get Data" button, and select "Excel" from the menu that appears. 2. In the "Open" dialog that appears, browse to the "Power BI Samples" folder and select the "Financial Sample" workbook, then click the "Open" button.
## Lab 2 -Power BI and Excel Page 24
## 3. When the "Navigator" dialog appears, it gives you two options, "Sheet1" and "financials". As it happens, these both contain the same data: in this case, "financials" is the table, and it's the only thing on Sheet1 in this workbook. Select "financials" and click the "Load" button. 4. Notice that the columns from the dataset appear in the "Data" pane on the right. Click the checkbox for "Gross Sales", followed by the checkbox for "Product" to create a column chart showing total gross sales by product.
## Lab 2 -Power BI and Excel Page 25
## Lab 2 -Power BI and Excel Page 26
## 5. Now click somewhere blank on the report surface to de-select the column chart. 6. In the Data pane, click the checkbox for "Country" followed by the checkbox for "Profit" to generate a map showing relative profit by country.
## Lab 2 -Power BI and Excel Page 27
NOTE:If the map doesn't appear, you may need to enable it by following these instructions: How to enable
map and filled map visuals (Web view)
## 7. Save the project as "ExcelDataSource.pbix". 8. Close Power BI. NOTE: We are closing Power BI because there is no CLOSE FILE option in Power BI. If you continue with Exercise 2 in the same file, you will add more information to the existing data model created here. Sometimes that is your intention (and we will do this later). However, It is NOT our intention to mix data in the same data model for this Lab. In this activity, you have imported a table of data from Excel and used it to generate two different visualizations of the data.
## Proceed to Exercise 2
Lab 2 -Power BI and Excel Page 28
## Exercise 2: Import an Excel Data Model and Power Views
Note:This exercise uses files that are located in a folder called "BPBEX1", which is on your lab machine's
desktop.
In this exercise, you learn how to import an Excel data model, along with any power queries and power
views, into Power BI Desktop, effectively converting your Excel-based analytic solution into a Power BI
one. For this activity you will use the "Retail Analysis Sample" workbook. The workbook is available in
the student lab files directory, and can also be directly downloaded via this link:
https://github.com/microsoft/powerbi-desktop-samples/raw/main/powerbi-service-samples/Retail%
20Analysis%20Sample-no-PV.xlsx
Note: Microsoft Silverlight reached the end of lifecycle in October 2021. In the Excel sample, we are
unable to see anything created with Power View but we will learn that the file can be imported to
Power BI successfully with most of the visuals (as well as the data model) intact.
In this exercise:
• Task 1: Explore the contents of the workbook.
• Task 2:Import the workbook into Power BI Desktop
Task 1: Explore the contents of the workbook
1. Browse to the "Power BI Samples" folder and double-click the "Retail Analysis Sample.xlsx"
workbook to open it in Excel. If you are asked to "Enable Content", do so. If any other prompts
appear, answer in the affirmative, e.g. "Yes", "Enable", etc.
It initially displays a sheet named "Info", which gives some background on the workbook's
contents.
2. Click the "Enable Content" button in the yellow warning banner to confirm the Security Warning as
well as the "Enable" button to enable the inactive add-ins.
3. Click on the "Overview" sheet to display it. Note that it is a Power View worksheet. It requires
Silverlight to display the visualization. Silverlight was obsoleted in October 2021 so we're unable to
Lab 2 -Power BI and Excel Page 29
## Silverlight to display the visualization. Silverlight was obsoleted in October 2021 so we're unable to see the visualizations.There is a yellow warning along the top that links to Silverlight installation, but this is non-functional again as of October 2021. If you are working on a machine that installed Silverlight prior to October 2021, the Power View worksheets will still display. Microsoft's intention is now for you to use only Power BI for ALL interactive visualizations. 3. Click on the other sheets to examine their contents and you'll notice that they're also unable to display correctly. 4. Select the "Power Pivot" ribbon and click the "Manage" button to display the workbook's data model. 5. Note that the data model contains several tables of data. Also note that it contains a number of calculated measures (which we will discuss later) and KPIs.
## Lab 2 -Power BI and Excel Page 30
## 6. Close the data model window, and exit out of Excel.
## Task 2: Import the workbook into Power BI Desktop
## 1. Switch to Power BI Desktop. (Note: If it is not running, start it by following these instructions: Start up Power BI Desktop and Create a New File.) On the File menu, select "Import-->Power Query, Power Pivot, Power View".
Lab 2 -Power BI and Excel Page 31
## 2. In the "Open" dialog, browse to the "Power BI Samples" directory and select "Retail Analysis Sample", then click the "Open" button. 3. When the "Import Excel Workbook Contents" dialog appears, click "Start".
## Lab 2 -Power BI and Excel Page 32
## 4. When the import completes, examine the contents of the resulting "Import Excel workbook contents" dialog. Note that Power BI Desktop has imported queries, tables, measures, KPIs, and Power View sheets. Click "Close". 5. Explore the resulting Power BI solution file. Note that while not all of the visuals have been successfully imported, each Power View sheet has become a page in the report. Also, the data has all been imported into the Power BI data model, along with the calculated measures and KPIs. 6. Save the solution as "ExcelDataModelImport.pbix" and close Power BI Desktop. 7. In this activity you have successfully imported an Excel book containing queries, a data model, and Power Views into Power BI Desktop.
## End of Lab
Lab 2 -Power BI and Excel Page 33
## End of Lab
Lab 2 -Power BI and Excel Page 34
## Lab 3: Power BI and Other Data
Before you begin this lab, close any existing Power BI Desktop solutions and exit Power BI Desktop.
Duration: 30 minutes
In this lab, you learn how to import a table of data from a Web page and create a report to visualize this
data. As part of this process, you navigate across tables available on a web page, and apply data
transformation steps to bring the table into a new shape.
Exercise 1: Analyzing Web page data using Power BI Desktop
Task 1: Add a Wikipedia page data source
Exercise 2: Shape data in the subject table
Task 1: Do initial shaping of the data
Task 2: Remove Other Columns to only display columns of interest
Task 3: Replace Values to clean up values in a selected column
Task 4: Filter values in a column
Task 5: Trim whitespace from a column
Task 6: Rename a column
Task 7: Filter out null values in a column
Task 8: Name a query
Exercise 3: Create visualizations using the Report view
Task 1: Load the query to your report
Task 2: Create a Map visualization
Lab 3 -Power BI and Other Data Page 35
## Exercise 1: Analyzing Web page data using Power BI Desktop
Before you begin this lab, close any existing Power BI Desktop solutions and exit Power BI Desktop.
In task 1, you import a Tournament Summary table from the UEFA European Football Championship
Wikipedia page at the following location: https://en.wikipedia.org/w/index.php?
title=UEFA_European_Championship&oldid=841580479
Task 1: Add a Wikipedia page data source
1. In the Getting Started dialogor in the Homeribbon tab, select Get data from other sources.
2. This brings up the Get Datadialog, where you can pick from a wide range of data sources to import data
into Power BI Desktop. We will select Webwhich is available under the Allor Othergroup.
Lab 3 -Power BI and Other Data Page 36
## 3. In the Web Contentdialog box, in the URLtext box, paste the Wikipedia URL: https://en.wikipedia.org/w/index.php?title=UEFA_European_Championship&oldid=841580479 Click OK.
## Lab 3 -Power BI and Other Data Page 37
## 4. Click OK. 5. If you are presented with the "Access Web Content" dialog box, leave "Anonymous" selected and click the Connectbutton. After establishing a connection to the web page, you see a list of tables available on this Wikipedia page in the Navigatordialog. You can single-click on each of these tables to preview the data. 6. In the Navigatorleft-pane, select the table named Table2(in older versions of Power BI it might also be named Results) and click the Transform Databutton. This will allow us to reshape this table before loading it to the Report, since the data is not in the shape that we need for our analysis.
## Lab 3 -Power BI and Other Data Page 38
## This will land a preview of the table in the Query view, where we can apply a set of transformation steps to clean up the data.
## Proceed to Exercise 2
Lab 3 -Power BI and Other Data Page 39
## Exercise 2: Shape data in the subject table
Now that you have the subject table selected for your data query, you learn how to perform
various data shaping and cleansing steps.
Task 1: Do initial shaping of the data
1. Push the top row into the header by clicking the Use First Row as Headersbutton in the
Transform group.
2. Remove the new top row by clicking theRemove Rowsbutton in theReduceRows section
of theHomeribbon, selecting theRemove Top Rowsoption, and entering "1" into the
resulting dialog box.
3. Rename the "Final" column to "Final Winner" by double-clicking the column name in the
header row and typing the new column name.
Task 2: Remove Other Columns to only display columns of interest
In this step, you remove all columns except Yearand Final Winner.
1. In the Query Previewgrid, select the Yearand Final Winnercolumns (use CTRL+ Click).
2. Right-click a column header in the Query Previewgrid, and click Remove Other Columns
to remove the unselected columns. Note that this operation is also available in the Home
ribbon tab, in the Manage Columnsgroup.
Lab 3 -Power BI and Other Data Page 40
## Task 3: Replace Values to clean up values in a selected column
## In this step, you replace the Details suffix in the Yearcolumn. Note that this suffix is on a new line so it is not visible in the table preview. However, if you click in one of the cells with a numeric value in the Year column, you will see the full value in the detailed view. 1. Select the Yearcolumn. 2. In the Homeribbon, click Replace Valuesin theTransformgroup, or right-click the Year column, and click Replace Valuesto replace Details with empty text. 3. In the Replace Valuesdialog box, type Details in the Value to Findtext box and leave the Replace Withtext box empty. 4. Click OK.
## Task 4: Filter values in a column
## In this step, you filter the Yearcolumn to display rows that do not contain "null" or “Year”. 1. Click the filter drop down arrow on the Yearcolumn. 2. In the Filterdrop-down, clear the (null)option. Click OK.
Lab 3 -Power BI and Other Data Page 41
## 3. Click OK. 4. Repeat steps 1, 2, and 3 to clear the Yearoption.
## Task 5: Trim whitespace from a column
## In this step, you remove leading and trailing whitespace in the Year column. 1. Click on the Yearcolumn.
Lab 3 -Power BI and Other Data Page 42
## 1. Click on the Yearcolumn. 2. On the Transformribbon, in the Text Columngroup, click on the Formatitem and select Trimfrom the dropdown menu. Note that the whitespace is removed from the Yearcolumn.
## Task 6: Rename a column
## Now that we have cleaned up the data in the Yearcolumn, we are going to work on the Final Winnercolumn. Since we are only looking at the list of winners, we can rename this column to Country. 1. Select the Final Winnerscolumn in the Query preview. 2. In the Query viewribbon, under the Transformtab and Any Columngroup, you will find Rename. This will make the column name editable. 3. Rename this column to Country.
## Task 7: Filter out null values in a column
## We also need to filter out null values in the Countrycolumn. In order to do this, we could use the filter menu as we saw in Step 3, or alternatively we can: 1. Right-click on one of the cells in the Countrycolumn that contain a null value. 2. Select Text Filters -> Does not Equalin the context menu. This creates a new filter step to remove rows with null values in the Countrycolumn.
## Task 8: Name a query
## In this step, you name your final query Euro Cup Winners. 1. In the Query Settingspane, in the Nametext box, enter Euro Cup Winners.
Lab 3 -Power BI and Other Data Page 43
## Proceed to Exercise 3
Lab 3 -Power BI and Other Data Page 44
## Exercise 3: Create visualizations using the Report view
Now that we have converted the data into the shape that we need for our analysis, we can load the
resulting table into our Report and create a few visualizations.
Task 1: Load the query to your report
1. In order to load the query results to Power BI Desktop and create a report, we select Close & Apply
from the Homeribbon.
This will trigger evaluation of the query and load of the table output to the Report.
You can see the resulting table fields in the Data paneat the right of the Report view.
Task 2: Create a Map visualization
Lab 3 -Power BI and Other Data Page 45
## Task 2: Create a Map visualization
## In order to create a visualization, we can drag fields from the Field listand drop them in the Report canvas. 1. Drag the Countryfield and drop it in the Report canvas. This will create a new visualization in the Report canvas. In this case, since we have a list of countries, it will create a Map visualization.
NOTE:If the map doesn't appear, you may need to enable it by following these instructions: How to enable
map and filled map visuals (Web view)
## We can easily change the type of visualization by clicking on a different icon in the Visualization pane. We are going to stay with the Mapvisualization type. We can also resize the visualization by dragging from one of the corners of the visualization up to the desired size.
Lab 3 -Power BI and Other Data Page 46
## Note that currently all the points in the map have the same size. We want to change this so that countries with more Euro Cup tournaments won are represented with a larger point in the map. 2. In order to do this, drag the Yearfield in the Data listto the Bubble Size box in the lower half of the Visualizations pane. 3. Save the file as "EuroCupWinners.pbix"
## Lab 3 -Power BI and Other Data Page 47
## 3. Save the file as "EuroCupWinners.pbix" As you can see, it is very easy to customize visualizations in your report, in order to present the data in the way that you want. Power BI Desktop provides a seamless end-to-end experience from getting data from a wide range of data sources and shaping it to meet your analysis needs to visualizing this data in rich and interactive ways. Once your report is ready, you can upload it to Power BIand create dashboards based on it, which you can share with other Power BI users. This concludes the Importing Data from the Webtutorial. You can download the completed Power BI Desktop file here.
## End of Lab
Lab 3 -Power BI and Other Data Page 48
## Lab 4A: Clean, Transform, and Load Data
Before you begin this lab, close any existing Power BI Desktop solutions and exit Power BI Desktop.
Duration: 45 minutes
Note:This activity uses files that are located in a folder called "BPBEX1", which is on your lab machine's
desktop.
With Power BI Desktop, you can connect to all sorts of different data sources, then combine and shape
them in ways that facilitate making interesting, compelling data analysis and visualizations. In this
tutorial, you'll learn how to combine data from two data sources.
It's common to have data spread across multiple data sources, such as product information in one
database, and sales information in another. The techniques you'll learn in this document include an Excel
workbook and an OData feed, but these techniques can be applied to other data sources too, like SQL
Server queries, CSV files, or any data source in Power BI Desktop.
In this lab, you import data from Excel (it includes product information) and from an OData feed (which
contains orders data). You'll perform transformation and aggregation steps, and combine data from both
sources to produce a Total Sales per Product and Yearreport that includes interactive visualizations.
Here's what the final report will look like:
Exercise 1: Analyzing sales data from Excel and an OData feed
Task 1: Get product data from an Excel workbook
Task 2: Import order data from an OData feed
Exercise 2: Combine the Products and Orders queries
Exercise 3: Build visuals using your data
Lab 4A -Clean, Transform, and Load Data Page 49
## Task
## Lab 4A -Clean, Transform, and Load Data Page 50
## Exercise 1: Analyzing sales data from Excel and an OData feed
The file used in this lab is Products.xlsx. If you are attending this class live, it is already in your lab
environment (C: Users Student Desktop BPBEX1 Power BI Samples).However, you can follow
the steps in this tutorial if you need the Products workbook, which you can download: Click here
to download Products.xlsx
(The above link address is: https://download.microsoft.com/download/1/4/E/14EDED28-6C58-4055-
A65C-23B4DA81C4DE/Products.xlsx)
In the Save Asdialog box, name the file Products.xlsx.
Task 1: Get product data from an Excel workbook
In this task, you import products from the Products.xlsx file into Power BI Desktop.
Part 1: Connect to an Excel workbook
1. Launch Power BI Desktop. (Start up Power BI Desktop and Create a New File)
2. From the Home ribbon, select Get Data-->Excel.
Excel is one of the Most Commondata connections, so you can select it directly from the
Get Datamenu.
If you select the Get Data button directly, you can also select File > Exceland select
Connect.
3. In the Open Filedialog box, select the Products.xlsxfile.
4. In the Navigatorpane, select the Productstable and then select Transform Data.
Lab 4A -Clean, Transform, and Load Data Page 51
## Part 2: Remove other columns to only display columns of interest
## In this step you remove all columns except ProductID, ProductName, UnitsInStock, and QuantityPerUnit. In Power BI Desktop, there are often a few ways to accomplish the same task. For example, many buttons in the ribbon can also be achieved by using the right-click menu on a column or a cell. Power BI Desktop includes Query Editor, which is where you shape and transform your data connections. Query Editor opens automatically when you select Editfrom Navigator. You can also open the Query Editor by selecting Edit Queriesfrom the Homeribbon in Power BI Desktop. The following steps are performed in Query Editor. 1. In Query Editor, select the ProductID, ProductName, QuantityPerUnit, and UnitsInStock columns (use Ctrl+Clickto select more than one column, or Shift+Clickto select columns that are beside each other). 2. Select Remove Columns> Remove Other Columnsfrom the ribbon, or right-click on a column header and click Remove Other Columns. (Keep ProductID, ProductName, QuantityPerUnit, and UnitsInStockcolumns)
Lab 4A -Clean, Transform, and Load Data Page 52
## Part 3: Change the data type of the UnitsInStock column
## When Query Editor connects to data, it reviews each field and to determine the best data type. For the Excel workbook, products in stock will always be a whole number, so in this step you confirm the UnitsInStockcolumn’s datatype is Whole Number. 1. Select the UnitsInStockcolumn. 2. Select the Data Type indicator(123)button in the column heading. 3. If not already a Whole Number, select Whole Numberfor data type from the drop down (the Data Type:button also displays the data type for the current selection). Note: There might be nothing to do in this section assuming the data type of the UnitsInStock column is already Whole Number.
'
## Power BI Desktop steps created
## As you perform query activities in Query Editor, query steps are created and listed in the Query Settingspane, in the Applied Stepslist. Each query step has a corresponding formula, also known as the "M" language. For more information about the “M” formula language, see Learn about Power BI formulas.
Lab 4A -Clean, Transform, and Load Data Page 53
Note: If it was necessary to change to the data type for the UnitsInStock column (as shown in
Part 3), the Applied Steps and the M script might look different from what is shown in these
images.
## Task 2: Import order data from an OData feed
In this task, you'll bring in order data. This step represents connecting to a sales system. You
import data into Power BI Desktop from the sample Northwind OData feed at the following URL,
which you can copy (and then paste) in the steps below:
https://services.odata.org/V3/Northwind/Northwind.svc/
## Part 1: Connect to an OData feed
1. From the Homeribbon tab in Query Editor, click the bottom of the New Source button to
expand the menu.
2. Select the OData Feed.
3. In the OData Feeddialog box, paste the URLfor the Northwind OData feed.
Lab 4A -Clean, Transform, and Load Data Page 54
## 4. Select OK. 5. In the Navigatorpane, select the Orderstable, and then select OK. Note: You can click a table name, without selecting the checkbox, to see a preview.
## Part 2: Expand the Order_Details table
## The Orderstable contains a reference to a Detailstable, which contains the individual products that were included in each Order. When you connect to data sources with multiples tables (such as a relational database) you can use these references to build up your query. In this step, you expand the Order_Detailstable that is related to the Orderstable, to combine
Lab 4A -Clean, Transform, and Load Data Page 55
## In this step, you expand the Order_Detailstable that is related to the Orderstable, to combine the ProductID, UnitPrice, and Quantitycolumns from Order_Detailsinto the Orderstable. This is a representation of the data in these tables: The Expandoperation combines columns from a related table into a subject table. When the query runs, rows from the related table (Order_Details) are combined into rows from the subject table (Orders). After you expand the Order_Detailstable, three new columns and additional rows are added to the Orderstable, one for each row in the nested or related table. 1. In the Query View, scroll to the Order_Detailscolumn (it's to the FAR RIGHT of the table). 2. In the Order_Detailscolumn, select the expand icon 
. 3. In the Expanddrop-down: 1. Select (Select All Columns)to clear all columns. 2. Select ProductID, UnitPrice, and Quantity. 3. Click OK.
## Lab 4A -Clean, Transform, and Load Data Page 56
## Part 3: Remove other columns to only display columns of interest
## In this step you remove all columns except OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantitycolumns. In the previous task, you used Remove Other Columns. For this task, you remove selected columns. 1. In the Query View, select all columns by completing a. and b.: a. Click the first column (OrderID). b. Shift+Click the last column (Shipper). 2. Now that all columns are selected, use Ctrl+Clickto unselect the following columns: OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity. 3. Now that only the columns we want to remove are selected, right-click on any selected column header and click Remove Columns. (Keep OrderDate, ShipCity, ShipCountry, Order_Details.ProductID, Order_Details.UnitPrice, and Order_Details.Quantity columns.)
## Part 4: Calculate the line total for each Order_Details row
## Power BI Desktop lets you to create calculations based on the columns you are importing, so you can enrich the data that you connect to. In this step, you create a Custom Columnto calculate the line total for each Order_Detailsrow. Calculate the line total for each Order_Detailsrow: 1. In the Add Columnribbon tab, click Custom Column. 2. In the Custom Columndialog box, in the Custom Column Formulatextbox, enter [Order_Details.UnitPrice]* [Order_Details.Quantity] 3. In the New column nametextbox, enter LineTotal.
Lab 4A -Clean, Transform, and Load Data Page 57
4. Click OK.
## Part 5: Set the datatype of the LineTotal field
## 1. Right click the LineTotalcolumn. 2. Select Change Typeand choose Decimal Number.
Lab 4A -Clean, Transform, and Load Data Page 58
## Part 6: Rename and reorder columns in the query
## In this step you finish making the model easy to work with when creating reports, by renaming the final columns and changing their order. 1. If necessary, in Query Editor, drag the LineTotalcolumn to the left, after ShipCountry (this step might be unnecessary). 2. Remove the Order_Details.prefix from the Order_Details.ProductID, Order_Details.UnitPriceand Order_Details.Quantitycolumns, by double-clicking on each column header, and then deleting that text from the column name.
## Power BI Desktop steps created
## As previously mentioned, performing query activities in Query Editor creates query steps, which are listed in the Query Settingspane, in the Applied Stepslist. Each query step has a corresponding Power Query formula, also known as the "M" language. For more information about this formula language, see Learn about Power BI formulas.
Lab 4A -Clean, Transform, and Load Data Page 59
## Note: If it was not necessary to move the LineTotal column as per Task 2, Part 6, then Applied Steps and the M scripts might look a little different from these images.
## Proceed to Exercise 2
Lab 4A -Clean, Transform, and Load Data Page 60
## Exercise 2: Combine the Products and Orders queries
Tuesday, September 21, 2021 4:28 PM
Power BI Desktop does not require you to combine queries to report on them. Instead, you can create
Relationshipsbetween datasets. These relationships can be created on any column that is common to
your datasets. For more information see Create and manage relationships.
In this tutorial, we have Orders and Products data that share a common 'ProductID' field, so we need to
ensure there's a relationship between them in the model we're using with Power BI Desktop. Simply
specify in Power BI Desktop that the columns from each table are related (i.e. columns that have the same
values). Power BI Desktop works out the direction and cardinality of the relationship for you. In some
cases, it will even detect the relationships automatically.
In this task, you confirm that a relationship is established in Power BI Desktop between the Productsand
Ordersqueries.
1. First, we need to load the model that we created in Query Editor into Power BI Desktop. From the Home
ribbon of Query Editor, select Close & Apply.
Power BI Desktop loads the data from the two queries.
2. Once the data is loaded, select the Manage Relationshipsbutton Modelingribbon.
Lab 4A -Clean, Transform, and Load Data Page 61
## 3. Select the New relationshipbutton. 4. A blank Relationship window will open. Select the Products table from the drop-down list and we see that the related Orders table automatically appears. 5. When we attempt to create the relationship, we see that one already exists! As shown in the Create Relationshipdialog (by the shaded columns), the ProductsIDfields in each query already have an established relationship.
## Lab 4A -Clean, Transform, and Load Data Page 62
## 6. Select Cancel, then Close the "Manage Relationships" dialog, and then select Modelview in Power BI Desktop.
## Lab 4A -Clean, Transform, and Load Data Page 63
## We see the following, which visualizes the relationship between the queries. 7. When you double-click the arrow on the line that connects the to queries, an Edit Relationshipdialog appears.
## Lab 4A -Clean, Transform, and Load Data Page 64
## 8. Change the Cross Filter Direction for this relationship to BOTH. 9. Click Save 10. Return to the Report View.
## Lab 4A -Clean, Transform, and Load Data Page 65
## Proceed to Exercise 3
Lab 4A -Clean, Transform, and Load Data Page 66
## Exercise 3: Build visuals using your data
Power BI Desktop lets you create a variety of visualizations to gain insights from your data. You
can build reports with multiple pages and each page can have multiple visuals. You can interact
with your visualizations to help analyze and understand your data. For more information about
editing reports, see Edit a Report.
In this task, you create a report based on the data previously loaded. You use the Data pane to
select the columns from which you create the visualizations.
Part 1: Create charts showing Units in Stock by Product and Total Sales
by Year
1. Click the Clustered Column Chartvisual in theVisualizationspane.
2. Drag UnitsInStockfrom the Data pane (the Data pane is along the right of the screen) into the
Y-axis well in the Visualizations pane.
3. Verify that UnitsInStock appears in the Valuewell. If it isn't (if it ended up in the Axiswell) drag
it to Value.
4. Verify that it is summarizing as a sum by clicking the dropdown arrow next to UnitsInStockin
the Valuewell and selecting Sumfrom the dropdown menu.
Lab 4A -Clean, Transform, and Load Data Page 67
## 5. Next, drag ProductNameto the X-axiswell. 6. Resize the visualization by dragging one of the handles to make it larger. 7. Select Sort By > UnitsInStockusing the ellipsis(...) in the top right corner of the visualization.
## Lab 4A -Clean, Transform, and Load Data Page 68
## 8. Drag OrderDateto the canvas beneath the first chart, then drag LineTotal(again, from the Data pane) onto the visual, then select Line Chart. 9. In the X-axis well of the Visualizations panel, click the down-arrow on the OrderDatefield and choose OrderDate.
## Lab 4A -Clean, Transform, and Load Data Page 69
## 10. The following visualization is created. 11. Next, drag ShipCountryto a space on the canvas in the top right. Because you selected a geographic field, a map was created automatically.
## NOTE:If the map doesn't appear, you may need to enable it by following these instructions: How to enable map and filled map visuals (Web view)
## 12. Now drag LineTotalto the Bubble Size field; the circles on the map for each country are now relative in size to the LineTotalfor orders shipped to that country.
## Lab 4A -Clean, Transform, and Load Data Page 70
## Part 2: Interact with your report visuals to analyze further
Power BI Desktop lets you interact with visuals that cross-highlight and filter each other to
uncover further trends. For more detail see Filtering and Highlighting in Reports
1. Click on the light blue circle centered in Canada.Note how the other visuals are filtered to show
Stock (ShipCountry) and Total Orders (LineTotal) just for Canada.
Lab 4A -Clean, Transform, and Load Data Page 71
2. Save the file as "Northwinds.pbix".
## Complete Sales Analysis Report
After you perform all these steps, you will have a Sales Report that combines data from
Products.xlsx file and Northwind OData feed. The report shows visuals that help analyze sales
information from different countries. You can download a completed Power BI Desktop file for
this tutorial here.
## End of Lab 4A
Lab 4A -Clean, Transform, and Load Data Page 72
## Lab 4B: Review
IMPORTANT
The content in this lab is based on a Microsoft tutorial that was written a few years ago --since that time, the web
pages that were originally used as data sources have changed. In the interest of providing a consistent experience for
ONLC students, we have created static web resources that contain copies of the relevant data and mimic the behavior
of the original web sites. In other words, you're not actuallygoing to get your data from bankrate.com and Wikipedia,
but Power BI will act as if you had. The web addresses (URLs) you'll use are:
https://onlcfiles.blob.core.windows.net/webpages/bankrate_whereretire.html(instead of the bankrate.com URL)
https://onlcfiles.blob.core.windows.net/webpages/wikipedia_USStates.html (instead of the wikipedia page)
Before you begin this lab, close any existing Power BI Desktop solutions and exit Power BI Desktop.
Duration: 45 minutes
Exercise 1: Get data from a web site
Exercise 2: Shape data
Exercise 3: Combine data
Exercise 4: Build reports
Exercise 5: Share your work
Lab 4B -Review Page 73
## Exercise 1: Get data from a web site
Imagine you’re retiring -you want to live where there’s lots of sunshine, preferable taxes, and good health care -or perhaps you’re a
data analyst, and you want that information to help your customers. For example, perhaps you want to help your sunglasses retailer
target sales where the sun shines most frequently.
Either way, the following Web resource has interesting data about those topics, and more:
http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx
NOTE: For the sake of this lab, instead of the above URL, you will use this URL:
https://onlcfiles.blob.core.windows.net/webpages/bankrate_whereretire.html
1. Select Get Data > Weband paste the address.
2. When prompted for an account key, change the option on the left to "Anonymous", then click Connect.
3. When asked about what kind of access to use, leave the default of "Anonymous" selected and click Connect.
At this point, the Queryfunctionality of Power BI Desktop goes to work. Query contacts the Web resource, and the Navigator
Lab 4B -Review Page 74
## At this point, the Queryfunctionality of Power BI Desktop goes to work. Query contacts the Web resource, and the Navigator window returns what it found on that Web page. In this case, it found a table (called "Ranking of best and worst states for retirement") and the overall Web Document. 3. We’re interested in the table, so we select it from the list. The Navigatorwindow displays a preview. 4. At this point we can edit the query before loading the table, by selecting Transform Datafrom the bottom of the window, or we can load the table. When we select Transform Data, Query Editor launches and a representative view of the table is presented. The Query Settings pane is displayed (if it’s not, you can select Viewfrom the ribbon, then Show > Query Settingsto display the Query Settings pane). Here’s what that looks like.
## Lab 4B -Review Page 75
For more information about connecting to data, see Connect to Data in Power BI Desktop.
In the next section, we adjust the data so it meets our needs. The process of adjusting connected data is called shapingdata.
## Proceed to Exercise 2
Lab 4B -Review Page 76
## Exercise 2: Shape data
Now that we’ve connected to a data source, we need to adjust the data to meet our needs. Sometimes adjusting means transformingthe data -
such as renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, and so on.
The Query editor in Power BI Desktop makes ample use of right-click menus, in addition to having tasks available on the ribbon. Most of what you
can select in the Transformribbon is also available by right-clicking an item (such as a column) and choosing from the menu that appears.
When you shape data in the Query Editor, you’re providing step-by-step instructions (that Query Editorcarries out for you) to adjust the data as
Query Editorloads and presents it. The original data source is not affected; only this particular view of the data is adjusted, or shaped.
The steps you specify (such as rename a table, transform a data type, or delete columns) are recorded by Query Editor, and each time this query
connects to the data source those steps are carried out so that the data is always shaped the way you specify. This process occurs whenever you
use the query in Power BI Desktop, or for anyone who uses your shared query, such as in the Power BIservice. Those steps are captured,
sequentially, in the Query Settingspane under Applied Steps.
The following image shows the Query Settingspane for a query that has been shaped -we’ll go through each of those steps in the next few
paragraphs.
Let’s get back to our retirement data, which we found by connecting to a Web data source, and shape that data to fit our needs.
In this case the query is just about perfect, so we really only have a couple of things to do to get the query where we want it:
1. Change the Table Name -the name of the query currently displays in the Query Settings pane as "Ranking of best and worst states for
retirement". Double-click it and change it to "RetirementStats"
2. Use First Row as Headers -click the Transform tab then click the second button from the left ("Use First Row as Headers"). The first row values will push
up into the column name spaces.
3. Remove the first row -notice that the first row contains values that we won't want in our report (Source: Bankrate’s 2019 “Best and worst states for
retirement” study). On the Home tab, click the Remove Rowsbutton and choose Remove Top Rows.
4. Enter a 1 in the box and click OK.
Lab 4B -Review Page 77
Notice that all of the ranking columns are still text data type. This needs to be fixed before we continue.
5. Click anywhere in the data, then use CTRL + A to select all.
6. On the Transform tab, click Detect data typeand notice that the data types all appear to be correct.
Don't "Close & Apply" -stay in Power Query for the next steps.
## Import and shape another table of data
That data about various states is interesting, and will be useful for building additional analysis efforts and queries. But there’s one problem: most
data out there uses a two-letter abbreviation for state codes, not the full name of the state. We need some way to associate state names with
their abbreviations.
We’re in luck: there’s another public data source that does just that, but it needs a fair amount of shaping before we can connect it to our
retirement table. Here’s the Web resource for state abbreviations:
http://en.wikipedia.org/wiki/List_of_U.S._state_abbreviations
NOTE: For the sake of this lab, instead of the above URL, you will use this URL:
https://onlcfiles.blob.core.windows.net/webpages/wikipedia_USStates.html
1. From the Homeribbon in Query Editor, we:
a. Select New Source > Weband type the address
Lab 4B -Review Page 78
## b. select OK c. If asked about authentication, leave the default of "Anonymous" and click Connect. d. The Navigatorwindow shows what it found on that Web page We select Codes and abbreviations for U.S. states, territories and other regionsbecause it includes the data we want, but it’s going to take quite a bit of shaping to pare that table’s data down. Each of these steps is also demonstrated in Shape and Combine Data in Power BI Desktop. To summarize those steps, here's what we do: 1. Click OK. A preview of the data loads into the query editor. 2. The top of the query looks like this:
## Lab 4B -Review Page 79
## 3. We need to clean it up a bit. Using the techniques covered with the RetirementStats query: a. Remove the first row. b. Use the (new) first row as headers. c. Remove the first tworows. The top of the query should now look like this: 1. Click the filter dropdown button on the header of the"Name and status of region_1"column and filter it to only include the values "Federal district","State", and"State (Commonwealth)". This preserves the rows containing the 50 states and Washington D.C. and discards all of the other rows (which we don't need).
## Lab 4B -Review Page 80
## 2. Remove all columns except for"Name and status of region"and"USPS". a. Click on the header of "Name and status of region"to select the column. b. Holding down the Ctrlkey, click the header of the "USPS"column. Now both columns are selected. c. Right-click the"USPS"header, and from the context menu that appears select Remove Other Columns. 3. Rename"Name and status of region"to "State Name"by double-clicking on the column name in the header and typing the new name. 4. Rename"USPS"to "State Code"by double-clicking on the column name in the header and typing the new name. 5. If the column data types aren't "Text" already, change the data types of both columns to "Text" by clicking on the data type icon at the left side of each header (currently showing as "ABC123" which stands for the "any" data type) and selecting Textfrom the menu that appears.
## Lab 4B -Review Page 81
10. Name the query "StateCodes" by entering the new name into the Namebox on the Query Setting pane at the right-hand side of the editor.
11. In the Query List (along the left hand side of the Power Query window), right-click on the StateCodes query and uncheckEnable Load. Since this
information will be merged into the RetirementStats query in Exercise 3, it is no longer necessary to have this query loaded into Power BI Desktop.
## Proceed to Exercise 3
Lab 4B -Review Page 82
## Exercise 3: Combine data
With the StateCodestable shaped, we can combine these two tables, or queries, into one; since the tables we now have are a
result of the queries we applied to the data, they’re often referred to as queries.
There are two primary ways of combining queries -mergingand appending.
When you have one or more columns that you’d like to add to another query, you mergethe queries. When you have
additional rows of data that you’d like to add to an existing query, you appendthe query.
In this case we want to merge queries. To get started, we select the query into whichwe want the other query to merge, then
select Merge Queriesfrom the Hometab on the ribbon.
1. Select the "RetirementStats" query from the Queries pane on the left.
2. On the Home ribbon, find the Combinegroup, click the Merge Queriesdropdown, and select Merge Queriesfrom the
menu.
3. The Mergewindow appears, prompting us to select which table we’d like merged into the selected table, and then, the
matching columns to use for the merge. Select Statefrom the RetirementStatstable (query), then select the StateCodes
query (easy in this case, since there’s only one other query -when you connect to many data sources, there are many
queries to choose from).
When we select the correct matching columns -Statefrom RetirementStats, andState Namefrom StateCodes -the
Mergewindow looks like the following, and the OKbutton is enabled.
Lab 4B -Review Page 83
## 4. When you click OK, a "Privacy Levels" dialog may appear. If it does, check the checkbox labeled "Ignore Privacy Levels checks for this file..." In a business environment, it might be necessary to worry about privacy levels, but this is an introductory exercise so we won't worry about them here. After the merge completes, a State Codecolumn is created at the end of the query, which is the contents of the table (query) that was merged with the existing query. All columns from the merged query are condensed into the new column, but you can select to Expandthe table, and include whichever columns you want. 5. To expand the merged table, and select which columns to include, select the expand icon
## Lab 4B -Review Page 84
## 5. To expand the merged table, and select which columns to include, select the expand icon 6. The Expandwindow appears. 7. In this case, we only want the State Codecolumn, so we select only that column. We clear the checkbox from Use original column name as prefixbecause we don’t need or want that; if we leave that selected, the merged column would be named StateCodes.State Code(the original column name, or StateCodes, then a dot, then the name of the column being brought into the query). Click OK. 8. If the "State Code" column comes in as the "Any" data type, click on the data type selector at the left of the column header and switch its data type to "Text". Note: Want to play around with how to bring in that StateCodes table? You can experiment a bit, and if you don’t like the results, just delete that step from the Applied Stepslist in the Query Settingspane; your query returns to the state prior to applying that Expandstep. It’s like a free do-over, which you can do as many times as you like until the expand process looks the way you want it. We now have a single query (table) that combined two data sources, each of which has been shaped to meet our needs. This query can serve as a basis for lots of additional, interesting data connections -such as housing cost statistics,
## Lab 4B -Review Page 85
This query can serve as a basis for lots of additional, interesting data connections -such as housing cost statistics,
demographics, or job opportunities in any state.
9. To apply the changes in Query Editorand load them into Power BI Desktop, select Close & Applyfrom the Homeribbon.
## Proceed to Exercise 4
Lab 4B -Review Page 86
## Exercise 4: Build reports
Additional changes can be made after the table is loaded, and you can reload a model to apply any changes you make. But for
now this will do. In Power BI Desktop Reportview, you can begin to build reports.
The Reportview has five main areas:
1. The ribbon, which displays common tasks associated with reports and visualizations
2. The Reportview, or canvas, where visualizations are created and arranged
3. The Pagestab area along the bottom, which lets you select or add a report page
4. The Visualizationspane, where you can change visualizations, customize colors or axes, apply filters, drag fields, and
more
5. The Datapane, where query elements and filters can be dragged onto the Reportview, or dragged to the Filtersarea of
the Visualizationspane
The Visualizationsand Datapanes can be collapsed by selecting the small arrow along the edge, providing more space in the
Reportview to build cool visualizations. When modifying visualizations, you'll also see these arrows pointing up or down,
which means you can expand or collapse that section, accordingly.
Lab 4B -Review Page 87
## To create a visualization, just drag a field from the Datalist onto the Reportview. In this case, let’s drag the Statefield from RetirementStats, and see what happens. 1. Drag the Statefield from the RetirementStatstable in the field list and drop it on the report surface. Look at that... Power BI Desktop automatically created a map-based visualization, because it recognized that the Statefield contained geolocation data. Notice that in the Visualizationspane, I can select different types of visualizations, and in the area below those icons, I can drag fields to different areas to apply a Legend, or otherwise modify the visualization.
## Lab 4B -Review Page 88
## For more information about reports, see Report View in Power BI Desktop. The first Report page provides a perspective of the data based on Overall rank. When we select one of the visualizations, the Data and Filterspanes shows which fields are selected, and the structure of the visualization (which fields are applied to the Shared Axis, Column Values, and Line Values). Let's adjust the map visual and add a few more visuals. 1. Change the map visual to a filled map to show overall rank. a. Click on the map to select it, and then click on the Filled Mapvisual in the Visualizations pane to change to that visual type. b. With the map still selected, click on the "Paint Brush" icon to display the Formatting pane. Then, expand the Fill Colorssection. c. In the Fill colorssection there is an fx button (similar to the Function button in Excel) which will open a dialog for
## Lab 4B -Review Page 89
## c. In the Fill colorssection there is an fx button (similar to the Function button in Excel) which will open a dialog for Conditional Formatting. Click this button. d. In the dialog that displays, switch Format styleto "Gradient" and switch the What field should we base this on? dropdown to "Overall rank" e. Change the Minimumcolor to blue and the Maximumcolor to white from the Theme Colors section.
## Lab 4B -Review Page 90
## f. Click OK. 2. Expand the Map settings --> Controlssection of the Formatting Pane. a. Switch Auto Zoom to the Off position. b. Switch Zoom Buttons to the On position 3. Resize the map and move it to the upper left corner of the page. Zoom into the United State (if necessary). 4. Click on a blank section of the report surface to deselect the map.
## Lab 4B -Review Page 91
## 4. Click on a blank section of the report surface to deselect the map. Now let's add a couple of column charts. 5. In the Data pane on the right, expand the "RetirementStats" table and click the checkboxes for "Overall rank", "Culture", "Wellness", and "State Code". 6. With the resulting column chart still selected, click on the Line and clustered column chartvisual icon. 7. Drag the "Sum of Wellness" field from the Column y-axis well to the Line y-axis well.
## Lab 4B -Review Page 92
## The resulting visual should look like this: 8. Move the visual to a spot below the map and stretch the width to the full width of the report page. 9. Click somewhere blank on the report surface to deselect the visual. 10. In the Data pane on the right, click the checkboxes for "Affordability", "Overall rank", "Weather", and "State Code".
## Lab 4B -Review Page 93
## 11. With the resulting column chart still selected, click on the Line and clustered column chartvisual icon. 12. Drag the "Weather" field from the Column y-axis well to the Line y-axis well.
## Lab 4B -Review Page 94
## 13. Arrange and resize the visuals by dragging them to resemble the following: 14. Save the file as "Retirement Stats" by clicking File --> Save and choosing an appropriate filename and location.
## Lab 4B -Review Page 95
## Proceed to Exercise 5
Lab 4B -Review Page 96
## Exercise 5: Share your work
Now that we have a Power BI Desktop report that’s reasonably complete, we can share it with others on the Power BI
service. There are a few ways to share your work in Power BI Desktop. You can publish to the Power BIservice, you can
upload the .pbix file directly from the Power BI service, or you can save the .pbix file and send it like any other file.
First, let's look at publishing to the Power BIservice directly from Power BI Desktop.
1. On the Homeribbon, select Publish.
You may be prompted to sign in to Power BI.
A dialog will appear to choose a Workspace to publish to. Choose "My Workspace" and click Select.
When the publish process is complete, you see the following dialog.
Lab 4B -Review Page 97
Click the link to open the report in Power BI and it will take you to the published report in the Power BI Service.
## When you sign in to Power BI, you'll see Power BI Desktop file you just loaded in the Dashboards, Reports, and Datasets sections of the service. 1. Once the file is uploaded, you can select the file from the Reportsin the left pane of the Power BI service. The Power BIservice displays the first page of the report.
## Sharing Reports
## You need a Power BI Pro license to share your content. The people you share it with do too, or the content needs to be in a workspace in aPremium capacity. Note:Microsoft E5 subscriptions include Power BI Pro licensing. When you share a dashboard o
[... truncated]
Skill Level: Beginner