La Courses

Log In

MIM1007 Microsoft Excel Data Analytics with Power Query & Power Pivot (2 Days)

Description

MIM1007 Microsoft Excel Data Analytics with Power Query & Power Pivot (2 Days)

Overview

Power Query (Get & Transform) and Power Pivot complement each other. Power Query is the recommended experience for importing data. With Power Query, you automate the process of importing, transforming and cleansing your data to save a TON of time with your job. For example, remove a column, change a data type or merge tables, in ways that meet your needs. Then, you can load your query into Excel to create charts and reports. Periodically, you can refresh the data to make it up to date. Power Query is available on three Excel applications, Excel for Windows, Excel for MAC and Excel for the Web.

Power Pivot is great for modelling the data you’ve imported. With Power Pivot, you can import millions of records from multiple data sources into a single Excel workbook, create calculated columns using Data Analysis Expressions (DAX) functions, create Data Model, create Key Performance Indicator (KPIs) to track performance against targets and create calculated measures that aggregate data from different rows on a PivotTable. 

Use both to shape your data in Excel so you can explore and visualize it in PivotTables and Pivot Charts. In short, with Power Query you get your data into Excel, either in worksheets or the Excel Data Model. With Power Pivot, you add richness to that Data Model. 

  • 93% positive reviews (27)
  • 209 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:

  • Find and connect to data from a wide variety of sources including performing online searches 
  • Learn how to cleanse data quickly and automatically without the need for tedious task work. 
  • Merge and shape data sources to match your data analysis requirements or prepare it for further analysis and modelling 
  • Learn how to create and modify table relationships, connecting multiple data tables 
  • Understand the differences between one-to-one, one-to-many, and many-to-many relationships 
  • Understand how Power Pivot builds on the functionality in Excel’s native tools, such as PivotTables, Slicers and key multiple criteria functions 
  • Learn how to write powerful formulae in Power Pivot’s Data Analysis Expressions (DAX) language 

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:

MODULE 1: POWER QUERY AKA GET TRANSFORM 
  • Introduction to Power Query  
  • Meet Power Query aka Get Transform 
  • Data Sources Supported by Power Query  
  • Understanding the Navigator Pane 
  • Query Editor Tabs 
  • Data Loading Options 
  • Creating a New Query from a Text File 
  • Creating a New Query from a CVS File 
  • Creating a New Query from an Excel workbook 
  • Creating a New Query from an Excel Table/Range 
  • Getting data from XML files 
  • Creating a New Query from the Web 
  • Creating a New Query from Access Database 
  • Connect a New Query to a folder of files 
  • Combining Data from Multiple Text Files 
  • Defining data types and errors  
  • Setting the Data Type of a Column 
  • Changing Data Types and Locales 
  • Naming Columns 
  • Moving Columns 
  • Removing Columns 
  • Splitting Columns 
  • Merging Columns 
  • Filtering Rows Using Auto-Filter 
  • Filtering Rows Using Number, Text, and Date Filters 
  • Filtering Rows by Range 
  • Removing Duplicate Values/Records 
  • Filtering Out Rows with Errors 
  • Sorting a Query 
  • Replacing Values with Other Values 
  • Filling in blank fields 
  • Concatenating columns  
  • Changing case 
  • Trimming and cleaning text 
  • Extracting the left, right, and middle values 
  • Number-specific query editing tools 
  • Date-specific query editing tools 
  • Add index and conditional columns 
  • Inserting Calculated Columns 
  • Custom Columns with M Calculations 
  • Inserting Custom Date and Time Columns 
  • Add a column from an example 
  • Creating and Using a Basic Custom Function 
  • Preparing for a parameter query 
  • Creating the base query 
  • Creating the parameter query 
  • Filling Up and Down to Replace Missing Values 
  • Grouping and Aggregating Data 
  • Pivoting and Unpivoting Data with Power Query 
  • Transposing a Table 
  • Reusing Query Steps 
  • Renaming Query Steps 
  • Understanding the Append Feature 
  • Creating the needed base queries 
  • Appending the data 
  • Understanding the Merge Feature 
  • Understanding Power Query joins 
  • Merging queries 
  • Choosing a Destination for Your Data 
  • Loading Data to the Worksheet Using the Default Excel Table Output 
  • Loading Data to Your Own Excel Tables 
  • Loading Data to Data Model/Power Pivot 
  • Loading Data Connection Only 
  • Viewing Tables in the Excel Data Model 
  • Advantages of Using the Excel Data Model 
  • Power Query and Table Relationships 
  • Breaking Changes 
  • Refreshing Queries Manually 
  • Automating Data Refresh 
  • Power Query Best Practices  
  • Introduction to Power Pivot  
  • Understanding Power Pivot 
  • Limitations of the Internal Data Model 
  • Understanding Acceptable Data Types 
  • Launching Power Pivot  
  • Tour of Power Pivot Window 
  • Power Pivot best practices 
  • Preparing Your Data  
  • Adding Excel Tables to Power Pivot 
  • Importing a Text File 
  • Copying and Pasting Data 
  • Importing Access Tables 
  • Importing Data from external Excel Files 
  • Adding and Maintaining Data in Power Pivot  
  • Example of Data Model 
  • Meet the Excel data model 
  • Access the Data Model 
  • The Data Model Window  
  • Understanding Key Fields 
  • Data vs. Diagram View 
  • Database normalization 
  • Data Models Table Types 
  • Data tables versus lookup tables 
  • Relationships versus merged tables 
  • Primary & Foreign Keys 
  • Create Table Relationships 
  • Connecting Lookups To Lookups 
  • Modify table relationships 
  • Active versus inactive relationships 
  • Relationship cardinality 
  • Connect multiple data tables 
  • Filter direction 
  • Hide fields from client tools 
  • Define hierarchies 
  • Data model best practices 
  • Creating a Date Table in Excel 
  • Marking a Table as a Date Table 
  • Creating the Date Table in Power Pivot 
  • Adding Sort By Columns to the Date Table 
  • Adding the Date Table to the Data Model 

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.