Power Query for Analysts

Moduł 1: Wprowadzenie do Power Query i podstawowe transformacje

Cel

W tym ćwiczeniu nauczysz się:

Importować dane z pliku CSV do Power Query. Sprawdzać i dostosowywać typy danych, koncentrując się na konwersji daty zapisanej jako tekst na rzeczywisty typ daty. Zastosować podstawowe filtrowanie danych. Korzystać z pomocy zewnętrznej (np. ChatGPT) w celu uzyskania wskazówek dotyczących tworzenia niestandardowego kodu M, bez bezpośredniego kopiowania rozwiązań.

Dostarczone dane

Otrzymujesz do pobrania plik CSV o nazwie 📂Plik:PQ_sales.csv, który zawiera dane zamówień sprzedaży. Plik zawiera następujące kolumny:

• OrderID (liczba całkowita)
• OrderDate (tekst, w niestandardowym formacie daty)
• Customer (tekst)
• Product (tekst)
• Quantity (liczba całkowita)
• Cost (liczba)

Instrukcje

• Krok 1: Importuj dane

1. Otwórz Power Query w Excelu lub Power BI.
2. Zaimportuj dane z pliku sales.csv.
3. Zauważ, że kolumna OrderDate została zaimportowana jako tekst ze względu na swój format (dd/MM/yyyy).

• Krok 2: Sprawdź i przekonwertuj typy danych

1. Sprawdź, czy każda kolumna ma poprawny typ danych.
2. Ręcznie przekształć kolumnę OrderDate z tekstu na typ daty.
3. Wskazówka: Jeśli napotkasz trudności, możesz zapytać ChatGPT o wskazówki, jak napisać funkcję M do konwersji tekstu na datę.

• Krok 3: Zastosuj podstawowe filtrowanie

1. Filtruj zestaw danych, tak aby pozostały tylko wiersze, w których koszt jest większy niż 200.
2. Propozycja: Użyj interfejsu graficznego Power Query lub napisz prosty skrypt M, aby zastosować filtr. Jeśli to konieczne, skonsultuj się z ChatGPT w celu uzyskania pomysłów na implementację tego filtru.

• Krok 4: Przejrzyj i zapisz swoją pracę

1. Potwierdź, że transformacje zostały poprawnie zastosowane, przeglądając podgląd danych.
2. Zapisz zapytanie i udokumentuj kroki, które podjąłeś.

Zadanie

Wykonaj kroki opisane powyżej w Power Query. Eksperymentuj z dostępnymi opcjami transformacji i staraj się zrozumieć, jak każdy krok wpływa na Twoje dane. Używaj ChatGPT w celu uzyskania wskazówek lub rozwiązywania problemów, ale unikaj kopiowania kompletnych rozwiązań dosłownie.

Moduł 2: Łączenie i scalanie danych z wielu źródeł

Cel

W tym ćwiczeniu nauczysz się:

Importować dane z wielu plików CSV do Power Query. Scalić (połączyć) dane z różnych źródeł na podstawie wspólnego klucza. Użyć lewego zewnętrznego łączenia (Left Outer Join) do dodania szczegółów klienta do zamówień sprzedaży. Korzystać z pomocy zewnętrznej (np. ChatGPT) w celu uzyskania wskazówek dotyczących pisania niestandardowego kodu M, bez kopiowania kompletnych rozwiązań. === Dostarczone dane === Otrzymujesz do pobrania dwa pliki CSV:

📂 Plik:PQ_sales2.csv – Zawiera dane zamówień sprzedaży:

📂 Plik:PQ_customers.csv – Zawiera informacje o klientach:

Instrukcje

Krok 1: Importuj dane Otwórz Power Query w Excelu lub Power BI. Zaimportuj dane z obu plików PQ_sales.csv i PQ_customers.csv. Sprawdź, czy oba zapytania zostały załadowane poprawnie.

Krok 2: Sprawdź i przekonwertuj typy danych Potwierdź, że każda kolumna ma odpowiedni typ danych w obu zapytaniach. Na przykład, zauważ, że kolumna OrderDate w PQ_sales.csv jest zaimportowana jako tekst z powodu niestandardowego formatu. Wskazówka: Użyj funkcji transformacji, jeśli jakiekolwiek dostosowania są potrzebne.

