Power Query – Data Transformation - Revision history

Gpruszczynski1: /* Extension: Add a Conditional Column */

2025-06-22T10:34:06Z

Extension: Add a Conditional Column

← Older revision		Revision as of 10:34, 22 June 2025
Line 172:		Line 172:
	You now have a summary table that shows how many responses fall into each satisfaction score. This can be used directly in Excel for charting or further analysis.		You now have a summary table that shows how many responses fall into each satisfaction score. This can be used directly in Excel for charting or further analysis.

	== ~~Extension:~~ Add a Conditional Column ==		== Add a Conditional Column ==
	'''Goal:''' Categorize satisfaction ratings into "Positive", "Neutral", or "Negative"		'''Goal:''' Categorize satisfaction ratings into "Positive", "Neutral", or "Negative"

Gpruszczynski1: /* 4. Group the data */

2025-06-22T10:33:05Z

4. Group the data

← Older revision		Revision as of 10:33, 22 June 2025
Line 157:		Line 157:
	=== 4. Group the data ===		=== 4. Group the data ===
	# Click the header of the `satisfaction` column to select it		# Click the header of the `satisfaction` column to select it
	# Go to the '''~~Transform~~''' tab → click '''Group By'''		# Go to the '''Home''' tab → click '''Group By'''
	# In the dialog box:		# In the dialog box:
	#* Ensure the selected column is `satisfaction`		#* Ensure the selected column is `satisfaction`

Gpruszczynski1: /* Additional Exercise: Cleanup and Pivot Prep */

2025-06-22T10:28:54Z

Additional Exercise: Cleanup and Pivot Prep

← Older revision		Revision as of 10:28, 22 June 2025
Line 128:		Line 128:
	* This is more efficient and avoids duplication of complex logic		* This is more efficient and avoids duplication of complex logic

	== ~~Additional Exercise:~~ Cleanup and Pivot Prep ==		== Cleanup and Pivot Prep ==
	'''Goal:''' Prepare the survey data for summary analysis in a pivot table.		'''Goal:''' Prepare the survey data for summary analysis in a pivot table.

Gpruszczynski1: /* Additional Exercise: Cleanup and Pivot Prep */

2025-06-22T10:28:40Z

Additional Exercise: Cleanup and Pivot Prep

@@ Line 129: / Line 129: @@
 == Additional Exercise: Cleanup and Pivot Prep ==
-'''Goal:''' Prepare survey data for pivot table analysis
+'''Goal:''' Prepare the survey data for summary analysis in a pivot table.
 '''Steps:'''
-# Load '''Survey_Results.xlsx''' and remove rows with missing values in the satisfaction column
-# Rename the satisfaction column to lowercase (e.g. "satisfaction")
+=== 1. Load the data ===
-# Group the table by satisfaction rating:
+# Open Excel
-#* Go to '''Transform''' → '''Group By''' → select satisfaction → operation: Count Rows
+# Go to the '''Data''' tab → click '''Get Data''' → '''From File''' → '''From Workbook'''
-# Load the final grouped table to a new worksheet in Excel
+# Select the file: '''Survey_Results.xlsx''' and click '''Import'''
 == Extension: Add a Conditional Column ==

Gpruszczynski1: /* Duplicating and Referencing Queries */

2025-06-22T10:26:49Z

Duplicating and Referencing Queries

@@ Line 108: / Line 108: @@
 ## This creates a new query that uses the output of the original query as its starting point
 ## It does not copy the steps — instead, it acts like a “live link” to the original query’s final result
 == Additional Exercise: Cleanup and Pivot Prep ==

Gpruszczynski1: /* Duplicating and Referencing Queries */

2025-06-22T10:25:24Z

Duplicating and Referencing Queries

