POWER QUERY W EXCELU I POWER BI ZBIERANIE I PRZEKSZTAŁCANIE DANYCH

SPIS TREŚCI

Przedmowa 11

O autorze 13

Podziękowania 14

Wprowadzenie 17

Rozdział 1. Wprowadzenie do Power Query 27

• Czym jest Power Query? 28
  • Historia Power Query w skrócie 29
  • Gdzie znajduje się Power Query? 32
• Główne komponenty Power Query 33
  • Pobieranie danych i tworzenie połączeń 33
  • Najważniejsze elementy edytora Power Query 34
• Ćwiczenie 1.1. Pierwsze kroki z Power Query 39
• Podsumowanie 43

Rozdział 2. Podstawowe operacje przygotowywania danych 45

• Wyodrębnianie informacji z zakodowanych danych 46
  • Fabryka Przygód 46
  • Ćwiczenie 2.1. Stary sposób: formuły Excela 47
  • Ćwiczenie 2.2. Część I. Nowy sposób 48
  • Ćwiczenie 2.2. Część II. Scalanie tabel wyszukiwania 51
  • Ćwiczenie 2.2. Część III. Tabele faktów i wyszukiwania 55
• Kolumna z przykładów 57
  • Ćwiczenie 2.3. Część I. Kolumna z przykładów – wprowadzenie 57
  • Kolumna z przykładów w praktyce 59
  • Ćwiczenie 2.3. Część II. Zamiana wielkości na oznaczenie zakresu 59
• Wyodrębnianie informacji z kolumn tekstowych 62
  • Ćwiczenie 2.4. Wyodrębnianie odnośników z komunikatów tekstowych 62
• Operacje na datach 69
  • Ćwiczenie 2.5. Operacje na różnych formatach dat 69
  • Ćwiczenie 2.6. Operacje na datach z różnymi ustawieniami regionalnymi 71
  • Wyodrębnianie elementów daty i czasu 74
• Przygotowanie modelu 75
  • Ćwiczenie 2.7. Dzielenie danych na tabelę faktów i tabelę wyszukiwania 75
  • Ćwiczenie 2.8. Zamiana wartości oddzielonych ogranicznikami na wiersze 78
• Podsumowanie 80

Rozdział 3. Łączenie danych z kilku źródeł 81

• Łączenie kliku tabel 81
  • Łączenie dwóch tabel 82
  • Ćwiczenie 3.1. Rowery i akcesoria 82
  • Ćwiczenie 3.2. Część I. Przekształcenie „Dołącz zapytania jako nowe” 84
  • Ćwiczenie 3.2. Część II. Zależności między zapytaniami i odwołania 84
  • Łączenie trzech lub więcej tabel 87
  • Ćwiczenie 3.2. Część III. Rowery + Akcesoria + Komponenty 87
  • Ćwiczenie 3.2. Część IV. Rowery + Akcesoria + Komponenty + Ubrania 88
• Łączenie tabel na szerszą skalę 89
  • Łączenie tabel zapisanych w folderze 89
  • Ćwiczenie 3.3. Łączenie skoroszytów z produktami zapisanymi w folderze 90
  • Uwagi do importowania plików z folderu 92
  • Łączenie arkuszy w skoroszycie 92
  • Ćwiczenie 3.4. Łączenie arkuszy – rozwiązanie 93
• Podsumowanie 97

Rozdział 4. Łączenie niezgodnych tabel 99

• Problem z niezgodnymi tabelami 99
  • Co to jest niezgodność tabel? 100
  • Symptomy niezgodności tabel i związane z tym ryzyka 100
  • Ćwiczenie 4.1. Uzgadnianie kolumn: podejście reaktywne 101
