Full program description

Microsoft Excel Power Query

What's In It For Me

  • Are you working with Microsoft Excel in your daily job routine which require creating report from multiple spreadsheet files? 
  • Do you need to perform data preparation such as data cleansing and data preparation to create a report? 
  • Then the new feature, Power Query in Microsoft Excel is the tool that you should familiarize yourself.

Course Overview

This course is scheduled for launch in Q2 2018 - Q3 2018. To express your interest in this course, please fill up our form at https://www.surveymonkey.com/r/6MBPSFV. We will contact you when we get closer to course launch date.

This course will help learner to understand the importance of managing the quality of the data that they are working on. And how data can be cleaned and transformed to remove noise. Learner will also work with relationship to link multiple datasets together for use later to create reports using PivotTable or any visualization tools.

Who Should Attend

Candidates who work with Microsoft Excel to create business reports.

This includes but not limited to

  1. Administrative Support Executive
  2. Managers

Course Duration

1 Day / 7 Hours

Course Outline

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

Certification Obtained and Conferred by

Awarded NTUC LearningHub Certificate of Completion

Course Objectives

  • Load data into Microsoft Power Query
  • Consolidate or merge multiple data files into a single master data file
  • Prepare the data for analysis using Pivot Table
  • Clean and Transform data

Pre-requisites

  • Learner should have some knowledge of creating reports using Microsoft Excel
  • Learner should have experience writing formulas using lookup functions
  • Understand what is macros

Medium of Instruction & Trainer

Medium of Instruction: English

Trainer: Trainee ratio is 1:20

Price