← Older revision		Revision as of 10:25, 22 June 2025
Line 103:		Line 103:
	#* Right-click the query you want to reuse		#* Right-click the query you want to reuse
	# To create a full copy: choose '''Duplicate'''		# To create a full copy: choose '''Duplicate'''
			## This creates a completely separate copy of the query
			## All applied steps are copied, and changes to the original will not affect the duplicate
	# To create a linked version that depends on the original: choose '''Reference'''		# To create a linked version that depends on the original: choose '''Reference'''
			## This creates a new query that uses the output of the original query as its starting point
			## It does not copy the steps — instead, it acts like a “live link” to the original query’s final result

	== Additional Exercise: Cleanup and Pivot Prep ==		== Additional Exercise: Cleanup and Pivot Prep ==

Gpruszczynski1: /* Unpivoting Columns */

2025-06-22T10:19:22Z

Unpivoting Columns

← Older revision		Revision as of 10:19, 22 June 2025
Line 64:		Line 64:

	== Unpivoting Columns ==		== Unpivoting Columns ==
			'''Goal:''' Reshape data so that monthly sales are listed in rows rather than across columns.

			'''File used:'''
			* [[Media:Monthly_Sales_2023.xlsx\|Download Monthly_Sales_2023.xlsx]]

	'''Steps:'''		'''Steps:'''
	# ~~Go to~~ Excel → Data tab → Get Data → From File → From Workbook		# In Excel → go to the '''Data''' tab → click '''Get Data''' → '''From File''' → '''From Workbook'''
	# Select ~~and load~~ '''~~Orders_2023~~.xlsx'''		# Select the file: '''Monthly_Sales_2023.xlsx''' and click '''Import'''
	# In Navigator ~~→ select~~ the worksheet ~~→ click~~ '''Transform Data'''		# In the Navigator window:
			#* Select the worksheet that appears (there is only one sheet in this file)
			#* Click the button '''Transform Data''' to open Power Query Editor
	# In Power Query Editor:		# In Power Query Editor:
	#* ~~Select all~~ columns ~~representing months (e.g.~~ Jan, Feb, ~~Mar)~~		#* Confirm that columns like `Jan`, `Feb`, ..., `Jun` are present
	#* Go to the '''Transform''' tab		#* Select all month columns by clicking on `Jan`, then holding Shift and clicking `Jun`
	~~#* Click~~ '''Unpivot Columns'''		# Go to the '''Transform''' tab → click '''Unpivot Columns'''
	# ~~This~~ will convert columns into ~~attribute~~-~~value pairs~~		# Power Query will convert each of the selected columns into two columns:
			#* One called '''Attribute''' (containing the month names)
			#* One called '''Value''' (containing the corresponding sales values)
			# You can rename the new columns:
			#* Right-click the column '''Attribute''' → choose '''Rename''' → type `Month`
			#* Right-click the column '''Value''' → choose '''Rename''' → type `Sales`

			'''Optional:'''
			* Change the data type of the new `Sales` column to a number:
			#* Click the small icon in the `Sales` column header (e.g. ABC123) and choose '''Whole Number'''

			'''Result:'''
			You now have a normalized table where each row represents a single product's sales in a particular month. This structure is ideal for analysis or pivoting in Excel.

	== Reordering Columns ==		== Reordering Columns ==

Gpruszczynski1 at 09:54, 22 June 2025

2025-06-22T09:54:41Z

Show changes

Gpruszczynski1 at 09:45, 19 June 2025

2025-06-19T09:45:19Z

← Older revision		Revision as of 09:45, 19 June 2025
Line 2:		Line 2:

	== Objective ==		== Objective ==
	~~In this module, you'll learn how to clean~~ and reshape data in Power ~~Query using~~ built-in ~~transformation~~ tools.		Clean and reshape data using Power Query’s built-in tools.

	== Files Used ==		== Files Used ==