• Łączenie niezgodnych tabel zapisanych w folderze 102
  • Ćwiczenie 4.2. Część I. Symptom braku wartości 102
  • Ćwiczenie 4.2. Część II. Założenie takiej samej kolejności kolumn i ujednolicenie nagłówków 104
  • Ćwiczenie 4.3. Proste ujednolicenie nagłówków za pomocą funkcji Table.TransformColumnNames 105
  • Tabela konwersji 108
  • Ćwiczenie 4.4. Transpozycja z użyciem tabeli konwersji 109
  • Ćwiczenie 4.5. Anulowanie przestawienia, scalenie i powtórne przestawienie 113
  • Ćwiczenie 4.6. Transpozycja samych nazw kolumn 114
  • Ćwiczenie 4.7. Ujednolicenie nazw kolumn za pomocą języka M 119
• Podsumowanie 122

Rozdział 5. Pozyskiwanie kontekstu 123

• Pozyskiwanie kontekstu zawartego w nazwach plików i skoroszytów 124
  • Ćwiczenie 5.1. Część I. Kolumna niestandardowa 124
  • Ćwiczenie 5.1. Część II. Pozyskiwanie kontekstu z nazwy pliku lub arkusza 125
• Pozyskiwanie kontekstu z tytułów tabel przed ich połączeniem 126
  • Ćwiczenie 5.2. Pozyskiwanie kontekstu z tytułów tabel za pomocą przekształcenia Drill Down 127
  • Ćwiczenie 5.3. Pozyskiwanie kontekstu z tytułów tabel podczas importowania plików z folderu 130
• Pozyskiwanie kontekstu z tytułów tabel po ich połączeniu 133
  • Ćwiczenie 5.4. Pozyskiwanie kontekstu z tytułów tabel zapisanych w arkuszach jednego skoroszytu 133
• Wskazówki kontekstowe 137
  • Ćwiczenie 5.5. Kolumna indeksu jako wskazówka 137
  • Ćwiczenie 5.6. Wyszukiwanie informacji kontekstowych w pobliżu określonych komórek 140
• Podsumowanie 143

Rozdział 6. Dekomponowanie tabel 145

• Rozpoznawanie źle zaprojektowanych tabel 146
• Wprowadzenie do anulowania przestawienia 148
  • Ćwiczenie 6.1. Przekształcenia Anuluj przestawienie kolumn i Anuluj przestawienie innych kolumn 149
  • Ćwiczenie 6.2. Anulowanie przestawienia tylko zaznaczonych kolumn 151
• Przetwarzanie sum całkowitych 152
  • Ćwiczenie 6.3. Dekompozycja tabeli zawierającej sumy całkowite 153
