Power Query Training online or onsite

• • 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.
 Csv
 Text
 Database
 Import data from access db
 SahrePoint or One Drive Folder
 Web
 SQL
 Other

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