Fast-track Power BI & DAX

Sample manual - first two chapters

Manual 1294 - 364 pages –

 TABLE OF CONTENTS (1 of 11)

1 GETTING STARTED Page 3 DATA MODELS Page

1.1 Getting Started in Power BI Desktop 13 3.1 Data Models 35
Example for this Chapter 13 Viewing a Model 35
Selecting Single Model Items 36
1.2 Working with Files 14
Selecting Multiple Items 36
Creating New Files 14 Searching for Fields 36
Opening and Saving Files 14
3.2 Model Diagrams 37
1.3 Views in Power BI Desktop 15
Arranging Tables in a Model 37
Switching Panes 15 Diagram Layouts 37
Report, Table and Model View 16 Collapsing and Expanding Tables 38
Controlling Expand/Collapse Field 38
1.4 Getting Data 17 Visibility
1.5 Transforming Data 18 Seeing Table Information 38

Editing Queries / Transforming Data 18 3.3 Hiding Objects 39

The Power BI Query Editor 19
Why you might Want to Hide Tables and 39
Promoting Row Headers 19 Fields
Replacing Values 20 Hiding Tables 40
Changing Data Types 20 Hiding Fields/Columns 40
Removing Columns 20
3.4 Model Properties 41
1.6 Creating Visuals 21
Table Properties 41
Inserting a Visual 21 Display Folders 41
Assigning Grouping Fields to a Visual 21 Default Number and Date Formatting 42
Assigning Numerical Fields 22 Changing the Default Aggregation for a 42
Field
1.7 Three Ways to Format Visuals 23
In Situ Selection 23 3.5 Relationships 43
Changing what’s on your Chart 23 The Need for Relationships 43
The Format Pane 24 Parent-Child Relationships 43
Creating a Relationship 44
1.8 Publishing your Report 25
Editing Relationships 44
The Effect of Relationships 45
2 IMPORTING DATA Page Cross-Filter Direction 45

2.1 Our Example 26

2.2 Importing from Different Sources 27
Re-Using a Data Source 27

2.3 Importing from Excel 28

2.4 Importing CSV or Text Files 29
2.5 Importing from SQL Server 29
Using Queries and Stored Procedures 31
Passing Arguments to Stored Procedures 31

2.6 Importing from a Website 32

2.7 Entering Data Manually 33
Pasting Data 33
Typing in Data 34

© Copyright 2024 Page 2

 TABLE OF CONTENTS (2 of 11)

4 VISUALS Page 7 TABLES Page

4.1 Overview of Visuals 46 7.1 Basic Tables 68
4.2 Adding and Changing Visuals 47 7.2 Working with Table Columns 68
Adding a Visual then Selecting its Data 47 Sorting Tables by Columns 69
Data-First Visual Creation 48 Re-ordering Columns 69
Changing Visual Types 48 Changing Column Widths Automatically 69
Changing Column Widths Manually 70
4.3 Working with Visuals 49
Changing Word Wrap Options 70
The Visual Header 49 Renaming Columns 71
Focus Mode 49
7.3 Aggregating Data 72
Spotlight Mode 49
Resizing a Visual 50 7.4 Formatting Numbers 73
Selecting Visuals 50
Formatting Numbers within a Single Table 73
Moving, Copying and Deleting Visuals 50
Formatting Numbers for all Visuals 74
Locking Visuals 51
Setting Custom Number Formats 74
Aligning and Distributing Visualisations 51
Grouping Visualisations 52 7.5 Working with Dates in Tables 75
Showing a Visual’s Underlying Data 53
Displaying Dates as Dates 75
4.4 The Selection Pane 54 Changing the Default Format for a Date 75
Setting a Custom Date Format 76
Viewing the Selection Pane 54
Changing the Visual Layer Order 55 7.6 Specific to Formatting Tables 77
Changing the Tab Order 55
Column Headers 77
Hiding Visuals 55
Totals 77
Font Size and Typeface 78
5 VISUAL INTERACTIONS Page Table Padding and Gridlines 78
Table Styles 79
5.1 Overview 56 Formatting Columns Individually 79
5.2 Editing Visual Interactions 57
What the 3 Symbols Mean 58 8 CONDITIONAL FORMATTING Page
8.1 Conditional Formatting 80
6 FORMATTING VISUALS Page 8.2 Applying Conditional Formatting 81
6.1 Finding Formatting Properties 59 Setting Conditional Formatting 81
Searching for a Property 59 Changing Conditional Formatting 81
Finding a Property 59 8.3 Gradient Effects 82
6.2 Common Formats to Apply 60 Adding a Middle Colour 82
Setting Visual Backgrounds 60 8.4 Rules-Based Conditional Formatting 83
Borders and Shadow Effects 61
Titles, Subtitles and Dividing Lines 62 8.5 Formatting Using Field Values 84

6.3 Header Icons 63 8.6 Data Bars 85

6.4 Tooltips 64 8.7 Displaying Icons 86

6.5 Useful General Formatting Tricks 65

The Wonderful Format Painter 65
Expanding and Collapsing Cards 66
Removing all Formatting 66

6.6 Themes 67

© Copyright 2024 Page 3

 TABLE OF CONTENTS (3 of 11)

9 MATRICES Page 11 CHART BASICS Page

9.1 Overview of Matrices 87 11.1 The Parts of a Chart 95
9.2 Creating a Matrix 88 11.2 Types of Chart Available 96
The Sections of a Matrix 88 11.3 Working with Charts 97
Sorting in a Matrix 88
Creating a Chart 97
9.3 Multiple Rows, Columns and Values 89 Suggesting Chart Types 97
Sorting Charts 98
Multiple Row Fields 89
Zoom Sliders 99
Multiple Values Fields 90
Excluding and Including Data 100
Multiple Column Fields 90
11.4 Quick Ways to Format Charts 101

