Power BI for Beginners

Power BI
for
Beginner
s
T. SHAEEN, J. SCHELLINCK | SYSABEE | DATA ACTION LAB
P. BOILY | UNIVERSITY OF OTTAWA | DEPARTMENT OF MATHEMATICS AND STATISTICS IDLEWYLD ANALYTICS | DATA ACTION LAB

S. DAVIES | DAVHILL | DATA ACTION LAB

Instructor – Patrick Boily
Employment
■ Professor Math/Stat [‘19 – now, uOttawa]
■ President [‘16 – now, Idlewyld Analytics]
■ Manager and Senior Consultant [’12 – ‘19, CQADS, Carleton]
■ Public Service [’08 – ’12, ASFC | StatCan | TC | TPSGC]
■ 60+ uni course; 250+ workshop days

Projects
■ GAC; NWMO; CATSA; etc.
■ 40+ projects

Specialization
■ Data visualization; data cleaning (… unfortunately)
■ Application of wide breadth of techniques to all kinds of data
■ Mathematical/statistical modeling

POWER BI FOR BEGINNERS 3

Instructor – Tristan Shaeen
Employment/Experience
■ AI Consultant [‘21 – now, Sysabee, AI Guides]
■ Instructor [‘21 – now, Data Action Lab]

Projects
■ Global Affairs Canada
■ Small and medium enterprises
■ Labour Market Information Council

Specialization
■ Statistical training
■ Data presentation and data storytelling

POWER BI FOR BEGINNERS 4

Course Material
Course Webpage:
Data Visualization Course Notes:
[Link]
[Link]

Contact Info:
Slack Workspace:
pboily@[Link]
tshaeen@[Link] [Link]

POWER BI FOR BEGINNERS 5

Course Description
Organizations have, and continue to accumulate, masses of data from multiple sources.
This data, being largely unformatted, does not yield readily pertinent information. Microsoft
tackled this issue by adding data analytics tools to their Microsoft Office 365 suite.

Power BI, the latest addition to Office 365, enables users to aggregate and interpret
massive data in a user-friendly environment. With this data visualization tool, analysts
build custom data analysis and dashboards with scheduled automated updates:
empowering managers’ decision making with accurate and intuitive real-time measures.

This fast-paced introductory course to Microsoft’s Power BI application covers key

concepts for delivering effective automated dashboards. This is a hands-on training
session: participants learn Power BI’s functionalities as they build an interactive report.
Best practices for data visualizations and dashboards are described and applied through
examples.

POWER BI FOR BEGINNERS 6

Learning Outcomes

At the end of this course, participants will be able to:

■ understand the Power BI Solution workflow to properly use the different apps
■ build an interactive report in Power BI Desktop app
■ combine multiple data sources and create dynamic data visualizations
■ *use storytelling tools: bookmarks, spotlight, focus, tooltip, axis hierarchy
■ explore advanced functionalities to transform and enhance data with Power Query
Editor and measures in DAX

POWER BI FOR BEGINNERS 7

 Sister Courses
DATA SCIENCE DATA VISUALIZATION AND INTRODUCTION
ESSENTIALS DASHBOARDS TO MACHINE
LEARNING
1. Non-Technical 1. Data Viz Concepts
1. Statistical Learning
Aspects 2. Dashboarding
2. Classification
2. Data Science 3. Storytelling with
Basics Data 3. Clustering
3. Data Preparation 4. Data Viz with ggplot2 4. Issues and Challenges
4. Data Engineering

POWER BI FOR BEGINNERS 8

The Set-Up: Borealis
Terraformers

Borealis Terraformers LLC is a terraforming company.

It does business, it transacts with consultants, it has employees, it pays

them, it delivers products, it records its transactions.

It also has projects (Major Capital MC, Minor Capital MIC, Operations and
Management O&M, and Salary SA), and departments.

POWER BI FOR BEGINNERS 9

The Plan: Course Components
Participant Activities (PA): follow along with the instructors.

Instructor Demos (ID): sit back, relax, and take it all in.

Behind the Scenes (BS): a summary of what has been prepared for you.

Dashboard Discussions (DD): let’s talk about the

dashboards. Basic Notions (BN): non-Power BI stuff

worth knowing about. Exercises: take it one step further.

POWER BI FOR BEGINNERS 10

The Plan: Important Documents
We will be exploring Power BI’s functionality by building a 6-page dashboard.

As we want to encourage you to play with the tool without fear that you will break
it (or what we have planned), we have prepared 8 Power BI dashboard files
(PBIX), at various stages of development, which you can load to re-enter the
stream at any moment.

