Mastering Microsoft Power Pivot And Power Query

Introduction

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.

Prerequisites

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

• Create basic formulas - Addition, Subraction, Multiplication and Division
• Use basic functions - AutoSum, Count, Max, Min and Average functions
• Print a spreadsheet with headers and footers added
• Create a basic chart
• Create a spreadsheet with simple formatting
• Clean duplicate records
• Use data validation command
• Use basic conditional formatting
• Create basic PivotTable and PivotCharts
• Must possess formulas knowledge - basic COUNTIF, SUMIF, IF and VLOOKUP

Objective

Upon completion of this program, participants should be able to know:

• Creating an impressive dashboard
• PivotTable and PivotCharts
• useful formulas
• Conditional Formatting
• Form Controls
• Working with Charts
• Working with Sparklines
• Design and Layout of Dashboard

Key Content

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

Who Should Attend?

This course is best suited to anyone in Business Intelligence, data managers, data analysts or project managers. This course would also suit anyone looking to extend their knowledge of Excel to understand some of the more advanced features and how they can be used to work together.

Methodology

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