Power Query – Power Pivot Overview: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older edit
No edit summary
 
(One intermediate revision by the same user not shown)
Line 2: Line 2:


== Objective ==
== Objective ==
Learn how to load data into the Data Model, build relationships, and use DAX measures in PivotTables.
Learn how to load multiple related tables into the Data Model using Power Query, create relationships in Power Pivot, and build PivotTables using DAX calculations.


== Files Used ==
== Files Used ==
* [[Media:PowerPivot_SalesData.xlsx|Download PowerPivot_SalesData.xlsx]]
* [[Media:PowerPivot_Customers.xlsx|Download PowerPivot_Customers.xlsx]]
* [[Media:PowerPivot_Products.xlsx|Download PowerPivot_Products.xlsx]]
* [[Media:PowerPivot_Sales.xlsx|Download PowerPivot_Sales.xlsx]]


== Enabling Power Pivot ==
== Enabling Power Pivot Add-in ==
'''Steps:'''
'''Steps:'''
# In Excel, click the '''File''' tab in the top left corner.
# Open Excel.
# Select '''Options''' from the bottom of the left menu.
# Go to the '''File''' tab → click '''Options'''.
# In the Excel Options window, select '''Add-ins'''.
# In the Excel Options window → select '''Add-ins'''.
# At the bottom, in the '''Manage''' dropdown, choose '''COM Add-ins'''.
# At the bottom, next to '''Manage''', select '''COM Add-ins''' from the dropdown → click '''Go...'''.
# Click the '''Go...''' button next to it.
# In the list, check '''Microsoft Power Pivot for Excel''' → click '''OK'''.
# In the COM Add-Ins window, check the box next to '''Microsoft Power Pivot for Excel'''.
# The '''Power Pivot''' tab will now appear in the ribbon.
# Click '''OK'''.
# The '''Power Pivot''' tab should now be visible in the Excel ribbon.


== Loading Data to Power Pivot ==
== Importing Data via Power Query into the Data Model ==
'''Steps:'''
'''Steps:'''
# In Excel, go to the '''Data''' tab → click '''Get Data''' → choose '''From Workbook'''.
# Go to the '''Data''' tab → click '''Get Data''' → '''From File''' → '''From Workbook'''.
# Select the file '''PowerPivot_SalesData.xlsx'''.
# Select and open '''PowerPivot_Customers.xlsx'''.
# In the Navigator pane, select the relevant sheet(s) and click '''Transform Data'''.
# In Navigator → check the sheet → click '''Transform Data'''.
# In Power Query Editor, verify the data is clean. Then click the dropdown next to '''Close & Load''' → choose '''Close & Load To...'''.
# In Power Query Editor → click the '''Home''' tab → click the dropdown under '''Close & Load''' → choose '''Close & Load To...'''.
# In the Import Data dialog:
# In Import Data dialog:
#* Select '''Only Create Connection'''
#* Select '''Only Create Connection'''
#* Check the box for '''Add this data to the Data Model'''
#* Check the box '''Add this data to the Data Model'''
# Click '''OK'''.
#* Click '''OK'''
# The data is now loaded into the Power Pivot model, not into a worksheet.
# Repeat these steps for '''PowerPivot_Products.xlsx''' and '''PowerPivot_Sales.xlsx'''.


== Opening the Power Pivot Window ==
== Opening the Power Pivot Window ==
'''Steps:'''
'''Steps:'''
# Click the '''Power Pivot''' tab on the Excel ribbon.
# Go to the Excel ribbon → click on the '''Power Pivot''' tab.
# Click the '''Manage''' button.
# Click '''Manage'''.
# The Power Pivot window will open in a new window, showing your data.
# The Power Pivot window will open showing the loaded tables.


== Creating Relationships ==
== Creating Relationships Between Tables ==
'''Steps:'''
'''Steps:'''
# In the Power Pivot window, go to the top ribbon and click on '''Diagram View'''.
# In the Power Pivot window → click the '''Diagram View''' button in the top-right.
# You'll see a visual layout of all tables in your model.
# Drag from '''CustomerID''' in the Sales table to '''CustomerID''' in the Customers table.
# If there are multiple tables, you can click and drag a field (e.g., `ProductID`) from one table to a matching field in another (e.g., `ProductID` in Sales and Products).
# Drag from '''ProductID''' in the Sales table to '''ProductID''' in the Products table.
# Alternatively, go to the '''Design''' tab → click '''Create Relationship'''.
# You should now see the relationships as arrows between the tables.
# In the dialog, select the two tables and matching columns.
# Click '''OK'''.


== Creating a DAX Measure ==
== Creating a DAX Measure ==
'''Steps:'''
'''Steps:'''
# In the Power Pivot window, ensure you're in the '''Data View'''.
# In the Power Pivot window → click on the '''Sales''' table.
# Scroll to the bottom of the data table (below the columns) where you'll see a cell labeled '''Add Measure''' or an empty row.
# Scroll to the bottom of the table.
# Click in that row and type the formula:
# In the empty cell under the data → type the formula:


   Total Revenue := SUM([Revenue])
   Total Sales := SUM([Total])


# Press '''Enter'''.
# Press '''Enter'''.
# The measure will now appear in the Fields list and can be used in PivotTables.
# The measure is now available in PivotTables.