10 TEXT BOXES, IMAGES AND Page 11.5 Chart Legends 102

SHAPES 11.6 Detail Labels 103
10.1 Overview 91 Leader Lines for Data Labels 104
Drawing Text Boxes, Images or Shapes 91 Total Labels 104

10.2 Images 92 11.7 Background and Gridlines 105

Scaling Images 92 Plot Area and Background 105

Adding a Hyperlink to an Image 92 Gridlines 105

10.3 Text Boxes 93 11.8 Axes 106

Inserting Values 93 Categorical versus Continuous 106

Formatting
10.4 Shapes 93 Formatting Axes 107
Scaling Ranges 107
Adding a Shape 94
Spacing Categories 107

11.9 Conditional Formatting 108

12 SMALL MULTIPLES Page

12.1 Overview of Small Multiples 109
12.2 Creating and Formatting Small 110
Multiples
Setting Grid Width and Height 110
Formatting Small Multiple Titles 111
Other Formatting Options 111
Suppressing Axis Titles 112
Using Different Axes for Different Charts 112

12.3 Making the Background Colour 113

Dynamic

© Copyright 2024 Page 4

 TABLE OF CONTENTS (4 of 11)

13 MORE ON CHARTS Page 17 GAUGES Page

13.1 Line, Combination and Area Charts 114 17.1 Gauges 136
Multiple Fields in Line Charts 114 Adding a Gauge 136
Line Formatting 115 Minimum, Maximum and Target Values 137
Secondary Axes 116 Formatting Gauges 137
Combination Charts 116
Area Charts 117
18 KEY PERFORMANCE INDICATORS Page
13.2 Pie, Donut and Treemap Charts 118 (KPIS)
Formatting Pie and Donut Charts 118 18.1 Overview of KPIs 138
13.3 Scatter and Bubble Charts 119 Creating a KPI 138
Creating Scatter Charts 119 18.2 Creating Targets 139
Making Bubble Charts 119
Formatting Scatter and Bubble Charts 120 Formatting KPIs 140
Animating Bubble Charts 121
19 SLICERS Page
14 GROUPING AND BINNING Page 19.1 Introducing Slicers 141
14.1 Grouping 122 19.2 Working with Slicers 142
Starting a Group 122 Creating a Slicer 142
Editing Groups 123 Selecting and Clearing Items 142
Using Group Fields 123 Changing Selection Behaviour 143
Searching in Slicers 143
14.2 Binning 123
Dropdown Slicers 143
Tile Slicers 144
15 DRILL-DOWN Page Customising your Slicer Header 145

15.1 Drill-Down for Charts 125 19.3 Hierarchical Slicers 146

What is Drill-Down? 125 19.4 Number and Date Slicers 147
Enabling Drill Down 125
Sliders 147
Drilling Down 126
Choosing Dates 147
Drilling Up 126
Picking Relative Dates 148
What Happens when you Drill Down 127
Changing the Anchor Date 148
Drill Down and Visual Interactions 127
Viewing the Next Hierarchy Level 128
Expanding All Levels in a Hierarchy 128 20 NEW SLICERS Page
15.2 Drill-Down in a Matrix 129 20.1 Overview of the New Slicer Visual 149
Choosing Row or Column Fields 129 Benefits of the New Slicer Visual 149
Enabling the Visual 149

16 CARDS Page 20.2 Working with the New Slicer 150

16.1 New Cards 130 Adding a New Slicer Visual 150
Basic Configuration 150
16.2 Working with Cards 131 Adding Images and Text 151
Creating a New Card 131 Adding Hover and Selection Effects 152
Formatting Card Values and Labels 131
Formatting the Numbers in Cards 132
Card Shapes 132
Formatting the Cards Themselves 133
Adding Images to Cards 133

16.3 Reference Labels 134

16.4 Multi-row Cards 135

© Copyright 2024 Page 5

 TABLE OF CONTENTS (5 of 11)

21 FILTERING REPORTS Page

21.1 How Filters Work 153 24 PAGE NAVIGATION Page

21.2 Working with Filters 154 24.1 Overview 171

Showing the Filters Pane 154 24.2 Page Navigators 172

Applying a Basic Filter 154 Creating Page Navigators 172
Adding Fields to the Filters Pane 155 Formatting Page Navigators 173
Removing a Filter 155 Displaying Page Navigators as Grids 173
Advanced Text Filters 155
Advanced Number Filters 156 24.3 Simple Custom Page Navigators 174
Relative Date Filtering 156 24.4 Dynamic Page Navigation 175
Top and Bottom Filters 157
Step 1 – Creating (and Editing) the Table 175
Sorting Filters 157
Step 2 – Creating the Slicer 176
21.3 Formatting Filters 158 Step 3 – Creating the Button 176
Step 4 – Setting the Button’s Action and 176
21.4 Controlling Filters for End Users 159 Tooltip
Locking and Hiding Filters 159 Step 5 – Showing your Menu on All 177
Report Filter Settings 159 Pages

22 DRILL-THROUGH FILTERS Page 25 CUSTOM VISUALS Page

22.1 What are Drill-Through Filters? 160 25.1 What are Custom Visuals? 178
22.2 Creating a Drill-through Filter 161 25.2 Adding Custom Visuals 179
Step 1 – Create the Main Report Page 161 Pinning Custom Visuals 179
Step 2 – Create the Drill-Through Target 161 25.3 Working with Custom Visuals 180
Page
Step 3 – Name and Hide your Drill- 162 Applying a Custom Visual 180
Through Page Removing Custom Visuals 180
Step 4 – Configuring your Drill-Through 162
Page
Step 5 – Testing your Drill-Through Page 162

