D5: Power Query in a Day (1 day)

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 is Excel’s best feature and it’s also key to the popularity of Power BI. It gives Excel and Power BI analysts 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.

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.

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

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

 

What you get from this course

• Be able to import data from a variety of disorganised layouts to quickly deliver key information in minutes not weeks
• Utilise best practice techniques
• Provides a solid framework and rules to allow you to automate your reporting and analysis immediately on returning to work.

Pre-requisites

This is a beginner’s course and assumes attendees have no prior knowledge of Power Query.

Previous use of Excel, VLOOKUP / XLOOKUP / Pivot Tables or Power BI would be considered a sensible base level of experience to get the most from this course.

The exercises will be conducted using the desktop version of Excel 365 or Power BI desktop. The techniques are the same in both with minor User Interface differences.

Software Required

Excel 365 or Power BI Desktop

Download and install the free Microsoft Power BI Desktop

For Excel 365 ensure you have a build version greater than 2203  (you can check via File > Account > the About Excel section contains the Version  (2203 means March of 2022)

Windows only (No Mac)

Agenda Highlights

Power Query Introduction

• General Overview
• Column from Examples
• Consolidating Files from a Folder
• Unpivoting
• Data Consolidation using Append
• Merging and 3 Join types including Left Outer, Left Anti, Inner
• Adding Last Refresh Date

Intermediate Techniques

• A more complicated data source layout
• Index Columns & Lists
• Handling Errors and Nulls
• Query Folding
• Custom and Conditional Columns
• Consolidating multiple files from OneDrive / SharePoint
• Referencing the next / previous row
• Transpose, Unpivot, Pivot
• Recap exercises

Who should attend?

• Excel users & analysts that focus on extracting, re-organizing and analysing data
• Power BI users connecting to data sources that are poorly organised
• Anyone interested in saving huge amounts of time in automating the work involved in creating recurring reports and dashboards

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 Valuable Professional for the 7th year running in 2023. This recognises his expertise and willingness to share his knowledge via blogs, articles, community events and YouTube videos. The Access Analytic YouTube channel now has over 85, 000 subscribers. 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

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