Mastering Microsoft Power Pivot & Power Query
Mastering Microsoft Power Pivot & Power Query 2019 Public Program in Malaysia Selangor, Malaysia, Kuala Lumpur (KL), Shah Alam Training, Workshop | Iconic Training Solutions Sdn Bhd

Mastering Microsoft Power Pivot & Power Query

Duration: 3 Days
Date: 25-27 November 2019
Time: 9am-5pm
Venue: SVOFO, Sunway Nexis Kota Damansara

Best Things That Power Pivot Brings to Excel

Unlike the traditional pivot table and Excel sheets, the user is limited to analyze data within a million row of data at a time, per table at a time (it is extremely slow). With the capability of Power Pivot, add-ins of Business Intelligent engine to Excel, it has since supercharged your excel into owning superpower to analyst more than a million rows of data (safely 100 million rows). Creating reports by connection to a relational data source(Multiple tables with relationship) created within excel using Power Pivot easily. With the fast performing processing engine, DAX(Data Analysis Expression) the functions used in Power Pivot allow users to customize any calculation whether to add in the calculated column or measure faster than ever.
Power Query is a very powerful tool that can help you find and connect to data from a wide variety of sources, from physical files to relational databases and even from the web. Perform and automate data cleansing and reshaping without having to do the same tedious work repeatedly. 

Course Outline:

Module 1 – Things you should know before getting into reporting

  • What to do when a big data bomb drops onto your hand?
  • ​How Excel behave when it comes to keeping too many unwanted, inconsistent and untidy data?

Module 2 – How to Build your first report with Pivot Table

  • 3 Steps in creating a report
  • Facelift the report
  • Organizing and sort out useful info
  • Using Pivot Table Value outside pivot table
  • Slicing and dicing data using Slicer and Timeline

Module 3 – Visualizing reports using Pivot Chart

  • Displaying the trends and comparison of data with Pivot Chart
  • Formatting Pivot Charts
  • How to use Pivot Chart to make the decisions easily

Module 4 – What is Power Pivot?

  • Difference in between Power Pivot and Pivot Table
  • Why should you use Power Pivot while analysing data.

Module 5 – Where should you begins with Power Pivot?

  • Import data from various sources
  • Why need to create relational database?
  • Why Power Pivot Better then Vlookup?

Module 6 – Adding calculated column using existing information from tables

  • How to use DAX function as faster calculation 
  • Define hierarchy
  • Differences in between Calculate Column and Measure

Module 7 – Say NO more to reporting routine

  • How to Create Reports by connecting to External Data Source
  • How to Create a report from multiple tables
  • How to Visualize Report
  • View dashboard base on latest updated data and by one click

Module 8 – What is Power Query?

  • How can it help to automate their tasks
  • Installing and enabling Power Query
  • Familiarize with Power Query Interface

Module 9 – How to loading and connect to different external data source?

  • Connect and load data from the internet
  • Getting data from Excel file
  • Extracting data from text files (.txt, .csv, etc)

Module 10 – Consolidating and merging all sources

  • How to Merging queries
  • How to Connecting all the files from the same folder
  • NO more traditional way of copy, cut and paste to gather all the sources into one place

Module 11 – Transforming and reshaping data

  • Editing Queries
  • Fixing data problem
  • Extracting date component with date time intelligent capability in Power Query

Module 12 – Loading and refreshing cleansed data

  • Loading transformed data into workbook
  • Load queries as connection only

Register online here -->>

Please leave your enquiry here, we will reply as soon as possible.

Switch To Desktop Version