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 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.
Submission Guidelines
Submit your Power Query file or screenshots of your work along with a brief summary of the transformations applied.