Line 10:		Line 10:
	== Removing Rows and Columns ==		== Removing Rows and Columns ==
	'''Steps:'''		'''Steps:'''
	# ~~Import the file~~ '''Survey_Results.xlsx'''		# Load '''Survey_Results.xlsx'''
	# In Power Query Editor:		# In Power Query Editor:
	#* Go to the '''Home''' tab → '''Remove Rows''' → '''Remove Blank Rows'''		#* Go to the '''Home''' tab → '''Remove Rows''' → '''Remove Blank Rows'''
	#* Select ~~any unnecessary~~ columns → Right-click → '''Remove Columns'''		#* Select columns to remove → Right-click → '''Remove Columns'''

	== Changing Data Types ==		== Changing Data Types ==
	'''Steps:'''		'''Steps:'''
	# Click the icon next to a column header (e.g. ABC123)		# Click the icon next to a column header (e.g. ABC123)
	# Choose ~~appropriate~~ type (e.g. '''Whole Number''', '''Date''', '''Text''')		# Choose data type (e.g. '''Whole Number''', '''Date''', '''Text''')
	# Or: ~~go to the~~ '''Transform''' tab → '''Data Type'''		# Or: '''Transform''' tab → '''Data Type'''

	== Renaming Columns ==		== Renaming Columns ==
	'''Steps:'''		'''Steps:'''
	# Double-click the column ~~header,~~ or		# Double-click the column name or
	# Right-click → '''Rename''' → Type ~~the~~ new name		# Right-click → '''Rename''' → Type new name

	== Filtering ~~Data~~ ==		== Filtering and Sorting ==
	'''Steps:'''		'''Steps:'''
	# Click ~~the~~ filter icon in ~~any~~ column header		# Click filter icon in column header
	# Use ~~checkboxes, number~~ filters, or ~~text search to apply filters~~		# Use built-in filters or checkboxes
			# Click arrow again to access '''Sort Ascending''' / '''Sort Descending'''
	~~== Sorting Data ==~~
	~~'''Steps:'''~~
	# Click ~~the dropdown~~ arrow ~~on any column~~
	~~# Choose~~ '''Sort Ascending''' or '''Sort Descending'''

	== Grouping Data ==		== Grouping Data ==
	'''Steps:'''		'''Steps:'''
	# Go to ~~the~~ '''Transform''' tab → ~~Click~~ '''Group By'''		# Select a column
	# Choose ~~the column to group on (e.g. Product or Region)~~		# Go to '''Transform''' tab → '''Group By'''
	~~# Choose an aggregation (e.g.~~ Sum, Count, ~~Average)~~		# Choose operation: Sum, Count, etc.

	== Splitting Columns ==		== Splitting Columns ==
	'''Steps:'''		'''Steps:'''
	# Select a column ~~with combined values~~ (e.g. ~~full name or product code~~)		# Select a column (e.g. Full Name)
	# Go to ~~the~~ '''Home''' tab → '''Split Column'''		# Go to '''Home''' tab → '''Split Column'''
	# Choose method: by delimiter~~, number of characters, etc.~~		# Choose method (e.g. by delimiter)

	~~== Unpivoting Data ==~~
	~~'''Use case:''' When you have month names as column headers and want to convert them into rows.~~

			== Unpivoting Columns ==
	'''Steps:'''		'''Steps:'''
	# ~~Import the file~~ '''Orders_2023.xlsx'''		# Load '''Orders_2023.xlsx'''
	# Select ~~the~~ columns ~~with month names~~		# Select month columns (e.g. Jan, Feb)
	# Go to ~~the~~ '''Transform''' tab → '''Unpivot Columns'''		# Go to '''Transform''' tab → '''Unpivot Columns'''

	== Reordering Columns ==		== Reordering Columns ==
	'''Steps:'''		'''Steps:'''
	# Drag ~~and drop~~ columns ~~directly in the preview grid,~~ or		# Drag columns to new position or
	# Right-click a column → '''Move''' → choose ~~position (Left, Right, Beginning, End)~~		# Right-click column → '''Move''' → choose direction

	== Duplicating and Referencing Queries ==		== Duplicating and Referencing Queries ==
	'''Steps:'''		'''Steps:'''
	# ~~In the Queries pane, right~~-click a query		# Right-click a query in the left pane
	# Choose:		# Choose:
	#* '''Duplicate''' – ~~makes~~ a copy ~~of the query with independent steps~~		#* '''Duplicate''' – creates a full copy
	#* '''Reference''' – creates a new ~~query~~ that ~~uses~~ the ~~output of the original~~		#* '''Reference''' – creates a linked copy

			== Additional Exercise: Cleanup and Pivot Prep ==
			'''Goal:''' Prepare survey data for analysis.

			'''Steps:'''
			# Remove rows with missing satisfaction scores
			# Change column names to lowercase
			# Group by satisfaction rating and count rows
			# Load results into a new sheet

			== Extension: Conditional Columns ==
			'''Goal:''' Create a new column that classifies satisfaction as Positive/Neutral/Negative.

			'''Steps:'''
			# Go to the '''Add Column''' tab → '''Conditional Column'''
			# Name: `SatisfactionLevel`
			# Logic:
			#* If Satisfaction = 4 or 5 → "Positive"
			#* If Satisfaction = 3 → "Neutral"
			#* If Satisfaction = 1 or 2 → "Negative"
			# Click OK

	----		----


	→ Continue to [[Power Query – Automation and Parameters\|Module 4: Automation and Parameters]]		→ Continue to [[Power Query – Automation and Parameters\|Module 4: Automation and Parameters]]