== Using Power Pivot in PivotTables ==
== Building a PivotTable from the Data Model ==
'''Steps:'''
'''Steps:'''
# In Excel, go to the '''Insert''' tab.
# In Excel → go to the '''Insert''' tab → click '''PivotTable'''.
# Click on '''PivotTable'''.
# In Create PivotTable dialog:
# In the Create PivotTable dialog:
#* Select '''Use this workbook’s Data Model'''
#* Select the option: '''Use this workbook’s Data Model'''.
#* Choose location (New worksheet or Existing worksheet)
#* Choose where you want the PivotTable to be placed (new worksheet or existing one).
# Click '''OK'''
# Click '''OK'''.
# Use fields from all related tables:
# In the PivotTable Fields pane, you'll see your Power Pivot tables.
#* Add `Region` from Customers to Rows
# Drag fields into Rows, Columns, and Values as needed.
#* Add `Category` from Products to Columns
 
#* Add `Total Sales` to Values
== Additional Exercise: Create PivotTable with DAX Measure ==
'''Goal:''' Analyze revenue by region.
 
'''Steps:'''
# Follow the steps to insert a PivotTable based on the Data Model.
# In the PivotTable Fields pane:
#* Drag `Region` into the '''Rows''' area.
#* Drag the DAX measure `Total Revenue` into the '''Values''' area.
# Click any cell in the PivotTable → right-click → choose '''Sort''' → '''Sort Largest to Smallest'''.
 
== Extension: Add Calculated Column ==
'''Goal:''' Add a column that estimates margin as 25% of revenue.


== Extension: Add a Calculated Column in Power Pivot ==
'''Steps:'''
'''Steps:'''
# In the Power Pivot window → go to the table where you want the column.
# In the Power Pivot window → select the '''Sales''' table.
# Scroll to the rightmost column → click into the empty column labeled '''Add Column'''.
# Scroll to the rightmost column → click into the empty column labeled '''Add Column'''.
# Type the formula:
# Type:


   =[Revenue] * 0.25
   =[Quantity] * 0.1


# Press '''Enter'''.
# Press '''Enter'''.
# Power Pivot will automatically name the column (you can rename it to `EstimatedMargin`).
# Rename the column to `EstimatedCommission`.
# Return to Excel and create a new PivotTable using this field as needed.


----
== Navigation ==


→ 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]]

Latest revision as of 13:40, 22 June 2025

Module 5: Power Pivot Overview

Objective

Learn how to load multiple related tables into the Data Model using Power Query, create relationships in Power Pivot, and build PivotTables using DAX calculations.

Files Used

Enabling Power Pivot Add-in

Steps:

  1. Open Excel.
  2. Go to the File tab → click Options.
  3. In the Excel Options window → select Add-ins.
  4. At the bottom, next to Manage, select COM Add-ins from the dropdown → click Go....
  5. In the list, check Microsoft Power Pivot for Excel → click OK.
  6. The Power Pivot tab will now appear in the ribbon.

Importing Data via Power Query into the Data Model

Steps:

  1. Go to the Data tab → click Get Data → From File → From Workbook.
  2. Select and open PowerPivot_Customers.xlsx.
  3. In Navigator → check the sheet → click Transform Data.
  4. In Power Query Editor → click the Home tab → click the dropdown under Close & Load → choose Close & Load To....
  5. In Import Data dialog:
    • Select Only Create Connection
    • Check the box Add this data to the Data Model
    • Click OK
  6. Repeat these steps for PowerPivot_Products.xlsx and PowerPivot_Sales.xlsx.

Opening the Power Pivot Window

Steps:

  1. Go to the Excel ribbon → click on the Power Pivot tab.
  2. Click Manage.
  3. The Power Pivot window will open showing the loaded tables.

Creating Relationships Between Tables

Steps:

  1. In the Power Pivot window → click the Diagram View button in the top-right.
  2. Drag from CustomerID in the Sales table to CustomerID in the Customers table.
  3. Drag from ProductID in the Sales table to ProductID in the Products table.
  4. You should now see the relationships as arrows between the tables.

Creating a DAX Measure

Steps:

  1. In the Power Pivot window → click on the Sales table.
  2. Scroll to the bottom of the table.
  3. In the empty cell under the data → type the formula:
 Total Sales := SUM([Total])
  1. Press Enter.
  2. The measure is now available in PivotTables.

Building a PivotTable from the Data Model

Steps:

  1. In Excel → go to the Insert tab → click PivotTable.
  2. In Create PivotTable dialog:
    • Select Use this workbook’s Data Model
    • Choose location (New worksheet or Existing worksheet)
  3. Click OK
  4. Use fields from all related tables:
    • Add `Region` from Customers to Rows
    • Add `Category` from Products to Columns
    • Add `Total Sales` to Values

Extension: Add a Calculated Column in Power Pivot

Steps:

  1. In the Power Pivot window → select the Sales table.
  2. Scroll to the rightmost column → click into the empty column labeled Add Column.
  3. Type:
 =[Quantity] * 0.1
  1. Press Enter.
  2. Rename the column to `EstimatedCommission`.

Navigation

→ 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=88933"