This page has 0 threads | Add post

Advanced Power BI (Data) course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Power BI courses).

Click here to download a sample of the first two chapters of this Advanced Power BI (Data) manual, or review how to license our courseware.

Chapter 1 - MANIPULATING COLUMNS▼

Section	Title	Subsections	Page
1.1	Splitting Columns	Splitting by Number of Characters Splitting by Delimiters Splitting by Positions Splitting by Change in Case or Character Type Splitting into Rows Retaining Quotation Marks	7
1.2	Merging Columns		12
1.3	Extracting Data	Replacing or Adding Columns An Example The Possible Options	13

Chapter 2 - CREATING COLUMNS▼

Section	Title	Subsections	Page
2.1	Ways to Create New Columns		15
2.2	Columns from Examples	Step 1 – Start the Feature Step 2 – Show some Examples Step 3 – Confirm the Formula Step 4 – Review your Formula Step 5 – Understanding your Formula	16
2.3	Built-up Columns		19
2.4	Custom Columns in M	M Prefixes Our Example – Elapsed Days Creating a Custom Column A Special Case: Last Refresh Date/time	20
2.5	Conditional Columns		23
2.6	Indexing Columns		24

Chapter 3 - COMBINING QUERIES▼

Section	Title	Subsections	Page
3.1	Ways to Combine Data		25
3.2	Loading Multiple Files from a Folder	Setting the Folder Combining the Files Setting the Template for Import Tidying Up the Results An Outline of How it Works	26
3.3	Appending Queries	Combining Queries using Append Tidying up your Results	29
3.4	Merging Data	Loading the Tables to Merge Merging the Data Flattening the Combined Table Final Touches	31
3.5	Fuzzy Merging	Setting a Similarity Threshold Enforce Case Sensitivity Using a Transformation Table	34

Chapter 4 - MANIPULATION TRANSFORMS▼

Section	Title	Subsections	Page
4.1	Unpivoting Data	Loading the Original Data Unpivoting the Data Renaming your Columns	36
4.2	Grouping Data	Optional Step – Remove Excess Columns Starting Grouping Specifying How to Group Data	38
4.3	Pivoting Columns	Remember Matrices Our Example for Pivot Columns Creating the Necessary New Columns Pivoting by Columns	40
4.4	Transposing Data	Replacing within a Column Joining Rows Together	42

Chapter 5 - NORMALISING DATA▼

Section	Title	Subsections	Page
5.1	Our Example		44
5.2	Extracting Unique Lists	Duplicating the Original Table Creating a Unique List of Directors/Genres	45
5.3	Numbering the Lists		46
5.4	Storing Id Numbers not Names	Merging the ID Column Expanding the Merged Columns Removing Unnecessary Additional Columns Making Sure the Right Table Loads The Final Relationships Finishing your Data Model	47

Chapter 6 - DATA PROFILING▼

Section	Title	Subsections	Page
6.1	Showing Profiling Data	Displaying Pop-up Column Profiles Grouping Profiled Data	50
6.2	Query Dependencies		52

Chapter 7 - FILTERING QUERIES▼

Section	Title	Subsections	Page
7.1	Removing Nulls and Errors		53
7.2	Filtering Dates	Previous or Next Time Periods Matching the Earliest/Latest Date Specific Time Periods	54

Chapter 8 - ERROR HANDLING▼

Section	Title	Subsections	Page
8.1	Default Treatment of Errors	Errors in the First 1000 Rows Errors in Subsequent Rows	56
8.2	Fixing Errors in a Separate Table	Step 1 – Duplicate the Table Step 2 – Remove the Errors from One Table Step 3 – Correct the Errors in the Second Table Merging the Data Back Together	58
8.3	Fixing Errors In Situ	Replacing Errors Handling with a Custom Column	60

Chapter 9 - DATASETS AND DATAFLOWS▼

Section	Title	Subsections	Page
9.1	Overview		61
9.2	Loading from a Power BI Dataset		62
9.3	Power BI Dataflows	Creating a Dataflow (the Data Source) Creating a Dataflow (the Transforms) Saving your Dataflow Loading Data from your Dataflow	63

Chapter 10 - ROW-LEVEL SECURITY▼

Section	Title	Subsections	Page
10.1	Overview of Row-Level Security	Limitations and Constraints	66
10.2	Roles in Power BI Desktop	Creating Roles Testing Roles	67
10.3	Roles in Power BI Service	Configuring the App Workspace Assigning Users to Roles Testing your Roles	69
10.4	Dynamic Security	Creating a Permissions Table Creating a Role	71
10.5	Creating Groups of Users	Creating the Tables Linking the Tables Creating the Role	72

Chapter 11 - SWITCHING DATA SOURCES▼

Section	Title	Subsections	Page
11.1	Changing the Data Source Manually		75
11.2	Switching the Data Source using M	Showing the Advanced Editor The Excel Query Creating a SQL Server Version of our Table Switching the Data Source Resolving Problems Viewing the Results	76

Chapter 12 - SIMPLE PARAMETERS▼

