Power Query for Analysts

Module 1: Introduction to Power Query and Basic Transformations

Objective

In this exercise, you will learn to:

• Import data from a CSV file into Power Query.
• Review and adjust data types, focusing on converting a date stored as text into a proper date type.
• Apply basic data filtering.
• Use external help (e.g., ChatGPT) to get guidance on creating custom M code, without directly copying solutions.

Files Provided

You can download the CSV file 📂Media:PQsales.csv which contains sales order data.

Instructions

• Step 1: Import the data

1. Open Power Query in Excel or Power BI.
2. Import the data from the file sales.csv.
3. Note that the *OrderDate* column was imported as text due to its format (dd/MM/yyyy).

• Step 2: Check and convert data types

1. Verify that each column has the correct data type.
2. Manually transform the *OrderDate* column from text to date type.
3. Tip: If you encounter difficulties, you can ask ChatGPT for hints on how to write an M function to convert text to date.

• Step 3: Apply basic filtering

1. Filter the dataset to keep only rows where the cost is greater than 200.
2. Suggestion: Use Power Query’s graphical interface or write a simple M script to apply the filter. If needed, consult ChatGPT for implementation ideas.

• Step 4: Review and save your work

1. Confirm that the transformations were applied correctly by reviewing the data preview.
2. Save the query and document the steps you have taken.

Task

Perform the steps described above in Power Query. Experiment with available transformation options and try to understand how each step affects your data. Use ChatGPT for hints or troubleshooting, but avoid copying complete solutions verbatim.

Module 2: Combining and Merging Data from Multiple Sources

Objective

In this exercise, you will learn to:

• Import data from multiple CSV files into Power Query.
• Merge data from different sources based on a common key.
• Use a Left Outer Join to add customer details to sales orders.
• Use external help (e.g., ChatGPT) to get guidance on writing custom M code, without copying complete solutions.

Files Provided

You can download the following two CSV files:

📂 Media:PQ_sales2.csv – Contains sales order data 📂 Media:PQ_customers.csv – Contains customer information

Instructions

• Step 1: Import the data

Open Power Query in Excel or Power BI. Import data from both files: PQ_sales.csv and PQ_customers.csv. Check that both queries have been loaded correctly.

• Step 2: Check and convert data types

Ensure that each column has the correct data type in both queries. For example, note that the *OrderDate* column in PQ_sales.csv may be imported as text due to a non-standard format. Tip: Use the transformation functions if any adjustments are needed.

• Step 3: Merge the data

Merge the *PQ_sales.csv* query with the *PQ_customers.csv* query. Use the *Customer* column as the matching key. Choose the Left Outer Join option so that every sales order is retained along with the corresponding customer details. Suggestion: If you’re not sure how to write M code for this merge, ask ChatGPT for tips on how to merge 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. Check the merged data to ensure that customer details have been correctly linked to the corresponding sales orders.

• Step 5: Save your work

Save your query and document the transformation steps you applied.

Task

Perform the steps described above in Power Query. Experiment with both the graphical interface and custom M code to complete the merge. Use external resources (e.g., ChatGPT) for guidance or troubleshooting, but avoid copying complete solutions verbatim.

Module 3: Creating Custom Columns and Functions

Objective

In this exercise, you will learn to:

• Create custom calculated columns in Power Query.
• Use built-in Power Query functions to manipulate text, numbers, and dates.
• Write custom M functions to automate transformations.
• Use ChatGPT to assist in writing and optimizing M code.

Files Provided

The following datasets are used for this exercise:

📂Media:PQ_sales2.csv (used in previous modules) 📂Media:PQ_discounts.csv (new dataset) – contains discount rates based on product type.

Instructions

• Step 1: Import the data

Open Power Query in Excel or Power BI. Import both files: *PQ_sales.csv* and *PQ_discounts.csv*. Ensure both tables are loaded correctly.

• Step 2: Create a custom column for total cost

In the *PQ_sales* table, add a new custom column: Go to **Add Column → Custom Column**. Name it `TotalCost`. Create a formula to calculate the total cost as:

`Quantity * Cost`  

Click OK and review the results.

• Step 3: Apply discounts using merge

Merge *PQ_sales* with *PQ_discounts* using the *Product* column as the key. Expand the `DiscountRate` column into the *PQ_sales* table. Add another custom column named `DiscountedPrice`:

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

Check that the new column correctly applies the discounts.

• Step 4: Create a custom function in M

Create a function to categorize products into price bands: Go to **Home → Advanced Editor**. Write an M function that takes `Cost` as input and returns a category:

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

Tip: If you're unsure how to construct the function, ask ChatGPT:

• "How to write an M function that categorizes prices into Low, Medium, and High?"*

• Step 5: Assign categories

In the *PQ_sales* table, add a custom column using the function. Name the column `PriceCategory`. Make sure the categories display correctly based on the values in the *Cost* column.

Task

• ✔ Complete all steps in Power Query.
• ✔ Experiment with both the graphical interface and M code.
• ✔ Use ChatGPT for troubleshooting or refining your M scripts.

Module 4: Advanced Data Transformations in Power Query

Objective

In this module, you will learn:

• ✔ How to pivot and unpivot data in Power Query
• ✔ How to split and merge columns for better data structure
• ✔ How to use conditional transformations
• ✔ How to leverage ChatGPT to build complex M scripts

Files Provided

This exercise introduces a new dataset: 📂 Media:PQ_sales_pivot.csv – contains monthly sales data in a pivoted format.

Instructions

Step 1: Import the data Open Power Query in Excel or Power BI. Import the file *PQ_sales_pivot.csv*. Ensure the table loads correctly.

Step 2: Unpivot the data The current table has a wide format that is not ideal for analysis. Unpivot the monthly columns so the data structure becomes:

• Product
• Category
• Month
• Sales Amount

How to do it:

1. Click **Transform → Use First Row as Headers** to make sure column names are correct.
2. Select the month columns (e.g., Jan 2025, Feb 2025, etc.).
3. Click **Transform → Unpivot Columns**.
4. Rename the resulting columns:

 `"Attribute"` → `Month`  
 `"Value"` → `Sales Amount`

Step 3: Splitting and merging columns

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

• Example of merging columns:*

To merge `Product` and `Category`, select both columns:

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

Step 4: Adding conditional transformations Add a new custom column named `Sales Performance` with the following logic:

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

Make sure the column correctly categorizes the sales performance.

Task

• ✔ Complete all steps in Power Query
• ✔ Experiment with unpivoting, splitting, merging, and conditional logic
• ✔ Use ChatGPT for troubleshooting or refining your M scripts

Module 5: Parameterization and Dynamic Queries in Power Query

Objective

In this module, you will learn:

• ✔ How to create parameters in Power Query
• ✔ How to use parameters for dynamic filtering and query control

Files Provided

This exercise uses the following files:

• 📂 Media:PQ_sales2.csv
• 📂 Media:PQ_parameters.xlsx – contains values for dynamic filtering

Instructions

• 🔹 Step 1: Load the CSV file into Power Query

1. Open Excel and go to **Data → Get Data → From File → From Text/CSV**
2. Select the file `PQ_sales.csv` and load it into Power Query
3. Make sure Power Query recognizes the data correctly

• 🔹 Step 2: Process the `Parameters` table

1. In Power Query, go to the **`Parameters`** table
2. **Transpose the table** – click **Transform → Transpose**
3. **Use the first row as headers** – click **Transform → Use First Row as Headers**
4. **Change the data types** for `startDate` and `endDate` to **Date**:
   1. Click the `startDate` column header → choose type `Date`
   2. Repeat for `endDate`

• 🔹 Step 3: Create separate queries for `startDate` and `endDate`

1. In the `Parameters` table, right-click the value in `startDate` → **Add as New Query**
2. Repeat this for `endDate`

• 🔹 Step 4: Change the data type of `OrderDate` in the `PQ Sales` table to date

1. Go back to the `PQ Sales` query
2. The `OrderDate` column contains dates in `DD MM YY` format
3. **Split the column into three parts**:
   1. Click **Transform → Split Column → By Delimiter**
   2. Choose **Space** (` `) as the delimiter
   3. You will get: `OrderDate.1`, `OrderDate.2`, `OrderDate.3` (day, month, year)
4. **Change their types to `Number` (Int64.Type)**
5. **Merge into proper `YYYY-MM-DD` format**:
   1. Click **Merge Columns**
   2. Order the columns as: `OrderDate.2`, `OrderDate.3`, `OrderDate.1` (month, year, day)
   3. Use `/` as the separator
   4. Rename the new column to `DateOrder`
   5. Change its type to **Date**

• 🔹 Step 5: Add a dynamic filter to `DateOrder`

1. Open the **Advanced Editor** (`View → Advanced Editor`)
2. Find the last step before `in`, such as:

"Renamed Columns" = Table.RenameColumns(#"Changed Type2",Template:"Merged", "DateOrder")

• 🔹 Step 6: Add the filter line:

#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [DateOrder] >= startDate and [DateOrder] <= endDate)

Ensure that `startDate` and `endDate` are in Date format.

Update the final `in` line to return the filtered table:

in
#"Filtered Rows"

• 🔹 Step 7: Check the results

1. Click **Done**
2. Verify that the data is correctly filtered
3. Click **Close & Load** to load the data into Excel

Module 6: Automating Data Combining and Refreshing in Power Query

Objective

In this module, you will learn:

• ✔ How to automatically import and combine files from a folder
• ✔ How to handle different column names across files
• ✔ How to prepare data for reporting regardless of source file structure
• ✔ How to set up automatic data refresh in Power Query

Files Provided

This exercise uses a set of sales files located in a single folder:

📂 Media:Sales_Jan.xlsx – Sales for January 📂 Media:Sales_Feb.xlsx – Sales for February 📂 Media:Sales_Mar.xlsx – Sales for March

Each file contains similar data, but the sales column names differ:

• In *Sales_Jan.xlsx*: the sales column is named `Total Sale`
• In *Sales_Feb.xlsx*: the column is named `Revenue`
• In *Sales_Mar.xlsx*: the column is named `SalesAmount`

The goal is to combine these files into a single dataset and standardize the column names.

Instructions

• Step 1: Load files from a folder

1. Open Power Query in Excel
2. Go to **Data → Get Data → From File → From Folder**
3. Select the folder containing the files (Sales_Jan.xlsx, Sales_Feb.xlsx, Sales_Mar.xlsx)
4. Click **Load** to add files to Power Query without combining them automatically

• Step 2: Use M code to load the data

1. Open **Advanced Editor** in Power Query
2. Paste the following M code and click **Done**:

let
// Load files from folder
Source = Folder.Files("C:\Users\pathToFolder..."),

// Add a column to access the Excel file contents
AddContent = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content])),

/ Expand content to view all data
ExpandContent = Table.ExpandTableColumn(AddContent, "Custom", {"Name", "Data"}, {"File Name", "Data"})

in
ExpandContent

1. After applying the code, you will see a new `Data` column

• Step 3: Expand the table contents

1. Click the expand icon next to the `Data` column
2. This reveals the full data from each file
3. Ensure that all relevant columns from all files are visible

• Step 4: Remove unnecessary columns

1. Review the table and remove technical columns (e.g., file path) not needed for analysis
2. Go to **Transform → Remove Columns** and select what to discard

• Step 5: Rename columns

1. Rename the varying sales columns to a consistent name (e.g., `Sales`)
2. Use **Transform → Rename Column** to apply a uniform structure

• Step 6: Remove unnecessary rows (e.g., repeated headers)

1. Apply a filter on the column containing sales values
2. Remove rows with repeated headers caused by merging files
3. Go to **Transform → Remove Rows → Remove Duplicates**, or filter manually

• Step 7: Enable automatic refresh

1. Go to **Data → Query Properties → Refresh data when opening the file**
2. Optionally set automatic refresh every X minutes
3. If a new file (e.g., *Sales_Apr.xlsx*) is added to the folder, Power Query will automatically include it upon refresh!

Task

• ✔ Load and combine data from *Sales_Jan.xlsx*, *Sales_Feb.xlsx*, and *Sales_Mar.xlsx*
• ✔ Standardize column names and format the data consistently
• ✔ Remove empty rows, unnecessary columns, and duplicates
• ✔ Set up auto-refresh so new files are included automatically
• ✔ Use ChatGPT to optimize the M code in Power Query

Module 7: Optimizing Query Performance in Power Query

Objective

In this module, you will learn:

• ✔ How to speed up Power Query when working with large datasets
• ✔ How to avoid inefficient operations that slow down queries
• ✔ How to use buffering and database-level transformations
• ✔ How to minimize the amount of data processed for better performance

Introduction

Power Query enables powerful data transformation, but with large datasets, performance can suffer. In this module, you will learn best practices to reduce query execution time.

Instructions

• Step 1: Avoid unnecessary operations on the entire dataset

1. Load a large CSV file: 📂Media:PQSales_Large.csv
2. Check the number of rows and columns – the more data, the more important the optimization
3. Remove unnecessary columns at the beginning of the query instead of the end
4. Apply early filtering to reduce the number of rows right after import

• Step 2: Use buffering (Table.Buffer)

1. Understand how step-by-step processing works – each operation may cause Power Query to recalculate previous steps
2. Add `Table.Buffer()` after the filter step to avoid re-processing:

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

Using `Table.Buffer()` ensures that the results are stored in memory and not recalculated at each step.

• Step 3: Minimize the number of loaded rows