Download [Link] from the course website, and unzip the file to an easily
accessible location on your hard drive, to which you will also download the
course datasets.

Step-by-step instructions for the PA and ID tasks are also available.

POWER BI FOR BEGINNERS 11

 The Plan: Course
Outline
[Link] Query III. Exploration
1. Load Data (PA) 6. Basic Data Visualization Notions (BN)
2. Transform Data (PA) 7. Load PBIX – 1 (PA)
8. Make and Format a Bar Chart (PA)
II. Preparation 9. Make and Format a Donut Chart (ID)
3. Change Data Types (ID)
10. Create a Table to Store Measures (ID)
4. Link Fields with Data Models 11. DAX vs. M. vs. GUI (BN)
(ID)
12. Create Calculated Measures (ID/PA)
5. Create a Filter (BS)
13. Make and Format a Donut Chart (ID)
14. Make and Format a Treemap (BS)

POWER BI FOR BEGINNERS 12

 The Plan: Course
Outline
III. Exploration (cont.) IV. Situational Awareness
15. Create a Calculated Measure (BS) 24. Basic Dashboard Notions (BN)
16. Load and Explore PBIX – 2 (PA) 25. Measures vs. Columns (BN)
17. Create a Calculated Column (BS) 26. Load and Explore PBIX – 3 (PA)
18. Format Decimal Places (BS) 27. Create a Calculated Column (ID)
19. Make and Format a Bar Chart 28. Make and Format a Table (BS)
(BS) 29. Make and Format a Column Chart (BS)
20. Make and Format a Line Chart 30. Load and Explore PBIX – 4 (PA)
(PA) 31. Make and Format a Bar Chart (PA)
21. Create a Calculated Measure (ID) 32. Make Slicers (PA)
22. Make and Format a Line Chart 33. Load and Explore PBIX – 5 (PA)
(ID)
23. Exploring the Data (DD)
POWER BI FOR BEGINNERS 13
 The Plan: Course
Outline
IV. Situational Awareness [Link] (cont.)
(cont.) 42. Load and Explore PBIX – 7 (PA)
34. Make and Format Column Charts (ID) 43. Apply a Page Filter (PA)
35. Load and Explore PBIX – 6 (PA) 44. Make and Format a Small Multiples Chart (PA)
36. Make and Format a Column Chart (PA) 45. Make and Format a Bar Chart (PA)
37. Monitoring the Situation (DD) 46. Annotate a Dashboard (ID)
47. Load and Explore PBIX – 8 (PA)
[Link] 48. Convincing an Audience (DD)
38. Basic Storytelling With Data Notions
(BN) VI. Miscellanea*
39. Create a Calculated Column (ID) 49. *Publish a Power BI Dashboard (BN)
40. Make and Format Small Multiples (ID) 50. *Integrate R in Power BI (BN)

41. Make and Format a Bar Chart (ID)

POWER BI FOR BEGINNERS 14

The Tool: Power BI
Dashboarding tools are, for the most part, story-driven tools:
Microsoft’s Power BI is one such tool (download and install the latest
version if this has not been done yet).
Its learning curve (reasonably steep, as with all new tools) is attenuated
somewhat by its distinctly “Microsoft-ish” point-and-click
functionality.

It allows dashboards to be easily published and distributed on the

web (either internally and/or externally).

But it does not play nicely with MacOS and Linux (there are workarounds).

POWER BI FOR BEGINNERS 15

 Iii] 9 Untitled - Power Bl Desktop Sign in • - 151 X

File Home Insert Modeling View Help

(home) ribbon
l[]X Cd u3 Uj @) EW lip- !]] M [rnJ 9..tJ.:.:I r of
Paste [l: Copy Get Excel Data SQL Enter Dataverse Recent Transform Refresh New Text More New Quick Sensitivity Publish
<$ c. - • - J;r tH data v workbook hub v Server data sources v data v visual box visuals v measure measure V

Clioboard Data Queries Inser t Calculations Sen sitivitv Share

1 1 report button y' Filters » Visualizations » Fields »

Build visual

[g] data button IP Search IP Search

Ii] model button Filters on this page --------------------------------------------ii

L laE. lllirn llIB

J
You haven't loaded any

Add data fields here E lfiltld±t: data yet. Get data

t;:;_ C9 © BE
Filters on all pages

L
12a lilll
lffl lffl
J
R Py
Add data fields here
ojg Iii 0
-··
Values
-------· -- ----- - - ------ -·------------------
Add data fields here
. ·-·... - -------------· - - ... ------------
Drill through

Cross-report ( eoff)

Keep all filters C)

