Power Pivot & Power Query | IT Training & Certification | Info Trek
Respect Your Dreams
Follow through on your goals with courses

Power Pivot & Power Query


Format What’s this?
Starting From
RM 1000.00
  1. 2 Day with 08 hours per day
  1. Mon 03 Oct 09:00 - Tue 04 Oct 17:00
  1. HRDF SBL Claimable
  2. Certificate of Attendance available
Starting From
RM 1000
  1. 2 Days
Request more information

Inquiry for: Myself    My Company

By providing your contact details, you agree to our Privacy Policy




Thank You

Our learning consultant will get back to you in 1 business day

Power Pivot & Power Query


Power Pivot

• import data from multiple data sources into a single Excel workbook
• create relationships between heterogeneous data
• create calculated columns and measures using formulas

Power Query
• Import source from multiple data source
• Different methods to append (stack) data tables whether they are within the same file or spread across multiple source files
• A variety of transformation techniques and patterns to solve various data challenges

Power Pivot
• Using large sets of data and need to compile multiple source data
• Need to manipulate data using formulas
• Automate data manipulation for future data

Power Query
• To extract only useful data from large and detailed data sets
• Cleanup data
• Automate the process of reshaping for future data


• Sales Executive / Managers

• Business Analysist

• Resource Planning Team

• Project Managers

• Purchasing Manager

• Financial Executive / Managers


Basic knowledge of Windows is essential with the following pre-requisites:

• Able to create a spreadsheet with simple formatting

• Able to use Auto Filter command

• Able to use basic functions – AutoSum, Count, Max, Min and Average functions

• Able to switch between task applications


This program will be conducted with interactive lectures, PowerPoint presentation, discussion, and practical exercise.


Power Pivot

• EXCEL 365

• Office Professional 2019

• Office Home & Business 2019

• Office Home & Student 2019

• Office 2016 Professional Plus (available via volume licensing only)

• Office 2013 Professional Plus

• Excel 2013 standalone

• Excel 2016 standalone

Power Query

• EXCEL 2016, 2019, 365 and

• EXCEL 2010 & 2013 Free Add-in : Download Add-In

Expand All


Lesson 1: Starting with PowerPivot

• Add-ins Power Pivot from Options

• Launch the PowerPivot window
• A Tour to PowerPivot window
• Switching Normal mode and Advanced Mode
• Exploring the PowerPivot tab
• Changing between Data & Diagram view

Lesson 2: Adding data to PowerPivot

• Get Data from database

• Get data from Excel files

• Get data from Text or CSV files sources and types supported in PowerPivot workbooks

• Add Data by using Excel Linked Tables

• Load from Power Query

Lesson 3: Preparing data for analysis

• Working with Tables

• Managing Columns

• Set Data Type and Format

• Filtering and Sorting Data

Lesson 4: Relationships & Refresh

• Understanding Types of Relationships

• Create a Relationship Between Tables

• View and Edit Relationships

• Delete Relationships

Lesson 5: Manage Connection

• Refresh with latest data

• Editing connection

• Deleting connection

Lesson 6: Calculated Column

• Building Calculated Columns within a Table

• Building calculate Column across multiple table

Lesson 7: Measures

• Creating a New Measure

• Formatting a Measure

• Creating Measure across multiple Tables

• Useful DAX Functions









• Modify and Delete Measures

Lesson 8: Key Performance Indicators (KPI's)

• Create New KPI

• Manage KPI

Lesson 9: Date Table

• Mark as Date Table

• Create a Date Table

Day 2 Lesson 10: Getting data from multiple of sources

• Import data from database

• Import data from Text or Text Files

• Import data from folder

Lesson 11: Managing Query

• Duplicate Query

• Rename Query

• Switching Between Power Query Editor and Excel Screen

• Viewing Applied Steps in Query Settings pane

• Renaming and Deleting Applied Steps

Lesson 12: Performing Simple Operation

• Identify Column Header via Promote and Demote

• Removing Repeated Headers from Subsequent Files

• Removing Duplicate Records

• Changing Column Headers

• Changing Data Type

• Removing, Moving, Renaming Columns

• Reorganize data with Sorting

• Get relevant data with Filter

Lesson 13: Joining multiple tables

• Joining multiple tables into master table via Append

• Merging Queries on Single or Multiple Matching Columns

• Returning Referenced Details on Those Matched

• Returning Results on Those Not Matched

Lesson 14: Getting Results

• Close and Load data into Excel from Power Query

• Refresh query to get latest data from source

Lesson 15: Data Reshaping

• Unpivoting Columns

• Pivoting Columns

• Splitting Column into Multiple Columns

• Merging Columns

• Fill empty rows with data above and below

Lesson 16: Output Different Result

• Manage number column with some calculation

• Converting Text to Uppercase, Lowercase

• Extracting Part of Text

• Cleaning up non-printable characters

• Showing Date Time as Date or Time

• Combining Date Column and Time Column as One Column

• Format or Extract date based on date value day, month and year

• Summing Values based on Grouping

• Replace value

Lesson 17: Managing Additional Column

• Create Custom Columns

• Create Conditional Column

• Indexing Column

Lesson 18: Managing Data Source

• Data Source Settings

• Change Source Location

• Change to Different Source

Jarina Muhamad Ismail

Jarina Muhamad Ismail

Since 2005, Jarina is involved in delivering learning programmes for Microsoft Excel, PowerPoint, Project, Outlook, Access, Word, Publisher and Visio. As a Microsoft Office Specialist (MOS) in all versions (2003-2013), she is using her expertise to provide timely solutions to the end-user which ranges from basic to advanced level including power users. She has experience in training and coaching various teams of professionals like executives, engineers, managers and directors to organize, analyze, create report and present business data more efficiently.
Read More

Course Reviews

No Remarks


0 Ratings