La Courses

Log In

MIM1008 Microsoft Excel PowerQuery & PivotTable (2 Days)

Description

MIM1008 Microsoft Excel PowerQuery & PivotTable (2 Days)

Overview

Power Query is a technology that allows you to find, link, merge and optimize your data sources for analysis. Power Query will be able to import, clean and evaluate millions of rows in the data model. Compared with other Excel tools it is an incredibly short learning curve. You set up a query once and use it again with a simple refresh. Power query helps to prepare your data for a pivot table report. A PivotTable is a powerful tool to calculate, summarize, and analyze data that lets you see comparisons, patterns, and trends in your data.

  • 98% positive reviews (12)
  • 278 students

This program can be tailored to your specific business needs.

A little bit of personalization goes a long way. Ask us for a no-obligation Training Needs Analysis (TNA) so we can tailor this, or any other CSG program to meet your learning outcomes.

Contact Us
Objectives

At the end of the training, participants would be able to:

  • Understand Excel Data thoroughly & prevent common mistake in Excel Reports 
  • Manage & Analyze Database/Excel List effectively 
  • Ability to connect data from another source/file 
  • Introduction to Macro Recording to automate repetitive task 
  • Manage worksheet & file Protection 

2 Days

  • Office professionals working with Microsoft Excel 
  • Data analysts and business intelligence professionals 
  • Financial analysts and accountants 
  • Project managers and team leads 
  • Individuals seeking to enhance their advanced Excel skills 
  • Professionals using Microsoft Excel for complex data analysis and reporting tasks

Modules:

Introduction to Power Query
  • Exploring Power Query User Interface 
  • 2013 Power Query Tab 
  • Excel 2016 Get & Transform Group 
  • Power Query Basics 
  • The Query Editor 
  • Understanding Query steps 
  • Refreshing Power Query data 
  • Overview of Query Actions 
  • Understanding Data Destinations 
  • Close & Load 
  • Close & Load To… 
  • Power Query Data Sources 
  • Data Sources Overview 
  • Power Query Data Sources 
  • Get Data from 
  • CSV and Text Files 
  • Current Excel worksheet 
  • Excel Workbooks 
  • Folder 
  • Database 
  • Transform data Overview 
  • Working with Columns 
  • Creating Custom Columns 
  • Pivot Column 
  • Unpivot columns
  • Filtering Rows 
  • Filter a column using Text Filters 
  • Filter a column using Number or Date/Time Filters 
  • Filter a column by Row Position 
  • Keep Top Rows 
  • Keep Top 100 Rows 
  • Keep Range of Rows 
  • Remove Top Rows 
  • Remove Alternate Rows 
  • Removing Duplicate Records 
  • Remove rows with errors
  • Changing Values 
  • Replacing Values 
  • Replace text values 
  • Replace number, Date/Time. or logical values 
  • Transposing a Table 
  • Grouping and Aggregate Rows 
  • Group Single Column 
  • Group Multiple Columns
  • Append Queries 
  • Perform an Append Operation 
  • Merge Queries
  • Get to know a PivotTable 
  • The Best Practice using a database for PivotTable 
  • Creating Pivot Table 
  • Database Pre-requisite for preparing a PivotTable Report 
  • Designing a PivotTable 
  • Adding Elements to the Report 
  • Creating a Report Filter 
  • Use Table Field as Report Filter 
  • Use the Report Filter 
  • Reset the Filter 
  • Use Slicers to Filter Report 
  • What is Slicers? 
  • Remove Slicer 
  • Update the Data Source 
  • Changes Data Sources 
  • Create a dynamic Range for the Data Table 
  • Format a PivotTable 
  • Use PivotTable Style 
  • Number and Text Format 
  • Explore the PivotTable Options 
  • Use the Value Field Settings 
  • Subtotals 
  • Show Value As 
  • PivotTable Print Options 
  • Grand Totals 
  • Report Layout 
  • Grouping, Sorting and Filtering 
  • Grouping Pivot Fields 
  • Dates 
  • Number Fields 
  • Text
  • Ungrouping 
  • Sort& Filtering the PivotTable Use “Fields, Item and Sets” 
  • Creating Calculated Field 
  • Creating Calculated Item 
  • Edit and Delete Calculated Field or Item
  • Convert PivotTable to PivotChart 
  • PivotTable Wizards 
  • Multiple Consolidation Ranges

Program Methodology

  • Hands-on Activities: Practical exercises to reinforce theoretical concepts.
  • Group Discussions: Opportunities for peer-to-peer learning and exchange of ideas.
  • Role Plays: Simulations of realistic situations to build practical skills.
  • Feedback Sessions: Reviews and reflections to encourage improvement.
  • Problem-solving Exercises: Develop critical thinking and decision-making skills.
  • Experiential Learning: Learning by doing, promoting active involvement.
  • Interactive Lectures: Engaging presentations by experts in the field.
  • Case Studies: Real-world scenarios for learners to apply their knowledge.
  • Quizzes & Tests: Regular assessments to track learning progress.

Choose a Training Partner that Stays Invested.

CSG is here for you before during and post trainings. Contact Us for customized training solutions or Explore our Programs.

Read our FAQs

Tell Us What You Need, We’ll Make it Happen

Welcome Back

Sign In

New here? Create an account.