Add drill-through fields here

1
► L Page
+ canvas filter pane visualizations fields pane
Page 1 of 1
----I->----- + 53%
The Data: Borealis
Terraformers
Let’s take a look at one dataset that we are going to use:
• Data Set - [Link]

Download it to your hard drive (you may

need to rename the file).
Code Description
O&M Operations and Maintenance
MC Major Capital
MIC Minor Capital
SA Salary
Tab – Journal Voucher Type Code Tab – Accounting Transactions

POWER BI FOR BEGINNERS 17

 The Data: Borealis
Terraformers
Let’s take a look at another dataset that we are going to use:
• Data Set - [Link]

Download this one to your hard drive too, in the same location (you may also need
to rename the file). Project FTE (- Group-
Code Date out + Level
PR001 01-Apr-18 2 AS-04
PR002 01-May-18 2 AS-05
PR003 01-Jun-18 2 ENG-01
PR004 01-Jul-18 4 PR-01
PR005 01-Aug-18 3 PA-03
PR006 01-Sep-18 2 AS-02
PR007 01-Oct-18 1 CR-03
PR008 01-Nov-18 3 FI-02
PR009 01-Dec-18 4 FO-03
PR010 01-Jan-19 2 CR-01
PR011 01-Feb-19 3 AS-03
Tab – Project Tombstone

POWER BI FOR BEGINNERS 18

 The Data: Borealis
Terraformers
Tab – Project FTE Count

POWER BI FOR BEGINNERS 19

 Expense
Date
Category

Effective
Journal
Date
Voucher Type
Code

have a belong to an Code

Description

Amount
have an Transactions

Journal Director
Voucher Accounting
Item Amount Control Number

Director

Budget

belong to is managed by a
O&M Budget a

has
Salary
a
Budget

Major Cap Project

Budget

Project
Minor Cap Identifier
Budget

Project Name
FTE Budget
[Link] Query
POWER BI FOR BEGINNERS

20
1. Load Data
(PA)
We might want to “tweak” the data before loading it
into Power BI. We can accomplish this using the
Power Query Interface (see detailed
instructions, p. 1).
1. Open Power BI.
2. Close the yellow “Hello” screen.
3. In the “Home” ribbon at the top of the Power BI screen,
there is a clickable region named “Get Data” – activate it
to bring up several data format options.
4. Among all the options, select “Excel”.
5. Click on Data Set - [Link], and select
“Open”.

POWER BI FOR BEGINNERS 21

1. Load Data
(PA)

POWER BI FOR BEGINNERS 22

2. Transform Data
(PA)
We do not need to transform data at the
source.
1. Click on the first TABLE (the one named
“Accounting_Transactions”) without
selecting the checkbox (yet) – this brings up a
display of the table (clicking on the other options
will bring up their displays);
2. Select the “Acounting_Transactions”
checkbox (don’t click on “Load” yet!);
3. We want to tweak the data before loading it, so
we edit the transformation by clicking on
“Transform Data”.

POWER BI FOR BEGINNERS 23

2. Transform Data
(PA)

POWER BI FOR BEGINNERS 24

2. Transform Data
(PA)
We proceed with the following steps to transform the data (see detailed
instructions, p. 1):
1. change the name of the table;
2. change the name of the “Accounting Effective Date” column to “Effective Date” and
“Journal Voucher Item Amount” to “Item Amount”;
3. remove the dates from March 2018 in the “Effective Date” column;
4. close and apply the transformation;
5. save the PBIX file;
6. retrieve the "Project_Tombstone" table from “Data Set – [Link]”.

POWER BI FOR BEGINNERS 25

2. Transform Data
(PA)

POWER BI FOR BEGINNERS 26

[Link]
POWER BI FOR BEGINNERS

27
[Link] Data Types (ID)

We proceed with the following steps (see the detailed instructions document):
1. go to the “Fields” pane and drop down on “Accounting Transactions”;
2. after clicking on “Item Amount” you will see that new options appear in the top
Ribbon, namely, “Column Tools”;
3. this is where we go to change the data type for “Item Amount” from a decimal
value to a currency; we also change “Effective Date” into a short date.

(see detailed instructions, p. 1)

POWER BI FOR BEGINNERS 28

4. Link Fields with the Data
Model (ID)
In the Model, the two tables are linked by the “Project Identifier”

variable. How did this happen?

We enter the “Data Model” view and remove the link. Now the two tables are
unlinked, meaning that they cannot “talk” to one another.

Here is how we would link them (see detailed instructions, p. 1).

POWER BI FOR BEGINNERS 29

4. Link Fields with the Data
Model (ID)

??
?

POWER BI FOR BEGINNERS 30

4. Link Fields with the Data
Model (ID)

POWER BI FOR BEGINNERS 31

4. Link Fields with the Data
Model (ID)

POWER BI FOR BEGINNERS 32

[Link] a Filter (BS)

For the first part of this dashboard (Exploration 1, Exploration 2), we only
use the data from 2018.

We create a page filter by setting the “Effective Date” to 2018.

POWER BI FOR BEGINNERS 33

III. Data
Exploration
POWER BI FOR BEGINNERS

34
[Link]-Analysis Use (BN)
Data visualization can be used to set the stage for analysis:
• detecting anomalous entries
invalid entries, missing values, outliers
• shaping the data transformations
binning, standardization, Box-Cox transformations, PCA-like transformations
• getting a sense for the data
data analysis as an art form, exploratory analysis
• identifying hidden data structure
clustering, associations, patterns informing the next stage of analysis

POWER BI FOR BEGINNERS 35

 A [Link] OF CHART TYPES
Data [Link] charts Data reduc:ti,0 n charts

Coml!J():sitio:n Distribution EV1J 1.1ftiit:J111 Relationship Profil"ng

H tognm

Ill 111 •I II
IDot plo Bui t Panto 11D Sutter-plot
HoMoni Conin ctec:I Scatterplot C.)'cl plot c1J p!o • rl!C

