Power Query Training online or onsite

Clean and transform your data fast and easily with Power Query

Power Query has been around for a long time now but it's surprising how few Excel users are aware of it. It's the easy way to connect to your databases, extract data, clean the data all ready for you to produce your reports

It can replace those old complex VBA Excel spreadsheets and no programming or coding knowledge is required

    • An Introduction to Power Query

Power Query allows you to clean your data in Excel or connect to a data source and clean the data before bringing it in to Excel. For example you may regularly change date formats, remove unwanted rows and columns, combine data from different worksheets and workbooks or add columns or rows of data. Power Query can do this and much more allowing you to refreshh your data cleaning each time you bring it in to Excel.

We run an introductory course and also provide consultancy service

Getting Started

 Introduction to Power Query
 A quick example
 Understanding queries
 Power Query User Interface – A Walkthrough

Cleaning and Transforming – Part 1

 Rectify Date Format using Data Type
 Cleaning Up Data with Case Change, TRIM, CLEAN
 Removing Duplicates and Blank Row
 Splitting Columns by one or multple delimiters
 Remove unwanted rows and columns
 Merging one or more Columns
 Filling columns up and down
 Sort and filter columns
 Split data into rows
 Replace error values
 Understanding applied steps
 Reusing queries
 Automate queries

Merging and combining data – Part 2

 Combine multiple tables from one or more workbooks.
 Different merge techniques – Merge Queries vs. Append Queries
 Extract data from a picture or pdf
 Extract data from a database
 JOIN Types
 Understanding JOIN Concept and its Types
 JOINS in Action
 Quick DOs and DONTs for Merge Queries
Creating a key ID column 

Merging Queries with more than 1 KeyID Column
Using Fuzzy Lookup

Re-Structuring data – Part 3

 Unpivot Column data
 Group repeated data
 Transpose data

Data Sources to connect to – Part 4

 Excel on SHarePoint or One Drive or your local PC
 Combine or Merge Multiple Excel workbook, text files or CSV files from a Folder.
 Import data from access db
 SahrePoint or One Drive Folder

Adding columns with formulas and new data – Part 5

 Adding Custom Column
 Adding a Conditional Column
 Adding a column using examples
 Use logical functions.
 Use Text functions

Using Date Functions

Loading data from Power Query back in to Excel  – Part 6

 To Excel Table
 To Pivot Table
 As a connection

Reuse Queries  – Part 7

Reuse in same workbook

Reuse queries in different workbooks


Contact us about Power Query

It’s always easier to contact us via email rather than phone. We’re out training most days of the week