Power BI: Power Query


Mastering Power Query will allow you to clean, tidy and reorganise data so it meets your needs, before loading into Power BI or Excel

1 day, 10:00 AM - 04:30 PM


Session information


Session 1
10:00 GMT - 12:30 GMT
2 hours, 30 minutes
    Session 2
    14:00 GMT - 16:30 GMT
    2 hours, 30 minutes
      • £595.00 excl. VAT


      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