Description

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

Merging Queries

  • How to denormalise your data by merging queries.
  • Using Merge to find non-matching data.
  • Using Merge to summarise data.
  • Using “Fuzzy” Merge

Appending Data

  • 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

Query Parameters

  • Creating Query Parameters
  • Managing and editing Parameters

Using Lists

  • 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