Power Query for Analysts: Difference between revisions

From Training Material
Jump to navigation Jump to search
← Older edit
Gpruszczynski (talk | contribs)
Bureaucrats, coordinator, Administrators, trainer
702 edits
Gpruszczynski (talk | contribs)
Bureaucrats, coordinator, Administrators, trainer
702 edits
No edit summary
Tag: Manual revert
 
(99 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Module 1: Introduction to Power Query and Basic Transformations ==
{{Cat|Power Query|001}}
== Modul 1: Wprowadzenie do Power Query i podstawowe transformacje ==


=== Objective ===
=== Cel ===
In this exercise you will learn to:
W tym ćwiczeniu nauczysz się:


Import data from a CSV file into Power Query.
Importować dane z pliku CSV do Power Query.
Examine and adjust data types, focusing on converting a text-formatted date into an actual date type.
Sprawdzać i dostosowywać typy danych, koncentrując się na konwersji daty zapisanej jako tekst na rzeczywisty typ daty.
Apply basic filtering to the data.
Zastosować podstawowe filtrowanie danych.
Use external help (e.g., ChatGPT) for hints on creating custom M code without directly copying solutions.
Korzystać z pomocy zewnętrznej (np. ChatGPT) w celu uzyskania wskazówek dotyczących tworzenia niestandardowego kodu M, bez bezpośredniego kopiowania rozwiązań.
=== Provided Data ===
=== Dostarczone dane ===
You are provided with a downloadable CSV file named 📂[[File:PQ_sales.csv]] that contains sales order data. The file includes the following columns:
Otrzymujesz do pobrania plik CSV o nazwie
📂[[Media:PQsales.csv]]  
który zawiera dane zamówień sprzedaży.


*OrderID (integer)
=== Instrukcje ===
*OrderDate (text, in a non-standard date format)
*Customer (text)
*Product (text)
*Quantity (integer)
*Cost (number)


=== Instructions ===
* Krok 1: Importuj dane


* Step 1: Import the Data
#Otwórz Power Query w Excelu lub Power BI.
#Zaimportuj dane z pliku sales.csv.
#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


#Open Power Query in Excel or Power BI.
#Sprawdź, czy każda kolumna ma poprawny typ danych.
#Import the data from the sales.csv file.
#Ręcznie przekształć kolumnę OrderDate z tekstu na typ daty.
#Observe that the OrderDate column is imported as text due to its format (dd/MM/yyyy).
#Wskazówka: Jeśli napotkasz trudności, możesz zapytać ChatGPT o wskazówki, jak napisać funkcję M do konwersji tekstu na datę.
* Step 2: Check and Convert Data Types
* Krok 3: Zastosuj podstawowe filtrowanie


#Verify that each column has the correct data type.
#Filtruj zestaw danych, tak aby pozostały tylko wiersze, w których koszt jest większy niż 200.
#Manually convert the OrderDate column from text to date type.
#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.
#Hint: If you have trouble, consider asking ChatGPT for guidance on how to write an M function for converting text to a date.
* Krok 4: Przejrzyj i zapisz swoją pracę
* Step 3: Apply Basic Filtering


#Filter the dataset so that only rows where Cost is greater than 200 remain.
#Potwierdź, że transformacje zostały poprawnie zastosowane, przeglądając podgląd danych.
#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.
#Zapisz zapytanie i udokumentuj kroki, które podjąłeś.
* Step 4: Review and Save Your Work


#Confirm that the transformations have been applied correctly by reviewing the data preview.
=== Zadanie ===
#Save your query and document the steps you took.


=== Task ===
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.


Complete the steps outlined above in Power Query.
== Modul 2: Laczenie i scalanie danych z wielu zrodel ==
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.


=== Cel ===


== Module 2: Combining and Merging Data from Multiple Sources ==
W tym ćwiczeniu nauczysz się:


=== Objective ===
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ń.
In this exercise you will learn to:
=== Dostarczone dane ===  
Otrzymujesz do pobrania dwa pliki CSV:


Import data from multiple CSV files into Power Query.
📂 [[Media:PQ_sales2.csv]] – Zawiera dane zamówień sprzedaży:
Merge (join) data from different sources based on a common key.
Use a Left Outer Join to add customer details to sales orders.
Leverage external help (e.g., ChatGPT) for hints on writing custom M code without copying complete solutions.
=== Provided Data ===
You are provided with two downloadable CSV files:


📂 [[File:PQ_sales2.csv]] – Contains sales order data:
📂 [[Media:PQ_customers.csv]] – Zawiera informacje o klientach:
 
📂 [[File:PQ_customers.csv]] – Contains customer information:
=== Instrukcje ===


=== Instructions ===
*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.


* Step 1: Import the Data
*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.


Open Power Query in Excel or Power BI.
*Krok 3: Scal dane
Import data from both PQ_sales.csv and PQ_customers.csv.
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ń.
Verify that both queries load correctly.
* Step 2: Check and Convert Data Types


Confirm that each column has the appropriate data type in both queries.
*Krok 4: Przejrzyj scalone dane
For example, note that the OrderDate column in PQ_sales.csv is imported as text due to its non-standard format.
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.
Hint: Use transformation functions if any adjustments are needed.
* Step 3: Merge the Data


Merge the PQ_sales.csv query with the PQ_customers.csv query.
*Krok 5: Zapisz swoją pracę
Use the Customer column as the matching key.
Zapisz swoje zapytanie i udokumentuj kroki transformacji, które zastosowałeś.
Select a Left Outer Join so that every sales order is retained along with its corresponding customer details.
Suggestion: If you’re unsure how to write the M code for this merge, ask ChatGPT for guidance on merging queries.
* Step 4: Review the Merged Data


Confirm that the resulting query includes additional columns (e.g., Region, CustomerSince) from the PQ_customers.csv file.
=== Zadanie ===
Inspect the merged data to ensure that customer details have been correctly joined to the appropriate sales orders.
* Step 5: Save Your Work


Save your query and document the transformation steps you applied.
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.


=== Task ===
== Modul 3: Tworzenie niestandardowych kolumn i funkcji ==


Complete the steps outlined above using Power Query.
=== Cel ===
Experiment with both the graphical interface and custom M code to perform the merge.
W tym ćwiczeniu nauczysz się:
Use external resources (e.g., ChatGPT) for hints or troubleshooting, but avoid copying complete solutions verbatim.


== Module 3: Creating Custom Columns and Functions ==
Tworzyć niestandardowe kolumny obliczeniowe w Power Query.


=== Objective ===
Używać wbudowanych funkcji Power Query do manipulacji tekstem, liczbami i datami.
In this exercise, you will learn to:


Create custom calculated columns using Power Query.
Pisać niestandardowe funkcje w języku M, aby automatyzować transformacje.


Use built-in Power Query functions to manipulate text, numbers, and dates.
Korzystać z ChatGPT do pomocy przy pisaniu i optymalizacji kodu M.


Write custom functions in the M language to automate transformations.
=== Dostarczone dane ===


Utilize ChatGPT to assist with writing and optimizing M code.
Do tego ćwiczenia wykorzystamy następujące zbiory danych:


=== Provided Data ===
📂[[Media:PQ_sales2.csv]] (używany w poprzednich modułach)


For this exercise, we will use the following datasets:
📂[[Media:PQ_discounts.csv]] (nowy zbiór danych) - zawiera stawki rabatowe w zależności od typu produktu.


📂[[File:PQ_sales2.csv]] (used in previous modules)
=== Instrukcje ===


📂[[File:PQ_discounts.csv]] (new dataset) - contains discount rates based on product type.
*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.


=== Instructions ===


* Step 1: Import the Data
<br>
*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.
 
 
<br>
*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])


# Open Power Query in Excel or Power BI.
Sprawdź, czy nowa kolumna poprawnie stosuje rabaty.
# Import both PQ_sales.csv and PQ_discounts.csv.
# Ensure that both tables are loaded correctly.
<br>
<br>
*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?" <br>


* Step 2: Creating a Custom Column for Total Cost
*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.


# In the PQ_sales table, add a new custom column:
=== Zadanie ===
# Navigate to Add Column → Custom Column.
*✔ Wykonaj wszystkie kroki w Power Query.
# Name it TotalCost.
*✔ Eksperymentuj zarówno z interfejsem graficznym, jak i z kodem M.
# Create a formula to calculate the total cost as:
*✔ Korzystaj z ChatGPT w celu rozwiązywania problemów lub poprawiania skryptu M.
  Quantity * Cost
 
#Click OK and check the results.
== Modul 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.
 
