In this Microsoft Excel Power Query course you'll learn how Excel gets data from almost anywhere (web, databases, Excel files, SharePoint, Salesforce etc.) and enables you to tidy it up it in many ways (clean, transform, merge and append). Non-technical Excel users can analyse data and produce reports quickly using it’s GUI interface that’s easier to learn than formulas or VBA.
What will you learn?
At the end of this Microsoft Excel Power Query course, delegates will be able to:
- Create effective and professional reports
- Gather and transform data from multiple sources
- Discover and combine data in mashups
- Learn about data model creation
- Explore, analyze, and visualize data
Audience?
Anyone using Excel to clean and reformat data to produce reports.
Course Contents
Introduction
- What is it?
- Installing Power Query
- The interface
Common Data Import Sources
- Working with CSV; Text; Excel Files
- Importing multiple files
- Working with web data
- Scraping Data from web pages
- Call to a Web Service
Folders and Multiple Files
- Get data from Windows File manager
- Combining Data from Multiple Files
Working with Columns
- Name; Move; Split; Merge
- Add/Remove; Format; reorder
Filtering and Sorting
- Using Auto-Filter
- Using Number, Text and Date Filters
- Filtering Rows by Range
- Removing Duplicate Values
- Filtering out Rows with Errors
- Sorting
- Grouping rows
Changing Values in a Table
- Replacing Values
- Transformations: - Text; Number; Date/Time
- Replacing Missing Values
- Removing spaces, special characters
Table Transformations
- Unpivoting Columns to Rows
- Transposing a Table
- Creating Custom Columns
Loading Data
- Into a Worksheet
- Into the Excel Data Model
- Table Relationships
- Refreshing Queries Manual & Auto
Query Editor
- Edit Query Steps and Settings
- Refresh a Query
Queries
- Understanding Power Query’s M language syntax
- Merging tables and queries
- Using functions in columns
- Using IF statements
- Creating custom functions
- Implementing dynamic parameter tables
- Creating calendar tables
- Sharing queries
- Best practice
Course Materials
Enliten IT will provide each delegate with a workbook and other useful reference materials where applicable. These may be either paper based or in Adobe pdf format or a combination thereof.
“"Really great training session. Alan was a great trainer, really engaging and made the session easy to follow and understand. The fact it was remote based training as well made the fact that he was able to keep up all following along and engaged throughout even more impressive!"”
Ben, Software One, Power BI Introduction
Prerequisites
Delegates must have advanced knowledge, experience and understanding of PivotTables.
Additional Information
NOTE: We only offer this Microsoft Excel Power Query course course on a private in-house basis.
Enliten IT reserves the right to modify course content and materials as required in the interests of continuous course improvement, at any time, without notification.
The suggested course duration is a guideline. Course topics and duration may be modified by the instructor based upon the knowledge and skill level of the course participants.