INTRODUCTION

The Excel Power Query gets data from almost anywhere (web sites, databases, Excel files, SharePoint, Salesforce etc.) 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.

 

OBJECTIVES

At the end of this 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, analyse, and visualize data

 

COURSE CONTENT

SESSION 1 : INTRODUCTION TO POWER QUERY

• Excel 213 – Installing Power Query
• Excel 2016 – Get & Transform
• Create and Edit a simple query

SESSION 2 : COMMON DATA IMPORT SOURCES

• Working with CSV; Text; Excel Files
• Importing multiple files
• Working with Web data
• Scraping Data from Web Pages
• Calling a Web Service

SESSION 3 : WORKING WITH FOLDERS AND MULTIPLE FILES

• Using data from Windows File manager
• Combining Data from Multiple Files

SESSION 4 : WORKING WITH COLUMNS

• Name; Move; Split; Merge Filtering and Sorting
• Using Auto-Filter
• Using Number, Text and Data Filters
• Filtering Rows by Range
• Removing Duplicate Values
• Filtering out Rows with Errors
• Sorting
• Grouping rows

SESSION 5 : CHANGING VALUES IN A TABLE

• Replacing Values
• Transformations: – Text; Number; Date/Time
• Replacing Missing Values
• Table Transformations
• Un-pivoting Columns to Rows
• Transposing a Table
• Creating Custom Columns

SESSION 6 : LOADING DATA

• Loading Data into a Worksheet
• Loading Data into the Excel Data Model
• Power Query and Table Relationships
• Refreshing Queries Manual & Auto

SESSION 7 : QUERY EDITOR

• Edit Query Steps
• Edit Query Step Settings
• Refresh a Query

SESSION 8 : QUERIES

• Understanding Power Query’s language syntax
• Merging 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

METHODOLOGY

An adult learning approach using a combination of lectures and simulation, exercises, hands-on with close supervision by the trainers, presentation, management games & group discussions

 

WHO SHOULD ATTEND?

Experienced Excel users wishing to create complex reports, or needing advanced data analytics to process, analyze and display data and those who are intrested.

 

TRAINER

Shamsiyah Abd Kadir

Fakhrul Syahmi

 

FEES (WITHOUT ACCOMODATION)

RM1,100.00 per pax

Early Bird RM1,000.00 per pax

(20 days before program)

2 pax RM 1,050.00 per pax

3 pax > RM1,000.00 per pax

(This fees is inclusive of 2 morning tea breaks, 2 lunch , 2 afternoon tea breaks, notes and certificate of attendance for the participants)