Power BI Data Methods (2019)

Course details

Power BI is a powerful data analytics and visualization tool that helps users monitor data, analyze trends, and make smarter decisions. The breadth of the tool's capabilities is quite wide across the entire application, ranging from custom data connectors to custom dashboard visuals. This course focuses on the data end of Power BI, also known as Power Query (the same Power Query found in Excel), and how this part of the application can automate the data querying process and restructuring of data sets. Instructor Helen Wall goes over the array of Power BI data connection options, from static files to Python scripts; shares key techniques for transforming unusable data; explains how to use the M formula language to improve efficiency and create custom queries; and more.

Learning objectives
Describe the steps in connecting to folders.
Explain the process of using list and record objects in data analysis.
Identify errors when creating code with M.
Define how to reference a list in a table as a column.

Concepts

0. Introduction

01 - The Power BI ecosystem
02 - What is Power BI
03 - Understanding ETL (extract, transform, and load)
04 - Focus on Power Query
05 - Course considerations

1. Extracting Data - Files

06 - Connecting to CSV or text files
07 - Manually entering data
08 - Connecting to an Excel file
09 - Connecting to a PDF file
10 - Connecting to folders

2. Extracting Data - Databases

11 - Connecting to databases
12 - Comparing data connection modes
13 - Query folding and native queries

3. Extracting Data - Web and Other Options

14 - Connecting to web tables
15 - Querying API data
16 - Querying REST API connections
17 - Configuring OData feeds
18 - Installing Python
19 - Running Python scripts

4. Transforming Data - Cleaning

20 - Leveraging metadata
21 - Leveraging data types
22 - Making initial field transformations
23 - Splitting fields
24 - Merging fields
25 - Cleaning text fields
26 - Transforming numerical fields
27 - Removing or replacing values
28 - Filtering and removing duplicates
29 - Accessing native query in cleaning

5. Transforming Data - Integration

30 - Introducing table objects
31 - Introducing list and record objects
32 - Working with binary objects
33 - Grouping data
34 - Pivoting data
35 - Transposing data
36 - Unpivoting data
37 - Accessing native query in integration

6. Transforming Data - Enrichment

38 - Leveraging text formulas
39 - Conditional formulas
40 - Filling up or down columns
41 - Leveraging date formulas
42 - Combining binary files with formulas
43 - Accessing native query in enrichment

7. Leveraging M Language - Logic and Syntax

44 - Working with Query Editor steps
45 - Breaking down syntax
46 - Renaming steps in M
47 - Consolidating M steps
48 - Adding data types as custom M code
49 - Connecting to zipped binary data

8. Leveraging M Language - Objects

50 - Utilizing parameters
51 - Creating list objects
52 - Referencing a list as a column in a table
53 - Leveraging record objects
54 - Leveraging list functions
55 - Creating date tables
56 - Looping with lists
57 - Combining list objects

9. Leveraging M language - Custom Functions

58 - Setting up custom functions
59 - Converting queries into functions
60 - Configuring custom filtering

10. Loading Data

61 - Configuring loading options
62 - Fixing errors
63 - Refreshing data
64 - Joining sets of data
65 - Composite models

Conclusion

66 - Next steps