Excel Power Query Fundamentals Course

Learning Microsoft Excel Power Query will enable you to fundamentally transform the way you work with business data.

It will significantly reduce the number of hours you spend manually manipulating extracted data into a usable analytical format. It will even reduce time, reliance on and the maintenance of data transforming and cleaning using MS Excel VBA Macros.

During the course you will complete a number of practical case study files which you will retain post course, as your own learning reference library. Each practical will via a step-by-step approach, coach you on the various key learning points, on how to quickly and effectively extract, transform and output cleaned data. These together with many practical user hints, and tips, will enable you to reduce many hours of repetitive data manipulation tasks into just minutes or even seconds at just the press of a refresh button, consistently time after time.

In just one day, the coaching you receive will ensure you are both confident and competent at extracting, importing, transforming, cleaning and reshaping your data into an output format which is ready to be easily analysed. Or even enable you to create a dataset in the correct format for importing into core business applications.

(1) Getting Started

• Overview of Excel Power Query
  
  • What is Power Query?
  • Why Use Power Query?
  • How to access Power Query?

• Introduction to the Power Query Editor Window
  
  • Checking your Power Query Settings
  • The Ribbon Tabs and Icon Groups
  • The Query Navigation Pane
  • Current View Window
  • Formula Bar, Status Bar
  • Query Settings Pane
    
    • Properties
    • Applied Steps

(2) Extracting Data

• How to Extract / Source Data
  
  • From a Named Range
  • From an Excel table
  • From another Excel workbook(s)
  • From PDF
  • Append data from Excel worksheets and multiple data sources
  • From Folder (multiple files)

(3) Transforming and Cleaning Data - Part 1

• Basics
  
  • TRIM, CLEAN
  • Correct Dates
  • Text - lower case, UPPER case, Capitalise Each Word
  • Choose Columns, Go To Columns
  • Keep Rows, Top, Bottom, Range
  • Remove Rows, Top, Bottom, Alternate, Blanks, Duplicates, Errors
  • Remove Columns, Remove Other Columns
  • Keep and Remove Blanks, Duplicates, Errors
  • Replace Values, Replace Errors
  • First Row as Header, First Header as Row
  • Fill Up, Fill Down
  • Sorting and Filtering
  • Split Columns, Merge Columns
  • Extract
    
    • Length
    • First Characters, Last Characters, Range
    • Text Before Delimiter, Text After Delimiter
    • Text Between Delimiters

(4) Transforming and Cleaning Data - Part 2

• Advanced
• Working with Data Types
• Add a Conditional Column
• Add a Custom Column
• Format - Add Prefix, Add Suffix
• Transforming Dates
  
  • Age, Date Only, Month, Quarter, Week
  • Subtract Days, Combine Date and Time, Earliest, Latest

(5) Advanced Transforming Techniques

• Transpose
• Group By
• Unpivot

(6) Queries

• Merge Queries vs. Append Queries
  
  • Merge Query
    
    • Merge data from an Excel workbook
    • Merge data from multiple Excel workbooks

• • Append Query
• Duplicate Query
• Reference Query
• Structuring your Queries by Group

(7) M Code and Advanced Editor

• Overview of M Code
• Applied Steps
• Introduction to Advanced Editor window

(8) Load

• Refresh Preview, Refresh All
• Close and Load and setting Default
• Close and Load To
  
  • Table
  • PivotTabIe Report
  • PivotChart
  • Only Create Connection

• Queries and Connections Windows
  
  • Refresh Query
  • Query Properties

• Dealing with Errors

This course is designed for existing advanced users of spreadsheets, who spend a substantial amount of their work time manually preparing data for analysis.

Previous experience of using Range Names, Structured Reference Tables, Pivot Tables and Formulas i.e.= IF, =IFS and new array formulas is required.

It is recommended that all attendees aim to arrive at least 15 minutes before the start of the course to ensure all pre-course administration and set-up are completed in a timely manner.