•
jjjjjjj!JI} j
-
•• • - ■ • •
.. •
•

ID S(: tter_p a Hat mop Mui lcHmen 0n1 I Pl Eubbl R • rden,b,I malJrtx Hor:wn

- - - • ■ ■
0 I
.. -· !Ii • • 0 • I ■ • - •
•I • - ■ •
■

Connected catwrplot
I - -
--- -
• - .•• . ii •..•
•

-------- ...
■
.
•

.. ..
.
v0.9
[Link] and Visualization
(BN)
Using visualizations as a tool
to explore/understand the
data
• high level of interactivity
• high level of detail
• all aspects of data should
be represented (tables,
columns, calculations etc.)
• no annotations or
POWER BI FOR BEGINNERS 37
 explanations required

POWER BI FOR BEGINNERS 38

[Link] a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 39

[Link] and
Format a Bar
Chart (PA)

We make a bar chart displaying

the sum of “Item Amount” for all
2018 project entries, by “Project
Name”.

We also format the chart to have

grey bars.

(see detailed instructions, p. 2)

POWER BI FOR BEGINNERS 40
[Link] and Format a Donut
Chart (ID)

We can create other types of charts,

such as a donut chart.

(see detailed instructions document,

p. 2)

We will revisit this chart when we talk

about measures.

POWER BI FOR BEGINNERS 41

10. Create a Table to Store
Measures (ID)
Before we build a first measure,
we create a “_Measure” table in
which the measures can reside.

(see detailed instructions, p. 2)

POWER BI FOR BEGINNERS 42

11. DAX vs. M vs.
GUI (BN)
Power BI is really two products:
• data is imported through Power Query, which is used to manipulate/transform the data
• when ready, Power Query “pushes” the data into Power BI, for visualization and manipulations

Power Query uses the language “M” in the background; Power BI uses “DAX”.

Basic charts are created using the Power BI Graphic User Interface (GUI);
for more complicated/sophisticated things, we need to use M and/or DAX.

POWER BI FOR BEGINNERS 43

11. DAX vs. M vs.
GUI (BN)
What is M?
• M is the data transformation engine in Power BI (prior to loading data into the model)
• M contains commands to transform data and to return the results of the query and
trans- formations to the Power BI data model
• normally, we use M Query to query data sources, clean, and load data
• instead of bringing 3 tables into Power BI, say, we can remove unneeded columns
and merge the tables into a single table to load into the model (this reduces load
and improves performance, which is crucial for larger datasets)

POWER BI FOR BEGINNERS 44

11. DAX vs. M vs.
GUI (BN)
What is the Data Analysis eXpression (DAX) language?
• DAX is the common language used by SQL Server Analysis Services Tabular, Power
BI, and Power Pivot in Excel
• DAX is the analytical engine in Power BI
• it is used once the data is loaded to create custom columns and tables (data) or
measures (functions)
• it has some similarities to Excel functions, but much more powerful in general

POWER BI FOR BEGINNERS 45

12. Create Calculated Measures
(ID/PA)
Measures are introduced when we want to focus on a column’s categories.

For instance, we might want to create charts that focus on the various “Item
Amount” categories :
• Total Major Capital
• Total Minor Capital
• Total Operations and Management
• Total Salary

(see detailed instructions, pp. 2-3)

POWER BI FOR BEGINNERS 46
13. Make and Format a Donut
Chart (ID)
We can now achieve the same
donut chart as we had obtained
when dragging “Journal Voucher
Type Code” onto the “Legend” field.

The difference is that we can now

re- order the categories as needed
and use them for new
measures/columns (see detailed
POWER BI FOR BEGINNERS 47
instructions, p. 3).

POWER BI FOR BEGINNERS 48

 14. Make and Format a Treemap
Chart (BS)

We can play around with the charts to

get a better understanding of the data.

For instance, we could create a

treemap showing the sum of “Item
Amount” transactions by director.

POWER BI FOR BEGINNERS 49

15. Create a Calculated Measure
(BS)
It could also be handy to have a way to report the large transaction values
as multiple of 1000$.

We created a calculated measure that does so: “Item Amount $k

Measure”. We will discuss measures further in a little while.

We create a new page for the dashboard, called “Exploration 2”, and we
place a page filter on it to ensure that we are only using 2018 transactions.

POWER BI FOR BEGINNERS 50

16. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 51

17. Create a Calculated Column
(BS)
We can also act on the entire column to create a new column: we will
do so with the same intent as the previous measure, “Item Amount $k”.

Note that:
• “Item Amount $k Measure” is a measure residing in the “_Measures” table
• “Item Amount $k” is a column residing in the “Accounting Transactions” table

While they ostensibly relate to the same idea (and are both calculated
using DAX formulas), they are emphatically different Power BI
entities (more on this later).
POWER BI FOR BEGINNERS 52
18. Format Decimal Places (BS)

Power BI can format the data once it enters the model.

For instance, we can format the original “Item Amount” column and the
calculated column “Item Amount $k” (from the “Accounting Transactions”
table) so that they are displayed with 2 decimal places.

POWER BI FOR BEGINNERS 53

19. Make and
Format a Bar
Chart (BS)

We create a bar chart showing the

sum of “Item Amount $k” for each
director.

This is equivalent to the treemap chart of

task 14 (except that the amounts are in
1000$ of dollars).

POWER BI FOR BEGINNERS 54

20. Make and Format a Line
Chart (PA)
For some variables (dates,
geography), we can create
hierarchies.
We use such hierarchies to create a
line chart (see detailed
instructions, p. 3).
Take some time to play around with
the hierarchies to drill down and up
with the time series graph to become
POWER BI FOR BEGINNERS 55
familiar with the concept.

POWER BI FOR BEGINNERS 56

21. Create a Calculated Measure
(ID)

There is a spike in the previous chart that seems like it would be worth
exploring in more detail.

To do so, we create a calculated measure (built from two

measures), the ratio of “Total Major Capital” to “Total Minor Capital”,
denoted “Major Minor Ratio” (see detailed instructions, p. 3).

POWER BI FOR BEGINNERS 57

22. Make and Format a Line
Chart (ID)

We create a new line chart

displaying the “Major Minor Ratio” by
Month, using the “Effective Date”
hierarchy (see detailed instructions,
p. 3).

This “sombrero” shape is unexpected…

might it be worth monitoring the

POWER BI FOR BEGINNERS 58

situation further?

POWER BI FOR BEGINNERS 59

23. Exploring the
Data (DD)

POWER BI FOR BEGINNERS 60

23. Exploring the
Data (DD)

POWER BI FOR BEGINNERS 61

 1. Continue exploring the dashboard. Can
you find anything interesting in the data?
2. Make and format the donut chart of task
13 using measures.
Exercis
3. Make and format the treemap chart of task 14.
es
4. Create the calculated measure of task 15.

5. Create the calculated column of task 17.

6. Format to decimal places as in task 18.

7. Create new charts to help you explore
the 2018 data.

POWER BI FOR BEGINNERS 58

[Link]
nal
Awareness
POWER BI FOR BEGINNERS

59
 [Wexler, Shaffer, Cotgreave, The Big Book of Dashboards]

24. Dashboards (BN)

A dashboard is any visual display of data used to monitor conditions and/or
facilitate understanding.

Examples:
• interactive display that allows people to explore motor insurance claims by city,
province, driver age, etc.
• PDF showing key audit metrics that gets e-mailed to a Department’s DG on a weekly basis.
• wall-mounted screen that shows call centre statistics in real-time.
• mobile app that allow hospital administrators to review wait times on an hourly- and
daily- basis for the current year and the previous year.

POWER BI FOR BEGINNERS 60

 24. Defining Context (BN)

Seconds Minutes Fraction of Hour Hours

Infographics/
Data Viz
Dashboar
ds
Reports and
Exploration
Data Art
POWER BI FOR BEGINNERS 61
24. Situational Awareness (BN)

Using visualizations as a tool to provide

a snapshot of the data
• medium level of interactivity
• not “scripted” but well organized
(e.g., categorized)
• summary data should be represented
• anomalies are highlighted
• often used for internal presentations

POWER BI FOR BEGINNERS 62

25. Measures vs.
Columns (BN)
In Excel, we add a calculation to a cell. In Power BI a calculation can “live”
in one of three places (not including in Power Query – more on that in
another course). We can do calculations that create:
1. a new column of data (a calculated column)
2. a new data value calculated solely to be used in a visualization (a calculated measure)
3. a new data table (a calculated table).

POWER BI FOR BEGINNERS 63

25. Measures vs.
Columns (BN)
Calculated Columns are:
■ additional data columns added to an existing data table
■ evaluated for each row in the table, immediately after “Enter” is hit to complete the formula
■ saved back into the model, therefore they take up space (with 1M data rows, a calculated
column adds 1M new data points).

Calculated Measures are:

■ evaluated only when used in a visual or when the visual is rendered (function)
■ not “saved” anywhere (aside from the cache)
■ preferred to calculated columns, but there are trade-offs with performance (report runtime vs.
pre- processed), storage space, and the type of expressions that can be used.

POWER BI FOR BEGINNERS 64

25. Measures vs.
Columns (BN)
When do we use calculated columns over measures? Sometimes either is
an option, but in most situations the computation needs determine
the choice.

A calculated column is used to do the following:

• place the calculated results in a slicer, or in the axis of a chart, or use the result as a
filter condition in a DAX query;
• define an expression that is strictly bound to the current row (for example, “Cost *
Volume” does not work on an average or on a sum of two or more columns);
• categorize text or numbers (for example, a range of values for a measure, a range of
customer ages, such as 0–18, 18–25, etc.).

POWER BI FOR BEGINNERS 65

25. Measures vs.
Columns (BN)
Typically, measures is used whenever a resulting calculation needs to be
displayed in the values area in the plot area of a chart:
• for instance, in calculating the cost percentage on a certain selection of data;
• they can use data from many different tables and therefore don’t “belong” to a table in
the same way a calculated column is;
• it is good practice to create a separate “home” for our measures (“_Measures”).

POWER BI FOR BEGINNERS 66

25. Measures vs.
Columns (BN)
Total Amount = sum('Accounting Transactions'[Item Amount])

• the measure’s name is “Total Amount”.

• the operator “=” indicates the beginning of the formula; when calculated, it returns a result
• the function (in this case “sum”) contains one argument, contained in the parentheses “( )”
• the table from which we are taking the data is found before the brackets “[ ]”; in this case
it is 'Accounting Transactions’.
• note that as the table name contains two words, it placed inside single quotations ‘ ’. If the
table name only had one word, it would not, i.e. Accounting_Transactions[Item
Amount]
• the referenced column from the table is placed inside the “[ ]”; such as [Item Amount]

POWER BI FOR BEGINNERS 67

26. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15, 17-22).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 68

