Power BI Data Methods

Description

In this course you will learn:

• Power BI is a powerful data analytics and visualization tool that helps users monitor data, analyze trends, and make smarter decisions
• 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
• the array of Power BI data connection options, from static files to Python scripts; shares key techniques for transforming unusable data;
• how to use the M formula language to improve efficiency and create custom queries; and more.

Syllabus:

• Introduction
• The Power BI ecosystem
• What is Power BI?
• Understanding ETL (extract, transform, and load)
• Focus on Power Query
• Course considerations
   

1. Extracting Data: Files

• Connecting to CSV or text files
• Manually entering data
• Connecting to an Excel file
• Connecting to a PDF file
• Connecting to folders
   

2. Extracting Data: Databases

• Connecting to databases
• Comparing data connection modes
• Query folding and native queries
   

3. Extracting Data: Web and Other Options

• Connecting to web tables
• Querying API data
• Querying REST API connections
• Configuring OData feeds
• Installing Python
• Running Python scripts
• 4. Transforming Data: Cleaning
• Leveraging metadata
• Leveraging data types
• Making initial field transformations
• Splitting fields
• Merging fields
• Cleaning text fields
• Transforming numerical fields
• Removing or replacing values
• Filtering and removing duplicates
• Accessing native query in cleaning
   

5. Transforming Data: Integration

• Introducing table objects
• Introducing list and record objects
• Working with binary objects
• Grouping data
• Pivoting data
• Transposing data
• Unpivoting data
• Accessing native query in integration
• 6. Transforming Data: Enrichment
• Leveraging text formulas
• Conditional formulas
• Filling up or down columns
• Leveraging date formulas
• Combining binary files with formulas
• Accessing native query in enrichment
• Syntax
• Working with Query Editor steps
• Breaking down syntax
• Renaming steps in M
• Adding data types as custom M code
• Connecting to zipped binary data
• Utilizing parameters
• Creating list objects
• Referencing a list as a column in a table
• Leveraging record objects
• Leveraging list functions
• Creating date tables
• Looping with lists
• Combining list objects
• Converting queries into functions
• Configuring custom filtering
   

6. Loading Data

• Configuring loading options
• Fixing errors
• Refreshing data
• Joining sets of data
• Composite models