Microsoft Excel : PowerQuery & PivotTable

26-27 Jul, 2021, Virtual Classroom Training

Register Now
PDF Brochure

IPA Training is Registered with

Learn from the Best

Venue Details

Virtual Classroom Training

Contact us

Juliany,
03 2283 6109
juliany@ipa.com.my

Phoebe,
03 2283 6100
phoebe@ipa.com.my 

FOR CUSTOMISED IN-HOUSE TRAINING
Jane,
03 2283 6101
Jane@ipa.com.my

ADDRESS 
A-28-5, 28th Floor, Menara UOA Bangsar, 
No.5, Jalan Bangsar Utama 1, 
59000 Kuala Lumpur
www.ipa.com.my

FOCUSING ON
  • Getting Started with Power Query
  • Transform Columns and Rows Part 1 – Part 1 & Part 2
  • Explore Queries
  • Getting Started with Pivot Table
  • Quick Summary using Pivot Table
  • Advance Analysis with Pivot Table
  • Building Interactive Chart & Others
COURSE OVERVIEW
Power Query is a technology that allows you to find, link, merge and optimize your data sources for analysis. Power Query will be able to import, clean and evaluate millions of rows in the data model. Compared with other Excel tools it is an incredibly short learning curve. You set up a query once and use it again with a simple refresh. Power query helps to prepare your data for a pivot table report. A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.
AFTER ATTENDING THIS COURSE YOU WILL RETURN TO YOUR JOB…
  1. Understanding Excel Data thoroughly & Preventing common mistake in Excel Reports.
  2. Managing & Analyzing Database/ Excel List effectively.
  3. Being able to connect data from another source/file.
  4. Introduction to Macro Recording to automate repetitive task.
  5. Managing worksheet & file Protection.
WHO SHOULD ATTEND
Personnel who use Microsoft Excel to analyse and prepare management reports, especially Administrative, Operational and Middle Management Staff.
METHODOLOGY
  • Guided Learning, group-paced, hands-on classroom training with real life examples for business applications.
  • Challenging exercises are set based on practical applications for typical organizational functions such as HR, Admin, Production, Sales etc.
DAY 1
9:00

INTRODUCTION TO POWER QUERY

  • Exploring Power Query User Interface
    - 2013 Power Query Tab
    - Excel 2016 Get & Transform Group
    - Power Query Basics
    - The Query Editor
  • Understanding Query steps
  • Refreshing Power Query data
  • Managing Existing Queries
  • Overview of Query Actions
  • Understanding Data Destinations
    - Close & Load
    - Close & Load To…
 

POWER QUERY DATA SOURCES

  • Data Sources Overview
    - Power Query Data Sources
  • Get Data from
    - CSV and Text Files
    - Current Excel worksheet
    - Excel Workbooks
    - Folder
    - Database
  • Reusing Recent Data Sources
10.45

TRANSFORM DATA – COLUMNS

  • Transform data Overview
  • Working with Columns
  • Creating Custom Columns
  • Pivot Column
  • Unpivot columns
  TRANSFORM DATA – ROWS
  • Filtering Rows
  • Filter a column using Text Filters
  • Filter a column using Number or Date/Time Filters
  • Filter a column by Row Position
    - Keep Top Rows
    - Keep Top 100 Rows
    - Keep Range of Rows
    - Remove Top Rows
    - Remove Alternate Rows
2:00

TRANSFORM DATA – ROWS

  • Removing Duplicate Records
  • Remove rows with errors
  • Sort a Table
  • Changing Values
    - Replacing Values
    - Replace text values
    - Replace number, Date/Time. or logical values
  • Transposing a Table
  • Grouping and Aggregate Rows
    - Group Single Column
    - Group Multiple Columns
3:45

ADVANCED POWERPOINT DRAWING TOOLS

  • Append Queries
    - Perform an Append Operation
  • Merge Queries
5:00 End of Day 1
DAY 2
9:00

PIVOTTABLE

  • Get to know a PivotTable
  • The Best Practice using a database for PivotTable
  • CREATING PIVOT TABLE
  • Database Pre-requisite for preparing a PivotTable Report
  • Designing a PivotTable
  • Adding Elements to the Report
  • Creating a Report Filter
    - Use Table Field as Report Filter
    - Use the Report Filter
    - Reset the Filter
  • Use Slicers to Filter Report
    - What is Slicers?
    - Remove Slicer
  • Update the Data Source
    - Changes Data Sources
    - Create a dynamic Range for the Data Table


10.45

CUSTOMIZING A PIVOTTABLE

  • Format a PivotTable
    - Use PivotTable Style
    - Number and Text Format
    - Explore the PivotTable Options
    - Use the Value Field Settings
  • Subtotals
  • Show Value As
  • PivotTable Print Options
  • Grand Totals
  • Report Layout
1:00 Lunch
2:00

CONVERT DATA TO INFOGRAPHICS

  • Grouping Pivot Fields
    - Dates
    - Number Fields
    - Text
  • Ungrouping
  • Sort& Filtering the PivotTable
 

USE “FIELDS, ITEM AND SETS”

  • Creating Calculated Field
  • Creating Calculated Item
  • Edit and Delete Calculated Field or Item
3:45

PIVOT CHARTS

  • Convert PivotTable to PivotChart
    PivotTable Wizards
  • Multiple Consolidation Ranges
5:00 End of Course