27. Create a Calculated Column
(ID)
We can use DAX to apply logical operations.

Take a simple scenario in which we flag any transactions for which “Item
Amount” is over $100,000. We create a calculated column as follows:
• if the “Item Amount” is equal to or greater then $100,000, then we add the word “Check”
• otherwise, we leave the value in the new column blank

(see the detailed instructions, p. 4)

Note that there is no filter on Effective Date year from this point on.
POWER BI FOR BEGINNERS 69
28. Make and Format a Table
(BS)
Visuals can also include tables.

POWER BI FOR BEGINNERS 70

POWER BI FOR BEGINNERS 71
29. Make and Format a Column
Chart (BS)
There are enough transactions in the dataset to produce a daily bar
chart time series (note the necessary scroller).

POWER BI FOR BEGINNERS 72

30. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15, 17-22, 27-29).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 73

31. Make and Format a Bar
Chart (PA)

We create a bar chart that uses

the “Check amount” calculated
column, for the various JVTC (see
detailed instructions, p. 4).

Do you notice anything surprising?

POWER BI FOR BEGINNERS 74

32. Make Slicers (PA)

We create a “Director” slicer and a “Journal

Voucher Type Code” slicer for the “Monitoring
2” page (see detailed instructions, p. 4).

The “Check amount” irregularity from the

