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.
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