'''📂 [[Media: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.
<br>
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
<br>
<br>


* Step 3: Applying Discounts Using a Merge
Jak to zrobić:


#Merge PQ_sales with PQ_discounts using the Product column as the key.
#Kliknij Transformuj → Użyj pierwszego wiersza jako nagłówków, aby upewnić się, że kolumny mają poprawne nazwy.
#Expand the DiscountRate column into PQ_sales.
#Zaznacz kolumny z miesiącami (Jan 2025, Feb 2025 itd.).
#Add another custom column called DiscountedPrice:
#Kliknij Transformuj → Unpivotuj kolumny.
[TotalCost] - ([TotalCost] * [DiscountRate])
#Zmień nazwy wynikowych kolumn:
#Verify that the new column correctly applies the discounts.
"Attribute" → Month
"Value" → Sales Amount
<br>
<br>
Krok 3: Dzielnie i scalanie kolumn
#Kolumna Month obecnie zawiera wartości takie jak "Jan 2025".
#Podziel tę kolumnę na Month Name i Year:
#Zaznacz kolumnę Month.
#Kliknij Transformuj → Podziel kolumnę → Według ogranicznika.
#Wybierz spację (" ") jako ogranicznik.
#Zmień nazwy nowych kolumn na Month Name i Year.
*Przykład scalania kolumn:


* Step 4: Creating a Custom Function in M
Jeśli chcesz scalić Product i Category, zaznacz obie kolumny.
#Create a function to categorize products into different price ranges:
#Navigate to Home → Advanced Editor.
#Write an M function that takes Cost as an input and returns a category:
Low if Cost < 500
#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?"
#Kliknij Transformuj → Scal kolumny.
#Użyj " - " jako separatora (np. "Monitor - Electronics").
<br>
<br>
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.
== Modul 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ń.
=== Dostarczone dane ===
W tym ćwiczeniu użyjemy pliku:
* '''📂 [[Media:PQ_sales2.csv]]'''
oraz nowego zestawu danych
* '''📂 [[Media:PQ_parameters.xlsx]]''', który zawiera wartości do dynamicznego filtrowania.
=== Instrukcje ===
* 🔹 Krok 1: Załaduj plik CSV do Power Query
# Otwórz Excela i przejdź do „Dane” → „Pobierz dane” → „Z pliku” → „Z pliku CSV”.
# Wybierz plik `PQ_sales.csv` i kliknij „Załaduj do Power Query”.
# Po załadowaniu pliku upewnij się, że Power Query rozpoznał poprawnie dane.
*🔹 Krok 2: Przetwórz tabelę `Parameters`
# W Power Query przejdź do tabeli **`Parameters`**.
# **Transponuj tabelę** – kliknij **„Przekształć” → „Transponuj”**.
# **Ustaw pierwszy wiersz jako nagłówki** – kliknij **„Przekształć” → „Użyj pierwszego wiersza jako nagłówków”**.
# **Zmień typy danych** dla `startDate` i `endDate` na **Date**:
## Kliknij nagłówek `startDate` → wybierz typ `Date`.
## Powtórz dla `endDate`.
* 🔹 Krok 3: Utwórz osobne zapytania dla `startDate` i `endDate`
# W tabeli `Parameters` kliknij prawym przyciskiem myszy na wartość w `startDate` → **„Dodaj jako nowe zapytanie”** (`Add as new query`).
# Powtórz tę operację dla `endDate`.
* 🔹 Krok 4: Zmień typ danych w kolumnie `OrderDate` w tabeli `PQ Sales` na date
# Wróć do zapytania `PQ Sales`.
# Kolumna `OrderDate` zawiera daty w formacie `DD MM YY`.
# **Rozdziel ją na trzy kolumny**:
## Kliknij **„Przekształć” → „Podziel kolumnę” → „Według ogranicznika”**.
## Wybierz **spację** (` `) jako ogranicznik.
## Powstają trzy nowe kolumny: `OrderDate.1`, `OrderDate.2`, `OrderDate.3` (dzień, miesiąc, rok).
# **Zmień typy danych** tych kolumn na `Number` (`Int64.Type`).
# **Połącz je w poprawny format `YYYY-MM-DD`**:
## Kliknij **„Scal kolumny”** (`Merge Columns`).
## Wybierz kolejność: `OrderDate.2`, `OrderDate.3`, `OrderDate.1` (miesiąc, rok, dzień).
## Ustaw separator `/`.
## Zmień nazwę kolumny na `DateOrder`.
## Zmień jej typ na **Date**.
* 🔹 Krok 5: Dodanie dynamicznego filtra do `DateOrder`
# Otwórz **Edytor Zaawansowany** (`View` → `Advanced Editor`).
# Znajdź ostatni krok przed `in`:
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Merged", "DateOrder"}})
* 🔹 Krok 6 :Dodaj filtr:
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DateOrder] >= startDate and [DateOrder] <= endDate)
Upewnij się, że startDate i endDate są w formacie Date.
Zmień końcową wartość in, aby zwracała #"Filtered Rows":
in
    #"Filtered Rows"
* 🔹 Krok 7: Sprawdzenie wyników
#Kliknij „Gotowe” (Done).
#Sprawdź, czy dane są filtrowane poprawnie.
#Kliknij „Zamknij i Załaduj” (Close & Load), aby zapisać dane w Excelu
== Modul 6: Automatyzacja Laczenia i Odswiezania Danych w Power Query ==
=== Cel ===
W tym module nauczysz się:
*✔ Jak automatycznie importować i łączyć pliki z folderu.
*✔ Jak radzić sobie z różnymi nazwami kolumn w różnych plikach.
*✔ Jak przygotować dane do raportowania niezależnie od struktury źródłowych plików.
*✔ Jak ustawić automatyczne odświeżanie danych w Power Query.
=== Dostarczone dane ===
W tym ćwiczeniu użyjemy zestawu wielu plików sprzedażowych, które znajdują się w jednym folderze.
'''📂 [[Media:Sales_Jan.xlsx]]''' – Sprzedaż za styczeń
'''📂 [[Media:Sales_Feb.xlsx]]''' – Sprzedaż za luty
'''📂 [[Media:Sales_Mar.xlsx]]''' – Sprzedaż za marzec
Każdy plik zawiera podobne dane, ale ma inne nazwy kolumn dla wartości sprzedaży:
W Sales_Jan.xlsx kolumna sprzedaży to Total Sale.
W Sales_Feb.xlsx kolumna sprzedaży to Revenue.
W Sales_Mar.xlsx kolumna sprzedaży to SalesAmount.
Celem jest połączenie tych plików w jeden zbiór i dostosowanie nazw kolumn, aby były spójne.
=== Instrukcje ===
*Krok 1: Pobranie plików z folderu
#Otwórz Power Query w Excelu.
#Wybierz Dane → Pobierz dane → Z pliku → Z folderu.
#Wskaż folder, w którym znajdują się pliki sprzedażowe (Sales_Jan.xlsx, Sales_Feb.xlsx, Sales_Mar.xlsx).
#Kliknij Załaduj (Load), aby dodać pliki do Power Query bez automatycznego łączenia.
*Krok 2: Zastosowanie kodu M do załadowania danych
#Otwórz Zaawansowany Edytor w Power Query.
#Wklej poniższy kod M i kliknij Gotowe:
let
    // Wczytanie plików z folderu
    Source = Folder.Files("C:\Users\pathToFolder..."),
   
    // Dodanie kolumny, która otworzy zawartość pliku Excel
    AddContent = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
   
    // Rozwinięcie zawartości, aby zobaczyć wszystkie dane
    ExpandContent = Table.ExpandTableColumn(AddContent, "Custom", {"Name", "Data"}, {"File Name", "Data"})
in
    ExpandContent
#Po zastosowaniu kodu zobaczysz tabelę z nową kolumną Data.
*Krok 3: Rozwinięcie zawartości tabel
#Kliknij ikonę rozwinięcia obok kolumny Data, aby zobaczyć pełne dane z plików.
#Upewnij się, że wszystkie kolumny z różnych plików są widoczne.
*Krok 4: Usunięcie zbędnych kolumn
#Sprawdź tabelę i usuń niepotrzebne kolumny (np. kolumny techniczne dotyczące plików, które nie są wymagane do analizy).
#Kliknij Przekształć → Usuń kolumny i usuń zbędne kolumny.
*Krok 5: Nadanie nazw kolumnom
#Ustaw własne nazwy kolumn zgodnie ze spójnym formatem (np. Total Sale → Sales).
#Kliknij Przekształć → Zmień nazwę kolumny dla każdej kolumny, aby nadać im jednolitą strukturę.
*Krok 6: Usunięcie zbędnych wierszy (powtórzeń nagłówków)
#Zastosuj filtr na kolumnie zawierającej wartości sprzedaży.
#Usuń wiersze zawierające powtórzenia nagłówków, które mogą występować z powodu łączenia wielu plików.
#Kliknij Przekształć → Usuń wiersze → Usuń duplikaty lub zastosuj filtr manualnie.
*Krok 7: Automatyczne odświeżanie danych
#Przejdź do Dane → Właściwości zapytania → Odświeżaj dane przy otwieraniu pliku.
#Możesz także ustawić automatyczne odświeżanie co X minut.
#Jeśli dodasz nowy plik do folderu (np. Sales_Apr.xlsx), Power Query automatycznie pobierze dane z nowego pliku po odświeżeniu!
=== Zadanie ===
*✔ Załaduj i połącz dane z Sales_Jan.xlsx, Sales_Feb.xlsx, Sales_Mar.xlsx.
*✔ Ujednolić nazwy kolumn i przekształcić dane w jednolity format.
*✔ Usuń puste wiersze, zbędne kolumny i duplikaty.
*✔ Ustaw automatyczne odświeżanie, aby nowe pliki były automatycznie dołączane.
*✔ Skorzystaj z ChatGPT do optymalizacji kodu M w Power Query.
== Modul 7: Optymalizacja Wydajnosci Zapytan w Power Query ==
=== Cel  ===
W tym module nauczysz się:
*✔ Jak przyspieszyć działanie Power Query w przypadku dużych zbiorów danych.
*✔ Jak unikać nieefektywnych operacji, które spowalniają zapytania.
*✔ Jak korzystać z funkcji buforowania i operacji na poziomie bazy danych.
*✔ Jak minimalizować ilość przetwarzanych danych dla lepszej wydajności.
=== Wprowadzenie ===
Power Query pozwala na transformację danych, ale przy dużych zbiorach może działać wolno. W tym module nauczysz się optymalnych praktyk, które pozwolą skrócić czas przetwarzania zapytań.
=== Instrukcje ===
*Krok 1: Unikanie niepotrzebnych operacji na całym zbiorze danych
#Wczytaj duży plik CSV 📂[[Media:PQSales_Large.csv]].
#Sprawdź liczbę wierszy i kolumn – im więcej danych, tym większe znaczenie ma optymalizacja.
#Usuń zbędne kolumny na początku zapytania, zamiast na końcu.
#Użyj Filtrowania wczesnego – zastosuj filtr, aby ograniczyć liczbę przetwarzanych wierszy od razu po imporcie.
*Krok 2: Używanie funkcji buforowania (Table.Buffer)
#Sprawdź, jak działa przetwarzanie kroków – każda operacja może powodować ponowne przeliczenie całego zapytania.
#Dodaj krok Table.Buffer() po filtrze, aby zapobiec ponownemu przetwarzaniu danych:
let
    Source = Csv.Document(File.Contents("C:\Users\gp\Desktop\PQ\Sales_Large.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    FilteredRows = Table.SelectRows(Source, each [Cost] > 500),
    BufferedData = Table.Buffer(FilteredRows)
in
    BufferedData
Zastosowanie Table.Buffer() powoduje, że Power Query zapisuje wyniki kroku w pamięci, zamiast ponownie przeliczać je w kolejnych krokach.
*Krok 3: Minimalizacja liczby wczytywanych wierszy
#Jeśli pracujesz z dużą bazą danych lub plikami CSV, załaduj tylko potrzebne kolumny i wiersze.
#Zamiast pobierać wszystkie dane, użyj funkcji Keep Top Rows do pobrania np. pierwszych 1000 wierszy dla testów.
#Użyj funkcji Remove Duplicates na wczesnym etapie, aby zmniejszyć liczbę przetwarzanych danych.
*Krok 4: Optymalizacja połączeń z bazą danych
#Jeśli pracujesz z SQL Server, Power BI lub inną bazą danych, unikaj pobierania całej tabeli do Power Query.
#Zamiast tego, filtrowanie i grupowanie wykonuj na poziomie bazy danych za pomocą przekształceń natywnych SQL.
Przykład:
let
    Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT OrderID, OrderDate, Customer, Product FROM Sales WHERE Cost > 500"])
in
    Source
Dzięki temu Power Query pobierze już przefiltrowane dane, zamiast filtrować całą tabelę w pamięci.
*Krok 5: Unikanie operacji „drill-down” na dużych zbiorach danych
#Power Query często automatycznie sugeruje „drill-down” (np. wybór pojedynczej wartości z tabeli).
#Jeśli wykonujesz operację na dużych zbiorach, staraj się pracować na całych tabelach, zamiast przekształcać pojedyncze rekordy.
*Krok 6: Automatyczne odświeżanie zoptymalizowanych zapytań
#Po zoptymalizowaniu zapytania, ustaw odświeżanie w Excelu lub Power Query, aby aktualizowało się w odpowiednich interwałach.
#W Excelu przejdź do Dane → Właściwości zapytania → Odświeżaj dane przy otwieraniu pliku.
=== Zadanie ===
#Wczytaj duży plik CSV (PQSales_Large.csv).
#Ogranicz liczbę wczytywanych wierszy i kolumn.
#Zastosuj Table.Buffer() i sprawdź różnicę w wydajności.
#Jeśli korzystasz z bazy danych, zoptymalizuj zapytanie SQL.
#Ustaw automatyczne odświeżanie zoptymalizowanego zapytania.
#Skorzystaj z ChatGPT do analizy wydajności i dalszej optymalizacji kodu M.
== Modul 8: Tworzenie dynamicznych raportow i paneli w Excelu z Power Query ==
=== Cel === 
W tym module nauczysz się: 
*✔ Jak wykorzystać Power Query do dynamicznego generowania raportów. 
*✔ Jak łączyć dane z różnych źródeł w jednym raporcie. 
*✔ Jak tworzyć interaktywne raporty przy użyciu tabel przestawnych. 
*✔ Jak zautomatyzować odświeżanie raportów w Excelu.
=== Dostarczone dane === 
Do tego ćwiczenia użyjemy następujących plików: 
*📂 [[Media:PQ_Sales_Data.xlsx]] – Dane sprzedażowe.
*📂 [[Media:PQ_Regions.xlsx]] – Dane o regionach sprzedaży.
*📂 [[Media:PQ_Targets.xlsx]] – Cele sprzedażowe.
=== Instrukcje ===
*Krok 1: Importowanie danych i łączenie źródeł*
# Otwórz Power Query w Excelu.
# Zaimportuj pliki PQ_Sales_Data.xlsx, PQ_Regions.xlsx i PQ_Targets.xlsx.
# Połącz dane ze sobą, używając klucza wspólnego – np. kolumny "Region".
# Sprawdź, czy dane są poprawnie połączone i wyświetlają się we właściwym formacie.
*Krok 2: Tworzenie dynamicznego raportu*
# Kliknij Zamknij i Załaduj do... i wybierz Tabelę przestawną.
# Wstaw tabelę przestawną w nowym arkuszu i upewnij się, że źródłem danych jest Power Query.
# W polach tabeli przestawnej ustaw:
  * Wiersze → Region.
  * Kolumny → Miesiąc.
  * Wartości → Suma Sprzedaży.
Sprawdź poprawność wyników i sformatuj tabelę.
*Krok 3: Dodanie warunkowego formatowania*
# Wybierz kolumnę "Suma Sprzedaży" w tabeli przestawnej.
# Kliknij Formatowanie warunkowe → Skalowanie kolorów.
# Ustaw gradientowe kolory dla niskiej i wysokiej sprzedaży.
# Zastosuj regułę "Większe niż" i podświetl wartości powyżej celu sprzedażowego (dane z PQ_Targets.xlsx).
=B5> VLOOKUP($A5,Targets!$A$2:$B$8,2,0)
*Krok 4: Automatyzacja odświeżania danych*
# Przejdź do Dane → Właściwości zapytania → Odświeżaj dane przy otwieraniu pliku.
# Możesz także ustawić odświeżanie automatyczne co X minut.
# Sprawdź, czy po dodaniu nowych danych do plików źródłowych raport aktualizuje się poprawnie.
=== Zadanie ===
# Załaduj i połącz dane z PQ_Sales_Data.xlsx, PQ_Regions.xlsx i PQ_Targets.xlsx.
# Utwórz tabelę przestawną i sformatuj dane dynamicznie.
# Dodaj warunkowe formatowanie na podstawie celów sprzedażowych.
# Ustaw automatyczne odświeżanie danych.
# Skorzystaj z ChatGPT do analizy i optymalizacji zapytań Power Query.
== Podsumowanie mod1 - mod8 ==
=== Cel ćwiczenia ===
W tym zadaniu podsumujesz wszystkie dotychczas poznane zagadnienia w Power Query poprzez wykonanie serii transformacji na danych dotyczących inwentaryzacji magazynowej i dostawców. Wykorzystasz import danych, filtrowanie, scalanie, dodawanie kolumn, niestandardowe funkcje i optymalizację zapytań.
=== Dostarczone dane ===
Do wykonania ćwiczenia wykorzystasz pliki:
* '''📂 [[Media:PQ_inventory.csv]]''' – Dane o stanie magazynowym
* '''📂 [[Media:PQ_suppliers.csv]]''' – Informacje o dostawcach
* '''📂 [[Media:PQ_orders.csv]]''' – Zamówienia dostaw do magazynu
=== Instrukcje ===
'''🔹 Krok 1: Importowanie danych'''
# Otwórz Power Query w Excelu lub Power BI.
# Zaimportuj trzy pliki CSV: `PQ_inventory.csv`, `PQ_suppliers.csv`, `PQ_orders.csv`.
# Sprawdź, czy dane zostały poprawnie załadowane.
'''🔹 Krok 2: Sprawdzenie i konwersja typów danych'''
# Upewnij się, że kolumny w każdym zbiorze mają poprawne typy danych.
# Problem do rozwiązania: kolumna `StockLevel` (poziom zapasów) została błędnie zaimportowana jako tekst, ponieważ zawiera wartości z jednostkami, np. `150 kg`, `200 l`, `75 szt`.
# Przekształć kolumnę `StockLevel`, aby zawierała tylko wartości liczbowe, a jednostkę zapisz w osobnej kolumnie `Unit`.
# Sprawdź, czy kolumna `SupplierID` została poprawnie rozpoznana jako liczba całkowita.
'''🔹 Krok 3: Scalanie danych'''
# Połącz `PQ_inventory.csv` z `PQ_suppliers.csv` za pomocą klucza `SupplierID`.
# Użyj `Left Outer Join`, aby zachować wszystkie rekordy magazynowe.
# Następnie scal `PQ_orders.csv` z `PQ_inventory.csv`, używając `ProductID` jako klucza.
# Sprawdź, czy do tabeli magazynowej dodano informacje o dostawcach i zamówieniach.
'''🔹 Krok 4: Tworzenie kolumn niestandardowych'''
# Dodaj kolumnę `ReorderLevel`, która oznaczy produkty wymagające zamówienia, gdy `StockLevel` jest mniejsze niż `MinimumStock`.
# Dodaj kolumnę `DaysSinceLastOrder`, która oblicza liczbę dni od ostatniego zamówienia na dany produkt.
# Utwórz niestandardową funkcję w M, która przypisuje priorytet zamówienia:
<pre>
if [StockLevel] < [MinimumStock] and [DaysSinceLastOrder] > 30 then "High"
else if [StockLevel] < [MinimumStock] then "Medium"
else "Low"
</pre>
# Dodaj kolumnę `OrderPriority` i przypisz do niej wynik tej funkcji.
'''🔹 Krok 5: Filtrowanie i transformacje warunkowe'''
# Usuń produkty, które są w stanie `Discontinued`.
# Dodaj nową kolumnę `SupplierRating`, która klasyfikuje dostawców według ich niezawodności:
<pre>
if [OnTimeDeliveryRate] > 95 then "Excellent"
else if [OnTimeDeliveryRate] >= 80 then "Good"
else "Poor"
</pre>
# Sprawdź, czy klasyfikacja działa poprawnie.
'''🔹 Krok 6: Przekształcanie struktury danych'''
# Wykonaj unpivotowanie kolumn `Stock_Jan`, `Stock_Feb`, `Stock_Mar`, aby uzyskać strukturę z kolumnami: `Product`, `Month`, `Stock Level`.
# Rozdziel kolumnę `ProductDetails` na `ProductName` i `Category`.
# Scal kolumny `SupplierName` i `Country`, używając ` - ` jako separatora.
'''🔹 Krok 7: Optymalizacja zapytania'''
# Zastosuj `Table.Buffer()`, aby poprawić wydajność przetwarzania.
# Usuń zbędne kolumny i duplikaty na początku przekształceń, a nie na końcu.
# Jeśli dane są duże, ogranicz liczbę wczytywanych wierszy do testowych 1000.
'''🔹 Krok 8: Eksport wyników'''
# Zapisz gotowe zapytanie jako tabelę w Excelu.
# Przetestuj odświeżanie danych po zmianie wartości w plikach źródłowych.
# Ustaw automatyczne odświeżanie zapytania.
=== Zadanie ===
*✔ Wykonaj wszystkie kroki opisane powyżej. 
*✔ Eksperymentuj zarówno z interfejsem graficznym, jak i kodem M. 
*✔ Zastosuj optymalizację zapytań, aby poprawić wydajność przetwarzania. 
*✔ Upewnij się, że transformacje działają poprawnie i wyniki są zgodne z oczekiwaniami. 
*✔ Skorzystaj z ChatGPT w celu rozwiązywania problemów lub optymalizacji skryptu M. 
== Modul 10: Import i analiza plików PDF w Power Query ==
=== Cel ===
W tym module nauczysz się:
* ✔ Jak importować dane z plików PDF do Power Query.
* ✔ Jak przekształcać dane i wykonywać analizy na poszczególnych raportach biznesowych.
* ✔ Jak wizualizować wyniki i wyciągać wnioski na podstawie raportów.
=== Dostarczone dane ===
Do ćwiczenia wykorzystamy pliki PDF zawierające raporty biznesowe:
* 📂 [[Media:Monthly_Sales_Report_Jan2024.pdf]] – Raport sprzedaży
* 📂 [[Media:Employee_Attendance_Q1_2024.pdf]] – Raport frekwencji pracowników
* 📂 [[Media:Customer_Feedback_Survey_2024.pdf]] – Raport opinii klientów
=== Instrukcje ===
🔹 '''Zadanie 1: Analiza raportu sprzedaży'''
# Oblicz sumaryczną sprzedaż dla wszystkich produktów.
# Znajdź produkt o najwyższej i najniższej sprzedaży.
# Oblicz średnią wartość transakcji na podstawie liczby transakcji i wartości sprzedaży.
# Grupuj dane według regionów i oblicz łączną sprzedaż dla każdego regionu.
# Utwórz tabelę przestawną przedstawiającą sprzedaż według regionów i produktów.
🔹 '''Zadanie 2: Analiza raportu frekwencji pracowników'''
# Oblicz średni wskaźnik frekwencji dla wszystkich działów.
# Znajdź dział z najwyższą i najniższą frekwencją.
# Utwórz nową kolumnę klasyfikującą frekwencję według kategorii:
## Wysoka: powyżej 95%
## Średnia: 85% - 95%
## Niska: poniżej 85%
# Przefiltruj dane tak, aby wyświetlić tylko pracowników z niską frekwencją.
# Stwórz wizualizację (np. wykres słupkowy) przedstawiającą średnią frekwencję dla poszczególnych działów.
🔹 '''Zadanie 3: Analiza raportu opinii klientów'''
# Oblicz średnią ocenę klientów na podstawie skali 1-5.
# Znajdź liczbę klientów, którzy wystawili ocenę 1 lub 5.
# Wygeneruj raport podsumowujący najczęściej powtarzające się pozytywne i negatywne opinie.
# Posortuj dane według oceny klientów od najniższej do najwyższej.
# Stwórz wykres kołowy pokazujący rozkład ocen klientów.
=== Podsumowanie ===
*✔ Wykonaj zadania analityczne dla każdego raportu oddzielnie. 
*✔ Zastosuj filtrowanie, sortowanie i grupowanie danych. 
*✔ Wykorzystaj tabele przestawne do agregowania wyników. 
*✔ Wizualizuj wyniki za pomocą wykresów w Excelu lub Power BI. 
*✔ Skorzystaj z ChatGPT, jeśli napotkasz trudności w analizie danych.
== Modul 11: Importowanie i analiza danych z sieci Web==
=== Cel ===
W tym module nauczysz się:
* ✔ Jak importować dane z tabel statystycznych dostępnych na stronach Wikipedii do Power Query.
* ✔ Jak przekształcać i analizować dane dotyczące krajów świata.
* ✔ Jak wizualizować wyniki porównań w Excelu lub Power BI.
=== Źródło danych ===
W tym ćwiczeniu wykorzystamy rzeczywiste dane tabelaryczne dotyczące krajów świata. Importujemy je bezpośrednio z Wikipedii, gdzie dostępne są zestawienia obejmujące:
* 📊 **Powierzchnię krajów świata** 🌍
* 📊 **Liczbę ludności w poszczególnych krajach** 👥
* 📊 **Produkt Krajowy Brutto (PKB) w podziale na państwa** 💰
'''źródła:'''
* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_powierzchni Lista państw świata według powierzchni]
* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_liczby_ludno%C5%9Bci Lista państw według liczby ludności]
* [https://pl.wikipedia.org/wiki/Lista_pa%C5%84stw_%C5%9Bwiata_wed%C5%82ug_PKB_nominalnego Lista państw według PKB nominalnego]
=== Instrukcje ===
🔹 '''Krok 1: Importowanie danych z Wikipedii'''
# Otwórz Power Query w Excelu lub Power BI.
# Wybierz opcję **Pobierz dane** → **Z sieci Web**.
# Wpisz adres URL jednej z powyższych stron Wikipedii.
# Po załadowaniu dostępnych tabel wybierz tę, która zawiera dane statystyczne (np. powierzchnia krajów, ludność, PKB).
# Kliknij **Załaduj do Power Query**, aby rozpocząć przekształcanie danych.
🔹 '''Krok 2: Przekształcanie i czyszczenie danych'''
# **Usuń zbędne kolumny**, pozostawiając tylko te, które są kluczowe dla analizy.
# **Zmień typy danych**, aby liczby były poprawnie interpretowane (np. `Powierzchnia` jako liczba, `PKB` jako waluta).
# **Usuń puste wartości** i ewentualne błędy w danych.
# **Zamień nazwy kolumn** na bardziej zrozumiałe, np. `Kraj`, `Powierzchnia (km²)`, `Ludność`, `PKB (mld USD)`.
🔹 '''Krok 3: Analiza i porównanie krajów'''
# **Oblicz gęstość zaludnienia**, dodając nową kolumnę według wzoru:
Gęstość zaludnienia = Ludność / Powierzchnia
# **Posortuj kraje według PKB**, aby zobaczyć najbogatsze i najbiedniejsze państwa.
# **Porównaj powierzchnię i liczbę ludności**, aby znaleźć największe i najmniejsze kraje oraz te o największej liczbie mieszkańców.
# **Zastosuj filtrowanie**, aby wyświetlić tylko wybrane kontynenty lub regiony świata.
🔹 '''Krok 4: Wizualizacja wyników'''
# Utwórz **tabelę przestawną** w Excelu, aby porównać powierzchnię, ludność i PKB krajów.
# Wstaw **wykres słupkowy**, aby pokazać największe gospodarki świata.
# Użyj **mapy cieplnej**, aby zobrazować gęstość zaludnienia w poszczególnych regionach.
# Dodaj **formatowanie warunkowe**, aby wyróżnić kraje o skrajnych wartościach statystycznych.


* Step 5: Assigning Categories
=== Zadanie ===
*✔ Zaimportuj dane dotyczące krajów świata z Wikipedii do Power Query. 
*✔ Przekształć i wyczyść dane, aby były gotowe do analizy. 
*✔ Oblicz gęstość zaludnienia oraz inne wskaźniki statystyczne. 
*✔ Stwórz wykresy i tabele porównawcze w Excelu lub Power BI. 
*✔ Skorzystaj z ChatGPT w razie problemów z importem lub analizą danych.


#In the PQ_sales table, add a custom column using the function.
#Name the column PriceCategory.
#Ensure that the categories appear correctly based on the Cost value.


=== Task ===
== Modul 12: Czyszczenie i przekształcanie danych tekstowych w Power Query ==
*✔ Complete all steps in Power Query.
*✔ Experiment with both the graphical interface and M code.
*✔ Use ChatGPT to troubleshoot or improve your M script.


=== Cel ===
W tym module nauczysz się:


== Module 4: Advanced Data Transformations in Power Query ==
* ✔ Jak usuwać zbędne spacje i poprawiać wielkość liter.
* ✔ Jak poprawiać formatowanie adresów e-mail.
* ✔ Jak ujednolicić numery telefonów.
* ✔ Jak poprawić błędy w kodach pocztowych.
* ✔ Jak rozdzielać i scalać tekst w tabelach.


=== Objective ===
=== Dane wejściowe ===
In this module, you will learn:
Dostaliśmy chaotycznie wprowadzone dane klientów, które wymagają czyszczenia.
*✔ 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 ===
'''Plik do pobrania:''' 
📂 [[Media:baza_klientow.xlsx|Pobierz plik baza_klientow.xlsx]]


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


'''📂 [[File:PQ_sales_pivot.csv]]''' - Pivoted data structure:
🔹 '''Krok 1: Czyszczenie imion i nazwisk'''


=== Instructions ===
# Usuń podwójne spacje.
# Zamień imiona i nazwiska na format "Pierwsza litera duża, reszta małe".
# Popraw literówki w nazwiskach.


* Step 1: Import the Data
🔹 '''Krok 2: Poprawienie formatowania adresów e-mail'''


#Open Power Query in Excel or Power BI.
# Zamień "@@" na "@".
#Import the file PQ_sales_pivot.csv.
# Zamień ".." na ".".
#Ensure that the table is loaded correctly.
# Zamień ",com" na ".com".
<br>
# Usuń zbędne spacje wokół adresów e-mail.
 
🔹 '''Krok 3: Normalizacja numerów telefonów'''
 
# Usuń spacje, nawiasy, kropki i znaki "+48".
# Konwertuj numery do formatu „601-123-456”.
 
🔹 '''Krok 4: Poprawienie kodów pocztowych'''
 
# Zamień "_" na "-".
# Usuń błędne znaki w kodach pocztowych.
 
🔹 '''Krok 5: Rozdzielanie i scalanie tekstu'''


* Step 2: Unpivot the Data
# Rozdziel kolumnę "Imię i Nazwisko" na dwie osobne kolumny.
# Scal kolumny "Adres" i "Miasto" w jedną kolumnę "Pełny Adres".


#The current table has a wide format, which is not ideal for analysis.
=== Oczekiwany efekt końcowy ===
#Unpivot the month columns so that the data is structured as:


*Product
Po wykonaniu operacji dane klientów powinny zostać poprawnie sformatowane i ujednolicone, gotowe do dalszej analizy w Excelu lub Power BI.
*Category
*Month
*Sales Amount
<br>


How to do this:
=== Zadanie ===
*✔ Oczyść dane klientów – popraw imiona, e-maile i telefony. 
*✔ Usuń duplikaty i błędne wpisy. 
*✔ Rozdziel nazwiska, scal adresy. 
*✔ Ujednolić format kodów pocztowych. 
*✔ Zastosuj formatowanie warunkowe w Excelu dla błędnych wartości.


#Click Transform → Use First Row as Headers to ensure the correct column names.
#Select the month columns (Jan 2025, Feb 2025, etc.).
#Click Transform → Unpivot Columns.
#Rename the resulting columns:
# "Attribute" → Month
#"Value" → Sales Amount
<br>
<br>


* Step 3: Splitting and Merging Columns
*1️⃣ Czyszczenie błędów w imionach i nazwiskach
*✅ Usunięcie podwójnych spacji
*✅ Zamiana nazwisk na pierwsza litera duża, reszta małe
*✅ Poprawienie literówek (np. „kowalsky” → „Kowalski”)


#The Month column currently has values like "Jan 2025".
*📌 Kod M do poprawy wielkości liter i usunięcia spacji:
#Split this column into Month Name and Year:
#Select the Month column.
#Click Transform → Split Column → By Delimiter.
#Choose the space (" ") delimiter.
#Rename the new columns as Month Name and Year.


Merge Columns Example:
Table.TransformColumns(Dane, {{"Imię i Nazwisko", Text.Proper}, {"Miasto", Text.Upper}})
*📌 Zamiana podwójnych spacji na pojedyncze:


If you want to merge Product and Category, select both.
Table.TransformColumns(Dane, {{"Imię i Nazwisko", each Text.Replace(_, "  ", " ")}})
*2️⃣ Poprawienie formatowania e-maili
*✅ Zamiana „@@” na „@”
*✅ Zamiana „..” na „.”
*✅ Poprawienie „,com” → „.com”


#Click Transform → Merge Columns.
*📌 Kod M do poprawy e-maili:
#Use " - " as a separator (e.g., "Monitor - Electronics").
<br>


* Step 4: Adding Conditional Transformations
Table.TransformColumns(Dane, {{"Email", each Text.Replace(Text.Replace(_, "@@", "@"), "..", ".")}})
*📌 Dodatkowo: usunięcie spacji wokół adresu e-mail


Add a new custom column called "Sales Performance":
Table.TransformColumns(Dane, {{"Email", Text.Trim}})
*3️⃣ Normalizacja numerów telefonów
*✅ Usunięcie nawiasów, kropek, myślników, spacji
*✅ Konwersja do jednolitego formatu 601-123-456


if [Sales Amount] < 300 then "Low"
*📌 Usunięcie zbędnych znaków z telefonu
else if [Sales Amount] >= 300 and [Sales Amount] < 800 then "Medium"
else "High"


Ensure that the column correctly categorizes sales performance.
Table.TransformColumns(Dane, {{"Telefon", each Text.Remove(_, {" ", "(", ")", "+", "-", "."})}})
*📌 Dodanie standardowego formatu numeru


=== Task ===
Table.AddColumn(Dane, "Telefon Formatowany", each Text.Middle([Telefon], 0, 3) & "-" & Text.Middle([Telefon], 3, 3) & "-" & Text.Middle([Telefon], 6, 3))
*✔ Complete all steps in Power Query.
*4️⃣ Poprawienie kodów pocztowych
*✔ Experiment with unpivoting, splitting, merging, and conditional logic.
*✅ Zamiana „_” na „-”
*✔ Use ChatGPT to troubleshoot or improve your M script.
*✅ Usunięcie błędnych znaków np. „WRO-567”


*📌 Kod M do poprawienia kodów pocztowych:


Table.TransformColumns(Dane, {{"Kod Pocztowy", each Text.Replace(_, "_", "-")}})
*5️⃣ Rozdzielanie i scalanie tekstu
*✅ Podział „Imię i Nazwisko” na Imię i Nazwisko
*✅ Scalanie Adres + Miasto → Pełny Adres


== Module 5: Parameterization and Dynamic Queries in Power Query ==
*📌 Kod M do podziału na Imię i Nazwisko:


=== Objective ===
Table.SplitColumn(Dane, "Imię i Nazwisko", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Imię", "Nazwisko"})
*📌 Kod M do scalania adresów:


In this module, you will learn:
Table.AddColumn(Dane, "Pełny Adres", each [Adres] & ", " & [Miasto])
*✔ 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 ===
== Modul 13: Grupowanie, agregowanie i analiza danych sprzedażowych w Power Query ==


For this exercise, we will use the PQ_sales.csv file and introduce a new dataset PQ_parameters.xlsx, which contains dynamic filter values.
=== Cel ===
W tym module nauczysz się:


'''📂 [[File:PQ_parameters.xlsx]]''' - Parameter table for dynamic filtering:
* ✔ Jak grupować dane według kategorii, klientów i miesięcy.
* ✔ Jak agregować dane (suma, średnia, liczba transakcji).
* ✔ Jak tworzyć segmentacje klientów.
* ✔ Jak przygotować raporty do dalszej analizy w Excelu.


=== Instructions ===
=== Dane wejściowe ===
Dostaliśmy dane sprzedażowe, które chcemy pogrupować według różnych kategorii i przeprowadzić analizę sprzedaży.


Step 1: Import the Data
'''Plik do pobrania:''' 
📂 [[Media:baza_sprzedazy.xlsx|Pobierz plik baza_sprzedazy.xlsx]]


#Open Power Query in Excel or Power BI.
=== Instrukcje ===
#Import both datasets:
*PQ_sales.csv (Sales transactions)
*PQ_parameters.xlsx (Filter parameters)
#Ensure that the tables load correctly.


*🔹 '''Krok 1: Wczytanie danych do Power Query'''


Step 2: Creating and Using Parameters
# Załaduj plik **baza_sprzedazy.xlsx** do Power Query.
Convert the PQ_parameters.xlsx table into named parameters:
# Sprawdź typy danych – upewnij się, że:
#Select the StartDate and EndDate values.
  - Kolumna **Data sprzedaży** jest w formacie daty.
#Go to Transform → Convert to Parameter.
  - Kolumna **Cena jednostkowa** i **Ilość** są w formacie liczbowym.
#Repeat this for MinCost and MaxCost.


Step 3: Applying Parameters to Filter Data
*🔹 '''Krok 2: Duplikowanie danych dla różnych grupowań'''
Filter Sales Data Based on Date Range:
if [OrderDate] >= StartDate and [OrderDate] <= EndDate
#Select the OrderDate column in PQ_sales.
#Apply a filter using StartDate and EndDate parameters.


Filter Sales Data Based on Cost Range:
# Kliknij prawym przyciskiem myszy na wczytanej tabeli w Power Query i wybierz **Powiel**.
# Powtórz tę operację dla każdej nowej grupy, aby mieć oddzielne tabele dla różnych grupowań.


if [Cost] >= MinCost and [Cost] <= MaxCost
*🔹 '''Krok 3: Grupowanie danych według produktów'''


#Select the Cost column in PQ_sales.
# Na tabeli **GrupowanieProdukt** wykonaj grupowanie.
#Apply a filter using MinCost and MaxCost parameters.
# Przejdź do zakładki **Strona główna** → **Grupuj według**.
# Wybierz **Produkt** jako kolumnę grupującą.
# W sekcji **Nowe kolumny** dodaj:
  - **Suma sprzedanych jednostek** (`SUM(Ilość)`).
  - **Łączna wartość sprzedaży** (`SUM(Cena jednostkowa * Ilość)`).
  - **Średnia cena jednostkowa** (`AVERAGE(Cena jednostkowa)`).


*🔹 '''Krok 4: Grupowanie danych według klientów'''


Step 4: Building a Dynamic Data Source
# Na tabeli **GrupowanieKlient** wykonaj grupowanie.
# Powtórz proces grupowania, tym razem wybierając **ID Klienta**.
# Dodaj:
  - **Liczbę transakcji** (`COUNT`).
  - **Łączną wartość zamówień** (`SUM(Cena jednostkowa * Ilość)`).
  - **Średnią wartość zamówienia** (`AVERAGE(Cena jednostkowa * Ilość)`).


Make the File Path Dynamic:
*🔹 '''Krok 5: Grupowanie miesięczne sprzedaży'''


Source = Excel.Workbook(File.Contents(FilePath), null, true)
# Na tabeli **GrupowanieMiesiąc** wykonaj grupowanie.
# Wybierz kolumnę **Data sprzedaży** i przekształć ją na **miesiąc**:
  - Kliknij **Dodaj kolumnę** → **Wyodrębnij** → **Miesiąc**.
# Pogrupuj dane według miesiąca i oblicz **łączną sprzedaż w każdym miesiącu**.


#Instead of using a static file path, create a parameter for the file location.
*🔹 '''Krok 6: Wykorzystanie zaawansowanych agregacji'''
#Where FilePath is a user-defined parameter.


=== Task ===
# Dodaj nową kolumnę, która wyliczy średnią wartość zamówienia dla danego klienta w stosunku do całej sprzedaży:
*✔ Create and use parameters to filter the data dynamically.
  ```m
*✔ Implement a dynamic data source with a parameterized file path.
  [Łączna wartość zamówień] / List.Sum(#"Tabela_Sprzedaż"[Łączna wartość zamówień])
*✔ Use ChatGPT to troubleshoot or improve your M script.
Stwórz segmentację klientów – dodaj kolumnę warunkową:
Jeśli sprzedaż > 10 000 zł → Kluczowy klient.
Jeśli sprzedaż 5 000 - 10 000 zł → Średni klient.
Jeśli sprzedaż < 5 000 zł → Mały klient.
=== Zadanie ===  
*✔ Zaimportuj dane sprzedażowe i sprawdź poprawność typów danych.
*✔ Powiel tabelę, aby utworzyć niezależne grupowania.
*✔ Pogrupuj dane według produktów, klientów i miesięcy na oddzielnych tabelach.
*✔ Oblicz sumy sprzedaży, średnie wartości zamówień i liczby transakcji.
*✔ Dodaj klasyfikację klientów na podstawie wartości zamówień.
*✔ Przygotuj tabelę przestawną do analizy wyników.

Latest revision as of 18:05, 10 March 2025

Category:Power Query

Modul 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 📂Media:PQsales.csv który zawiera dane zamówień sprzedaży.

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.

Modul 2: Laczenie i scalanie danych z wielu zrodel

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:

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

📂 Media: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.

Modul 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:

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

📂Media: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.

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

📂 Media: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.
  • 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.

Modul 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ń.

Dostarczone dane

W tym ćwiczeniu użyjemy pliku:

oraz nowego zestawu danych

Instrukcje

  • 🔹 Krok 1: Załaduj plik CSV do Power Query
  1. Otwórz Excela i przejdź do „Dane” → „Pobierz dane” → „Z pliku” → „Z pliku CSV”.
  2. Wybierz plik `PQ_sales.csv` i kliknij „Załaduj do Power Query”.
  3. Po załadowaniu pliku upewnij się, że Power Query rozpoznał poprawnie dane.
  • 🔹 Krok 2: Przetwórz tabelę `Parameters`
  1. W Power Query przejdź do tabeli **`Parameters`**.
  2. **Transponuj tabelę** – kliknij **„Przekształć” → „Transponuj”**.
  3. **Ustaw pierwszy wiersz jako nagłówki** – kliknij **„Przekształć” → „Użyj pierwszego wiersza jako nagłówków”**.
  4. **Zmień typy danych** dla `startDate` i `endDate` na **Date**:
    1. Kliknij nagłówek `startDate` → wybierz typ `Date`.
    2. Powtórz dla `endDate`.
  • 🔹 Krok 3: Utwórz osobne zapytania dla `startDate` i `endDate`
  1. W tabeli `Parameters` kliknij prawym przyciskiem myszy na wartość w `startDate` → **„Dodaj jako nowe zapytanie”** (`Add as new query`).
  2. Powtórz tę operację dla `endDate`.
  • 🔹 Krok 4: Zmień typ danych w kolumnie `OrderDate` w tabeli `PQ Sales` na date
  1. Wróć do zapytania `PQ Sales`.
  2. Kolumna `OrderDate` zawiera daty w formacie `DD MM YY`.
  3. **Rozdziel ją na trzy kolumny**:
    1. Kliknij **„Przekształć” → „Podziel kolumnę” → „Według ogranicznika”**.
    2. Wybierz **spację** (` `) jako ogranicznik.
    3. Powstają trzy nowe kolumny: `OrderDate.1`, `OrderDate.2`, `OrderDate.3` (dzień, miesiąc, rok).
  4. **Zmień typy danych** tych kolumn na `Number` (`Int64.Type`).
  5. **Połącz je w poprawny format `YYYY-MM-DD`**:
    1. Kliknij **„Scal kolumny”** (`Merge Columns`).
    2. Wybierz kolejność: `OrderDate.2`, `OrderDate.3`, `OrderDate.1` (miesiąc, rok, dzień).
    3. Ustaw separator `/`.
    4. Zmień nazwę kolumny na `DateOrder`.
    5. Zmień jej typ na **Date**.
  • 🔹 Krok 5: Dodanie dynamicznego filtra do `DateOrder`
  1. Otwórz **Edytor Zaawansowany** (`View` → `Advanced Editor`).
  2. Znajdź ostatni krok przed `in`:
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",Template:"Merged", "DateOrder")
  • 🔹 Krok 6 :Dodaj filtr:
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DateOrder] >= startDate and [DateOrder] <= endDate)

Upewnij się, że startDate i endDate są w formacie Date.

Zmień końcową wartość in, aby zwracała #"Filtered Rows": 

in
    #"Filtered Rows"
  • 🔹 Krok 7: Sprawdzenie wyników
  1. Kliknij „Gotowe” (Done).
  2. Sprawdź, czy dane są filtrowane poprawnie.
  3. Kliknij „Zamknij i Załaduj” (Close & Load), aby zapisać dane w Excelu

Modul 6: Automatyzacja Laczenia i Odswiezania Danych w Power Query

Cel

W tym module nauczysz się:

  • ✔ Jak automatycznie importować i łączyć pliki z folderu.
  • ✔ Jak radzić sobie z różnymi nazwami kolumn w różnych plikach.
  • ✔ Jak przygotować dane do raportowania niezależnie od struktury źródłowych plików.
  • ✔ Jak ustawić automatyczne odświeżanie danych w Power Query.

Dostarczone dane

W tym ćwiczeniu użyjemy zestawu wielu plików sprzedażowych, które znajdują się w jednym folderze.

📂 Media:Sales_Jan.xlsx – Sprzedaż za styczeń

📂 Media:Sales_Feb.xlsx – Sprzedaż za luty

📂 Media:Sales_Mar.xlsx – Sprzedaż za marzec

Każdy plik zawiera podobne dane, ale ma inne nazwy kolumn dla wartości sprzedaży:

W Sales_Jan.xlsx kolumna sprzedaży to Total Sale.

W Sales_Feb.xlsx kolumna sprzedaży to Revenue.

W Sales_Mar.xlsx kolumna sprzedaży to SalesAmount.

Celem jest połączenie tych plików w jeden zbiór i dostosowanie nazw kolumn, aby były spójne.

Instrukcje

  • Krok 1: Pobranie plików z folderu
  1. Otwórz Power Query w Excelu.
  2. Wybierz Dane → Pobierz dane → Z pliku → Z folderu.
  3. Wskaż folder, w którym znajdują się pliki sprzedażowe (Sales_Jan.xlsx, Sales_Feb.xlsx, Sales_Mar.xlsx).
  4. Kliknij Załaduj (Load), aby dodać pliki do Power Query bez automatycznego łączenia.
  • Krok 2: Zastosowanie kodu M do załadowania danych
  1. Otwórz Zaawansowany Edytor w Power Query.
  2. Wklej poniższy kod M i kliknij Gotowe:
let
    // Wczytanie plików z folderu
    Source = Folder.Files("C:\Users\pathToFolder..."),
    
    // Dodanie kolumny, która otworzy zawartość pliku Excel
    AddContent = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),
    
    // Rozwinięcie zawartości, aby zobaczyć wszystkie dane
    ExpandContent = Table.ExpandTableColumn(AddContent, "Custom", {"Name", "Data"}, {"File Name", "Data"})

in
    ExpandContent
  1. Po zastosowaniu kodu zobaczysz tabelę z nową kolumną Data.
  • Krok 3: Rozwinięcie zawartości tabel
  1. Kliknij ikonę rozwinięcia obok kolumny Data, aby zobaczyć pełne dane z plików.
  2. Upewnij się, że wszystkie kolumny z różnych plików są widoczne.
  • Krok 4: Usunięcie zbędnych kolumn
  1. Sprawdź tabelę i usuń niepotrzebne kolumny (np. kolumny techniczne dotyczące plików, które nie są wymagane do analizy).
  2. Kliknij Przekształć → Usuń kolumny i usuń zbędne kolumny.
  • Krok 5: Nadanie nazw kolumnom
  1. Ustaw własne nazwy kolumn zgodnie ze spójnym formatem (np. Total Sale → Sales).
  2. Kliknij Przekształć → Zmień nazwę kolumny dla każdej kolumny, aby nadać im jednolitą strukturę.
  • Krok 6: Usunięcie zbędnych wierszy (powtórzeń nagłówków)
  1. Zastosuj filtr na kolumnie zawierającej wartości sprzedaży.
  2. Usuń wiersze zawierające powtórzenia nagłówków, które mogą występować z powodu łączenia wielu plików.
  3. Kliknij Przekształć → Usuń wiersze → Usuń duplikaty lub zastosuj filtr manualnie.
  • Krok 7: Automatyczne odświeżanie danych
  1. Przejdź do Dane → Właściwości zapytania → Odświeżaj dane przy otwieraniu pliku.
  2. Możesz także ustawić automatyczne odświeżanie co X minut.
  3. Jeśli dodasz nowy plik do folderu (np. Sales_Apr.xlsx), Power Query automatycznie pobierze dane z nowego pliku po odświeżeniu!

Zadanie

  • ✔ Załaduj i połącz dane z Sales_Jan.xlsx, Sales_Feb.xlsx, Sales_Mar.xlsx.
  • ✔ Ujednolić nazwy kolumn i przekształcić dane w jednolity format.
  • ✔ Usuń puste wiersze, zbędne kolumny i duplikaty.
  • ✔ Ustaw automatyczne odświeżanie, aby nowe pliki były automatycznie dołączane.
  • ✔ Skorzystaj z ChatGPT do optymalizacji kodu M w Power Query.


Modul 7: Optymalizacja Wydajnosci Zapytan w Power Query

Cel

W tym module nauczysz się:

  • ✔ Jak przyspieszyć działanie Power Query w przypadku dużych zbiorów danych.
  • ✔ Jak unikać nieefektywnych operacji, które spowalniają zapytania.
  • ✔ Jak korzystać z funkcji buforowania i operacji na poziomie bazy danych.
  • ✔ Jak minimalizować ilość przetwarzanych danych dla lepszej wydajności.

Wprowadzenie

Power Query pozwala na transformację danych, ale przy dużych zbiorach może działać wolno. W tym module nauczysz się optymalnych praktyk, które pozwolą skrócić czas przetwarzania zapytań.

Instrukcje

  • Krok 1: Unikanie niepotrzebnych operacji na całym zbiorze danych
  1. Wczytaj duży plik CSV 📂Media:PQSales_Large.csv.
  2. Sprawdź liczbę wierszy i kolumn – im więcej danych, tym większe znaczenie ma optymalizacja.
  3. Usuń zbędne kolumny na początku zapytania, zamiast na końcu.
  4. Użyj Filtrowania wczesnego – zastosuj filtr, aby ograniczyć liczbę przetwarzanych wierszy od razu po imporcie.
  • Krok 2: Używanie funkcji buforowania (Table.Buffer)
  1. Sprawdź, jak działa przetwarzanie kroków – każda operacja może powodować ponowne przeliczenie całego zapytania.
  2. Dodaj krok Table.Buffer() po filtrze, aby zapobiec ponownemu przetwarzaniu danych:
let
    Source = Csv.Document(File.Contents("C:\Users\gp\Desktop\PQ\Sales_Large.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    FilteredRows = Table.SelectRows(Source, each [Cost] > 500),
    BufferedData = Table.Buffer(FilteredRows)
in
    BufferedData

Zastosowanie Table.Buffer() powoduje, że Power Query zapisuje wyniki kroku w pamięci, zamiast ponownie przeliczać je w kolejnych krokach.

  • Krok 3: Minimalizacja liczby wczytywanych wierszy
  1. Jeśli pracujesz z dużą bazą danych lub plikami CSV, załaduj tylko potrzebne kolumny i wiersze.
  2. Zamiast pobierać wszystkie dane, użyj funkcji Keep Top Rows do pobrania np. pierwszych 1000 wierszy dla testów.
  3. Użyj funkcji Remove Duplicates na wczesnym etapie, aby zmniejszyć liczbę przetwarzanych danych.
  • Krok 4: Optymalizacja połączeń z bazą danych
  1. Jeśli pracujesz z SQL Server, Power BI lub inną bazą danych, unikaj pobierania całej tabeli do Power Query.
  2. Zamiast tego, filtrowanie i grupowanie wykonuj na poziomie bazy danych za pomocą przekształceń natywnych SQL.

Przykład: 

let
    Source = Sql.Database("ServerName", "DatabaseName", [Query="SELECT OrderID, OrderDate, Customer, Product FROM Sales WHERE Cost > 500"])
in
    Source

Dzięki temu Power Query pobierze już przefiltrowane dane, zamiast filtrować całą tabelę w pamięci.

  • Krok 5: Unikanie operacji „drill-down” na dużych zbiorach danych
  1. Power Query często automatycznie sugeruje „drill-down” (np. wybór pojedynczej wartości z tabeli).
  2. Jeśli wykonujesz operację na dużych zbiorach, staraj się pracować na całych tabelach, zamiast przekształcać pojedyncze rekordy.
  • Krok 6: Automatyczne odświeżanie zoptymalizowanych zapytań
  1. Po zoptymalizowaniu zapytania, ustaw odświeżanie w Excelu lub Power Query, aby aktualizowało się w odpowiednich interwałach.
  2. W Excelu przejdź do Dane → Właściwości zapytania → Odświeżaj dane przy otwieraniu pliku.

Zadanie

  1. Wczytaj duży plik CSV (PQSales_Large.csv).
  2. Ogranicz liczbę wczytywanych wierszy i kolumn.
  3. Zastosuj Table.Buffer() i sprawdź różnicę w wydajności.
  4. Jeśli korzystasz z bazy danych, zoptymalizuj zapytanie SQL.
  5. Ustaw automatyczne odświeżanie zoptymalizowanego zapytania.
  6. Skorzystaj z ChatGPT do analizy wydajności i dalszej optymalizacji kodu M.


Modul 8: Tworzenie dynamicznych raportow i paneli w Excelu z Power Query

Cel

W tym module nauczysz się:

  • ✔ Jak wykorzystać Power Query do dynamicznego generowania raportów.
  • ✔ Jak łączyć dane z różnych źródeł w jednym raporcie.
  • ✔ Jak tworzyć interaktywne raporty przy użyciu tabel przestawnych.
  • ✔ Jak zautomatyzować odświeżanie raportów w Excelu.

Dostarczone dane

Do tego ćwiczenia użyjemy następujących plików:

Instrukcje

  • Krok 1: Importowanie danych i łączenie źródeł*
  1. Otwórz Power Query w Excelu.
  2. Zaimportuj pliki PQ_Sales_Data.xlsx, PQ_Regions.xlsx i PQ_Targets.xlsx.
  3. Połącz dane ze sobą, używając klucza wspólnego – np. kolumny "Region".
  4. Sprawdź, czy dane są poprawnie połączone i wyświetlają się we właściwym formacie.
  • Krok 2: Tworzenie dynamicznego raportu*
  1. Kliknij Zamknij i Załaduj do... i wybierz Tabelę przestawną.
  2. Wstaw tabelę przestawną w nowym arkuszu i upewnij się, że źródłem danych jest Power Query.
  3. W polach tabeli przestawnej ustaw:
 * Wiersze → Region.
 * Kolumny → Miesiąc.
 * Wartości → Suma Sprzedaży.

Sprawdź poprawność wyników i sformatuj tabelę.
  • Krok 3: Dodanie warunkowego formatowania*
  1. Wybierz kolumnę "Suma Sprzedaży" w tabeli przestawnej.
  2. Kliknij Formatowanie warunkowe → Skalowanie kolorów.
  3. Ustaw gradientowe kolory dla niskiej i wysokiej sprzedaży.
  4. Zastosuj regułę "Większe niż" i podświetl wartości powyżej celu sprzedażowego (dane z PQ_Targets.xlsx).
=B5> VLOOKUP($A5,Targets!$A$2:$B$8,2,0)


  • Krok 4: Automatyzacja odświeżania danych*
  1. Przejdź do Dane → Właściwości zapytania → Odświeżaj dane przy otwieraniu pliku.
  2. Możesz także ustawić odświeżanie automatyczne co X minut.
  3. Sprawdź, czy po dodaniu nowych danych do plików źródłowych raport aktualizuje się poprawnie.

Zadanie

  1. Załaduj i połącz dane z PQ_Sales_Data.xlsx, PQ_Regions.xlsx i PQ_Targets.xlsx.
  2. Utwórz tabelę przestawną i sformatuj dane dynamicznie.
  3. Dodaj warunkowe formatowanie na podstawie celów sprzedażowych.
  4. Ustaw automatyczne odświeżanie danych.
  5. Skorzystaj z ChatGPT do analizy i optymalizacji zapytań Power Query.

Podsumowanie mod1 - mod8

Cel ćwiczenia

W tym zadaniu podsumujesz wszystkie dotychczas poznane zagadnienia w Power Query poprzez wykonanie serii transformacji na danych dotyczących inwentaryzacji magazynowej i dostawców. Wykorzystasz import danych, filtrowanie, scalanie, dodawanie kolumn, niestandardowe funkcje i optymalizację zapytań.

Dostarczone dane

Do wykonania ćwiczenia wykorzystasz pliki:

Instrukcje

🔹 Krok 1: Importowanie danych

  1. Otwórz Power Query w Excelu lub Power BI.
  2. Zaimportuj trzy pliki CSV: `PQ_inventory.csv`, `PQ_suppliers.csv`, `PQ_orders.csv`.
  3. Sprawdź, czy dane zostały poprawnie załadowane.

🔹 Krok 2: Sprawdzenie i konwersja typów danych

  1. Upewnij się, że kolumny w każdym zbiorze mają poprawne typy danych.
  2. Problem do rozwiązania: kolumna `StockLevel` (poziom zapasów) została błędnie zaimportowana jako tekst, ponieważ zawiera wartości z jednostkami, np. `150 kg`, `200 l`, `75 szt`.
  3. Przekształć kolumnę `StockLevel`, aby zawierała tylko wartości liczbowe, a jednostkę zapisz w osobnej kolumnie `Unit`.
  4. Sprawdź, czy kolumna `SupplierID` została poprawnie rozpoznana jako liczba całkowita.

🔹 Krok 3: Scalanie danych

  1. Połącz `PQ_inventory.csv` z `PQ_suppliers.csv` za pomocą klucza `SupplierID`.
  2. Użyj `Left Outer Join`, aby zachować wszystkie rekordy magazynowe.
  3. Następnie scal `PQ_orders.csv` z `PQ_inventory.csv`, używając `ProductID` jako klucza.
  4. Sprawdź, czy do tabeli magazynowej dodano informacje o dostawcach i zamówieniach.

🔹 Krok 4: Tworzenie kolumn niestandardowych

  1. Dodaj kolumnę `ReorderLevel`, która oznaczy produkty wymagające zamówienia, gdy `StockLevel` jest mniejsze niż `MinimumStock`.
  2. Dodaj kolumnę `DaysSinceLastOrder`, która oblicza liczbę dni od ostatniego zamówienia na dany produkt.
  3. Utwórz niestandardową funkcję w M, która przypisuje priorytet zamówienia:
if [StockLevel] < [MinimumStock] and [DaysSinceLastOrder] > 30 then "High" 
else if [StockLevel] < [MinimumStock] then "Medium" 
else "Low"
  1. Dodaj kolumnę `OrderPriority` i przypisz do niej wynik tej funkcji.

🔹 Krok 5: Filtrowanie i transformacje warunkowe

  1. Usuń produkty, które są w stanie `Discontinued`.
  2. Dodaj nową kolumnę `SupplierRating`, która klasyfikuje dostawców według ich niezawodności:
if [OnTimeDeliveryRate] > 95 then "Excellent" 
else if [OnTimeDeliveryRate] >= 80 then "Good" 
else "Poor"
  1. Sprawdź, czy klasyfikacja działa poprawnie.

🔹 Krok 6: Przekształcanie struktury danych

  1. Wykonaj unpivotowanie kolumn `Stock_Jan`, `Stock_Feb`, `Stock_Mar`, aby uzyskać strukturę z kolumnami: `Product`, `Month`, `Stock Level`.
  2. Rozdziel kolumnę `ProductDetails` na `ProductName` i `Category`.
  3. Scal kolumny `SupplierName` i `Country`, używając ` - ` jako separatora.

🔹 Krok 7: Optymalizacja zapytania

  1. Zastosuj `Table.Buffer()`, aby poprawić wydajność przetwarzania.
  2. Usuń zbędne kolumny i duplikaty na początku przekształceń, a nie na końcu.
  3. Jeśli dane są duże, ogranicz liczbę wczytywanych wierszy do testowych 1000.

🔹 Krok 8: Eksport wyników

  1. Zapisz gotowe zapytanie jako tabelę w Excelu.
  2. Przetestuj odświeżanie danych po zmianie wartości w plikach źródłowych.
  3. Ustaw automatyczne odświeżanie zapytania.

Zadanie

  • ✔ Wykonaj wszystkie kroki opisane powyżej.
  • ✔ Eksperymentuj zarówno z interfejsem graficznym, jak i kodem M.
  • ✔ Zastosuj optymalizację zapytań, aby poprawić wydajność przetwarzania.
  • ✔ Upewnij się, że transformacje działają poprawnie i wyniki są zgodne z oczekiwaniami.
  • ✔ Skorzystaj z ChatGPT w celu rozwiązywania problemów lub optymalizacji skryptu M.

Modul 10: Import i analiza plików PDF w Power Query

Cel

W tym module nauczysz się:

  • ✔ Jak importować dane z plików PDF do Power Query.
  • ✔ Jak przekształcać dane i wykonywać analizy na poszczególnych raportach biznesowych.
  • ✔ Jak wizualizować wyniki i wyciągać wnioski na podstawie raportów.

Dostarczone dane

Do ćwiczenia wykorzystamy pliki PDF zawierające raporty biznesowe:

Instrukcje

🔹 Zadanie 1: Analiza raportu sprzedaży

  1. Oblicz sumaryczną sprzedaż dla wszystkich produktów.
  2. Znajdź produkt o najwyższej i najniższej sprzedaży.
  3. Oblicz średnią wartość transakcji na podstawie liczby transakcji i wartości sprzedaży.
  4. Grupuj dane według regionów i oblicz łączną sprzedaż dla każdego regionu.
  5. Utwórz tabelę przestawną przedstawiającą sprzedaż według regionów i produktów.

🔹 Zadanie 2: Analiza raportu frekwencji pracowników

  1. Oblicz średni wskaźnik frekwencji dla wszystkich działów.
  2. Znajdź dział z najwyższą i najniższą frekwencją.
  3. Utwórz nową kolumnę klasyfikującą frekwencję według kategorii:
    1. Wysoka: powyżej 95%
    2. Średnia: 85% - 95%
    3. Niska: poniżej 85%
  4. Przefiltruj dane tak, aby wyświetlić tylko pracowników z niską frekwencją.
  5. Stwórz wizualizację (np. wykres słupkowy) przedstawiającą średnią frekwencję dla poszczególnych działów.

🔹 Zadanie 3: Analiza raportu opinii klientów

  1. Oblicz średnią ocenę klientów na podstawie skali 1-5.
  2. Znajdź liczbę klientów, którzy wystawili ocenę 1 lub 5.
  3. Wygeneruj raport podsumowujący najczęściej powtarzające się pozytywne i negatywne opinie.
  4. Posortuj dane według oceny klientów od najniższej do najwyższej.
  5. Stwórz wykres kołowy pokazujący rozkład ocen klientów.

Podsumowanie

  • ✔ Wykonaj zadania analityczne dla każdego raportu oddzielnie.
  • ✔ Zastosuj filtrowanie, sortowanie i grupowanie danych.
  • ✔ Wykorzystaj tabele przestawne do agregowania wyników.
  • ✔ Wizualizuj wyniki za pomocą wykresów w Excelu lub Power BI.
  • ✔ Skorzystaj z ChatGPT, jeśli napotkasz trudności w analizie danych.

Modul 11: Importowanie i analiza danych z sieci Web

Cel

W tym module nauczysz się:

  • ✔ Jak importować dane z tabel statystycznych dostępnych na stronach Wikipedii do Power Query.
  • ✔ Jak przekształcać i analizować dane dotyczące krajów świata.
  • ✔ Jak wizualizować wyniki porównań w Excelu lub Power BI.

Źródło danych

W tym ćwiczeniu wykorzystamy rzeczywiste dane tabelaryczne dotyczące krajów świata. Importujemy je bezpośrednio z Wikipedii, gdzie dostępne są zestawienia obejmujące:

  • 📊 **Powierzchnię krajów świata** 🌍
  • 📊 **Liczbę ludności w poszczególnych krajach** 👥
  • 📊 **Produkt Krajowy Brutto (PKB) w podziale na państwa** 💰

źródła:

Instrukcje

🔹 Krok 1: Importowanie danych z Wikipedii

  1. Otwórz Power Query w Excelu lub Power BI.
  2. Wybierz opcję **Pobierz dane** → **Z sieci Web**.
  3. Wpisz adres URL jednej z powyższych stron Wikipedii.
  4. Po załadowaniu dostępnych tabel wybierz tę, która zawiera dane statystyczne (np. powierzchnia krajów, ludność, PKB).
  5. Kliknij **Załaduj do Power Query**, aby rozpocząć przekształcanie danych.

🔹 Krok 2: Przekształcanie i czyszczenie danych

  1. **Usuń zbędne kolumny**, pozostawiając tylko te, które są kluczowe dla analizy.
  2. **Zmień typy danych**, aby liczby były poprawnie interpretowane (np. `Powierzchnia` jako liczba, `PKB` jako waluta).
  3. **Usuń puste wartości** i ewentualne błędy w danych.
  4. **Zamień nazwy kolumn** na bardziej zrozumiałe, np. `Kraj`, `Powierzchnia (km²)`, `Ludność`, `PKB (mld USD)`.

🔹 Krok 3: Analiza i porównanie krajów

  1. **Oblicz gęstość zaludnienia**, dodając nową kolumnę według wzoru:
Gęstość zaludnienia = Ludność / Powierzchnia
  1. **Posortuj kraje według PKB**, aby zobaczyć najbogatsze i najbiedniejsze państwa.
  2. **Porównaj powierzchnię i liczbę ludności**, aby znaleźć największe i najmniejsze kraje oraz te o największej liczbie mieszkańców.
  3. **Zastosuj filtrowanie**, aby wyświetlić tylko wybrane kontynenty lub regiony świata.

🔹 Krok 4: Wizualizacja wyników

  1. Utwórz **tabelę przestawną** w Excelu, aby porównać powierzchnię, ludność i PKB krajów.
  2. Wstaw **wykres słupkowy**, aby pokazać największe gospodarki świata.
  3. Użyj **mapy cieplnej**, aby zobrazować gęstość zaludnienia w poszczególnych regionach.
  4. Dodaj **formatowanie warunkowe**, aby wyróżnić kraje o skrajnych wartościach statystycznych.

Zadanie

  • ✔ Zaimportuj dane dotyczące krajów świata z Wikipedii do Power Query.
  • ✔ Przekształć i wyczyść dane, aby były gotowe do analizy.
  • ✔ Oblicz gęstość zaludnienia oraz inne wskaźniki statystyczne.
  • ✔ Stwórz wykresy i tabele porównawcze w Excelu lub Power BI.
  • ✔ Skorzystaj z ChatGPT w razie problemów z importem lub analizą danych.


Modul 12: Czyszczenie i przekształcanie danych tekstowych w Power Query

Cel

W tym module nauczysz się:

  • ✔ Jak usuwać zbędne spacje i poprawiać wielkość liter.
  • ✔ Jak poprawiać formatowanie adresów e-mail.
  • ✔ Jak ujednolicić numery telefonów.
  • ✔ Jak poprawić błędy w kodach pocztowych.
  • ✔ Jak rozdzielać i scalać tekst w tabelach.

Dane wejściowe

Dostaliśmy chaotycznie wprowadzone dane klientów, które wymagają czyszczenia.

Plik do pobrania: 📂 Pobierz plik baza_klientow.xlsx

Instrukcje

🔹 Krok 1: Czyszczenie imion i nazwisk

  1. Usuń podwójne spacje.
  2. Zamień imiona i nazwiska na format "Pierwsza litera duża, reszta małe".
  3. Popraw literówki w nazwiskach.

🔹 Krok 2: Poprawienie formatowania adresów e-mail

  1. Zamień "@@" na "@".
  2. Zamień ".." na ".".
  3. Zamień ",com" na ".com".
  4. Usuń zbędne spacje wokół adresów e-mail.

🔹 Krok 3: Normalizacja numerów telefonów

  1. Usuń spacje, nawiasy, kropki i znaki "+48".
  2. Konwertuj numery do formatu „601-123-456”.

🔹 Krok 4: Poprawienie kodów pocztowych

  1. Zamień "_" na "-".
  2. Usuń błędne znaki w kodach pocztowych.

🔹 Krok 5: Rozdzielanie i scalanie tekstu

  1. Rozdziel kolumnę "Imię i Nazwisko" na dwie osobne kolumny.
  2. Scal kolumny "Adres" i "Miasto" w jedną kolumnę "Pełny Adres".

Oczekiwany efekt końcowy

Po wykonaniu operacji dane klientów powinny zostać poprawnie sformatowane i ujednolicone, gotowe do dalszej analizy w Excelu lub Power BI.

Zadanie

  • ✔ Oczyść dane klientów – popraw imiona, e-maile i telefony.
  • ✔ Usuń duplikaty i błędne wpisy.
  • ✔ Rozdziel nazwiska, scal adresy.
  • ✔ Ujednolić format kodów pocztowych.
  • ✔ Zastosuj formatowanie warunkowe w Excelu dla błędnych wartości.


  • 1️⃣ Czyszczenie błędów w imionach i nazwiskach
  • ✅ Usunięcie podwójnych spacji
  • ✅ Zamiana nazwisk na pierwsza litera duża, reszta małe
  • ✅ Poprawienie literówek (np. „kowalsky” → „Kowalski”)
  • 📌 Kod M do poprawy wielkości liter i usunięcia spacji:
Table.TransformColumns(Dane, {{"Imię i Nazwisko", Text.Proper}, {"Miasto", Text.Upper}})
  • 📌 Zamiana podwójnych spacji na pojedyncze:
Table.TransformColumns(Dane, Template:"Imię i Nazwisko", each Text.Replace( , " ", " "))
  • 2️⃣ Poprawienie formatowania e-maili
  • ✅ Zamiana „@@” na „@”
  • ✅ Zamiana „..” na „.”
  • ✅ Poprawienie „,com” → „.com”
  • 📌 Kod M do poprawy e-maili:
Table.TransformColumns(Dane, Template:"Email", each Text.Replace(Text.Replace( , "@@", "@"), "..", "."))
  • 📌 Dodatkowo: usunięcie spacji wokół adresu e-mail
Table.TransformColumns(Dane, Template:"Email", Text.Trim)
  • 3️⃣ Normalizacja numerów telefonów
  • ✅ Usunięcie nawiasów, kropek, myślników, spacji
  • ✅ Konwersja do jednolitego formatu 601-123-456
  • 📌 Usunięcie zbędnych znaków z telefonu
Table.TransformColumns(Dane, {{"Telefon", each Text.Remove(_, {" ", "(", ")", "+", "-", "."})}})
  • 📌 Dodanie standardowego formatu numeru
Table.AddColumn(Dane, "Telefon Formatowany", each Text.Middle([Telefon], 0, 3) & "-" & Text.Middle([Telefon], 3, 3) & "-" & Text.Middle([Telefon], 6, 3))
  • 4️⃣ Poprawienie kodów pocztowych
  • ✅ Zamiana „_” na „-”
  • ✅ Usunięcie błędnych znaków np. „WRO-567”
  • 📌 Kod M do poprawienia kodów pocztowych:
Table.TransformColumns(Dane, Template:"Kod Pocztowy", each Text.Replace( , " ", "-"))
  • 5️⃣ Rozdzielanie i scalanie tekstu
  • ✅ Podział „Imię i Nazwisko” na Imię i Nazwisko
  • ✅ Scalanie Adres + Miasto → Pełny Adres
  • 📌 Kod M do podziału na Imię i Nazwisko:
Table.SplitColumn(Dane, "Imię i Nazwisko", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, false), {"Imię", "Nazwisko"})
  • 📌 Kod M do scalania adresów:
Table.AddColumn(Dane, "Pełny Adres", each [Adres] & ", " & [Miasto])

Modul 13: Grupowanie, agregowanie i analiza danych sprzedażowych w Power Query

Cel

W tym module nauczysz się:

  • ✔ Jak grupować dane według kategorii, klientów i miesięcy.
  • ✔ Jak agregować dane (suma, średnia, liczba transakcji).
  • ✔ Jak tworzyć segmentacje klientów.
  • ✔ Jak przygotować raporty do dalszej analizy w Excelu.

Dane wejściowe

Dostaliśmy dane sprzedażowe, które chcemy pogrupować według różnych kategorii i przeprowadzić analizę sprzedaży.

Plik do pobrania: 📂 Pobierz plik baza_sprzedazy.xlsx

Instrukcje

  • 🔹 Krok 1: Wczytanie danych do Power Query
  1. Załaduj plik **baza_sprzedazy.xlsx** do Power Query.
  2. Sprawdź typy danych – upewnij się, że:
  - Kolumna **Data sprzedaży** jest w formacie daty.
  - Kolumna **Cena jednostkowa** i **Ilość** są w formacie liczbowym.
  • 🔹 Krok 2: Duplikowanie danych dla różnych grupowań
  1. Kliknij prawym przyciskiem myszy na wczytanej tabeli w Power Query i wybierz **Powiel**.
  2. Powtórz tę operację dla każdej nowej grupy, aby mieć oddzielne tabele dla różnych grupowań.
  • 🔹 Krok 3: Grupowanie danych według produktów
  1. Na tabeli **GrupowanieProdukt** wykonaj grupowanie.
  2. Przejdź do zakładki **Strona główna** → **Grupuj według**.
  3. Wybierz **Produkt** jako kolumnę grupującą.
  4. W sekcji **Nowe kolumny** dodaj:
  - **Suma sprzedanych jednostek** (`SUM(Ilość)`).
  - **Łączna wartość sprzedaży** (`SUM(Cena jednostkowa * Ilość)`).
  - **Średnia cena jednostkowa** (`AVERAGE(Cena jednostkowa)`).
  • 🔹 Krok 4: Grupowanie danych według klientów
  1. Na tabeli **GrupowanieKlient** wykonaj grupowanie.
  2. Powtórz proces grupowania, tym razem wybierając **ID Klienta**.
  3. Dodaj:
  - **Liczbę transakcji** (`COUNT`).
  - **Łączną wartość zamówień** (`SUM(Cena jednostkowa * Ilość)`).
  - **Średnią wartość zamówienia** (`AVERAGE(Cena jednostkowa * Ilość)`).
  • 🔹 Krok 5: Grupowanie miesięczne sprzedaży
  1. Na tabeli **GrupowanieMiesiąc** wykonaj grupowanie.
  2. Wybierz kolumnę **Data sprzedaży** i przekształć ją na **miesiąc**:
  - Kliknij **Dodaj kolumnę** → **Wyodrębnij** → **Miesiąc**.
  1. Pogrupuj dane według miesiąca i oblicz **łączną sprzedaż w każdym miesiącu**.
  • 🔹 Krok 6: Wykorzystanie zaawansowanych agregacji
  1. Dodaj nową kolumnę, która wyliczy średnią wartość zamówienia dla danego klienta w stosunku do całej sprzedaży:
  ```m
  [Łączna wartość zamówień] / List.Sum(#"Tabela_Sprzedaż"[Łączna wartość zamówień])

Stwórz segmentację klientów – dodaj kolumnę warunkową: 

Jeśli sprzedaż > 10 000 zł → Kluczowy klient.
Jeśli sprzedaż 5 000 - 10 000 zł → Średni klient.
Jeśli sprzedaż < 5 000 zł → Mały klient.

Zadanie

  • ✔ Zaimportuj dane sprzedażowe i sprawdź poprawność typów danych.
  • ✔ Powiel tabelę, aby utworzyć niezależne grupowania.
  • ✔ Pogrupuj dane według produktów, klientów i miesięcy na oddzielnych tabelach.
  • ✔ Oblicz sumy sprzedaży, średnie wartości zamówień i liczby transakcji.
  • ✔ Dodaj klasyfikację klientów na podstawie wartości zamówień.
  • ✔ Przygotuj tabelę przestawną do analizy wyników.
Retrieved from "https://training-course-material.com/index.php?title=Power_Query_for_Analysts&oldid=88718"