Power Query – Power Pivot Overview: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older editNewer edit →
No edit summary
No edit summary
Line 2: Line 2:


== Objective ==
== Objective ==
In this module, you’ll get an introduction to Power Pivot and learn how to build a basic data model from Power Query output.
Learn how to load data into the Data Model, build relationships, and use DAX measures in PivotTables.


== Files Used ==
== Files Used ==
* [[Media:PowerPivot_SalesData.xlsx|Download PowerPivot_SalesData.xlsx]]
* [[Media:PowerPivot_SalesData.xlsx|Download PowerPivot_SalesData.xlsx]]
== What is Power Pivot? ==
Power Pivot is an Excel add-in that allows you to build relationships between tables and perform advanced data analysis using the Data Model.
It works seamlessly with Power Query by allowing you to load cleaned and transformed data directly into the Data Model.


== Enabling Power Pivot ==
== Enabling Power Pivot ==
'''Steps:'''
'''Steps:'''
# In Excel, go to the '''File''' tab → '''Options'''
# In Excel → '''File''' tab → '''Options'''
# Select '''Add-ins'''
# Go to '''Add-ins'''
# At the bottom, next to '''Manage''', choose '''COM Add-ins''' and click '''Go'''
# At the bottom, choose '''COM Add-ins''' → click '''Go'''
# Check '''Microsoft Power Pivot for Excel''' → Click '''OK'''
# Check '''Microsoft Power Pivot for Excel''' → click '''OK'''
# You will now see a '''Power Pivot''' tab in the Excel ribbon
# The '''Power Pivot''' tab will appear on the ribbon


== Loading Data to Power Pivot ==
== Loading Data to Power Pivot ==
'''Steps:'''
'''Steps:'''
# Import the file '''PowerPivot_SalesData.xlsx''' via Power Query
# Import '''PowerPivot_SalesData.xlsx''' via Power Query
# In the Power Query Editor, click '''Close & Load To...'''
# In the Power Query Editor, click '''Close & Load To...'''
# In the import dialog, select:
# Choose:
#* '''Only Create Connection'''
#* '''Only Create Connection'''
#* Check '''Add this data to the Data Model'''
#* Check '''Add this data to the Data Model'''
Line 31: Line 26:
== Opening the Power Pivot Window ==
== Opening the Power Pivot Window ==
'''Steps:'''
'''Steps:'''
# Go to the '''Power Pivot''' tab in Excel
# Go to the '''Power Pivot''' tab
# Click '''Manage'''
# Click '''Manage'''
# This opens a separate Power Pivot window showing the data model
# This opens the Power Pivot window with your loaded table


== Creating Relationships ==
== Creating Relationships ==
'''Steps:'''
'''Steps:'''
# If you have multiple tables in the model, go to the '''Diagram View'''
# Go to the Power Pivot window → '''Diagram View'''
# Drag fields from one table to another to create relationships (e.g. Customer ID → Customer ID)
# Drag and drop fields to link tables (if more than one table is present)
# You can also go to the '''Design''' tab → '''Create Relationship'''
# Or: go to the '''Design''' tab → '''Create Relationship'''
 
== Adding Measures (DAX) ==
You can create calculated fields (measures) using DAX (Data Analysis Expressions).


== Creating a DAX Measure ==
'''Steps:'''
'''Steps:'''
# In the Power Pivot window, click into the bottom row of the table
# In Power Pivot window, click into the bottom row (under the table)
# Type a formula, e.g.:
# Type:
<syntaxhighlight lang="dax">
<syntaxhighlight lang="dax">
Total Revenue := SUM([Revenue])
Total Revenue := SUM([Revenue])
</syntaxhighlight>
</syntaxhighlight>
# Press Enter — the measure appears in the model
== Using Power Pivot in PivotTables ==
'''Steps:'''
# In Excel, go to '''Insert''' → '''PivotTable'''
# Choose: '''Use this workbook’s Data Model'''
# Build a PivotTable using fields from Power Pivot
== Additional Exercise: Create PivotTable with DAX Measure ==
'''Goal:''' Analyze revenue by region.
'''Steps:'''
# Build a PivotTable based on the Data Model
# Use `Region` as a row field
# Use `Total Revenue` as the values field
# Sort by revenue descending


# Press Enter — the measure will appear in the model and can be used in PivotTables
== Extension: Add Calculated Column ==
'''Goal:''' Add a margin estimate.


== Using Power Pivot in PivotTables ==
'''Steps:'''
'''Steps:'''
# Go to Excel → Insert → PivotTable
# In the Power Pivot window, click a new column
# In the dialog, select '''Use this workbook’s Data Model'''
# Use formula:
# Choose fields from your Power Pivot tables to build a PivotTable with relationships
<syntaxhighlight lang="dax">
EstimatedMargin := [Revenue] * 0.25
</syntaxhighlight>
# Use it in a new PivotTable analysis


----
----


→ Continue to [[Power Query – Summary and Exercises|Module 6: Summary and Final Exercises]]
→ Continue to [[Power Query – Summary and Exercises|Module 6: Summary and Final Exercises]]


Return to [[Power_Query_–_Basic|Main Page]]
Return to [[Power_Query_–_Basic|Main Page]]

Revision as of 09:49, 19 June 2025

Module 5: Power Pivot Overview

Objective

Learn how to load data into the Data Model, build relationships, and use DAX measures in PivotTables.

Files Used

Enabling Power Pivot

Steps:

  1. In Excel → File tab → Options
  2. Go to Add-ins
  3. At the bottom, choose COM Add-ins → click Go
  4. Check Microsoft Power Pivot for Excel → click OK
  5. The Power Pivot tab will appear on the ribbon

Loading Data to Power Pivot

Steps:

  1. Import PowerPivot_SalesData.xlsx via Power Query
  2. In the Power Query Editor, click Close & Load To...
  3. Choose:
    • Only Create Connection
    • Check Add this data to the Data Model
  4. Click OK

Opening the Power Pivot Window

Steps:

  1. Go to the Power Pivot tab
  2. Click Manage
  3. This opens the Power Pivot window with your loaded table

Creating Relationships

Steps:

  1. Go to the Power Pivot window → Diagram View
  2. Drag and drop fields to link tables (if more than one table is present)
  3. Or: go to the Design tab → Create Relationship

Creating a DAX Measure

Steps:

  1. In Power Pivot window, click into the bottom row (under the table)
  2. Type:
Total Revenue := SUM([Revenue])
  1. Press Enter — the measure appears in the model

Using Power Pivot in PivotTables

Steps:

  1. In Excel, go to Insert → PivotTable
  2. Choose: Use this workbook’s Data Model
  3. Build a PivotTable using fields from Power Pivot

Additional Exercise: Create PivotTable with DAX Measure

Goal: Analyze revenue by region.

Steps:

  1. Build a PivotTable based on the Data Model
  2. Use `Region` as a row field
  3. Use `Total Revenue` as the values field
  4. Sort by revenue descending

Extension: Add Calculated Column

Goal: Add a margin estimate.

Steps:

  1. In the Power Pivot window, click a new column
  2. Use formula:
EstimatedMargin := [Revenue] * 0.25
  1. Use it in a new PivotTable analysis

→ Continue to Module 6: Summary and Final Exercises

Return to Main Page

Retrieved from "https://training-course-material.com/index.php?title=Power_Query_–_Power_Pivot_Overview&oldid=88891"