PowerQuery for Excel

If you spend hours and hours getting the data and then cleaning it up (every month) then the new PowerQuery add in for Excel is for you. 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

  • It was excellent.  I have personally asked most of the people who were there what they thought about it, and everybody was really positive and confirmed that they learned a lot. I also need to mention De Wet, we all thought he was excellent and he really kept the training interactive.  He was definitely the best trainer I have seen so far.- Melco
  • 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 Dashboard 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