22.3 Three Ways to Drill Through 163

Modern Tooltips (Left Clicking) 163
Right-Clicking 163
Using a Drill-Through Button 163

22.4 Adding a Context-Sensitive Title 164

22.5 Extra Drill-through Options 165
Keeping All Filters 165
Drill-through from Summarised Fields 165

23 BUTTONS AND SHAPES Page

23.1 Overview 166
Types of Clickable Objects 166
Types of Action 166

23.2 Adding Clickable Shapes 167

23.3 Adding Clickable Images 167
23.4 Adding Clickable Buttons 169
Adding the Button 169
Setting Default, Hover, Selection and 169
Disabled Effects
Formatting Buttons 170

© Copyright 2024 Page 6

 TABLE OF CONTENTS (6 of 11)

26 QUERYING DATA Page 27 MAPS Page

26.1 What are Queries? 181 27.1 Overview of Maps 195
26.2 Working with Queries 182 27.2 Choosing a Map Visual 196
Opening the Query Editor 182 27.3 Maps Using Latitude and Longitude 197
The Query Editor 182
Stopping Aggregation for Simple Maps 197
Default Query Steps 183
Changing the Aggregation Method for 198
Viewing Data at Different Steps 183 Locations
Editing a Query Step 183
Renaming Steps 184 27.4 Maps without Latitude and Longitude 199
Deleting a Query Step 185 Using a Recognised Geographical Entity 199
Deleting Multiple Query Steps 185 Categorising Columns as Places 199
Adding a New Step 185 Fixing Locations to the UK 200
Viewing M Formulae 186 Converting Postcodes to 200
Applying Query Changes 186 Latitude/Longitude
26.3 Common Transforms 187 27.5 General Maps 201
Changing Data Types 187 Treating Maps as Visuals 201
Renaming Columns 187 Changing the Map Style 201
Removing Columns 188 Conditional Formatting 202
Removing Rows 188 Manual Zoom Settings 203
Sorting Rows 189 Viewing Controls 204
Filtering Rows 189 Selecting Points on a Map 204
Splitting Columns by Delimiter 190 Selecting within Driving Time/Distance 205
Splitting Columns by Number of 190 Drill-Down in Maps 205
Characters
Extracting Values 191 27.6 Specific Types of Maps 206
Replacing Values 191 Bubble Maps 206
Duplicating Columns 191 Heat Maps 207
26.4 Creating New Columns 192 Cluster Maps 208

Creating a Column by Example 192 27.7 Layers 208

Creating a Formula 192 3D Column Layers 209
Creating Conditional Columns 194 Traffic Layers 209
Reference Layers 210

© Copyright 2024 Page 7

 TABLE OF CONTENTS (7 of 11)

28 PUBLISHING Page
28.1 Overview of Publishing 211 30 BASIC CALCULATED COLUMNS Page

28.2 An Infinite Number of Variables 212 30.1 Calculated Columns 232

Your Power BI Licence 212 Key Features of Calculated Columns 232

Linking to Data 212 Creating a Calculated Column 232
Data Sources 213 Entering a Formula 233
Calculated Column Properties 234
28.3 Workspaces 214 Using Calculated Columns in Visuals 234
Choosing a Workspace 214 30.2 Referencing Columns and Tables 235
28.4 Creating Workspaces 215 Qualifying Column Names 235
28.5 Publishing Reports 216 Table Names 235
Referencing Columns in Other Tables 236
28.6 Viewing and Editing Reports 217 The Related Function 236
Reports and Datasets 217 30.3 Editing DAX Code 237
Viewing Individual Reports 217
Editing a Report 218 Multiple Lines and Indenting 237
Comments 238
28.7 Dashboards and Tiles 219 Keyboard Shortcuts 238
Adding Tiles to Dashboards 219
Using Tiles 219
31 WORKING WITH DATA TYPES Page
28.8 Lineage View 220
31.1 DAX Data Types 239
28.9 Refreshing Data 220
Viewing a Column’s Data Type 239
Types of Connection 221
Types of Refresh 222
31.2 Working with Numbers 240
Web Connections: a Warning 222 Basic Arithmetic 240
Viewing and Managing Connections 223 Controlling the Calculation Order 240
Viewing Individual Connections 223 Safely Dividing Numbers 241
Scheduling Refreshes 224 Numeric Functions 242
28.10 Sharing and Exporting Reports 225 31.3 Working with Text 243
Creating a PowerPoint Presentation 225 Writing Text in Calculations 243
Exporting to PDF 226 Concatenating Text 243
Generating a Public URL for your Report 226
31.4 Text Functions 244
Embedding your Report in a Website 227
Creating a Power BI Report File 227 Finding and Extracting Text 244
Sharing a Report 227 Replacing Text 244
Generating Text 245
Converting and Formatting Text 245
29 INTRODUCTION TO DAX IN POWER Page
BI 31.5 Working with Dates 246

29.1 DAX in Power BI 228 Entering Date and Time Values 246
Returning the Current Date and Time 246
How DAX is Used 1 – Calculated 228 Calculating Date and Time Values 247
Columns
Calculating the Difference Between 247
How DAX is Used 2 – Measures 228 Dates
How DAX is Used 3 – Calculated Tables 229 Extracting Date Parts 248
Choosing the Type of Calculation 229 Formatting Dates 248
Where Else is DAX Used? 229

