PowerQuery for Excel

If you spend hours and hours getting the data and then cleaning it up (every month) then the new PowerQuery for Excel is for you.

Below some of the features of PowerQuery and why you must learn more about it.

It is free and you can (easily)

  • Join datasets together (no more copy and paste for half an hour!)
Append files to each other
  • Unpivot a data set to make it normal ( a normalised database)
unpivot
  • Compare lists to find the matching/ unique items for reconciliations
Compare Lists
  • Replay your data cleanup steps so that you build it once and use it often
  • Delete unnecessary rows
removing-unnecessary -rows
  • Fill in gaps
introduction-to-fill-in-gaps-in-excel
  • Change data format
convert-dates
  • Split columns
introduction-to-text-to-columns
  • Identify and remove duplicates
Introduction-to-identify-and-remove-duplicates-in-excel

Course Outline

  • Traditional Excel features you need to be aware of
  • PowerQuery vs Traditional Excel
  • Where does PowerQuery fit into the Excel world
  • Key Differences for Excel Users
  • The Data Cleanup Process
  • PowerQuery
  • The PowerQuery Ribbon
  • Your First PowerQuery
  • The PowerQuery Ribbons
  • Getting Data into PowerQueryAppend files to each other
    • From a Table WITHIN the existing workbook
    • From an external source- excel, text or database
    • From all the files in a folder- NB
    • Other Sources
    • Unpivot- NB
  • Transforming the data in PowerQuery
  • A Word on the Formula bar
  • How to UNDO in PowerQuery
  • Data Type- critical in PowerQuery
  • Text Changes
  • Number Changes
  • Removing unnecessary rowsremoving-unnecessary -rows
  • Filling in gaps in data
  • Convert Text fields into usable numbers
  • Identify and remove duplicates
  • Splitting Columns
  • Merging columns
  • Transposing Data
  • Comparing lists to each other
  • Correcting Date Issues
  • Create Custom Columns
  • M Formulas

Feedback & Who Should Attend

  • I thought the course was great and really liked the Power Query session. De Wet was also great in presenting- Stanlib
  • One of the best trainers I’ve ever had the pleasure of working with. Experienced, knowledgeable, helpful, patient and engaging – a must keep- Deloitte’s Consulting

Typical Attendees

  • Business owners, managers, financial managers, directors, bookkeepers,
  • marketers, sales representatives, sales managers, sales executives or team
  • HR managers,

and anyone who spends hours and hours cleaning the data and almost no time analysing it.

Pre Requisite (s)

Knowledge required for Excel PowerQuery Training:

  1. Basic Excel skills
  • Opening & closing files
  • Moving around the spreadsheet
  • Inserting & deleting rows and columns
  • Inserting text & formulas
  • Deleting cells
  • Copying formulas
  • Printing
  1. Basic formulas (+-*/) e.g. =C5+C7+sum(C9:C12), and =D5*D6/12 3. Formatting
  2. Formatting