D3: Power Query & Power Pivot for Excel (2-3 days)

In-Person and Virtual Classroom(in-house only) - Live, instructor-led, interactive training

All of our courses are available for In-house Training. We can also run training virtually. We provide live, instructor-led, interactive courses and our world-class instructors guide you through every step. Our Course Facilitators have over 20 years' experience in delivering training for clients all over the world. A number of Public Courses are scheduled for Perth. Face-to-Face interstate and international training is back in full swing but can still be delivered using online platforms.

 

Our Public courses are aimed at smaller groups of 3 – 8 people so that there is more opportunity for the instructor to assist on a 1-1 basis and discuss the attendee’s specific real-world scenarios.

For Public Courses a minimum of 3 attendees must have signed up 4 weeks in advance of the course otherwise the course will be postponed, and full refunds offered. The 3rd day option also requires a minimum of 3 attendees to proceed.

Power Query & Power Pivot are the biggest Excel news items in the last 10 years. They are the bedrock of Power BI, so learn once and apply your skills in Excel and Power BI.

Power Query and Power Pivot give Excel users the power to draw information out of multiple sources, link it together and perform calculations on it that can then be sliced & diced any way you want.

This hands-on course is focused on real-world techniques utilising the amazing capabilities of Power Query, Power Pivot and Power BI.

The course covers the core elements needed to get an understanding of what Power BI, Power Pivot and Power Query can do. You will use Power Query to extract and shape data and then load it into Power Pivot.  You will build a complex Power Pivot Model from scratch, write DAX formulas and set up interactive reports.

The second day delves deeper into all of the topic areas demonstrating how to get the most from these amazing tools.  You will learn extra advanced Power BI features and get a more in-depth understanding of Power Query and Power Pivot reporting techniques

The third day is when you really sink your teeth in.  Instead of using simplified training examples, you bring your own data and build some analysis models for this, with guidance and assistance from our expert facilitator.  This gives you something that’s immediately applicable, which you can impress your colleagues/boss with and use in your work tomorrow!

Register your interest and we’ll let you know about future courses
Run this course in-house at your company

See what attendees are saying about our courses

 

Expert Facilitator | Customise & run this course In-House | Course Enquiry

What you get from this course

  • Understand how the Power features in Excel build on the functionality of Excel’s native tools, such as PivotTables, slicers and key analytical functions
  • Be able to import data from a variety of sources and relate them quickly to deliver key information in minutes not weeks
  • Utilise best practice database design, realising the power of the Data Model by using LOOKUP and efficient database design
  • Provides an introduction to powerful calculations via Data Analysis Expressions (DAX) language

Pre-requisites

This is an intermediate course and users should already be comfortable with Excel to get the most from this course.

Participants should be familiar with functions & concepts such as VLOOKUP and SUMIFS.

The focus of the course on Excel in Office 365 but will work with 2013/2016 and is also applicable to Power BI Desktop.

Software Required

Office 365 (will work with Excel 2016 or Excel 2019)

Windows only (No Mac)

Note: PowerQuery and PowerPivot are only available on the Windows versions of Excel.  Currently, they are NOT AVAILABLE on any Mac version.

Everyone: please also download and install the free Microsoft Power BI Desktop software.  This is not available for Macs either.

Agenda Highlights

Days 1 & 2:

PowerPivot (Data Model) Introduction

•            Creating your first Power Pivot Model

•            Mapping tables

•            Joining multiple tables together and understanding relationships

•            Creating a Calendar Table

•            Utilising a Calendar Table

•            Building an interactive dashboard with Pivot Charts, Card Visuals and Slicers

 

In-depth Power Pivot Models

•            Introducing DAX Measures

•            Filters and Slicers

•            Best practice tips and pitfalls to avoid

•            Calculated Columns

•            Creating useful time-based measures with more advanced DAX formulas

•            CALCULATE explained

Power Query

•            Exploring the User Interface

•            Power Query techniques for cleaning up messy data

•            How to “un-pivot” data

•            Re-using a pre-built Power Query Calendar

 

Power BI Desktop & POWERBI.com

•            Power BI Desktop

•            A comparison with Power Pivot and Power Query

•            An overview of the graphical interface

•            PowerBI.com

•            Publishing and Sharing your Dashboards

Day 3 [optional]: Your Excel / Power BI Project

Day 3 is optional, however, it’s when most participants get the greatest benefit from this course so we highly recommend taking this extra day.

Bring your own data and apply your skills to work on your own, real-life project, with the guidance and coaching of our expert facilitator.

You’ll not only practise your newly learned skills, but you’ll also walk away with data models you can apply to your own data in the office tomorrow.

Who should attend?

  • Excel users & analysts that focus on extracting, re-organizing and analysing data
  • Excel users & analysts involved in creating visualisations & data modelling
  • Anyone interested in saving huge amounts of time in automating the work involved in creating recurring reports and dashboards

Related Courses

D5 Power Query in a Day (1 day)
The course covers the core elements needed to get an understanding of what Power Query can do and how to apply multiple techniques to convert messy data into clean tables for analysis.
D6 Power BI Essentials (2-3 days)
All the key aspects you need to know to begin developing Power BI reports for the business. Create stunning interactive reports and share amazing insights! Power BI gives Excel users the power to extract data from multiple sources, link it together, perform calculations and create powerful visualisations.
D7.1 DAX Booster for Power BI (1 day)
This intermediate to advanced hands-on course is focused on enhancing your understanding of the amazing capabilities of DAX. Get an understanding of the key elements of DAX language, with a sound understanding of key data modelling concepts.
D7.2 Power Query Booster (1 day)
This intermediate to advanced hands-on course is focused on enhancing your understanding of the amazing capabilities of Power Query and the ”M” language. Get a solid understanding of Power Query functionality, the “M” language, and how to handle more complex data transformations.

 