29.2 The Construct-a-Creature Database 230

The Tables and Relationships 230

29.3 Getting Help with DAX 231

Getting Help within Power BI 231
Other Sources of Help 231

© Copyright 2024 Page 8

 TABLE OF CONTENTS (8 of 11)

32 CONDITIONAL FUNCTIONS Page 34 AGGREGATION FUNCTIONS Page

32.1 The IF Function 249 34.1 Aggregating Column Values 265
Testing a Single Condition 249 Basic Aggregation Functions 265
Comparison Operators 249 Functions for Counting 266
The IN Operator 249 Dealing with Boolean Values 266
Nesting IF Functions 250
34.2 Aggregating Expressions 267
Combining Logical Tests 250
The NOT Operator 250 The AggregateX Functions 267

32.2 Working with Blanks 251 34.3 Iterators and Row Context 268
Producing a Blank 251 A Reminder of Filter Context 268
Blank Arithmetic 251 Row Context in Iterator Functions 269
Testing for Blanks 251 The Final Result 269
The COALESCE Function 252 How to Spot Iterators 269

32.3 Testing for Errors 253

The ISERROR and IFERROR Functions 253 35 THE CALCULATE FUNCTION Page
Avoiding Error Functions 253 35.1 Introducing the CALCULATE Function 270
32.4 The SWITCH Function 254 Expressions in the CALCULATE Function 270
A Simple SWITCH Function 254 35.2 Adding New Filters 271
Logical Tests in a SWITCH Function 254
Basic Filter Expressions 271
Adding Multiple Filters 272
33 BASIC MEASURES Page Filter Arguments and Filter Context 272
Multiple Columns in Filter Arguments 273
33.1 Introduction to Measures 255
35.3 Replacing Filters 274
Measures vs. Calculated Columns 255
Implicit Measures 255 Replacing an Existing Filter 274
Comparing Differently Filtered Measures 275
33.2 Creating a Measure 256
Dealing with Blank Values 275
Adding a Measure to a Table 256
35.4 Keeping Filters 276
Formatting Measures 257
Displaying a Measure in a Visual 257 The Problem with the Default Behaviour 276
Referencing Measures 258 The KEEPFILTERS Function 277
Using the VALUES Function 277
33.3 Filter Context 259
35.5 Removing Filters 278
What is Filter Context? 259
How DAX Applies Filter Context 260 Removing Every Filter 278
Using the ALL Function 279
33.4 Measures Tables 261
Comparing Filtered and Unfiltered Values 279
Creating a Separate Measures Table 261 Removing Filters from Specific Fields 280
Moving Measures 262 An Issue with Sort-By Fields 281
Removing Filters from a Specific Table 281
33.5 Quick Measures 263
35.6 Special Filter Removal Functions 282
Creating a Quick Measure 263
Editing a Quick Measure 264 The ALLEXCEPT Function 282
Using a Quick Measure 264 The ALLSELECTED Function 283

© Copyright 2024 Page 9

 TABLE OF CONTENTS (9 of 11)

36 VARIABLES Page 39 CONTEXT TRANSITION Page

36.1 Introduction to Variables 284 39.1 What is Context Transition? 300
Using Variables in Measures 284 Row and Filter Context 300

36.2 How Variables are Evaluated 285 39.2 Context Transition in Calculated 301
Columns
Lazy Evaluation 285
DAX Variables are Constants 285 Row Context in Calculated Columns 301
Performing Context Transition 301
36.3 Debugging with Variables 286
Implicit Context Transition 302
Returning Different Variables 286 The RELATEDTABLE Function 302

36.4 Nesting Variables 287 39.3 Context Transition in Measures 303

Variables in Functions 287 Row Context in Measures 303
Variable Scope 288 Context Transition in Measures 304
The Effect of Filter Context 304
Removing Filters 305
37 THE FILTER FUNCTION Page
39.4 Ranking Values 306
37.1 Introduction to the FILTER Function 289
The RANKX Function 306
A Basic FILTER Example 289 Ranking in Calculated Columns 306
Using the CALCULATE Function 289 Context Transition in Calculated Columns 307
How CALCULATE and FILTER are 290 Ranking in Measures 307
Related
Using Multiple Filters 290
Using Variables 290

37.2 FILTER vs. CALCULATE 291

Referencing Multiple Fields 291
Using Fields from Different Tables 291
Referring to Measures 292
Replacing Filters 293

37.3 The CALCULATETABLE Function 294

Using CALCULATETABLE 294

38 FILTERS AND RELATIONSHIPS Page

38.1 Relationships and Filter Direction 295
The Problem with Relationships 295
Changing the Cross Filter Direction 296
Solving the Problem using Filters 297

38.2 Cross Filter Direction in Measures 298

The CROSSFILTER Function 298
Using Single and Both Filter Directions 299
Simultaneously
Multiple CROSSFILTER Functions 299

© Copyright 2024 Page 10

 TABLE OF CONTENTS (10 of 11)

40 TIME INTELLIGENCE Page 41 CUSTOM CALENDARS Page