• Dekompozycja tabeli z hierarchią agregacji danych 2(2 155
  • Ćwiczenie 6.4. Dekompozycja tabeli z hierarchią agregacji danych 2(2 i datami 155
  • Ćwiczenie 6.5. Dekompozycja tabeli z hierarchią agregacji danych 2×2 158
• Przetwarzanie sum częściowych 160
  • Ćwiczenie 6.6. Przetwarzanie sum częściowych 160
• Podsumowanie 162

Rozdział 7. Zaawansowane metody dekomponowania i agregowania tabel 163

• Dekomponowanie tabeli z wielopoziomową hierarchią agregacji danych 164
  • Wirtualna tabela przestawna, pola wierszy i kolumn 164
  • Ćwiczenie 7.1. Dekompozycja tabeli z hierarchią agregacji danych N×M 165
• Uogólnienie sekwencji przekształceń dekomponujących tabelę 167
  • Ćwiczenie 7.2. Zaczynając od końca 168
  • Ćwiczenie 7.3. Tworzenie funkcji FnDekomponujTabelę 169
• Przekształcenie Kolumna przestawna 178
  • Ćwiczenie 7.4. Przywrócenie niewłaściwie zdekomponowanej tabeli 178
  • Ćwiczenie 7.5. Agregowanie tabel z wielowierszowymi rekordami 180
• Podsumowanie 184

Rozdział 8. Praca grupowa 185

• Pliki lokalne, parametry i szablony 186
  • Niewłaściwe korzystanie z lokalnych plików 186
  • Ćwiczenie 8.1. Zdefiniowanie parametru z nazwą ścieżki 187
  • Ćwiczenie 8.2. Tworzenie szablonów w Power BI 189
  • Ćwiczenie 8.3. Definiowanie parametrów w Excelu 191
• Praca nad współdzielonymi plikami i folderami 197
  • Importowanie danych z plików zapisanych w usługach OneDrive dla Firm i SharePoint 197
  • Ćwiczenie 8.4. Tworzenie zapytań łączących się z usługami OneDrive dla Firm i SharePoint 199
  • Ćwiczenie 8.5. Od lokalnych folderów do usługi SharePoint 200
• Zagadnienia bezpieczeństwa 202
  • Usuwanie zapytań w drodze inspekcji skoroszytu Excela 203
• Podsumowanie 204

Rozdział 9. Wprowadzenie do języka M 205

• Nauka języka M 206
  • Etapy opanowania języka M 206
  • Informacje online 209
  • Informacje offline 209
  • Ćwiczenie 9.1. Zmienna #shared i dokumentacja wbudowanych funkcji 209
• Bloki konstrukcyjne w języku M 211
  • Ćwiczenie 9.2. Witaj, świecie! 211
  • Wyrażenie let 213
  • Zakresy i scalanie wyrażeń z wielu zapytań 215
  • Typy danych, operatory i wbudowane funkcje 217
• Podstawowe typy danych w języku M 218
  • Typ number 219
  • Typ time 220
  • Typ date 220
  • Typ duration 221
  • Typ text 222
  • Typ null 222
  • Typ logical 223
• Typy złożone 224
  • Typ list 224
  • Typ record 227
  • Typ table 230
• Warunki i wyrażenia „if” 232
  • Wyrażenie „if-then-else” 233
  • Wyrażenie if wewnątrz wyrażenia let 233
• Funkcje niestandardowe 235
  • Wywoływanie funkcji 236
  • Wyrażenie each 237
• Zaawansowane tematy 237
  • Obsługa błędów 238
  • Leniwe i zachłanne przetwarzanie wyrażeń 239
  • Pętle 239
  • Rekurencja 240
  • Funkcja List.Generate 240
  • Funkcja List.Accumulate 241
• Podsumowanie 242

Rozdział 10. Od złych do dobrych zapytań 245

• Źródła pułapek i ich skutki 246
  • Świadome działanie 247
  • Dobre praktyki 248
  • Modyfikacje formuł M 248
• Pułapka 1. Ignorowanie paska formuły 249
  • Ćwiczenie 10.1. Wyszukiwanie w pasku formuły statycznych odwołań do nazw kolumn 249
• Pułapka 2. Zmiana typów danych 251
• Pułapka 3. Niebezpieczne filtrowanie 253
  • Ćwiczenie 10.2. Część I. Filtrowanie czarnych produktów 254
  • Działanie warunków w filtrach 255
  • Ćwiczenie 10.2. Część II. Wyszukiwanie wartości w panelu filtru 257
• Pułapka 4. Zmiana kolejności kolumn 257
  • Ćwiczenie 10.3. Część I. Zmiana kolejności wybranych kolumn 258
  • Ćwiczenie 10.3. Część II. Niestandardowa funkcja FnZmianaKolejnościKolumn 260
• Pułapka 5. Usuwanie i wybieranie kolumn 260
  • Ćwiczenie 10.4. Usunięcie niepotrzebnych kolumn 261
• Pułapka 6. Zmiana nazw kolumn 263
  • Ćwiczenie 10.5. Zmiana nazw losowych kolumn 263
• Pułapka 7. Dzielenie kolumn 266
  • Ćwiczenie 10.6. Niewłaściwy podział kolumny 267
• Pułapka 8. Scalanie kolumn 269
• Inne pułapki i techniki zapobiegawcze 270
• Podsumowanie 270

Rozdział 11. Podstawy analizy tekstu 273

• Wyszukiwanie słów kluczowych w kolumnach tekstowych 274
  • Ćwiczenie 11.1. Proste wyszukiwanie słów kluczowych 274
  • Wyszukiwanie słów kluczowych z użyciem iloczynu kartezjańskiego 277
  • Ćwiczenie 11.2. Iloczyn kartezjański 279
  • Ćwiczenie 11.3. Wyszukiwanie słów kluczowych za pomocą niestandardowej funkcji 285
  • Jakiej metody użyć: zwykłego wyszukiwania, iloczynu kartezjańskiego czy niestandardowej funkcji? 288
• Dzielenie wpisów na słowa 288
  • Ćwiczenie 11.4. Trywialne dzielenie wpisów na słowa 288
  • Ćwiczenie 11.5. Odrzucenie stop-słów 293
  • Ćwiczenie 11.6. Wyszukiwanie słów kluczowych w wyodrębnionych słowach 295
  • Ćwiczenie 11.7. Chmura słowna w Power BI Desktop 302
• Podsumowanie 304

Rozdział 12. Zaawansowana analiza tekstu: rozpoznawanie znaczenia 305

• Usługa Microsoft Azure Cognitive Services 306
  • Klucze API i zasoby Azure 307
  • Uwagi dotyczące korzystania z usługi Azure Cognitive Services za pomocą edytora Power Query 310
• Tłumaczenie tekstu 312
  • Opis interfejsu Translator Text API 312
  • Ćwiczenie 12.1. Proste tłumaczenie 313
  • Ćwiczenie 12.2. Tłumaczenie wielu komunikatów 317
• Analiza nastroju tekstu 321
  • Czym jest interfejs Sentiment Analytics API 322
  • Ćwiczenie 12.3. Implementacja niestandardowej funkcji FnOkreślenieNastroju 324
  • Ćwiczenie 12.4. Przetwarzanie dużych ilości danych 333
• Wyodrębnianie kluczowych fraz 335
  • Ćwiczenie 12.5. Przystosowanie kodu do wyodrębniania kluczowych fraz 336
• Obsługa wielu języków 338
  • Zmiana oznaczenia języka 338
  • Dynamiczne wykrywanie języka 339
  • Ćwiczenie 12.6. Przystosowanie kodu do wykrywania języka 339
• Podsumowanie 340

Rozdział 13. Analiza sieci społecznościowych 341

• Pierwsze kroki z konektorem do Facebooka 342
  • Ćwiczenie 13.1. Uzyskiwanie informacji o ulubionych stronach 342
• Analiza sieci znajomych 347
  • Ćwiczenie 13.2. Wyszukiwanie bezpośrednich i pośrednich znajomych za pomocą Power BI 347
  • Ćwiczenie 13.3. Wyszukiwanie stron, które polubili znajomi 349
• Analiza stron na Facebooku 351
  • Ćwiczenie 13.4. Proste odczytywanie wpisów i komentarzy ze stron Facebooka 352
  • Krótka dygresja: pobieranie danych za wybrany okres 355
  • Ćwiczenie 13.5. Analiza aktywności użytkowników: zliczanie komentarzy i udostępnień 356
  • Ćwiczenie 13.6. Analiza porównawcza stron 358
• Podsumowanie 361

Rozdział 14. Końcowy projekt: wszystko razem 363

• Ćwiczenie 14.1. Ratowanie sytuacji u Światowych Importerów 363
  • Wskazówki 364
  • Część I. Przygotowanie danych 365
  • Część II. Dekompozycja tabel 367
  • Część III. Agregacja danych za 2018 r. 368
  • Część IV. Scalenie przychodów za lata 2015 – 2017 i za rok 2018 369
• Ćwiczenie 14.2. Porównanie tabel i śledzenie hakera 369
  • Wskazówki 370
  • Rozwiązanie 370
  • Szukanie śladów hakera w zmienionej tabeli 371
• Podsumowanie 372