previous bar chart suggests that it could be
useful to monitor transactions in the Minor
Capital (MIC) regime (especially for director C.
Power).

POWER BI FOR BEGINNERS 75

33. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15, 17-22, 27-29, 31-32).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 76

34. Make and Format Column
Charts (ID)
We can compute various statistics for each of the directors: #
transactions, average/standard deviation of “Item Amount”, etc. (see
detailed instructions, pp. 4-5). Note that C. Power has outlying
behaviour.

POWER BI FOR BEGINNERS 77

35. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15, 17-22, 27-29, 31-32, 34).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 78

36. Make and Format a Column
Chart (PA)

We can compute various statistics

for each of the directors: maximum
“Item Amount”, in this case (see
detailed instructions, p. 5).

Note that C. Power again has outlying

behaviour.

POWER BI FOR BEGINNERS 79

37. Monitoring the
Situation (DD)

POWER BI FOR BEGINNERS 80

37. Monitoring the
Situation (DD)

POWER BI FOR BEGINNERS 81

 1. Continue exploring the dashboard. Can
you find anything interesting in the data?
Exercis
2. Make and format the table of task 28.
es
3. Make and format the column chart of task 29.

4. Create new charts, using calculated

measures and columns as necessary, to help
you monitor the situation in the dataset.

