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:

 OrderID,OrderDate,Customer,Product,Quantity,Cost 1001,25 01 15,John Smith,Monitor,2,600 1002,25 01 18,Alice Brown,Keyboard,1,150 1003,25 01 20,John Smith,Mouse,1,75 1004,25 01 22,Bob Johnson,Computer,1,3200 1005,25 01 25,Alice Brown,Printer,1,850 1006,25 01 26,Emily Davis,Laptop,1,1500 1007,25 01 27,Michael Wilson,Smartphone,2,999 

File:PQ customers.csv – Contains customer information:

 Customer,Region,CustomerSince John Smith,North,2019-05-01 Alice Brown,South,2020-03-15 Bob Johnson,West,2018-11-20 Emily Davis,East,2021-07-10 Michael Wilson,North,2017-09-05 

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.