Section	Title	Subsections	Page
12.1	Parameters, Slicers and Filters	Parameters Needed for this Example	79
12.2	Working with Parameters	Accessing Parameters Creating Parameters Managing Parameters Loading Parameters into Power BI	80
12.3	Entering Values for Parameters	Entering Parameter Values in Query Editor Entering Parameter Values in Power BI Desktop	82
12.4	Filtering using Parameters		83
12.5	Referencing Parameters in Power BI	Step 1 – Adding a Quick Measure to Reference the Parameter Value Step 2 – Create a Card to Display the Measure	84
12.6	Using Parameters with Templates		86

Chapter 13 - DROPDOWN PARAMETERS▼

Section	Title	Subsections	Page
13.1	Our Example		87
13.2	Creating a Simple List Parameter		88
13.3	Using List Queries for Parameters	Step 1 – Create a Query Giving a Single Column of Data Step 2 – Make this a List Query Step 3 – Creating the Dropdown Parameter	89

Chapter 14 - PARAMETERS AND STORED PROCEDURES▼

Section	Title	Subsections	Page
14.1	Overview		91
14.2	Loading Data from a Stored Procedure		92
14.3	Making Parameters Dynamic	Creating the Parameters Referencing the Parameters in the Advanced Editor Editing Parameter Values	93

Chapter 15 - DYNAMIC CONNECTIONS▼

Section	Title	Subsections	Page
15.1	SQL Server Dynamic Connections	First Enable Parameterisation of Data Sources Creating a Parameter Using this Parameter to Get Data Referencing your Parameter Changing the Server or Database The Query Editor Step Created	95
15.2	Dynamic Connections to Excel	Step 1 – Create a Connection (Get Data) Step 2 – Create a Parameter Step 3 – Parameterising the Connection Used Step 4 - Changing the Worksheet Used	98

Chapter 16 - WHAT-IF PARAMETERS▼

Section	Title	Subsections	Page
16.1	Our Example	The Steps to Follow	101
16.2	Creating and Using What-If Parameters	Step 1 – Create the What-If Parameter Step 2 – Customise the Slicer Step 3 – Create a Quick Measure to Show your Data Step 4 – Display your Measure in a Visual	102

Chapter 17 - CUSTOM FUNCTIONS▼

Section	Title	Subsections	Page
17.1	Overview		106
17.2	Example – Splitting Postcodes	Step 1 – Getting the Initial Data Step 2 - Creating a Parameter Step 3 - Duplicate your Parameter Step 4 – Create a Query Based on the Parameter Value Step 5 - Turning this One-Off Query into a Function Step 6 - Understanding the Resulting List of Queries Step 7 – Test your Function Step 8 – Invoke your Function for Each Table Row Step 9 – Expand the Results	107
17.3	Example - Combining Selected Worksheets	Setting Up your Base Query and Parameter Link the Query to the Parameter Creating your Custom Function Invoking your Custom Function Expanding the Results	111
17.4	Example – Combining All Worksheets		114
17.5	Combining Tables from Web Pages	Getting the Table of Genres Getting at the URL Creating a Parameter Getting the List of Films for a Genre Integrating the List of Films and your Parameter Creating the Function Invoking the Function	115
17.6	Getting External Links		119

Chapter 18 - POWER APPS IN POWER BI▼

Section	Title	Subsections	Page
18.1	Overview	Sharing Data Sources	120
18.2	Embedding an App	Step 1 - Create a Power BI Report Step 2 – Adding an App Visual Step 3 – Creating or Choosing the App to Show	121

Chapter 19 - POWER BI DESKTOP FLOWS▼

Section	Title	Subsections	Page
19.1	Our Example	Some Limitations of Running Flows from Power BI Desktop	123
19.2	Creating a Power Automate Button	Step 1 – Creating the Button Step 2 – Assigning Data Fields Step 3 – Start Editing your Flow Step 4 – Add Actions (and Data) Step 5 – Saving and Applying your Flow Step 6 – Formatting your Button Step 7 – Test your Button	124
19.3	Editing Power BI Flows in Power Automate		127

Chapter 20 - USING APIS▼

Section	Title	Subsections	Page
20.1	Introducing APIs		128
20.2	Some Simple Examples	Random Dog Pictures XKCD Cartoons Displaying Images Properly	129
20.3	Case Study – Exchange Rates	Finding an API Getting the URL Needed Turning the Results into a Usable Table Adding Country Codes Getting the UK Exchange Rate	132
20.4	Multiple Table APIs	Importing individual Tables Simplifying Column Titles The Need for a Bridging Table Starting the Bridging Table Removing Non-Essential Columns Expanding the List of Films	135

Chapter 21 - HTML AND JSON▼

Section	Title	Subsections	Page
21.1	Overview		138
21.2	Scraping HTML	Creating an HTML.Table Function Expanding the Table Returned Getting the Readable Text Only	139
21.3	Transforming JSON	Parsing JSON (or XML) Expanding the Resulting Record Extracting the List of Topics Creating the Relationship	141

Chapter 22 - DATA INSIGHTS▼

Section	Title	Subsections	Page
22.1	Overview of Data Insights	Three Warnings	143
22.2	Text Analytics	Detecting Language Extracting Key Phrases Scoring Sentiment	144
22.3	Tagging Images		146

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

Choose software

Power BI course manuals