Gpruszczynski1 at 09:09, 19 June 2025

2025-06-19T09:09:47Z

← Older revision		Revision as of 09:09, 19 June 2025
Line 1:		Line 1:
			= Module 3: Data Transformation Techniques =

			== Objective ==
			In this module, you'll learn how to clean and reshape data in Power Query using built-in transformation tools.

			== Files Used ==
			* [[Media:Survey_Results.xlsx\|Download Survey_Results.xlsx]]
			* [[Media:Orders_2023.xlsx\|Download Orders_2023.xlsx]]

			== Removing Rows and Columns ==
			'''Steps:'''
			# Import the file '''Survey_Results.xlsx'''
			# In Power Query Editor:
			#* Go to the '''Home''' tab → '''Remove Rows''' → '''Remove Blank Rows'''
			#* Select any unnecessary columns → Right-click → '''Remove Columns'''

			== Changing Data Types ==
			'''Steps:'''
			# Click the icon next to a column header (e.g. ABC123)
			# Choose appropriate type (e.g. '''Whole Number''', '''Date''', '''Text''')
			# Or: go to the '''Transform''' tab → '''Data Type'''

			== Renaming Columns ==
			'''Steps:'''
			# Double-click the column header, or
			# Right-click → '''Rename''' → Type the new name

			== Filtering Data ==
			'''Steps:'''
			# Click the filter icon in any column header
			# Use checkboxes, number filters, or text search to apply filters

			== Sorting Data ==
			'''Steps:'''
			# Click the dropdown arrow on any column
			# Choose '''Sort Ascending''' or '''Sort Descending'''

			== Grouping Data ==
			'''Steps:'''
			# Go to the '''Transform''' tab → Click '''Group By'''
			# Choose the column to group on (e.g. Product or Region)
			# Choose an aggregation (e.g. Sum, Count, Average)

			== Splitting Columns ==
			'''Steps:'''
			# Select a column with combined values (e.g. full name or product code)
			# Go to the '''Home''' tab → '''Split Column'''
			# Choose method: by delimiter, number of characters, etc.

			== Unpivoting Data ==
			'''Use case:''' When you have month names as column headers and want to convert them into rows.

			'''Steps:'''
			# Import the file '''Orders_2023.xlsx'''
			# Select the columns with month names
			# Go to the '''Transform''' tab → '''Unpivot Columns'''

			== Reordering Columns ==
			'''Steps:'''
			# Drag and drop columns directly in the preview grid, or
			# Right-click a column → '''Move''' → choose position (Left, Right, Beginning, End)

			== Duplicating and Referencing Queries ==
			'''Steps:'''
			# In the Queries pane, right-click a query
			# Choose:
			#* '''Duplicate''' – makes a copy of the query with independent steps
			#* '''Reference''' – creates a new query that uses the output of the original

			----


			→ Continue to [[Power Query – Automation and Parameters\|Module 4: Automation and Parameters]]


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