40.1 Introduction to Time Intelligence 308 41.1 Why use Custom Calendars? 324
Calendar Tables 308 Disabling Automatic Calendars 324
The Date Column 309
41.2 Creating a Custom Calendar 325
Referring to Calendar Tables 309
The CALENDARAUTO and CALENDAR 325
40.2 Time Intelligence Functions 310 Functions
General Time Intelligence Functions 310 Adding Extra Columns 326
Using the DATEADD Function 310 Financial Years 327
How DATEADD Works 311 41.3 Finishing the Calendar 328
Using the DATESINPERIOD Function 311
Using the Current Date 312 Marking as a Date Table 328
Using Specific Dates 312 Changing Default Aggregations 328
Setting Sort-By Columns 329
40.3 To Date Calculations 313 Creating Hierarchies 329
Returning Date Ranges 313 Hiding Fields 330
Calculating Running Totals 313 Creating a Relationship 330
Total To Date Functions 314 41.4 Using a Custom Calendar 331
Easier Running Total Calculations 314
Specifying Year End Dates 315 Creating Visuals 331
Calculating Life to Date Values 315 Time Intelligence Functions 331

40.4 Next and Previous Periods 316 41.5 Multiple Date Fields 332
Next and Previous Period Functions 316 Using Multiple Calendars 332
Comparing Entire Previous Years 317 Using a Single Calendar 333
Comparing Parts of Previous Years 318 Changing the Active Relationship 333
The USERELATIONSHIP Function 334
40.5 Period Start and End Dates 319
41.6 Special Dates 335
Period Start and End Functions 319
Start and End Dates 320
Opening and Closing Balances 321
First and Last Non-Blank Dates 321
First and Last Non-Blank Values 322
Non-Blank Opening Balances 322

40.6 Moving Averages 323

Calculating a Moving Average 323

© Copyright 2024 Page 11

 TABLE OF CONTENTS (11 of 11)

42 DYNAMIC MEASURES Page 43 VISUAL CALCULATIONS Page

42.1 Dynamic Labels 336 43.1 What are Visual Calculations? 346
Why use Dynamic Labels? 336 43.2 Creating Visual Calculations 347
42.2 Returning a Single Value 337 Creating a Visual 347
Adding a Visual Calculation 347
The VALUES Function 337
Writing the Formula 348
Testing for a Single Value 338
Using Pre-Defined Visual Calculations 349
The SELECTEDVALUE Function 338
43.3 Editing a Visual Calculation 350
42.3 Concatenating Values 339
Formatting the Results 350
The CONCATENATEX Function 339
Editing the Formula 350
More Complex Expressions 339
43.4 Hiding Fields in a Visual 351
42.4 Filtered Values 340
43.5 Running Totals 352
Testing for Filtered Values 340
Testing for Cross Filtered Values 340 Calculating a Running Total 352
Selecting the Top N Rows 341 Running Totals and Hierarchies 352
Resetting a Running Total 353
42.5 Disconnected Slicers 342
Changing the Direction 354
Creating a Disconnected Table 342
43.6 Moving Averages 355
Creating a Disconnected Slicer 343
Referencing the Selected Value 343 Calculating a Moving Average 355
Excluding the Current Row 355
42.6 Formatting with Measures 344
43.7 Referring to Other Rows 356
Calculating Colours with Measures 344
Using Measures in Conditional 345 Next and Previous Rows 356
Formatting Setting the Step Value 357
Choosing Colours with Slicers 345 First and Last Rows 357

43.8 Referring to Parent Values 358

Collapsing Filters 358
Collapsing Filters Relatively 359
Collapsing All Filters 359

43.9 Referring to Child Rows 360

Expanding Filters 360
Expanding Filters Relatively 360

43.10 Controlling Group Headers 361

Testing for the Presence of a Column 361

© Copyright 2024 Page 12

 Chapter 1 - Getting Started

CHAPTER 1 - GETTING STARTED

1.1 Getting Started in Power BI Desktop

This chapter describes the basic workflow you’ll use to build a report in Power BI Desktop:

Stage Details
Loading and Loading one or more tables from various data sources, cleansing the data and
transforming data linking the tables together if necessary.
Creating a report Using the data that you’ve loaded to create a report, including visuals like charts.
Publishing this Publishing the results to your report server (usually Microsoft’s Power BI Service)
so that other people can view your reports.

You’ll find much more detail on the ideas mentioned in this chapter in later parts of this courseware.

Example for this Chapter

To demonstrate the basic process of building a report, we’ll import a table of data from a webpage
and create and publish a report based upon this:

At the time of writing these are the snow

conditions at selected resorts courtesy of
https://www.igluski.com/snow-reports .
We’ll transform the data to tidy it up (for
example, we can remove columns we
don’t need).

We’ll then present this data using visuals such as this chart:

We can use the imported, cleaned data to create a variety of

visuals, such as this chart.

Finally we will publish this report to the Power BI Service,

so that anyone in your organisation can see it:

The final report published to Power BI Service, and viewed

through your browser.

© Copyright 2024 Page 13

 Chapter 1 - Getting Started

1.2 Working with Files

Creating New Files

You can create a new report in Power BI Desktop in the following ways:

a) If you already have Power BI b) Click on this icon to create a new

Desktop open, select the File menu. report.

Opening and Saving Files

You can open and save files using options in the File menu:

a) From the Power BI Desktop menu choose the File menu.

b) Choose one of these menu items to open or save a report (the

dialog box which appears is similar whichever you choose).

c) Click on this button to find the d) Files that you save in e) Click on this button to open
folder which contains (or will Power BI Desktop will have the report you’ve chosen or
contain) your report. a pbix extension. to save this report

Wise Every time you open a Power BI Desktop report a new instance of the application
Owl’s will launch, leaving the current report you’re working on unaffected. To close a report
Hint you must close down the Power BI Desktop application containing it (there is no
option to close a report but still leave Power BI Desktop running).

© Copyright 2024 Page 14

 Chapter 1 - Getting Started

1.3 Views in Power BI Desktop

The most important components of the Power BI Desktop screen are as follows:

These tools allow you to switch between This is the Power You can use the Pane Switcher to change
the 3 main views in Power BI Desktop: BI Desktop menu what to look at. The two main views are
Report view, Table view and Model view. and ribbon. Data and Format, as explained below.

These tools allow you to switch between You can use the slider to zoom in and out, although you
designing reports for viewing on desktop may find it easier to hold down the Ctrl key and use your
computers or mobile phones. mouse wheel instead.

Switching Panes

You can use the icons on the right-hand side of your Power BI screen to choose what to show:

Click on this icon to see the data tables in your model …

… or this icon to see the format properties of the thing

you currently have selected (in this case a chart visual).

You can also use these tools on the View tab of the
Power BI Desktop ribbon to choose what you want to
view.

© Copyright 2024 Page 15

 Chapter 1 - Getting Started

Report, Table and Model View

You can switch between the three views of a report using the tools on the left of the screen:

View Icon What it shows Example view

The report that

you’re creating,
Report
consisting of visuals
and shapes.

The tables of data

that you’ve loaded
Table into your model (you
can see one table at
a time in this view).

The links between

the tables in your
Model model, called
relationships in
Power BI Desktop.

Wise The 4th icon – if present – allows you to create queries in DAX to interrogate the data
Owl’s
Hint
upon which your report is based, but this is definitely not something to consider in
this courseware chapter!

© Copyright 2024 Page 16

 Chapter 1 - Getting Started

1.4 Getting Data

The first stage in building a report is to find some data!

a) From the ribbon, choose Home | Get Data then choose a type of
data source. Here we’re choosing a Web source.

b) Each type of data source requires different information to allow

your report to connect to it. For a web source you need to enter
the URL of the page and then click OK. You may also be asked to
enter credentials if the website requires some.

c) Tick which table you want to

import from this page (the best
way to see what each looks like
is to click on it).

d) Click on this button to load the

data into your model.

You can optionally rename a table after importing it into your report:

a) Click on this icon to show the Data pane.

b) Choose Rename from the menu which

appears.

c) Enter a new name for your table and

press Enter .

© Copyright 2024 Page 17

 Chapter 1 - Getting Started

1.5 Transforming Data

You’ll often need to make changes to the data you have imported so that it can be presented easily
in visuals. This process is known as transforming data.

We will change the column

headings, turn some
column into numbers and
remove the final column.

Wise
Owl’s As with everything else in this chapter, we will go into this topic in much more detail
Hint later in this courseware.

Editing Queries / Transforming Data

Each table that you import into a report generates a query which tells Power BI Desktop which data
to get (and how to get it). You can edit these queries in (at least) 3 different ways:

You can right-click on a table in the Fields pane and choose Edit
query…

…or you can choose this option from the Home tab of the ribbon
(although it says it’s doing something different, transforming data and
editing a query are actually the same thing!).

Alternatively, you could click on this button at the point at which you’re
first loading data to go directly into the Power BI Query Editor.

© Copyright 2024 Page 18

 Chapter 1 - Getting Started

The Power BI Query Editor

Choosing to edit a query as described above opens the Power Query Editor tool within Power BI
Desktop.

Although you’re still working in the

same Power BI Desktop file, the
Power Query Editor has a different
ribbon with options related to
modifying data.

When you have finished cleaning your

data click Close & Apply to close the
Power Query Editor.

Wise This program to edit Power BI queries has gone by many names in the past! This
Owl’s courseware will call it Query Editor, although this name seems to have been
Hint abandoned by Microsoft. Little known fact: everything that you can do using Query
Editor in Power BI Desktop you can also do when getting data in Excel.

Promoting Row Headers

For our example the first thing you need to do is to make the first row your table headers:

a) To tell Power BI Desktop to use the first

row of the table as column headers, click
on this tool on the Home tab of the
Query Editor ribbon:

b) Query Editor will add two steps: one to promote your row headers (as
requested), and then another to change the data types of the columns
following this step. Query Editor will often insert silent additional steps like
this to second-guess your intentions, and (to be fair) nearly always gets
these right.

© Copyright 2024 Page 19

 Chapter 1 - Getting Started

Replacing Values

To allow us to average snowfalls for our data we need to remove the cm suffices then convert the
resulting data to integer numbers:

a) Select the first column whose cm suffices you

want to remove, then hold down the Shift key
and select the last one (this is the easiest way to
select multiple columns in Query Editor).

b) Right-click on the selected columns and choose

to replace values.

c) Choose to replace the text cm with nothing, then select OK .

Changing Data Types

You can now change the data

types of the 3 columns you have
selected:

Right-click on the 3 columns and

choose to change their data types
to Whole Number (note that this
would have generated errors if we
had done this earlier).

Removing Columns

Finally, we’re not interested in the last snowfall depth, so we’ll remove this column.

Right-click on the Last Snowfall column and remove it

from your query.

© Copyright 2024 Page 20

 Chapter 1 - Getting Started

1.6 Creating Visuals

Visuals are the tables, charts or other gizmos which display the data in your report. There are many
types of visual ( you’ll learn a lot more about them in later chapters of this courseware).

Inserting a Visual

Probably the easiest way to add a visual to a report is as follows:

a) Make sure that you’re in Report view, otherwise you won’t be able to add
visuals!

b) Click on the side bar to the right of the visuals pane (this appears on both
the Home tab and the Insert tab of the ribbon).

b) Choose which visual you want to create. If you get this wrong,
you can always change it after creating your visual by clicking
here:

Assigning Grouping Fields to a Visual

Once you have inserted a visual you can begin assigning fields to it:

The easiest way to

