Microsoft Excel Power Query
Subject
In this Power Query course, you will learn a process of importing data, appending and merging tables, conditional logic, data transformation, and organization. The course starts with the basics of Power Query and works its way up. This means that you'll be a pro in no time, even if you're a beginner with the tool.The Excel Power Query gets data from e.g databases, Excel files, and enables you to manipulate it in many ways (clean, transform, merge and append) using a straightforward interface add-in to Microsoft Excel. Non-technical Excel users can access large corporate databases to conduct analysis and produce reports.
Course Objectives
Create effective and professional reportsGather and transform data from multiple sources
Discover and combine data in mashups
Learn about data model creation
Explore, analyse, and visualize data
Duration
1 DayPre-requisites
Excel AdvancedTarget Audience
Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyse and display data.Course Content
Introduction to Power Query
Installing Power Query Create and Edit a simple queryCommon Data Import Sources
Working with CSV; Text; Excel FilesImporting multiple files
Working with Web data
Scraping Data from Web Pages
Calling a Web Service
Finding Data using a Power Query Online Search
Working with Folders and Multiple Files
Using data from Windows File managerCombining Data from Multiple Files
Working with Columns
Name; Move; Split; MergeFiltering and Sorting
Using Auto-FilterUsing 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 ValuesTransformations: - Text; Number; Date/Time
Replacing Missing Values
Table Transformations
Un-pivoting Columns to RowsTransposing a Table
Creating Custom Columns
Loading Data
Loading Data into a WorksheetLoading Data into the Excel Data Model
Power Query and Table Relationships
Refreshing Queries Manual & Auto
Query Editor
Edit Query StepsEdit Query Step Settings
Refresh a Query
Queries
Understanding Power Query's language syntaxMerging tables and queries
Using Power Query functions in columns
Using conditional (IF) statements
Creating custom Power Query functions
Implementing dynamic parameter tables
Creating calendar tables
Sharing queries
Best practices for Query organization
...back to ITC Training