1. When working with large databases or CSV files, load only the needed columns and rows
2. Use **Keep Top Rows** to load e.g., the first 1000 rows for testing
3. Apply **Remove Duplicates** early to reduce the volume of data being processed

• Step 4: Optimize database connections

1. If working with SQL Server, Power BI, or another database, avoid importing full tables into Power Query
2. Instead, apply filtering and grouping on the database side using native SQL

Example:

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

This ensures Power Query pulls only the filtered data instead of processing the entire table in memory.

• Step 5: Avoid "drill-down" operations on large datasets

1. Power Query often suggests drill-downs (e.g., selecting a single value from a table)
2. When working with large data, operate on whole tables instead of individual records

• Step 6: Automatically refresh optimized queries

1. Once optimized, configure the query to refresh regularly
2. In Excel, go to **Data → Query Properties → Refresh data when opening the file**

Task

• Load the large CSV file (*PQSales_Large.csv*)
• Limit the number of loaded rows and columns
• Apply `Table.Buffer()` and observe performance improvements
• If using a database, optimize your SQL query
• Set up auto-refresh for the optimized query
• Use ChatGPT to analyze performance and further optimize M code

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ł*

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.

Summary: Modules 1–8

Objective

In this exercise, you will summarize all the key concepts learned so far in Power Query by performing a series of transformations on inventory and supplier data. You will apply data import, filtering, merging, column creation, custom functions, and query optimization.

Files Provided

The following files are used for this exercise:

• 📂 Media:PQ_inventory.csv – Inventory stock data
• 📂 Media:PQ_suppliers.csv – Supplier information
• 📂 Media:PQ_orders.csv – Warehouse delivery orders

Instructions

🔹 Step 1: Import data

1. Open Power Query in Excel or Power BI
2. Import the three CSV files: `PQ_inventory.csv`, `PQ_suppliers.csv`, `PQ_orders.csv`
3. Make sure all datasets are loaded correctly

🔹 Step 2: Check and convert data types

1. Ensure all columns in each dataset have correct data types
2. Issue to solve: the `StockLevel` column was incorrectly imported as text because it contains units like `150 kg`, `200 l`, `75 pcs`
3. Transform the `StockLevel` column to extract numeric values and store the unit in a new column `Unit`
4. Verify that the `SupplierID` column is recognized as an integer

🔹 Step 3: Merge data

1. Merge `PQ_inventory.csv` with `PQ_suppliers.csv` using the `SupplierID` key
2. Use a **Left Outer Join** to retain all inventory records
3. Then merge `PQ_orders.csv` with `PQ_inventory.csv` using the `ProductID` key
4. Verify that supplier and order info have been successfully added to the inventory table

🔹 Step 4: Create custom columns

1. Add a column `ReorderLevel` that flags products needing restocking when `StockLevel` is less than `MinimumStock`
2. Add a column `DaysSinceLastOrder` that calculates the number of days since the last order for each product
3. Create a custom M function that assigns order priority:

if [StockLevel] < [MinimumStock] and [DaysSinceLastOrder] > 30 then "High" 
else if [StockLevel] < [MinimumStock] then "Medium" 
else "Low"

1. Add a column `OrderPriority` and apply this function

🔹 Step 5: Conditional filtering and transformations

1. Remove products that have a `Discontinued` status
2. Add a new column `SupplierRating` that classifies suppliers by reliability:

if [OnTimeDeliveryRate] > 95 then "Excellent" 
else if [OnTimeDeliveryRate] >= 80 then "Good" 
else "Poor"

1. Verify that the rating logic works correctly

🔹 Step 6: Reshape the data structure

1. Unpivot columns `Stock_Jan`, `Stock_Feb`, `Stock_Mar` into: `Product`, `Month`, `Stock Level`
2. Split the `ProductDetails` column into `ProductName` and `Category`
3. Merge the `SupplierName` and `Country` columns using `" - "` as a separator

🔹 Step 7: Query optimization

1. Apply `Table.Buffer()` to improve performance
2. Remove unused columns and duplicates at the beginning of the transformations, not at the end
3. If working with large data, limit the loaded rows to a test sample of 1000

🔹 Step 8: Export results

1. Load the final query as a table into Excel
2. Test data refresh by updating source files
3. Set up auto-refresh for the query

Task

• ✔ Complete all the steps listed above
• ✔ Experiment with both the graphical interface and M code
• ✔ Apply query optimization to improve performance
• ✔ Ensure all transformations are correct and results are as expected
• ✔ Use ChatGPT for troubleshooting or optimizing your M script

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

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:

• Lista państw świata według powierzchni
• Lista państw według liczby ludności
• Lista państw według PKB nominalnego

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.