Power Query – Power Pivot Overview - Revision history

Gpruszczynski1: /* Additional Exercise: Analyze by Region and Product */

2025-06-22T13:40:37Z

Additional Exercise: Analyze by Region and Product

@@ Line 65: / Line 65: @@
 #* Add `Category` from Products to Columns
 #* Add `Total Sales` to Values
 == Extension: Add a Calculated Column in Power Pivot ==

Gpruszczynski1 at 13:22, 22 June 2025

2025-06-22T13:22:55Z

Show changes

Gpruszczynski1 at 13:08, 22 June 2025

2025-06-22T13:08:31Z

← Older revision		Revision as of 13:08, 22 June 2025
Line 9:		Line 9:
	== Enabling Power Pivot ==		== Enabling Power Pivot ==
	'''Steps:'''		'''Steps:'''
	# In Excel → '''File''' tab → '''Options'''		# In Excel, click the '''File''' tab in the top left corner.
	# ~~Go to~~ '''Add-ins'''		# Select '''Options''' from the bottom of the left menu.
	# At the bottom, choose '''COM Add-ins''' ~~→ click~~ '''Go'''		# In the Excel Options window, select '''Add-ins'''.
	# ~~Check~~ '''Microsoft Power Pivot for Excel''' ~~→ click~~ '''OK'''		# At the bottom, in the '''Manage''' dropdown, choose '''COM Add-ins'''.
	# The '''Power Pivot''' tab ~~will appear on~~ the ribbon		# Click the '''Go...''' button next to it.
			# In the COM Add-Ins window, check the box next to '''Microsoft Power Pivot for Excel'''.
			# Click '''OK'''.
			# The '''Power Pivot''' tab should now be visible in the Excel ribbon.

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

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

	== Creating Relationships ==		== Creating Relationships ==
	'''Steps:'''		'''Steps:'''
	# ~~Go to~~ the Power Pivot window → '''Diagram View'''		# In the Power Pivot window, go to the top ribbon and click on '''Diagram View'''.
	# ~~Drag~~ and ~~drop fields to link tables~~ (~~if more than~~ one table ~~is present~~)		# You'll see a visual layout of all tables in your model.
	# ~~Or:~~ go to the '''Design''' tab → '''Create Relationship'''		# 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).
			# Alternatively, go to the '''Design''' tab → click '''Create Relationship'''.
			# In the dialog, select the two tables and matching columns.
			# Click '''OK'''.

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

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

	# Press Enter ~~— the~~ measure ~~appears~~ in the ~~model~~		# Press '''Enter'''.
			# The measure will now appear in the Fields list and can be used in PivotTables.

	== Using Power Pivot in PivotTables ==		== Using Power Pivot in PivotTables ==
	'''Steps:'''		'''Steps:'''
	# In Excel, go to '''Insert''' → '''PivotTable'''		# In Excel, go to the '''Insert''' tab.
	# ~~Choose~~: '''Use this workbook’s Data Model'''		# Click on '''PivotTable'''.
	# ~~Build a~~ PivotTable ~~using fields from~~ Power Pivot		# In the Create PivotTable dialog:
			#* Select the option: '''Use this workbook’s Data Model'''.
			#* Choose where you want the PivotTable to be placed (new worksheet or existing one).
			# Click '''OK'''.
			# In the PivotTable Fields pane, you'll see your Power Pivot tables.
			# Drag fields into Rows, Columns, and Values as needed.

	== Additional Exercise: Create PivotTable with DAX Measure ==		== Additional Exercise: Create PivotTable with DAX Measure ==
Line 55:		Line 71:

	'''Steps:'''		'''Steps:'''
	# ~~Build~~ a PivotTable based on the Data Model		# Follow the steps to insert a PivotTable based on the Data Model.
	# ~~Use~~ `Region` ~~as a row field~~		# In the PivotTable Fields pane:
	# ~~Use~~ `Total Revenue` as the ~~values field~~		#* Drag `Region` into the '''Rows''' area.
	# Sort ~~by revenue descending~~		#* 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 ==		== Extension: Add Calculated Column ==
	'''Goal:''' Add a margin ~~estimate~~.		'''Goal:''' Add a column that estimates margin as 25% of revenue.

	'''Steps:'''		'''Steps:'''
	# In the Power Pivot window, click ~~a new~~ column		# In the Power Pivot window → go to the table where you want the column.
	# ~~Use~~ formula:		# Scroll to the rightmost column → click into the empty column labeled '''Add Column'''.
			# Type the formula:

	~~EstimatedMargin :~~= [Revenue] * 0.25		=[Revenue] * 0.25

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

	----		----

