Power Query for Analysts
Module 1: Introduction to Power Query and Basic Transformations
Objective
In this exercise you will learn to:
Import data from a CSV file into Power Query. Examine and adjust data types, focusing on converting a text-formatted date into an actual date type. Apply basic filtering to the data. Use external help (e.g., ChatGPT) for hints on creating custom M code without directly copying solutions.
Provided Data
You are provided with a downloadable CSV file named File:PQ sales.csv that contains sales order data. The file includes the following columns:
- OrderID (integer)
- OrderDate (text, in a non-standard date format)
- Customer (text)
- Product (text)
- Quantity (integer)
- Cost (number)
Instructions
- Step 1: Import the Data
Open Power Query in Excel or Power BI. Import the data from the sales.csv file. Observe that the OrderDate column is imported as text due to its format (dd/MM/yyyy).
- Step 2: Check and Convert Data Types
Verify that each column has the correct data type. Manually convert the OrderDate column from text to date type. Hint: If you have trouble, consider asking ChatGPT for guidance on how to write an M function for converting text to a date.
- Step 3: Apply Basic Filtering
Filter the dataset so that only rows where Cost is greater than 200 remain. Suggestion: Use the graphical interface of Power Query or write a simple M script to apply the filter. If needed, consult ChatGPT for ideas on how to implement this filter.
- Step 4: Review and Save Your Work
Confirm that the transformations have been applied correctly by reviewing the data preview. Save your query and document the steps you took.
Task
Complete the steps outlined above in Power Query. Experiment with the transformation options available and try to understand how each step affects your data. Use ChatGPT for hints or troubleshooting, but avoid copying complete solutions verbatim.
Module 2: Combining and Merging Data from Multiple Sources
Objective
In this exercise you will learn to:
Import data from multiple CSV files into Power Query. Merge (join) data from different sources based on a common key. Use a Left Outer Join to add customer details to sales orders. Leverage external help (e.g., ChatGPT) for hints on writing custom M code without copying complete solutions.
Provided Data
You are provided with two downloadable CSV files:
- File:PQ sales2.csv – Contains sales order data:
- File:PQ customers.csv – Contains customer information:
Instructions
- Step 1: Import the Data
Open Power Query in Excel or Power BI. Import data from both PQ_sales.csv and PQ_customers.csv. Verify that both queries load correctly.
- Step 2: Check and Convert Data Types
Confirm that each column has the appropriate data type in both queries. For example, note that the OrderDate column in PQ_sales.csv is imported as text due to its non-standard format. Hint: Use transformation functions if any adjustments are needed.
- Step 3: Merge the Data
Merge the PQ_sales.csv query with the PQ_customers.csv query. Use the Customer column as the matching key. Select a Left Outer Join so that every sales order is retained along with its corresponding customer details. Suggestion: If you’re unsure how to write the M code for this merge, ask ChatGPT for guidance on merging queries.
- Step 4: Review the Merged Data
Confirm that the resulting query includes additional columns (e.g., Region, CustomerSince) from the PQ_customers.csv file. Inspect the merged data to ensure that customer details have been correctly joined to the appropriate sales orders.
- Step 5: Save Your Work
Save your query and document the transformation steps you applied.
Task
Complete the steps outlined above using Power Query. Experiment with both the graphical interface and custom M code to perform the merge. Use external resources (e.g., ChatGPT) for hints or troubleshooting, but avoid copying complete solutions verbatim.
Module 3: Creating Custom Columns and Functions
Objective
In this exercise, you will learn to:
Create custom calculated columns using Power Query.
Use built-in Power Query functions to manipulate text, numbers, and dates.
Write custom functions in the M language to automate transformations.
Utilize ChatGPT to assist with writing and optimizing M code.
Provided Data
For this exercise, we will use the following datasets:
PQ_sales.csv (used in previous modules)
PQ_discounts.csv (new dataset) - contains discount rates based on product type.
📂 PQ_discounts.csv - Discount rates per product:
Instructions
- Step 1: Import the Data
Open Power Query in Excel or Power BI.
Import both PQ_sales.csv and [PQ_discounts.csv].
Ensure that both tables are loaded correctly.
- Step 2: Creating a Custom Column for Total Cost
In the PQ_sales table, add a new custom column:
Navigate to Add Column → Custom Column.
Name it TotalCost.
Create a formula to calculate the total cost as:
Quantity * Cost
Click OK and check the results.
- Step 3: Applying Discounts Using a Merge
Merge PQ_sales with PQ_discounts using the Product column as the key.
Expand the DiscountRate column into PQ_sales.
Add another custom column called DiscountedPrice:
[TotalCost] - ([TotalCost] * [DiscountRate])
Verify that the new column correctly applies the discounts.
- Step 4: Creating a Custom Function in M
Create a function to categorize products into different price ranges:
Navigate to Home → Advanced Editor.
Write an M function that takes Cost as an input and returns a category:
Low if Cost < 500
Medium if Cost is between 500 and 1500
High if Cost > 1500
Hint: If you're unsure how to structure the function, ask ChatGPT:"How do I write an M function that categorizes prices into Low, Medium, and High?"
- Step 5: Assigning Categories
In the PQ_sales table, add a custom column using the function.
Name the column PriceCategory.
Ensure that the categories appear correctly based on the Cost value.
Task
- ✔ Complete all steps in Power Query.
- ✔ Experiment with both the graphical interface and M code.
- ✔ Use ChatGPT to troubleshoot or improve your M script.