Krok 3: Scal dane Scal zapytanie PQ_sales.csv z zapytaniem PQ_customers.csv. Użyj kolumny Customer jako klucza dopasowania. Wybierz lewy zewnętrzny join (Left Outer Join), aby każde zamówienie sprzedaży zostało zachowane wraz z odpowiednimi szczegółami klienta. Propozycja: Jeśli nie wiesz, jak napisać kod M do tego scalenia, zapytaj ChatGPT o wskazówki dotyczące scalania zapytań.

Krok 4: Przejrzyj scalone dane Potwierdź, że wynikowe zapytanie zawiera dodatkowe kolumny (np. Region, CustomerSince) z pliku PQ_customers.csv. Sprawdź scalone dane, aby upewnić się, że szczegóły klientów zostały poprawnie połączone z odpowiednimi zamówieniami sprzedaży.

Krok 5: Zapisz swoją pracę Zapisz swoje zapytanie i udokumentuj kroki transformacji, które zastosowałeś.

Zadanie

Wykonaj kroki opisane powyżej w Power Query. Eksperymentuj z interfejsem graficznym i niestandardowym kodem M w celu wykonania scalania. Korzystaj z zasobów zewnętrznych (np. ChatGPT) w celu uzyskania wskazówek lub rozwiązywania problemów, ale unikaj kopiowania kompletnych rozwiązań dosłownie.

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:

📂File:PQ sales2.csv (used in previous modules)

📂File:PQ discounts.csv (new dataset) - contains discount rates based on product type.

Instructions

• Step 1: Import the Data

1. Open Power Query in Excel or Power BI.
2. Import both PQ_sales.csv and PQ_discounts.csv.
3. Ensure that both tables are loaded correctly.

• Step 2: Creating a Custom Column for Total Cost

1. In the PQ_sales table, add a new custom column:
2. Navigate to Add Column → Custom Column.
3. Name it TotalCost.
4. Create a formula to calculate the total cost as:

Quantity * Cost

1. Click OK and check the results.

• Step 3: Applying Discounts Using a Merge

1. Merge PQ_sales with PQ_discounts using the Product column as the key.
2. Expand the DiscountRate column into PQ_sales.
3. Add another custom column called DiscountedPrice:

[TotalCost] - ([TotalCost] * [DiscountRate])

1. Verify that the new column correctly applies the discounts.

• Step 4: Creating a Custom Function in M

1. Create a function to categorize products into different price ranges:
2. Navigate to Home → Advanced Editor.
3. Write an M function that takes Cost as an input and returns a category:

Low if Cost < 500

1. 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

1. In the PQ_sales table, add a custom column using the function.
2. Name the column PriceCategory.
3. 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.

Module 4: Advanced Data Transformations in Power Query

Objective

In this module, you will learn:

• ✔ How to pivot and unpivot data in Power Query.
• ✔ How to split and merge columns for better data structuring.
• ✔ How to use conditional transformations.
• ✔ How to leverage ChatGPT for complex M scripting.

Provided Data

For this exercise, we will introduce a new dataset: PQ_sales_pivot.csv, which contains monthly sales figures for different products.

📂 File:PQ sales pivot.csv - Pivoted data structure:

Instructions

• Step 1: Import the Data

1. Open Power Query in Excel or Power BI.
2. Import the file PQ_sales_pivot.csv.
3. Ensure that the table is loaded correctly.

• Step 2: Unpivot the Data

1. The current table has a wide format, which is not ideal for analysis.
2. Unpivot the month columns so that the data is structured as:

• Product
• Category
• Month
• Sales Amount

How to do this:

1. Click Transform → Use First Row as Headers to ensure the correct column names.
2. Select the month columns (Jan 2025, Feb 2025, etc.).
3. Click Transform → Unpivot Columns.
4. Rename the resulting columns:
5. "Attribute" → Month
6. "Value" → Sales Amount

• Step 3: Splitting and Merging Columns

