Two half-days of four hours
The addition of Power Query add-ins will revolutionize the way you work with Excel. We now have highly powerful tools accessible to all Excel users to manipulate data and produce dynamic reports.
You'll learn how to import your data and how to transform it in an automated way with Power Query. As an extra, we'll go over how to model your data to produce Power Pivot reports (more advanced pivot tables.)
The Trainers
Your team of specialized trainers consist of Stéphanie Perreault, CPA, Marcel Dubé, Silène Saadeh, Stéphane L'Archevêque (ADM.A) and François Jasmin, CPA. They have extensive experience in finance and accounting or IT departments. This training was developed with the tasks and responsibilities of payroll professionals in mind.
Prerequisites
- This training is not designed for Apple computer users.
- Previously taken the Pivot Table class offered by the NPI.
- Participants must have fundamental knowledge of Microsoft Office Excel and using Microsoft Office 365 – Microsoft 2019 or greater.
- Participants must have access to 2 screens, or 2 laptops or a laptop and a tablet.
- Connect one laptop on ZOOM and upload the exercises on the second laptop.
- Connect the tablet on ZOOM and upload the exercises on the laptop.
- Access to a microphone preferable.
How to access your session:
Three business days before the live event, you will be sent information on how to access your online seminar. If you do not receive the login information three days before the seminar, check your email SPAM folder. If it's not there, please contact membership@payroll.ca as soon as possible.
Section 1 - Power Query
Introduction
- What is Power Query?
- Why use Power Query?
- How do I refresh queries?
- Power Query Editor ribbon overview
- Power Query Advanced Editor Overview
Importing data
- Automate the import of data from different external sources
- Excel workbook,
- Text file
- Folder
Transforming data
- Convert dates to proper format
- Changing data types
- Sort data
- Split columns
- Merge columns
- Remove unnecessary columns and rows
- Group data
- Filter data
- Pivot and unpivot columns
Adding columns
- Create columns with custom calculations
- Create columns with conditional calculations
- Add an index column
Combine queries
- Merge queries (equivalent to VLOOKUP)
- Append (consolidate) queries
Section 2 - Extra Power Pivot
Data modeling
Importing data into the model
Introduction to DAX functions and creating a date (calendar)
Creation of reports with data from the model and pivot tables
Please note that after registering online for this event, you will receive an email confirmation that confirms your registration. Confirmation emails are sent approximately within 2 business days after the registration has been processed.
Three business days before the live event, you will be sent the link to access the online PD seminar on the scheduled day.
Also, detailed instructions on accessing the online PD seminar will be emailed to you on the morning of the event.
Cancellations or Transfers: requests must be received in writing at least four (4) business days prior to the online seminar date. Please note a cancellation fee or transfer fee in the amount of $50 plus applicable taxes will be applied. Cancellation or transfer requests received after the login information has been provided are not eligible for a transfer or refund.