POWER BI FOR BEGINNERS 81

V. Storybook
POWER BI FOR BEGINNERS

82
 [C. Nussbaumer Knaflic, Storytelling with Data]

38. Evolving a Chart (BN)

1. Change graph type

(trope)
POWER BI FOR BEGINNERS 83
 [Based on C. Nussbaumer Knaflic, Storytelling with Data]

38. Evolving a Chart (BN)

2. Remove border and 3. Remove

gridlines markers

POWER BI FOR BEGINNERS 84

 [Based on C. Nussbaumer Knaflic, Storytelling with Data]

38. Evolving a Chart (BN)

4. Clean-up axis labels and 5. Colour code the

legend lines

POWER BI FOR BEGINNERS 85

 [Based on C. Nussbaumer Knaflic, Storytelling with Data]

38. Evolving a Chart (BN)

Lag in Tickets Processed Since May Layoffs
6. Tell the
story

We have not been able to

keep up with tickets
received since two employees
were let go in May

POWER BI FOR BEGINNERS 86

38. Storytelling with Data (BN)

Using visualizations as a tool to

explain the data and communicate
the story
• low level of interactivity
• low level of detail
• key aspects of data should be represented
• annotations and explanations drive
the “story”

POWER BI FOR BEGINNERS 87

39. Create a Calculated Column
(ID)

When we were monitoring the situation, we realized that there was a

potential issue with C. Power transactions:
• gigantic 820,000$ transactions coded as Minor Capital late in the data
• small average transaction value
• small standard deviation value

We create a “Power” calculated column (identifying all transactions

assigned to this director) to assist with storytelling (see detailed instructions,

POWER BI FOR BEGINNERS 88

p. 5).

POWER BI FOR BEGINNERS 89

40. Make and Format Small
Multiples (ID)
We use this calculated measure to
display a bar chart small
multiple, displaying the total
minor capital for each day of the
month, for each director separately,
with a different colour for C. Power
(see detailed instructions, p. 5).
The profiles for 5 of the 7 directors
show high daily transaction
volumes, but there is an anomaly!
POWER BI FOR BEGINNERS 90
41. Make and Format a Bar
Chart (ID)
The last panel is empty; we create a
visual that showcases the
frequency of individual “Item
Amount” values (see detailed
instruction, pp. 5-6).

All values are unique, except for

8200$, which appears in 64
transactions by …

POWER BI FOR BEGINNERS 91

C. Power – the game is afoot!

(this is easier to spot with a chart filter).

POWER BI FOR BEGINNERS 92

42. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15, 17-22, 27-29, 31-
32, 34, 39-41).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 93

43. Apply a Page Filter (PA)

So we have discovered that small Minor Capital transactions (<10,000$) are

highly unusual in this dataset.

We apply a page filter to “Storybook 2” so that the corresponding charts are all
affected by a 10,000$ “Item Amount” limit (see detailed instructions, p. 6).

POWER BI FOR BEGINNERS 94