1. The Month column currently has values like "Jan 2025".
2. Split this column into Month Name and Year:
3. Select the Month column.
4. Click Transform → Split Column → By Delimiter.
5. Choose the space (" ") delimiter.
6. Rename the new columns as Month Name and Year.

Merge Columns Example:

If you want to merge Product and Category, select both.

1. Click Transform → Merge Columns.
2. Use " - " as a separator (e.g., "Monitor - Electronics").

• Step 4: Adding Conditional Transformations

Add a new custom column called "Sales Performance":

if [Sales Amount] < 300 then "Low"
else if [Sales Amount] >= 300 and [Sales Amount] < 800 then "Medium"
else "High"

Ensure that the column correctly categorizes sales performance.

Task

• ✔ Complete all steps in Power Query.
• ✔ Experiment with unpivoting, splitting, merging, and conditional logic.
• ✔ Use ChatGPT to troubleshoot or improve your M script.

Module 5: Parameterization and Dynamic Queries in Power Query

=== Objective ===In this module, you will learn:*✔ How to create parameters in Power Query.*✔ How to use parameters to filter and control query results dynamically.*✔ How to build dynamic data sources based on user input.*✔ How to leverage ChatGPT for writing and optimizing M code for parameterization.

=== Provided Data ===For this exercise, we will use the PQ_sales.csv file and introduce a new dataset PQ_parameters.xlsx, which contains dynamic filter values.

📂 File:PQ parameters.xlsx - Parameter table for dynamic filtering:

Instructions

Step 1: Import the Data

1. Open Power Query in Excel or Power BI.
2. Import both datasets:

PQ_sales.csv (Sales transactions)

PQ_parameters.xlsx (Filter parameters)

1. Ensure that the tables load correctly.

Step 2: Creating and Using Parameters Dynamically

1. Load the PQ_parameters.xlsx table into Power Query.
2. Ensure that the table has two columns: ParameterName and Value.
3. Click on Transform → Use First Row as Headers to make sure column names are correctly applied.
4. Convert the table into a record for easy reference:

Select the ParameterName column and pivot it so that each parameter becomes a column.

Click Transform → Pivot Column, setting Value as the values column.

This should create a record with fields StartDate, EndDate, MinCost, MaxCost.

1. Ensure that all values are in the correct data type (Date for StartDate and EndDate, Number for MinCost and MaxCost).
2. Now, the parameters are dynamically retrieved from the PQ_parameters.xlsx file.

Step 3: Applying Parameters to Filter Data

Filter Sales Data Based on Date Range:

1. Open the PQ_sales query in Power Query.
2. Ensure that the OrderDate column is in Date format. If not, change it to Date using "Transform → Data Type → Date".
3. Click on the filter dropdown for the OrderDate column.
4. Select "Date Filters → Custom Filter".
5. In the filter conditions:

Set the first condition to is after or equal to and reference the StartDate from the record.

Set the second condition to is before or equal to and reference the EndDate from the record.

1. Click OK to apply the filter.
2. Alternatively, use an M expression to filter data:

Table.SelectRows(PQ_sales, each [OrderDate] >= Parameters[StartDate] and [OrderDate] <= Parameters[EndDate])

Filter Sales Data Based on Cost Range:

1. Ensure that the Cost column is in Number format.
2. Click on the filter dropdown for the Cost column.
3. Select "Number Filters → Between".
4. Set the first condition to greater than or equal to and reference the MinCost from the record.
5. Set the second condition to less than or equal to and reference the MaxCost from the record.
6. Click OK to apply the filter.
7. Alternatively, use an M expression:

Table.SelectRows(PQ_sales, each [Cost] >= Parameters[MinCost] and [Cost] <= Parameters[MaxCost])

Step 4: Building a Dynamic Data Source

Make the File Path Dynamic:

Source = Excel.Workbook(File.Contents(Parameters[FilePath]), null, true)

1. Instead of using a static file path, create a parameter for the file location.
2. Where FilePath is a user-defined parameter stored in PQ_parameters.xlsx.

Task

• ✔ Create and use parameters to filter the data dynamically.
• ✔ Implement a dynamic data source with a parameterized file path.
• ✔ Use ChatGPT to troubleshoot or improve your M script.