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.

Moduł 3: Tworzenie niestandardowych kolumn i funkcji

Cel

W tym ćwiczeniu nauczysz się:

Tworzyć niestandardowe kolumny obliczeniowe w Power Query.

Używać wbudowanych funkcji Power Query do manipulacji tekstem, liczbami i datami.

Pisać niestandardowe funkcje w języku M, aby automatyzować transformacje.

Korzystać z ChatGPT do pomocy przy pisaniu i optymalizacji kodu M.

Dostarczone dane

Do tego ćwiczenia wykorzystamy następujące zbiory danych:

📂Plik:PQ_sales2.csv (używany w poprzednich modułach)

📂Plik:PQ_discounts.csv (nowy zbiór danych) - zawiera stawki rabatowe w zależności od typu produktu.

Instrukcje

• Krok 1: Importuj dane

Otwórz Power Query w Excelu lub Power BI. Zaimportuj oba pliki: PQ_sales.csv i PQ_discounts.csv. Upewnij się, że obie tabele zostały załadowane poprawnie.

• Krok 2: Tworzenie niestandardowej kolumny dla całkowitego kosztu

W tabeli PQ_sales dodaj nową niestandardową kolumnę: Przejdź do Dodaj kolumnę → Kolumna niestandardowa. Nazwij ją TotalCost. Utwórz formułę do obliczenia całkowitego kosztu jako:

Quantity * Cost

Kliknij OK i sprawdź wyniki.

• Krok 3: Zastosowanie rabatów za pomocą scalania

Scal PQ_sales z PQ_discounts, używając kolumny Product jako klucza. Rozwiń kolumnę DiscountRate do tabeli PQ_sales. Dodaj kolejną niestandardową kolumnę o nazwie DiscountedPrice:

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

Sprawdź, czy nowa kolumna poprawnie stosuje rabaty.

• Krok 4: Tworzenie niestandardowej funkcji w M

Utwórz funkcję do kategoryzowania produktów w różne przedziały cenowe: Przejdź do Strona główna → Edytor zaawansowany. Napisz funkcję M, która przyjmuje Cost jako wejście i zwraca kategorię:

Low if Cost < 500 Medium if Cost between 500 and 1500 High if Cost > 1500

Wskazówka: Jeśli nie jesteś pewny, jak zbudować funkcję, zapytaj ChatGPT: "Jak napisać funkcję M, która kategoryzuje ceny na Low, Medium i High?"

• Krok 5: Przypisywanie kategorii

W tabeli PQ_sales dodaj niestandardową kolumnę, używając funkcji. Nazwij kolumnę PriceCategory. Upewnij się, że kategorie są wyświetlane poprawnie na podstawie wartości w kolumnie Cost.

Zadanie

• ✔ Wykonaj wszystkie kroki w Power Query.
• ✔ Eksperymentuj zarówno z interfejsem graficznym, jak i z kodem M.
• ✔ Korzystaj z ChatGPT w celu rozwiązywania problemów lub poprawiania skryptu M.

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.