Facilitator: Jeff Robson

Masters (Applied Finance), B.Com (Accounting & Information Systems), FAIM, F Fin, GAICD, CISA, Excel Expert| LinkedIn | Jeff is the founding Director and Principal Business Analyst at Access Analytic Solutions. He has over 20 years’ experience in chartered accounting, business management and consulting. Jeff holds a Masters in Applied Finance and is a Certified Information Systems Auditor. He is highly regarded and recognised around the world as an expert in the field of financial modelling and Power BI reporting. His achievements have been recognised by his peers and he is a Fellow of both the Financial Services Institute of Australasia and the Australian Institute of Management. Jeff has extensive experience in best practice management reporting, data analytics, business analysis, and financial modelling and is also an entertaining international presenter and trainer on these subjects. Jeff is the author of a number of white papers on financial modelling on subjects such as Financial Modelling Best Practices and Financial Model Auditing. He provides training for companies throughout Australia, New Zealand, Asia, South Pacific, the Middle East, and Africa.

Facilitator: Wyn Hopkins

Excel MVP, B.Sc. (Hons) Accounting & Financial Management | LinkedIn | Wyn qualified as a Chartered Accountant with PricewaterhouseCoopers in the UK in 2000, before working as an analyst for the FTSE 100 companies HBOS and Barclays. Microsoft awarded him the status of  Most ValuableProfessional for the 5th year running in 2021. This recognises his expertise and willingness to share his knowledge via blogs, articles and community events Wyn is an experienced Trainer and Excel / Power BI Developer. Since joining Access Analytic in 2007, he has delivered hundreds of amazing solutions for clients including many for Chevron and other leading organisations in Perth.

Facilitator: Yury Tokarev

PhD, Chartered Accountant, Bachelor of Business (Finance and Economics), Microsoft Certified Excel Expert (MCEE) | LinkedIn | Yury is a Director and an International Training Specialist at Access Analytic Solutions. He has over 25 years of experience in corporate finance, chartered accounting, and consulting. Prior to his current role he worked in Business Services and Advisory at WHK Chartered Accountants and Corporate Finance at one of the top European Investment Banks. Yury has wealth of experience having delivered hundreds of Financial Modelling, Power BI and Excel solutions to clients ranging from start-ups to multi-billion corporations, including Chevron, Woodside, Northern Star Resources, Quadrant Energy, TransAlta and many more. Yury is also an international trainer on these subjects, and provides training for companies throughout Australia, Asia, the Middle East, South Pacific and South America.

Benefits & Inclusions

Practical: Highly experienced facilitators who actively and regularly consult to clients also.
Applicable: apply what you learn to your job to further your career.
Hands-on: the course is designed to provide maximum hands-on experience.
Passionate: Our facilitators love this subject!
Clear: we specialise in translating complex subjects into concepts that are easy to understand.
On-going help: you can always e-mail the facilitator.
Comprehensive course notes.
Networking: meet industry peers and hear how they approach this subject.
Certificate of completion, showing 7.5 hrs of Continuing Professional Development (CPD) per day.
… and much more!

Companies today are demanding increasing value and greater returns from their training budget.

The best way to achieve this is to run in-house courses where we come to your office and provide a customised course for your staff.

Benefits

  • More Relevant: we can customise our courses to suit your training needs and may even be able to incorporate some of your own data or models into the training to make it more relevant and useful for the participants
  • More Flexible: schedule the training when it suits you best.  You can even schedule the training days across multiple weeks to reduce the impact on workloads & schedules.
  • More Convenient: we come to your office so there’s no travel time or cost for your staff.  We are happy to travel to remote office locations, interstate or overseas.
  • More Affordable: in-house courses are up to 80% cheaper than sending the same number of people on a public course.  This lets you train more people for less!
  • No hidden extras: we provide a fixed fee up-front that includes all costs
  • Small or Large: in-house training is cost-effective for groups as small as 4 people.  You can even organise your own group if you wish by combining with other companies/colleagues!

Call us on +61 8 6210 8500 to discuss your requirements or enquire online.

Discount:
A 10% discount applies when booking 10+ full days of training in any 30 day period

 

Calendar

Power BI Essentials (2 days) Face-to-Face Training

ATI Mirage Level 1/863 Hay Street, Perth, WA, Australia

All the key aspects you need to know to begin developing Power BI reports for your business. Create stunning interactive reports and share amazing insights! Power BI gives Excel users the power to extract data from multiple sources, link it together, perform calculations and create powerful visualisations.

Get Tickets A$1,499.00 – A$1,699.00

Power BI Essentials (2 days) Face-to-Face Training

ATI Mirage Level 1/863 Hay Street, Perth, WA, Australia

All the key aspects you need to know to begin developing Power BI reports for your business. Create stunning interactive reports and share amazing insights! Power BI gives Excel users the power to extract data from multiple sources, link it together, perform calculations and create powerful visualisations.

Get Tickets A$1,499.00 – A$1,699.00

Power BI Essentials (2 days) Face-to-Face Training

ATI Mirage Level 1/863 Hay Street, Perth, WA, Australia

All the key aspects you need to know to begin developing Power BI reports for your business. Create stunning interactive reports and share amazing insights! Power BI gives Excel users the power to extract data from multiple sources, link it together, perform calculations and create powerful visualisations.

Get Tickets A$1,499.00 – A$1,699.00