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.

Moduł 5: Parametryzacja i dynamiczne zapytania w Power Query

Cel ćwiczenia

W tym module nauczysz się:

• ✔ Jak tworzyć parametry w Power Query.
• ✔ Jak używać parametrów do dynamicznego filtrowania i sterowania wynikami zapytań.
• ✔ Jak budować dynamiczne źródła danych w oparciu o dane wejściowe użytkownika.*✔ Jak wykorzystać ChatGPT do pisania i optymalizacji kodu M dla parametrów.

Dostarczone dane

W tym ćwiczeniu użyjemy pliku PQ_sales.csv oraz nowego zestawu danych PQ_parameters.xlsx, który zawiera wartości do dynamicznego filtrowania.

📂 File:PQ parameters.xlsx - Tabela z parametrami do filtrowania:

Instrukcje

Krok 1: Importowanie danych

Otwórz Power Query w Excelu lub Power BI.

Zaimportuj oba zestawy danych:

PQ_sales.csv (Dane transakcji sprzedaży).

PQ_parameters.xlsx (Parametry filtrowania).

Upewnij się, że tabele zostały poprawnie załadowane.

Krok 2: Tworzenie i używanie parametrów dynamicznie

Załaduj tabelę PQ_parameters.xlsx do Power Query.

Upewnij się, że tabela zawiera dwie kolumny: ParameterName (Nazwa parametru) i Value (Wartość).

Kliknij Przekształć → Użyj pierwszego wiersza jako nagłówków, aby poprawnie przypisać nazwy kolumn.

Przekształć tabelę w rekord, aby łatwo odwoływać się do wartości:

Zaznacz kolumnę ParameterName i wykonaj Przestawienie kolumny.

Kliknij Przekształć → Przestaw kolumnę, ustawiając Value jako kolumnę wartości.

Powinno to utworzyć rekord zawierający pola StartDate, EndDate, MinCost, MaxCost, FilePath (jeśli chcemy dynamicznie pobierać dane z innego pliku).

Upewnij się, że wszystkie wartości mają poprawny typ danych (Data dla StartDate i EndDate, Liczba dla MinCost i MaxCost, Tekst dla FilePath).

Teraz parametry są dynamicznie pobierane z pliku PQ_parameters.xlsx.

Krok 3: Zastosowanie parametrów do filtrowania danych

Filtrowanie danych sprzedaży według zakresu dat:

Otwórz zapytanie PQ_sales w Power Query.

Upewnij się, że kolumna OrderDate ma typ Data. Jeśli nie, zmień go na Data poprzez Przekształć → Typ danych → Data.

Kliknij ikonę filtra w kolumnie OrderDate.

Wybierz Filtry dat → Filtr niestandardowy.

W oknie filtrów ustaw:

Pierwszy warunek na jest po lub równe i odwołaj się do StartDate z rekordu.

Drugi warunek na jest przed lub równe i odwołaj się do EndDate z rekordu.

Kliknij OK, aby zastosować filtr.

Alternatywnie, możesz użyć wyrażenia M do filtrowania danych:

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

Filtrowanie danych sprzedaży według zakresu kosztów:

Upewnij się, że kolumna Cost ma typ Liczba.

Kliknij ikonę filtra w kolumnie Cost.

Wybierz Filtry liczb → Pomiędzy.

Ustaw pierwszy warunek na większe niż lub równe i odwołaj się do MinCost z rekordu.

Ustaw drugi warunek na mniejsze niż lub równe i odwołaj się do MaxCost z rekordu.

Kliknij OK, aby zastosować filtr.

Alternatywnie, możesz użyć wyrażenia M:

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

Krok 4: Tworzenie dynamicznego źródła danych

Dynamiczne pobieranie danych z pliku o ścieżce przechowywanej w parametrze:

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

Jeśli chcemy pobierać dane z pliku, którego ścieżka może się zmieniać, możemy dodać parametr FilePath do PQ_parameters.xlsx.

FilePath powinien zawierać pełną ścieżkę do pliku, np. C:\Dane\Raport.xlsx.

Po aktualizacji wartości w PQ_parameters.xlsx, Power Query automatycznie załaduje dane z nowej lokalizacji.

Zadanie

• ✔ Stwórz i wykorzystaj parametry do dynamicznego filtrowania danych.
• ✔ Zaimplementuj dynamiczne źródło danych wykorzystujące parametryzowaną ścieżkę pliku.
• ✔ Skorzystaj z ChatGPT do analizy i optymalizacji kodu M w Power Query.