PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 1 | 9
Data Query
The query editor in Power BI is used to extract, transform and load data files into the Power BI.
Step 1: - Loading the data into Power BI
Click on Get Data (red box) and select the source, from which data needs to be loaded as shown in
the following figure.
Browse to the location, select the file and click on open as show in the fig. below
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 2 | 9
A navigator window will open up, select the required tables or sheets from the left-hand side (Green
box) and click on transform data (Red box) to load the data in power query editor.
Data Transformation
Step 2: - Applying transformation on the data
After clicking on transform data, power query editor will get open.
The user can also click on transform data from ribbons to enter the power query editor as show in
fig below.
Fig below shows the power query editor used for data transformation
On LHS queries section show the tables loaded and on RHS applied steps show the data
transformation steps applied on a particular query
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 3 | 9
Normalization and Modelling
Step 3: - Creating Relationship
Click on the model tab (Red box), the tab will show all the tables pulled by the query
To create or edit relationship between tables click on manage relationship in the home tab
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 4 | 9
Select New to open the Create relationship dialog box, where we can select the tables, columns,
and any additional settings we want for our relationship.
In the first drop-down list, select Vendor as the first table, then select the Vendor code column.
This side is the one side of our relationship.
In the second drop-down list, Sales is selected as the second table. Select the Vendor ID column.
This side is the many side of our relationship. Accept the defaults for the relationship options, and
then select OK.
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 5 | 9
Dax Modelling
Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build
formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models
Step 4
Click on the Data tab.
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 6 | 9
User can create new measure and calculated columns from Calculations in the table tools
Visualization
Step 5: - Creating Visuals
In the report tab (green box), from visualization section (red box) select a visual that you want to
create as shown in fig. below.
From the fields section select the values column and axis column and drag it to Axis and Values in
Visualization
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 7 | 9
Stacked Bar Graph
Select the stacked bar graph from Visualizations
Select “Product Category” column from “Product” table and “Sales” column from Sales table located
in fields section and drag it to Axis and Values respectively.
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 8 | 9
Axis
We want sales data by Product Category, so drag the product category first. We want to drill down the
data item-wise so, I will add product in the Axis field.
Values
Now, we want to show number of items sold in each product category. For this, let's add Sales in
values field.
Legend
The Legend field is also a useful feature provided by Stacked Bar Chart. It is used to represent our
bar in different colors with different categories.
Tooltip
When you need to achieve the functionality on hover of the bar chart, tooltip is very useful.
Ex: On hovering over the bar chart, I want to show final bill amount and sales.
Drag both the fields in tooltips. After setting all these above properties, our chart will look like the
following.
PBI-3000 Microsoft Power BI
Participant Kit
Solution Document | Rel. 042020
Page 9 | 9
