Realdolmen Education

Details

Excel Power Query and Power Pivot

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

0.5 day(s)

MS Excel: Using Power Query and Power Pivot to analyze data

Duration

0,5 day

Audience

This course is aimed at users who want to process large amounts of external data in well-arranged Excel reports.

Prerequisites

A knowledge of basic Excel skills (input and edit data, use functions and formulas) is required. Being able to create, edit, sort and filter Excel tables. Being able to create and edit Pivot Tables and Pivot Charts.

Objectives

Learn to use Power Query and Power Pivot.

Methods

An interactive hands-on classroom-based training.

Description

Users can work with Pivot Table to analyze figures rapidly and efficiently. However, to process huge amounts of external data in Excel, Pivot Tables are not the best tool. You need Power Pivot. PowerPivot in all Excel versions since version 2010.

The Power Query tool can also serve to use external data in Excel as the basis of Pivot Tables, charts, ...

We begin with a short introduction to relational databases. Then, we show how to create a Power Pivot model based on imported and dynamic Excel tables. We also show how to use a Power Pivot model as the basis for Pivot Tables in Excel.

We’ll cover some DAX functions to add calculations to the Power Pivot model.

Then we learn how to use Power Query to analyze external data in Excel.

Contents

  • Introduction
    • What is Power Pivot
    • What is Power Query
    • Excel database principles
    • Import data from databases into Excel
    • Excel database vs. relational database
    • Excel vs. Power Pivot
    • Activate Power tools
  • Power Pivot
    • Start Power Pivot
    • Explore Power Pivot screen
    • The 4 elements of a Power Pivot model (input, calculations, output, variables)
    • DAX functions
  • Power Query
    • Link with external sources
    • Transform data
    • Combine data sources
    • Use data