assign fields to a visual
is to click on this tool.

You can then click on

each part of a chart
(here we’re saying what
will appear on the
horizontal axis of this
column chart) then tick
the field you want to
include.

© Copyright 2024 Page 21

 Chapter 1 - Getting Started

Assigning Numerical Fields

You can assign numerical fields in the same way, then change how you want to aggregate them:

a) Click on the Y axis box the field well and choose to

show the Upper Slopes snow depth to get this:

b) Click on the field you’ve added and change it:

c) Once you’ve assigned a numerical field to a

chart you can click on it to say how you want to
aggregate it (here we’ve changed from
summing the upper slopes’ snow depth for each
country – which would be fairly meaningless –
to averaging it).

© Copyright 2024 Page 22

 Chapter 1 - Getting Started

1.7 Three Ways to Format Visuals

Much of your time in Power BI Desktop will probably be spent applying formatting like this:

The chart as it initially appears – the

title is hard to read and doesn’t stand
out, and it could do with a bit of oomph.

A chart which has been formatted within

an inch of its life (including the addition
of data labels showing the average
upper bound snow level by country).

In Situ Selection

There are a few parts of a chart that you can edit on the chart itself:

The parts of the chart you can edit directly are shown in red boxes.

When you double-click on any of these with the chart selected you will
be able to change the text displayed.

Changing what’s on your Chart

You can choose to add or remove some parts of your

chart using the following icon:

Click on this icon to add or remove certain chart

components (here we can add or remove the title, data
labels or a slider).

Wise
Owl’s The More options button is less useful than you might think: it just takes you to the
Hint Format pane on the right-hand side of Power BI Desktop.

© Copyright 2024 Page 23

 Chapter 1 - Getting Started

The Format Pane

You’ll spend much of your time in Power BI Desktop using the Format pane:

a) Select the visual you want to

format (otherwise you’ll see the
formatting properties of the page to
which it belongs instead).

b) Optionally, reduce the number of

properties displayed by typing in
part of the name of the one you
want to change (here we’ve typed
Title in the search box).

c) Expand a card to show its range of

formatting options, and make
changes.

d) Some of the changes made to

change the title to this:

Wise As a short-cut, double-click on a visual to select any part of it that you want to format;
Owl’s
Hint
the relevant card will automatically then be selected in the Format pane.

© Copyright 2024 Page 24

 Chapter 1 - Getting Started

1.8 Publishing your Report

When you’ve finished your report you will probably want to share it!

a) Check that you’re signed

in to a Power BI account.

b) Click on this Publish

button on the Home tab of
the Power BI ribbon.

c) If you have unsaved changes you will be asked at this point to

save them (you can’t publish a report which contains unsaved
changes).

d) Choose to publish
to the default My
workspace (see
hint below for
more on this).

e) Click on this link to see your report in Power BI f) Your report as it will appear to the world
Service on the Internet (you may be asked to log in (provided of course that they have the right
to your account again at this point). security level and licence …).

Wise A (much) later chapter in this courseware will cover publishing in more detail,
Owl’s
Hint
including an explanation of workspaces (and why you might want to create them),
how to create dashboards and much more besides.

© Copyright 2024 Page 25

 Chapter 2 - Importing Data

CHAPTER 2 - IMPORTING DATA

2.1 Our Example

Our example is based on a relational database which keeps track of sales of soft toys. The diagram
below shows which type of data source we’ll use to import each table:

Excel PBI Dataset Excel

Excel

Website Typed in

Typed in

CSV file
Excel
SQL Server

Pasted (Word)

Excel

SQL Pasted
Server Website
CSV file
Excel Excel

Wise Once you’ve loaded your data into Power BI from disparate data sources all tables
Owl’s
Hint
will be treated equally (so for example you can join a table imported from Excel with
one imported from a website without any problem).

© Copyright 2024 Page 26

 Chapter 2 - Importing Data

2.2 Importing from Different Sources

This section shows how to import data into a report from a variety of common data source types.
Regardless of which data source type you’re using, you can begin the import process as follows:

a) From the ribbon choose Home | Get Data. You can also click the top
half of the Get Data tool to open the dialog box shown below, or click
on this link in a new report:

b) Pick a data source type from the Common data sources list, or click
More… to see more choices.

Wise What happens next depends on which data source type you’ve chosen, but it
Owl’s
Hint
inevitably involves launching some type of wizard which will help you import your
data.

Re-Using a Data Source

You can quickly re-use a recent data source as shown below:

a) From the ribbon choose Home |

Recent Sources.

b) Pick from the list of databases,

workbooks, websites, etc from which
you’ve already imported data.

© Copyright 2024 Page 27

 Chapter 2 - Importing Data

2.3 Importing from Excel

To start importing from an Excel workbook, use this short-cut:

Power BI gives you a special Excel tool

because it’s such a popular choice.

Double-click on a workbook containing

one or more worksheets or named
ranges that you want to import.

The dialog box which appears lists the contents of the workbook you have selected. You can choose
which parts of the workbook you want to import as shown below:

Tick the box next to the name of

any item you want to import.
Here we’ve chosen to import the
Centre, Region, Sales and
Town worksheets.

The dialog box will list

worksheets in your workbook,
but also named ranges in the file
such as RegionList here. These
names ranges have a different
icon next to them and appear at
the bottom of the list of options.

When you’ve chosen which

worksheets or named ranges you
want to import choose either to
load them into your model or to
go to Query Editor for further
processing.

Note that Power BI Desktop will where

possible build relationships between the
worksheets you’ve imported:

Power BI Desktop creates these relationships for this

