SMS-113     Power Query and Power Pivot 

• Overview
• Course Objectives
• Course Content
• For Whom
• Schedule and Hours

<

>

If you are ready to take your Excel skills to new heights and game to change the way you use Excel for data analysis, this course is for you.  This course introduces Excel’s powerful data modelling and business intelligence tools: Power Query and Power Pivot.
 
Excel Power Query is one of the most powerful features and the easiest to learn & use within Microsoft Excel You can use Power Query to consolidate several files with the same format, clean & transform your data that you download from your company’s database system and display it in a report for Excel to work with. Power Query a great alternative to Excel VBA as it enables you to process your tables simply by clicking on buttons without coding, visualize the operations step by step without running a single macro and edit the steps order of different steps easily through dragging-and-dropping.
 
Excel Power Pivot is often associated with Power Query to ease data analysis and time related calculations. Power Pivot enables you to analyse databases of several million lines by extracting aggregated information according to the rules set by your business. It is simply the next step of data transformation, and it allows users to create data models from tables previously cleaned by Power Query.

This workshop aims to equip you with the skills to set Excel’s data modelling and business intelligence tools use Power Query and Power Pivot

• Use Power Query to clean the data by replacing values and filtering the data
• Use Power Query to reshape the data by removing all columns
• Use Power Query to Import Multiple CSV Files
• Use Power Query to merge 3 Tables into One in PowerPivot
• Use Power Pivot to create PivotTables and Pivot Charts
• Use Power Pivot to create DAX functions and KPI
• Use Power Pivot to import and create relationships between tables
• Use Power Pivot to create an interactive Dashboard

• Introduction to BIG Data Analysis
  
• Exploring Excel Power Query
  
• Exploring Excel Power Pivot 
• Creating Data Model, PivotTable and PivotChart
  
• Creating Calculations in Power Pivot
  
• Building a Dashboard with Power Pivot 
  

Microsoft Excel 2016 / 365 / 2019 users who do not have experience in using Power Query and Power Pivot.
 
Participants must be frequent users of PivotTable and proficient in PivotTable and PivotChart.
 
Users who need to work with more than 1,048,576 rows of data (maximum rows in Excel worksheet) and want to go beyond the limits.

This is a 2-days programme.
​
This course runs from 9:00am – 5:00pm with an hour for lunch and periodic short breaks.
 
This is an intensive session designed to include as much discussion as possible. Lunch and breaks provide an opportunity for further questioning and discussion with the instructors.

We are HRD Corp (Human Resource Development Corporation) Registered Training Provider.
​All of our training modules are claimable under HRD Corp.