44. Make and Format Small
Multiples (PA)
The odd nature of C. Power’s small
regime transactions (<10,000$) is clear
from the small multiple
storytelling bar chart: the unusual
transactions take place regularly (1st,
15th of every month) and no other
director has even a fraction of the # of
small transactions.

Cart him off to jail!

POWER BI FOR BEGINNERS 95

(see detailed instructions, p. 6)

POWER BI FOR BEGINNERS 96

45. Make and Format a Bar
Chart (PA)

The last panel is empty; we create

a visual that showcases the
frequency of daily “Item Amount”
transactions, with an emphasis on
C. Power’s contribution to the
overall profile (see detailed
instruction, p. 6).

POWER BI FOR BEGINNERS 97

46. Annotate a Bar Chart (BN)

The best data stories are combinations of text and visuals.

Power BI is not necessarily the greatest tool for that last step (although it
can do some of it, it does not do so naturally); don’t hesitate to use other
tools as needed.

POWER BI FOR BEGINNERS 98

47. Load a PBIX File (PA)
Load and explore PBIFB – [Link] (tasks 1-5, 8-10, 12-15, 17-22, 27-29, 31-
32, 34, 39-41, 43-46).
1. Go to “Transform Data” in the “Home” Ribbon.
2. Click on “Data Source Settings”.
3. Highlight one of the data files.
4. Click on “Change Source”.
5. Click on “Browse” and navigate to the corresponding file’s location on your system.
6. Repeat for the other file.
7. Press “Close”.
8. Apply changes from the yellow warning message.

POWER BI FOR BEGINNERS 99

48. Convincing an
Audience (DD)

POWER BI FOR BEGINNERS 10

48. Convincing an
Audience (DD)

POWER BI FOR BEGINNERS 10

Exercis
1. Annotate the storybooks of tasks 48.
es
2. Create new storybooks for the data,
and annotate them.

POWER BI FOR BEGINNERS 99

[Link]*
POWER BI FOR BEGINNERS

100
49. Publish a Power BI
Dashboard (BN)
Once the dashboard is created, it can be “published” it to a website as an
iFrame (a webpage inside a webpage).

This allows audiences to fully interact with the dashboard without the need
to install Power BI on their computers.

The dashboard can be published publicly, or to pushed to other organization

members (individuals with a shared Office 365 and/or Azure environments).

POWER BI FOR BEGINNERS 101

49. Publish a Power BI
Dashboard
This can be done by logging(BN)
into Power BI using a valid Microsoft
credential (the IT department can determine if the regular windows login will
work).

After hitting the “Publish” button, Power BI will ask to save the
dashboard to a workspace (like an online folder).

POWER BI FOR BEGINNERS 102

49. Publish a Power BI
Dashboard
The next step is login to(BN)
workspace.
the online Power BI

IT Departments can provide guidelines to share the

dashboard internally to an organization.

To publish to a public website (disclaimer ALL public

links are searchable), the Power BI workspace needs
to have this functionality enabled.

Navigate to the dashboard in the workspace, click

on “File” and then “Embed Report” and finally
“Publish to web (public)”.
POWER BI FOR BEGINNERS 103
49. Publish a Power BI
Dashboard (ID)
At this point, the iFrame link can be copied and embedded into the HTML
of the website of interest. The end user will then be able to see the
(publicly available) dashboard on the website and interact with it as
needed.

POWER BI FOR BEGINNERS 104

POWER BI FOR BEGINNERS 105
 [[Link]

50. Integrate R in Power BI (BN)

The programming languages R and Python
can be used for data wrangling and
visualizations.* Ensure that you have R
downloaded onto your system and point
PowerBI at your R installation.
Even beginners may require a visual that is
not easily accessible through Power BI. To
create an R chart, click on the ‘R’ button
under the “Visualizations” panel.
[*Guy in a Cube does a great introductory video.]

POWER BI FOR BEGINNERS 106

References
POWER BI FOR BEGINNERS

107
References
P. Boily, S. Davies, J. Schellinck, The Practice of Data Visualization. Data Action
Lab, 2022.
P. Boily, J. Schellinck, Data Understanding, Data Analysis, and Data Science.
Data Action Lab, 2022.

A. Saxton, P. Leblanc. Guy in a Cube. Guy in a Cube, LLC, 2022.

M. Russo, A. Ferrari, D. Perilli. sqlbi. SQLBI, 2022.

N.B. The Microsoft resources found under the help ribbon in PowerBI can also be very helpful.
There are links for guided learning, training videos, documentation, support, a blog, and community.

POWER BI FOR BEGINNERS 108