Search our courses

Course Duration – 1 day

Course Subjects:-


  • Power Query Editor and basic transformations
  • Quick insights and data quality & distribution
  • Connecting to multiple data sources
  • Load data into Excel from Multiple Sources such as SharePoint, csv, Web, and even Email
  • Refresh Data and Power Query Refresh options

Common Transformations

  • Split Columns (e.g. by delimiter, no. of characters, positions, digit to non-digit, etc.)
  • Format Columns (e.g. UPPERCASE, lowercase, Capitalize Each Word, Trim, Clean, etc.)
  • Statistic Functions (Sum, Minimum, Maximum, Medium, Average, Standard Deviation, Count Distinct Values)
  • Replace Values
  • Rounding data
  • Filtering columns (Number filters and Text filters)
  • Spot duplicates between two columns
  • Understand data types (and why are they so important when dealing with data)
  • Fill and Replace Values
  • Finding and Correcting Errors in Data

Pivoting and transposing data

  • Pivoting and unpivoting columns
  • Transpose columns

Powerful Transformations

  • Conditional Columns in Power Query (equivalent of IF and NESTED IF statements – but much easier to create/amend)
  • Merge Data with another File (equivalent and much more efficient than a VLOOKUP)
  • Column from Example (an even more powerful version of Flash Fill)
  • Appending data between different files
  • Combining all files in a folder
  • Extract Text Based on Pattern
  • Fuzzy matching
  • Group By (grouping sets of data by a certain factor)
  • Power Query Navigation Tips
  • Finding and Correcting Errors in Data
  • Create Custom Columns
  • Excel’s Data Model (used in Pivot Tables to give them extra power e.g. combine different sources to create a bespoke Pivot Table)

Time Transformations

  • Date & Time Transformations (building calendars , working out age, work out time and date differences, duration, etc.)


  • Applied Steps
  • Refresh options