Mastering Power Query will allow you to clean, tidy and reorganise data so it meets your needs, before loading into Power BI or Excel
Before you can report on your data, it invariably requires cleaning and restructuring before it can be imported into the Business Intelligence tools you're using. The Power Query editor provides a tool to clean, tidy and reorganise your data so it can be reported on by Power BI or loaded into Excel.
What this course will do for you
Overview of Queries in Power BI Desktop
- Understanding the role of Queries in data import.
- Understanding Extract, Transform & Load (ETL)
- Displaying the Query Editor.
Connecting the Data Sources
- Viewing the variety of data sources available.
- Connecting to a variety of data sources.
Applying Data Types
- How Query will change Data Types on load.
- Understanding the different Data Types.
Exploring a Variety of Data Transformations
- Filtering columns & rows
- Splitting and merging columns
- Trimming and Cleaning data
- Find & Replace, using Fill Down
- Using Date Transformations
Working with Applied Steps
- Understanding the Applied Steps pane
- Renaming and Annotating Steps
Adding Columns to a Table
- Creating Custom Columns
- Using the Column from Example option
- How to use Conditional Columns
- Using an Index Column
Unpivoting, Pivoting and Transposing Data
- Exploring the problem with “pivoted” data.
- Using the Unpivot and Pivot transformations.
- Combining Unpivot with Transpose
- How to denormalise your data by merging queries.
- Using Merge to find non-matching data.
- Using Merge to summarise data.
- Using “Fuzzy” Merge
- Using the Append Queries option
- Using the Connection to Folder to append
- CSV files and Excel Files.
Understanding Disable Load
- Organising Queries into Groups
- Reasons not to load the data
Grouping and Summarising
- How to group data in a Query
- Creating summaries on grouped data
- Creating Query Parameters
- Managing and editing Parameters
- Understanding the use of Lists
- Creating lists using New Query
- Creating lists using M
- Using a List as input for Parameters.
Looking at the M Language
- Using the Advanced Editor
- Working with Lists, Records and Tables
- Using the Let expression