Power Query for Analysts: Difference between revisions

From Training Material
Jump to navigation Jump to search
Newer edit →
Gpruszczynski (talk | contribs)
Bureaucrats, coordinator, Administrators, trainer
702 edits
Created page with "== 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 CSV file named..."
 
Gpruszczynski (talk | contribs)
Bureaucrats, coordinator, Administrators, trainer
702 edits
Line 9: Line 9:
Use external help (e.g., ChatGPT) for hints on creating custom M code without directly copying solutions.
Use external help (e.g., ChatGPT) for hints on creating custom M code without directly copying solutions.
=== Provided Data ===
=== Provided Data ===
You are provided with a CSV file named sales.csv that contains sales order data. The data includes the following columns:
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)
OrderID (integer)
OrderDate (text, in the format dd/MM/yyyy)
OrderDate (text, in a non-standard date format)
Customer (text)
Customer (text)
Product (text)
Product (text)
Quantity (integer)
Quantity (integer)
Cost (number)
Cost (number)
The content of the file is as follows (40 rows):


<pre> OrderID,OrderDate,Customer,Product,Quantity,Cost 1001,15/01/2025,John Smith,Monitor,2,600 1002,18/01/2025,Alice Brown,Keyboard,1,150 1003,20/01/2025,John Smith,Mouse,1,75 1004,22/01/2025,Bob Johnson,Computer,1,3200 1005,25/01/2025,Alice Brown,Printer,1,850 1006,26/01/2025,Emily Davis,Laptop,1,1500 1007,27/01/2025,Michael Wilson,Smartphone,2,999 1008,28/01/2025,Olivia Martinez,Tablet,1,400 1009,01/02/2025,William Garcia,Headphones,3,90 1010,03/02/2025,Emma Rodriguez,Webcam,2,140 1011,05/02/2025,Daniel Lee,Monitor,1,300 1012,07/02/2025,Sophia Harris,Keyboard,2,100 1013,09/02/2025,Christopher Clark,Mouse,2,50 1014,11/02/2025,Isabella Lewis,Computer,1,2800 1015,13/02/2025,Matthew Walker,Printer,1,900 1016,15/02/2025,Charlotte Hall,Laptop,1,1400 1017,17/02/2025,David Allen,Smartphone,1,1200 1018,19/02/2025,Amelia Young,Tablet,2,750 1019,21/02/2025,Joseph Hernandez,Headphones,1,60 1020,23/02/2025,Abigail King,Webcam,1,130 1021,25/02/2025,Ethan Wright,Monitor,2,550 1022,27/02/2025,Mia Scott,Keyboard,1,80 1023,01/03/2025,Noah Green,Mouse,3,45 1024,03/03/2025,Liam Adams,Computer,1,3000 1025,05/03/2025,Harper Baker,Printer,1,800 1026,07/03/2025,James Nelson,Laptop,2,2800 1027,09/03/2025,Evelyn Carter,Smartphone,2,2100 1028,11/03/2025,Alexander Mitchell,Tablet,1,450 1029,13/03/2025,Sofia Perez,Headphones,2,120 1030,15/03/2025,Benjamin Roberts,Webcam,1,100 1031,17/03/2025,Chloe Turner,Monitor,1,350 1032,19/03/2025,Henry Phillips,Keyboard,2,90 1033,21/03/2025,Ella Campbell,Mouse,1,55 1034,23/03/2025,Jack Parker,Computer,1,3100 1035,25/03/2025,Grace Evans,Printer,1,950 1036,27/03/2025,Logan Edwards,Laptop,1,1600 1037,29/03/2025,Victoria Collins,Smartphone,1,1100 1038,31/03/2025,Samuel Stewart,Tablet,2,850 1039,01/04/2025,Avery Sanchez,Headphones,3,135 1040,03/04/2025,Leo Morris,Webcam,2,160 </pre>
=== Instructions ===
=== Instructions ===


Revision as of 11:14, 25 February 2025

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.

Retrieved from "https://training-course-material.com/index.php?title=Power_Query_for_Analysts&oldid=88547"