This page has 0 threads | Add post

Courseware

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	Accessing Split Column Menu Splitting by Delimiter Splitting by Number of Characters Splitting by Positions Splitting by Change in Case or Character Type Splitting into Rows Retaining Quotation Marks	6
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	20
2.5	Special Case - Last Refresh Date/Time		22
2.6	Conditional Columns		23
2.7	Indexing Columns		24

Chapter 3 - COMBINING QUERIES▼

Section	Title	Subsections	Page
3.1	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	25
3.2	Appending Queries	Combining Queries using Append Tidying up your Results	28
3.3	Merging Data	Loading the Tables to Merge Merging the Data Flattening the Combined Table Final Touches	30
3.4	Fuzzy Merging	Setting a Similarity Threshold Enforcing Case Sensitivity Using a Transformation Table	32

Chapter 4 - MANIPULATION TRANSFORMS▼

Section	Title	Subsections	Page
4.1	Unpivoting Data	Loading the Original Data Unpivoting the Data Renaming your Columns	35
4.2	Grouping Data	Optional Step – Remove Excess Columns Starting Grouping Specifying How to Group Data	37
4.3	Pivoting Columns	Remember Matrices Pivoting: Creating the Necessary New Columns Pivoting by Columns Using our Pivoted Columns	39
4.4	Transposing Data: Replacing Text		41
4.5	Transposing Data: Joining Rows Together	Step 1 – Transposing the Data Step 2 – Reversing your Columns Step 3 – Merging your Columns Step 4 – Transposing Back	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 Column Profiles	Showing Pop-up Profile Data Grouping Profiled Data	50

Chapter 7 - FILTERING QUERIES▼

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

Chapter 8 - ERROR-HANDLING▼

Section	Title	Subsections	Page
8.1	Default Treatment of Errors	Errors in the First 1000 Rows Errors in Subsequent Rows	55
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 Step 4 - Merging the Data Back Together	57
8.3	Fixing Errors In Situ	Replacing Errors Handling with a Custom Column	59

Chapter 9 - DATAFLOWS▼

Section	Title	Subsections	Page
9.1	Creating Dataflows	Step 1 – Start the Dataflow Step 2 - Create your Dataflow’s Transforms Step 3 – Save your Dataflow	60
9.2	Basing Reports on Dataflows		63

Chapter 10 - ROW-LEVEL SECURITY▼

Section	Title	Subsections	Page
10.1	Overview of Row-Level Security	Limitations and Constraints	64
10.2	Roles in Power BI Desktop	Creating Roles Testing Roles	64
10.3	Roles in Power BI Service	Configuring the App Workspace Assigning Users to Roles Testing your Roles (or Users)	67
10.4	Dynamic Security	Creating a Permissions Table Creating a Role	70
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	Our Example		74
11.2	Changing the Data Source Manually		75
11.3	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	81
12.4	Using Parameters	Filtering by Parameter Values the Parameter Values Chosen	82
12.5	Using Parameters with Templates	Creating a Template Opening (Using) a Template	85
12.6	Query Dependencies		87

Chapter 13 - DROPDOWN PARAMETERS▼

Section	Title	Subsections	Page
13.1	Our Example		88
13.2	Creating a Simple List Parameter		89
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	90
13.4	Referencing the Dropdown Parameter		92

Chapter 14 - PARAMETERS AND STORED PROCEDURES▼

Section	Title	Subsections	Page
14.1	Overview		93
14.2	Loading Data from a Stored Procedure		94
14.3	Making Parameters Dynamic	Creating the Parameters Referencing the Parameters in the Advanced Editor Editing Parameter Values Displaying the Parameters in your Report	95

Chapter 15 - DYNAMIC CONNECTIONS▼

Section	Title	Subsections	Page
15.1	SQL Server Dynamic Connections	First Enable Parameterisation of Data Sources Creating a Parameter Referencing your Parameter Changing the Server or Database The Query Editor Step Created	98
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	100

Chapter 16 - CUSTOM FUNCTIONS▼

Section	Title	Subsections	Page
16.1	Overview		104
16.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	105
16.3	Example - Combining Selected Worksheets	Step 1 - Set Up your Base Query and Parameter Step 2 - Link the Query to the Parameter Step 3 - Create your Custom Function Step 4 – Create a Table of Years Step 5 - Invoke your Custom Function Step 6 – Expand the Results	109
16.4	Combining Tables from Web Pages	Step 1 – Get a List of Pages to Visit Step 2 – Create a Parameter Step 3 – Load the Data for One Page Step 4 – Parameterise your Query Step 5 – Create a Custom Function Step 6 – Invoking the Custom Function Step 7 – Expand your Results	112

Chapter 17 - HTML AND JSON▼

Section	Title	Subsections	Page
17.1	Our Example		117
17.2	Extracting Text from HTML	Starting our Example Creating an HTML.Table Function Creating a Column to Parse HTML Expanding the Table Returned Getting the Readable Text Only	118
17.3	Transforming JSON	Parsing JSON (or XML) Expanding the Resulting List as Rows Expanding the Resulting List as a Single String Extracting the List of Topics Creating the Relationship	121
17.4	Getting External Links	Preparing the Way Loading HTML from One Website Turning your HTML into a Table Extracting Links from HTML Parametrise your Query Creating a Custom Function Invoking this Custom Function	124

Chapter 18 - USING APIS▼

Section	Title	Subsections	Page
18.1	Introducing APIs		127
18.2	Some Simple Examples	Random Dog Pictures XKCD Cartoons	127
18.3	A Digression: Displaying Images Properly		130
18.4	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	130
18.5	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 Creating the Bridging Table Changing the Relationship Directions Using your Tables	134

Chapter 19 - DATA INSIGHTS▼

Section	Title	Subsections	Page
19.1	Overview	Three Warnings	138
19.2	Text Analytics	Detecting Language Extracting Key Phrases Scoring Sentiment	139
19.3	Tagging Images	Our Example Asking to Tag Images Interpreting the Results	141

Chapter 20 - LINKING TO POWER AUTOMATE▼

Section	Title	Subsections	Page
20.1	Our Example		143
20.2	Creating the Button and Flow	Step 1 – Create the Button Step 2 – Create your Flow Step 3 – Compose your Data Step 4 - Format your Table as HTML Step 5 – Send your Email Step 6 – Saving and Assigning your Flow Step 7 – Formatting your Button	144
20.3	Testing your Button	Some Limitations of Running Flows from Power BI Desktop	148

Chapter 21 - POWER APPS IN POWER BI▼

Section	Title	Subsections	Page
21.1	Overview	Sharing Data Sources	149
21.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	150

Chapter 22 - POWER BI IN POWER APPS▼

Section	Title	Subsections	Page
22.1	Overview		152
22.2	Creating the Power BI Report	Setting a Custom Page Size Publishing the Page	153
22.3	Creating your Dashboard	Pinning a Live Page to a Dashboard Hiding the Tile’s Title and Subtitle	154
22.4	Showing the Live Page in Power Apps		156

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

Choose software

Power BI course manuals