Gpruszczynski1: /* Extension: Add Calculated Column */

2025-06-19T09:50:46Z

Extension: Add Calculated Column

← Older revision		Revision as of 09:50, 19 June 2025
Line 66:		Line 66:
	# In the Power Pivot window, click a new column		# In the Power Pivot window, click a new column
	# Use formula:		# Use formula:
	~~<syntaxhighlight lang="dax">~~
	EstimatedMargin := [Revenue] * 0.25		EstimatedMargin := [Revenue] * 0.25
	~~</syntaxhighlight>~~
	# Use it in a new PivotTable analysis		# Use it in a new PivotTable analysis

Gpruszczynski1: /* Creating a DAX Measure */

2025-06-19T09:50:19Z

Creating a DAX Measure

← Older revision		Revision as of 09:50, 19 June 2025
Line 40:		Line 40:
	# In Power Pivot window, click into the bottom row (under the table)		# In Power Pivot window, click into the bottom row (under the table)
	# Type:		# Type:
	~~<syntaxhighlight lang="dax">~~
	Total Revenue := SUM([Revenue])		Total Revenue := SUM([Revenue])
	~~</syntaxhighlight>~~
	# Press Enter — the measure appears in the model		# Press Enter — the measure appears in the model

Gpruszczynski1 at 09:49, 19 June 2025

2025-06-19T09:49:02Z

← Older revision		Revision as of 09:49, 19 June 2025
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]]

Gpruszczynski1 at 09:16, 19 June 2025

2025-06-19T09:16:49Z

← Older revision		Revision as of 09:16, 19 June 2025
Line 1:		Line 1:
			= Module 5: Power Pivot Overview =

			== 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.

			== Files Used ==
			* [[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 ==
			'''Steps:'''
			# In Excel, go to the '''File''' tab → '''Options'''
			# Select '''Add-ins'''
			# At the bottom, next to '''Manage''', choose '''COM Add-ins''' and click '''Go'''
			# Check '''Microsoft Power Pivot for Excel''' → Click '''OK'''
			# You will now see a '''Power Pivot''' tab in the Excel ribbon

			== Loading Data to Power Pivot ==
			'''Steps:'''
			# Import the file '''PowerPivot_SalesData.xlsx''' via Power Query
			# In the Power Query Editor, click '''Close & Load To...'''
			# In the import dialog, select:
			#* '''Only Create Connection'''
			#* Check '''Add this data to the Data Model'''
			# Click '''OK'''

			== Opening the Power Pivot Window ==
			'''Steps:'''
			# Go to the '''Power Pivot''' tab in Excel
			# Click '''Manage'''
			# This opens a separate Power Pivot window showing the data model

			== Creating Relationships ==
			'''Steps:'''
			# If you have multiple tables in the model, go to the '''Diagram View'''
			# Drag fields from one table to another to create relationships (e.g. Customer ID → Customer ID)
			# You can also go to the '''Design''' tab → '''Create Relationship'''

			== Adding Measures (DAX) ==
			You can create calculated fields (measures) using DAX (Data Analysis Expressions).

			'''Steps:'''
			# In the Power Pivot window, click into the bottom row of the table
			# Type a formula, e.g.:
			<syntaxhighlight lang="dax">
			Total Revenue := SUM([Revenue])
			</syntaxhighlight>

			# Press Enter — the measure will appear in the model and can be used in PivotTables

			== Using Power Pivot in PivotTables ==
			'''Steps:'''
			# Go to Excel → Insert → PivotTable
			# In the dialog, select '''Use this workbook’s Data Model'''
			# Choose fields from your Power Pivot tables to build a PivotTable with relationships

			----


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

Gpruszczynski1 at 17:51, 18 June 2025

2025-06-18T17:51:57Z

← Older revision		Revision as of 17:51, 18 June 2025
Line 1:		Line 1:
	Return to [[Power_Query_–_Basic\|Main Page]]		Return to [[Power_Query_–_Basic\|Main Page]]

			* [[Power Query – Introduction\|Module 1: Introduction to Power Query]]
			* [[Power Query – Importing and Managing Data\|Module 2: Importing and Managing Data]]
			* [[Power Query – Data Transformation\|Module 3: Data Transformation Techniques]]
			* [[Power Query – Automation and Parameters\|Module 4: Automation and Parameters]]
			* [[Power Query – Power Pivot Overview\|Module 5: Power Pivot Overview]]
			* [[Power Query – Summary and Exercises\|Module 6: Summary and Final Exercises]]

Gpruszczynski1: Created page with "Return to Main Page"

2025-06-18T17:50:19Z

Created page with "Return to Main Page"

New page

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