Microsoft Excel Power Query by NTUC LearningHub

Introduction to Power Query

• Installing Power Query
• Creating a Simple Query
• Editing a Simple Query

Common Data Import Sources

• Working with CSV files
• Working with Text Files
• Working with Excel Files
• Working with other file types

Working with Data from the Web

• Scraping Data from Web Pages
• Calling a Web Service
• Finding Data Using a Power Query Online Search

Working with Folders and Multiple Files

• Working from Data from the Windows File System
• Combining Data from Multiple Files

Working with Columns

• Moving Columns
• Removing Columns
• Splitting Columns
• Merging Columns

Filtering and Sorting Rows

• Using AutoFilter
• Using Number, Text and Date Filters
• Filtering Rows by Range
• Removing Duplicate Values
• Filtering out Rows with Errors
• Sorting

Changing Values in a Table

• Replacing Values
• Text Transformations
• Number Transformations
• Date/Time Transformations
• Replacing Missing Values

Table Transformations

• Unpivoting Columns to Rows
• Transposing a Table
• Creating Custom Columns

Loading Data

• Loading Data into a Worksheet
• Loading Data into the Excel Data Model
• Power Query and Table Relationships
• Refreshing Queries Manually and Automatically