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

Power Pivot & Power Query

Location

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

COURSE OVERVIEW

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

WHEN TO USE
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

AUDIENCE

• Sales Executive / Managers

• Business Analysist

• Resource Planning Team

• Project Managers

• Purchasing Manager

• Financial Executive / Managers


PREREQUISITES

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


METHODOLOGY

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

REQUIREMENT

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

Modules

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

o FILTER

o ALL

o RELATED

o TOTALYTD

o TOTALQTD

o TOTALMTD

o SAMEPERIODLASTYEAR

o CALCULATE

• 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

0 Ratings