Power Query Pro
Get your data from chaos to clean in seconds.
Transform data into powerful insights without the manual cleaning
You have data. Lots of it. But it’s messy, scattered, and hard to control.
Instead of analyzing and obtaining insight, you waste hours cleaning columns and reformatting rows. If you’re like most Excel users, you spend more time preparing the data, than actually using it.
Excel is powerful, it shouldn’t be this frustrating.
This is where Power Query comes in, and where Power Query Pro is the course you need to master it.
There’s a better way to work with data.
Power Query is the tool all Excel users need, but very few know it even exists.
Power Query is a powerful data transformation tool built directly into Excel. It allows us to connect, clean, and shape data from virtually any source.
Power Query can:
- Unpivot and reshape data into the perfect shape for calculation
- Refresh reports for new data with 1-click
- Save hours by replacing manual copy and paste processes
But only if you know how to use it properly.
In the Power Query Pro course, we will show you how to use Power Query to turn your data from chaos to clean in seconds.
No matter your current experience level, whether you’re a business analyst, financial professional, or data enthusiast, this course will transform how you work with Excel.
Power Query Pro is only available within the Excel Academy.
What’s included?
✓ 7 hours of on-demand video (90+ lessons).
✓ Real-world scenarios to work through
✓ Certificate of completion
✓ English closed captions
✓ Ask questions and get expert answers
Syllabus:
Introduction
- Introduction
- Why Power Query
- Example Scenarios
- Learning Journey
- Updates and Versions
Getting started with Power Query
- Getting started with Power Query
- Power Query interface
- Applying transformations
- Load into an Excel Table
- Editing a query
- Query refresh
- Basic transformations
- Data insights
- How to stop auto-applied steps
- Default query load settings
- Renaming steps
- Sharing queries between workbooks
- Progress summary
Working with single source inputs
- Working with delimited files
- Working with fixed width text files
- Working with Excel worksheets
- Working with Tables (external)
- Working with Named Ranges (external)
- Working with Tables & Named Ranges (internal)
- Working with OneDrive and SharePoint
- Working with Web sources
- Working with PDFs
- Working with databases
- Progress recap
Working with multiple source inputs
- Introduction to using multiple sources and queries
- Loading multiple sources into Power Query
- Combining multiple Tables from a workbook
- Data refresh from combining multiple Tables from a workbook
- Combining multiple sheets from a workbook
- Combining multiple sheets with unhelpful naming
- Combining files in a folder
- Understanding the combine files process
- Refreshing combined files
- Future-proofing for incorrect file types
- Restoring file information on combine
- Working with files and folders
- Alternative method to combine files in a folder
- Append queries
- Merge queries
- Understanding the 6 types of merge
- Duplicate and reference queries
- Query dependency view
- Query organisation
- Progress recap
Common transformations
- Transformations – Introduction
- Unpivot & Conditional columns
- Transpose
- Unstacking columns
- Merge columns and separate with a line feed
- Split a delimited list into rows
- Multiple header rows – Method 1
- Multiple header rows – Method 2
- Multiple header rows – Merged cells
- Dealing with changing column headers
- Pivot and unpivot
- Group By
- If statement
- Fuzzy matching
- Column from example
- Percent of total
- Referencing values from other rows in a query
- Referencing a query from another query
- Transformation – Review
- Progress recap
Loading, refresh, and parameters
- Loading, refreshing, and parameters – Introduction
- Pointing Power Query to a new source
- Dynamic parameters to change data sources
- Dynamic parameters to change query steps
- Using merge instead of parameters
- Refresh options
- Reviewing the data load options
- Advanced transformations – Introduction
Advanced transformations
- Advanced Transformations – Introduction
- Introduction to formulas
- Editing formulas in the formula bar
- Nesting functions
- Custom Functions – Part 1
- Custom Functions – Part 2
- Custom Functions – Part 3
- Query organisation – single vs cascading
- Introduction to privacy settings
- Formula Firewall – Part 1
- Formula Firewall – Part 2
- Date & time of last refresh
- Filter All in Power Query
Wrap up
- Wrap-up
- Feedback
- Certificate
Get the time-saving benefits of Power Query today.
Join the Excel Academy
Enrol today to get instant access to the Power Query Pro course.
Follow the course
Discover how to transform your data into the perfect shape for calculation.
Apply the learning
Apply the skills and start saving time today.
Power Query makes all the difference
Without Power Query:
✖ Waste time copy-pasting from different workbooks
✖ Risk of errors increases
✖ Repeating the same time-consuming tasks
With Power Query:
✓ Combine data from multiple sources
✓ Refresh reports with 1-click
✓ Eliminates manual work
Power Query will change the way you work with Excel forever.
You’ve been using Excel the hard way. It’s time to let Excel do the heavy lifting.
Build the skills that make Excel faster and smarter.
Power Query Pro is only available within the Excel Academy.