example (we’ve tidied the diagram up a bit). You’ll
learn how and why Power BI Desktop creates
relationships between pairs of loaded tables in another
chapter in this courseware.

© Copyright 2024 Page 28

 Chapter 2 - Importing Data

2.4 Importing CSV or Text Files

You can import from CSV files as well as a variety of other text file types.

CSV stands for Comma Separated Values. The value in one column is
separated from the next by a comma. Our example file also includes a
row of column headers.

To begin importing from a text file like this:

Choose to get data from a Text/CSV file … … then browse for and double-click the file you want to import.

You can then choose exactly how the text file is configured using the dialog box which appears:

The preview of your data is a If Power BI Desktop hasn’t Power BI Desktop attempts to work out the
good way to check if you’ve picked the correct delimiter, data type of each column using a sample of
selected the correct options. you can choose a new one. rows. You can set the sample size here.

Optionally you can click on this button to train Power BI on which When you’ve finished configuring the
columns you want to import, although it’s usually easier to import text file, click the Load button to import it
everything then remove from the query the columns you don’t want. into your Power BI report.

© Copyright 2024 Page 29

 Chapter 2 - Importing Data

2.5 Importing from SQL Server

You can import data from a SQL Server database as shown in the diagram below:

w
a) Like Excel, SQL
Server has a
dedicated import tool!

b) Enter a server name and, optionally, the name of a database.

c) Choose to Import the data and then click OK (see the hint
below for more on what DirectQuery means).

d) If required enter your credentials to connect to the

server you have chosen. Click Connect when
you’ve done so - you may then have to confirm
you’re happy to use an unencrypted connection:

e) In the next dialog box you

can pick from a list of
tables to import. Here
we’ve chosen to import
the Environment table.

Wise If you’re wondering, DirectQuery means you don’t import the data into your model:
Owl’s you just link to it. On the plus side this means that the data in your visuals is always
Hint up to date, but on the downside reports may run more slowly, and there are
numerous limitations (for example, you can only use a few types of data source and
you can’t use something called calculated columns).

© Copyright 2024 Page 30

 Chapter 2 - Importing Data

Using Queries and Stored Procedures

Rather than choosing to import from a list of tables, you can write a query to return your data. This
is more complicated but provides much more control over which data you get.

a) It’s much easier to test your

query in SQL Server
Management Studio than it
is to type it into Power BI!
When your query or stored
procedure is working, copy
the query text or the name
of the stored procedure to
the clipboard.

b) While loading SQL Server data, choose to show advanced options.

c) Choose either to execute a

stored procedure (left) or run a
query (right). Either option will
then let you load your data:

Be careful: Power BI Desktop

seems to have a preference for
choosing Direct Query when
you load data from SQL Server
like this; be sure to set this back
to Import.

Passing Arguments to Stored Procedures

CREATE PROC spListTowns(
Note that you can now pass arguments to a stored procedure @region varchar(100)
)
using these advanced options: AS
-- list the towns in any given
Here we have a stored procedure listing out all the towns for any region
given region. We could load this as follows: SELECT
t.TownName AS Town,
t.TownId
FROM
Town AS t
JOIN Region AS r ON t.RegionId
= r.RegionId
WHERE
r.RegionName = @region

© Copyright 2024 Page 31

 Chapter 2 - Importing Data

2.6 Importing from a Website

Power BI Desktop makes it easy to grab data from a website, as shown below:

a) Find a website which contains

a table of data that you want to
import (this one is at
wiseowl.co.uk/sundry/pbd1/ ).

b) Choose to get data from a

Web source in the Other
category.

c) Enter the URL of the page

containing the table you
want to import and click
OK.

d) If this is the first time

you’ve connected to this
page you’ll be asked if you
want to use any
credentials. Here we’re
opting to connect to the site
anonymously.

e) Tick the box next to any table you

want to import.

f) Choose one of these buttons to

load the data directly or to further
process it before loading:

© Copyright 2024 Page 32

 Chapter 2 - Importing Data

2.7 Entering Data Manually

As well as importing existing data, Power BI Desktop allows you to enter data into a model manually.

Pasting Data

Although you can’t import directly from Word, you can copy and paste:

a) In Word, select the table you want to import and

copy it. b) Click on this button to enter data into a new table.

c) Right-click on the empty grid and choose to paste in

your data.

d) Power BI Desktop will decide whether the first row

of your table should become the header columns.

e) Give your table

a better name.

f) Choose to Load
it into your data
model.

Wise
Owl’s If you copy and paste data, you obviously won’t be able to refresh the resulting table
Hint to bring in updates.

© Copyright 2024 Page 33

 Chapter 2 - Importing Data

Typing in Data

The final option for loading data into a model in Power BI Desktop is to type it in!

a) From the ribbon select this

icon to enter data.

b) You can overtype any

column name to rename it.

c) Type in the data that you

want to store in the table.

d) Right-click to insert any

additional columns that you
need.

e) Type in any data for new

columns that you’ve added.

f) Give the table a name and

click Load to add it to the
report.

© Copyright 2024 Page 34

 What we do!

Basic Advanced Systems /

training training consultancy

Microsoft Excel

VBA macros
Office

Office Scripts

Microsoft Access

Power BI and DAX

Power BI, etc

Power Apps

Power Automate (both)

SQL

Reporting Services
SQL Server

Report Builder

Integration Services

Analysis Services

Visual C#
Coding and AI

VB programming

AI tools

Python

www.wiseowl.co.uk | (0161) 883 3606 | sales@wiseowl.co.uk

www.wiseowl.co.uk | (0161) 883 3606 | sales@wiseowl.co.uk