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.

Moduł 4: Zaawansowane transformacje danych w Power Query

Cel

W tym module nauczysz się:

• ✔ Jak pivotować i unpivotować dane w Power Query.
• ✔ Jak dzielić i scalać kolumny w celu lepszej strukturyzacji danych.
• ✔ Jak używać transformacji warunkowych.
• ✔ Jak wykorzystać ChatGPT do tworzenia złożonych skryptów M.

Dostarczone dane

Do tego ćwiczenia wprowadzimy nowy zbiór danych:

PQ_sales_pivot.csv, który zawiera miesięczne dane sprzedaży różnych produktów.

📂 Plik:PQ_sales_pivot.csv - Struktura danych w formacie pivot.

Instrukcje

Krok 1: Importuj dane Otwórz Power Query w Excelu lub Power BI. Zaimportuj plik PQ_sales_pivot.csv. Upewnij się, że tabela została poprawnie załadowana.
Krok 2: Unpivotowanie danych Obecna tabela ma szeroki format, który nie jest idealny do analizy. Unpivotuj kolumny miesięczne, aby dane miały strukturę:

• Product
• Category
• Month
• Sales Amount

Jak to zrobić:

1. Kliknij Transformuj → Użyj pierwszego wiersza jako nagłówków, aby upewnić się, że kolumny mają poprawne nazwy.
2. Zaznacz kolumny z miesiącami (Jan 2025, Feb 2025 itd.).
3. Kliknij Transformuj → Unpivotuj kolumny.
4. Zmień nazwy wynikowych kolumn:

"Attribute" → Month "Value" → Sales Amount
Krok 3: Dzielnie i scalanie kolumn

1. Kolumna Month obecnie zawiera wartości takie jak "Jan 2025".
2. Podziel tę kolumnę na Month Name i Year:
3. Zaznacz kolumnę Month.
4. Kliknij Transformuj → Podziel kolumnę → Według ogranicznika.
5. Wybierz spację (" ") jako ogranicznik.
6. Zmień nazwy nowych kolumn na Month Name i Year.
7. Przykład scalania kolumn:

Jeśli chcesz scalić Product i Category, zaznacz obie kolumny.

1. Kliknij Transformuj → Scal kolumny.
2. Użyj " - " jako separatora (np. "Monitor - Electronics").

Krok 4: Dodanie transformacji warunkowych

• Dodaj nową niestandardową kolumnę o nazwie "Sales Performance":

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

• Upewnij się, że kolumna poprawnie kategoryzuje wyniki sprzedaży.

Zadanie

• ✔ Wykonaj wszystkie kroki w Power Query.
• ✔ Eksperymentuj z unpivotowaniem, dzieleniem, scalaniem i logiką warunkową.
• ✔ Korzystaj z ChatGPT w celu rozwiązywania problemów lub poprawiania skryptu M.

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.