img
iTrainingExpert Logo

Power Query & Power Pivot

Description

Power Query & Power Pivot


“Do we use Power Pivot or Power Query?” – I get this question all the time when teaching Power BI classes. The answer to the question, of course is – You use both! Use Power Query to get, clean, shape and transform your data
Use Power Pivot to model your huge data, define calculations and establish relationship between tables/ queries.
This 1 day training is an introductory course to Power Query and Power Pivot.
You are somewhat expected to have intermediate MS-Excel expertise and this includes working knowledge of the Pivot Table.
You will be introduced to the basics of the both the power tools, connecting to data sources, learning to cleanse and edit the data, reconnect broken relationships, remodel data and putting them into Excel worksheets for further analysis.

By the end of the Power Query & Power Pivot live session, you will be able to:
  • grasp the basics of the both the power query and power pivot tools
  • connect to data source
  • cleanse and edit the data
  • reconnect broken relationships
  • remodel data
  • put data them into Excel worksheets for further analysis 

Data miners, data analysts, project managers, revenue managers, finance managers, journalists & those who are performing roles related to Business Intelligence (BI) in Excel.
This includes all back-end employees performing reporting, analysis roles wishing to explore better tools in handling data.

This course is suited for participants who are regular users of Excel already familiar with intermediate Excel functions and those who are already familiar with the Pivot Table Required software: Excel 2016 or above. Required add-in to be set up in Excel: Power Query. Participants need to ensure Power Query is in operational condition before training commence Internet connection for participants required.

9.00AM - 10.30AM 
Module 1: Introduction

  • Big data components
  • Big data and small data
  • Sources of big data
  • Big data structure
  • Big data analysis
  • Tools and technology

10.30AM - 10.45AM Break

10.45AM - 1.00PM 
Module 2: An introduction to Table

  • Function of the Table
  • Creating the Table
  • Advance Filtering
  • Slicers

1.00PM - 2.00PM Lunch

2.00PM - 3.30PM 
Module 3: Excel Power Query

  • What is Power Query?
  • Connecting to data source & importing data
  • Creating a new query
  • Merging & appending queries
  • Automating consolidation of files
  • Creating an index
  • Changing data types, data format & replacing values
  • Merging columns
  • Unpivoting & pivoting columns
  • Replacing values in columns

3.30PM - 3.45PM Break

3.45PM - 5.00PM 
Module 4: Excel Power Pivot

  • Getting started with Power Pivot
  • Importing data into Power Pivot
  • Understanding Power Pivot & Relationships
  • Creating Relationships between Tables
  • KPIs
  • Hierarchies
  • Data Analysis Expressions (DAX) language- SUM, SUMX, CALCULATE, RELATED, &&, FILTER
  • Visualizing data from Power Pivot

Intensive hands-on approach on real-life case-study data. You will be using big data and conventional Excel datasets. Completion of dashboard projects are required to complete this course successfully

SETUP REQUIREMENTS
Required software: Excel 2016 Professional, Silverlight
Internet connection for participants required

VINCENT CHIN

Chartered Accountant, FCCA
Microsoft Certified Trainer
Certified Six Sigma Black Belt
HRD Corp Certified trainer

Vincent has held financial, continuous improvement and I.T. positions in the banking, shared services and hospitality industry. He is a FCCA, Certified Six Sigma Black Belt, Microsoft Certified Trainer, Microsoft Office Specialist and HRD Corp accredited trainer. His previous roles include being an internal change consultant of a global team, a financial shared-services local head and a finance regional head. He currently trains professionals in Excel and Lean Six Sigma covering topics related to finance, business intelligence tools, pivot tables, dashboards, VBA and process improvement. 

Vincent brings to you his 20 years’ of work and training experience in multiple industries where he was in charge of financial and business reengineering functions. He also develops complex VBA applications regularly in process improvement projects which requires his expertise.

One of his highest achievements in the role of a Financial Controller for several multinational conglomerates, he was being able to turn a loss making company into a profitable one within the year. Vincent has also managed a portfolio of business improvement projects with realized benefits of USD2 million in the prior year in an international bank.

Vincent has trained employees from Genting Group, Dell, Motorola, Technip, CIMB, Malakoff, Digi, IBM, Starwood Hotels & Resorts, Petronas ICT, HSBC.

Vincent adapts a highly practical approach in his training and relates topics he conducts to real life scenarios. Because he is a no-nonsense person, his solid background in financial operations and process re-engineering experience aid him to create a unique training methodology that mimic closely the solutions that are needed in his participants’ organizations. 

OR 

In the event of substitution, we have a faculty of equally talented specialists who can cater to your organization's specific needs.

To contact Vincent , please call +6012 6869 628, +603 8074 9056, +603 -8082 3707 or info@itrainingexpert.com  

For training and advisory services, feel free to contact us at info@itrainingexpert.com or call us at +603 8082 3707 | +603 8074 9056 | +6012 6869 628 | +6018 2175 123

Vincent is an excellent trainer with excellent knowledge in the subject.”

Housekeeping Director, Westin Langkawi. Before this I never understood what Six Sigma was. Vincent crystalised it and cla so well rified it for me” - Manager, HSBC.

I never knew Excel could do so much in forecasting.

Normal Fee Early Bird Group Fee
Sign up 1 pax Sign up 1 pax Sign up 3 pax or more
Pay 1 day(s) before course starts Pay 14 day(s) before course starts Pay 14 day(s) before course starts
MYR 2990 per pax MYR 2600 per pax MYR 2500 per pax
USD 890 per pax USD 760 per pax USD 730 per pax

Upon successful completion of this program, you will receive Certificate of Achievement.

1. Online Payment by Credit card: You can opt to register and pay online with our latest payment integration system through our website.
2. Bank Transfer- You can also opt to use GIRO or telegraphic transfer of payment via international banks.
3. HRD Corp Claimable Courses Skim Bantuan Latihan Khas- Applicable to MalaysianEmployer Only



We'd happy to help.
Feel free to talk to us today, however most suitable for you.
Mobile call/ whatsapp: +6012 6869 628 | +6018 2175 123
Office: +603 8074 9056
Email: info@itrainingexpert.com
Website: www.itrainingexpert.com

USD 890

Available Sessions

Session Location Register
28 - 29 Nov 2024 SELANGOR, Malaysia Register
14 - 15 Jan 2025 SELANGOR, Malaysia Register
22 - 23 May 2025 SELANGOR, Malaysia Register
07 - 08 Oct 2025 SELANGOR, Malaysia Register
27 - 28 Nov 2025 SELANGOR, Malaysia Register

Share to your friends