Excel Power Query

This course is designed to give students the ability to use Power Query in Excel to automate the process of getting, cleaning, and transforming data from various sources, saving significant time and effort compared to manual methods. It allows users to connect to different data locations, reshape the data by removing columns or changing data types, and then refresh the data automatically when the source changes, all without needing to code.

Analysts, managers, data scientists and anyone using Microsoft Excel regularly work with data and need to clean, combine, or analyse information efficiently. If you want to save time, this is the course for you.

We recommend that students have knowledge equivalent to the topics covered in our Excel Timesaving Features and Excel Data Analysis and Reporting courses, OR:

• Be comfortable writing and working with intermediate to advanced Excel formulas.
• Demonstrate how to write a successful VLOOKUP.
• Have the desire to work with data from multiple tables or sources (might currently be using VLOOKUP, copy paste or other method to combine data in Excel).
• Have used or created a PivotChart or PivotTable.
• Basic understanding of Power Query

You will see Excel in a brand-new light. The Excel Power Tools (Power Query) make transforming data in Excel a breeze. Freeing you up to focus your time on what matters most.

After completing this course, students will be able to: 

• Pre-format and import a .CSV file.
• Import Excel data from worksheets in separate workbooks in a folder.
• Import data from a report.
• Import data from a multiple sources
  

Module 1: Importing Data from Files (Introduction to Power Query) Get & Transform Data with Power Query.

In this module we will see how you can easily import a single CSV file or even an entire folder of files (imagine weekly exports from your financial system) directly into Excel.

Module 2: Importing Data from Excel Reports (Transform Data with Power Query)

Learn how to navigate Power Query Settings and Applied Steps to split columns, fill and copy data down to the next value, unpivot data, and more. Save and apply the from Power Query and next time you need this report updated, you’ll only need to click the “Refresh” button.

Module 3: Importing Data from multiple sources (Introduction to Relationships in Excel)

Import data from a number of worksheets from a file, or from CSV files or even the web and create relationships in Excel. We will also touch on the importance of having a Date table in your data model and explore hierarchies (such as Year, Quarter, Month, Day).