Cyfroteka.pl

klikaj i czytaj online

Cyfro
Czytomierz
00310 006195 19033448 na godz. na dobę w sumie
Power Query w Excelu i Power BI. Zbieranie i przekształcanie danych - książka
Power Query w Excelu i Power BI. Zbieranie i przekształcanie danych - książka
Autor: Liczba stron: 376
Wydawca: Helion Język publikacji: polski
ISBN: 978-83-283-6062-4 Data wydania:
Lektor:
Kategoria: ebooki >> komputery i informatyka >> bazy danych >> inne
Porównaj ceny (książka, ebook (-30%), audiobook).

Czy praca na danych kojarzy Ci się z frustrującym ręcznym wklejaniem i oczyszczaniem danych w Excelu? A może tracisz mnóstwo energii na importowanie nieuporządkowanych danych pochodzących z różnych źródeł? Być może podejrzewasz, że Twoje arkusze kryją mnóstwo wartościowych informacji, ale nie wiesz, w jaki sposób je wydobyć i ile godzin musisz na to poświęcić? A może spędzasz sporo czasu na tworzeniu skomplikowanych raportów w Power BI lub programowaniu baz danych SQL Server? Dzięki wiedzy zawartej w tej książce wykonasz te zadania prościej i szybciej, a efekty będą o wiele bardziej niezawodne!

Ten praktyczny przewodnik po narzędziu Power Query nauczy Cię efektywnie przetwarzać dane w Excelu: importować, oczyszczać, przekształcać i wydobywać potrzebne informacje. Dowiesz się, jak sprawnie wykonywać typowe zadania i prace analityczne, jak zwiększyć swoją skuteczność dzięki opanowaniu podstaw języka M oraz jak zautomatyzować proces przygotowywania danych do przetwarzania. Zapoznasz się z możliwościami Microsoft Cognitive Services oraz konektora Power Query Web, a także z wykorzystaniem sztucznej inteligencji do rozpoznawania treści tekstu. Nabyte umiejętności będziesz mógł przetestować w realistycznym projekcie, któremu poświęcono ostatni rozdział!

W książce między innymi:

Power Query i Power BI: sprostasz wyzwaniu przetwarzania danych!

Znajdź podobne książki Ostatnio czytane w tej kategorii

Darmowy fragment publikacji:

Tytuł oryginału: Collect, Combine, and Transform Data Using Power Query in Excel and Power BI (Business Skills) Tłumaczenie: Andrzej Watrak ISBN: 978-83-283-6062-4 Authorized translation from the English language edition, entitled COLLECT, COMBINE, AND TRANSFORM DATA USING POWER QUERY IN EXCEL AND POWER BI, 1st Edition by RAVIV, GIL, published by Pearson Education, Inc, publishing as Microsoft Press, Copyright © 2019 by Gil Raviv. All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Pearson Education, Inc. Polish language edition published by Helion SA, Copyright © 2020. Microsoft and the trademarks listed at http://www.microsoft.com on the “Trademarks” web page are trademarks of the Microsoft group of companies. All other marks are the property of their respective owners. Wszelkie prawa zastrzeżone. Nieautoryzowane rozpowszechnianie całości lub fragmentu niniejszej publikacji w jakiejkolwiek postaci jest zabronione. Wykonywanie kopii metodą kserograficzną, fotograficzną, a także kopiowanie książki na nośniku filmowym, magnetycznym lub innym powoduje naruszenie praw autorskich niniejszej publikacji. Wszystkie znaki występujące w tekście są zastrzeżonymi znakami firmowymi bądź towarowymi ich właścicieli. Autor oraz Helion SA dołożyli wszelkich starań, by zawarte w tej książce informacje były kompletne i rzetelne. Nie biorą jednak żadnej odpowiedzialności ani za ich wykorzystanie, ani za związane z tym ewentualne naruszenie praw patentowych lub autorskich. Autor oraz Helion SA nie ponoszą również żadnej odpowiedzialności za ewentualne szkody wynikłe z wykorzystania informacji zawartych w książce. Helion SA ul. Kościuszki 1c, 44-100 Gliwice tel. 32 231 22 19, 32 230 98 63 e-mail: helion@helion.pl WWW: http://helion.pl (księgarnia internetowa, katalog książek) Drogi Czytelniku! Jeżeli chcesz ocenić tę książkę, zajrzyj pod adres http://helion.pl/user/opinie/poquex Możesz tam wpisać swoje uwagi, spostrzeżenia, recenzję. Printed in Poland. • Kup książkę • Poleć książkę • Oceń książkę • Księgarnia internetowa • Lubię to! » Nasza społeczność Spis tre(cid:258)ci Przedmowa ......................................................................................................11 O autorze ..........................................................................................................13 Podzi(cid:218)kowania .................................................................................................14 Wprowadzenie .................................................................................................17 Rozdzia(cid:239) 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(cid:239) 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 Poleć książkęKup książkę 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 (cid:146)(cid:200)czenie danych z kilku (cid:283)róde(cid:239) .........................................................................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 (cid:146)(cid:200)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 SPIS TRE(cid:165)CI Rozdzia(cid:239) 3. Rozdzia(cid:239) 4. 4 Poleć książkęKup książkę SPIS TRE(cid:165)CI Rozdzia(cid:239) 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(cid:239) 6. Dekomponowanie tabel .................................................................................145 Rozpoznawanie źle zaprojektowanych tabel .......................................................................... 146 Wprowadzenie do anulowania przestawienia ........................................................................ 148 Rozdzia(cid:239) 7. Ć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(cid:117)2 ...................................................... 155 Ćwiczenie 6.4. Dekompozycja tabeli z hierarchią agregacji danych 2(cid:117)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 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 5 Poleć książkęKup książkę SPIS TRE(cid:165)CI Rozdzia(cid:239) 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(cid:239) 9. Wprowadzenie do j(cid:218)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 6 Poleć książkęKup książkę SPIS TRE(cid:165)CI 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(cid:239) 10. Od z(cid:239)ych do dobrych zapyta(cid:241) .........................................................................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(cid:239) 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 7 Poleć książkęKup książkę SPIS TRE(cid:165)CI 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(cid:239) 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(cid:239) 13. Analiza sieci spo(cid:239)eczno(cid:258)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 8 Poleć książkęKup książkę SPIS TRE(cid:165)CI Rozdzia(cid:239) 14. Ko(cid:241)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 9 Poleć książkęKup książkę Poleć książkęKup książkę ROZDZIA(cid:146) 4. (cid:146)(cid:200)czenie niezgodnych tabel Połącz ekstrema, a otrzymasz prawdziwy środek. — Friedrich Schlegel W TYM ROZDZIALE NAUCZYSZ SI(cid:125): (cid:132) oceniać skutki błędnego połączenia tabel, (cid:132) poprawnie łączyć tabele z kolumnami o niezgodnych nazwach, (cid:132) poprawnie łączyć niezgodne tabele zapisane w folderze, (cid:132) ujednolicać nazwy kolumn za pomocą tabeli konwersji, (cid:132) stosować sposoby ujednolicania tabel różniące się złożonością i wydajnością. W rozdziale 3., „Łączenie danych z kilku źródeł”, dowiedziałeś się, jak łączyć tabele, aby uprościć sobie analizę danych i tworzenie raportów. Często zdarza się, że tabele mają różne formaty lub stopniowo zmieniają się w wyniku modyfikowania ich przez różnych użytkowników nieświadomych potencjalnego zagrożenia, na jakie narażają w ten sposób tworzenie raportów. Ten rozdział jest poświęcony poważnemu zadaniu: łączeniu niezgodnych tabel. Aby uświadomić sobie skalę problemu, wyobraźmy sobie, że tabele są podobne do skarpetek. Operacje, które wykonywałeś w rozdziale 3., były podobne do zakładania skarpetek. W tym rozdziale dowiesz się, jak sobie radzić ze skarpetkami, które są nie do pary. Na pewno zgodzisz się, że jeżeli skarpetki są takie same, noszenie ich jest przyjemnością. Jednak prawdziwym koszmarem jest zakładanie różnych skarpetek. Czasami znalezienie w stosie ubrań dwóch jednakowych skarpetek okazuje się niemożliwością. Czy można połączyć ekstrema i założyć skarpetki nie do pary? W praktyce, jeżeli mamy na myśli tabele, jest to możliwe. Ten rozdział opisuje, jak za pomocą Power Query radzić sobie z takimi tabelami, choćby były nie wiadomo jak niezgodnie. Problem z niezgodnymi tabelami W rozdziale 3. łączyłeś tabele, wykorzystując różne techniki. Ręcznie importowałeś kilka tabel i łączyłeś je, stosując przekształcenia Dołącz zapytania i Dołącz zapytania jako nowe. Importowałeś również tabele zapisane w folderze oraz w różnych arkuszach skoroszytu. We wszystkich ćwiczeniach przyjęte było milczące założenie, że wszystkie tabele mają ten sam format. Poleć książkęKup książkę POWER QUERY W EXCELU I POWER BI Zrozumiałe jest, że tabele przeznaczone do połączenia muszą mieć wspólny format. Jest to jednak idealistyczne założenie, rzadko sprawdzające się w praktyce. Często będziesz miał do czynienia z różnymi formatami tabel lub — co gorsza — z formatami, które w miarę upływu czasu będą różniły się coraz bardziej. W tym podrozdziale opisane są najczęściej spotykane przypadki niezgodności tabel i sposoby oceniania ich skutków. Co to jest niezgodno(cid:258)(cid:202) tabel? W tym rozdziale niezgodne tabele są to tabele zawierające tego samego rodzaju dane umieszczone w kolumnach o różnych nazwach. Załóżmy, że Tabela1 składa się z kolumn o nazwach pracownik, wiek i płeć, natomiast Tabela2 z kolumn Nazwisko, Wiek i K/M. Tabela 4.1 przedstawia różnice pomiędzy kolumnami obu tabel. TABELA 4.1. Przykład niezgodnych nazw kolumn Tabela1 Tabela2 Niezgodno(cid:258)(cid:202) Kolumna 1 Kolumna 2 Kolumna 3 pracownik wiek płeć Nazwisko Wiek K/M Różne nazwy Różne wielkości liter w nazwach Różne nazwy Symptomy niezgodno(cid:258)ci tabel i zwi(cid:200)zane z tym ryzyka Teraz, kiedy wiesz już, czym są niezgodne tabele, możesz poznać symptomy niezgodności towarzyszące łączeniu tabel lub odświeżaniu raportu opartego na tabelach, które pierwotnie miały takie same formaty, ale które po pewnym czasie zmieniły się. Podczas łączenia tabel wszystkie kolumny o takich samych nazwach są poprawnie łączone. Do tabeli wynikowej są kopiowane wszystkie wiersze z pierwszej kolumny. Ta sama zasada dotyczy wartości w drugiej kolumnie itd. Wszystkie wiersze kolumny są dołączane do tabeli wynikowej. Jak jednak Power Query radzi sobie z niezgodnymi kolumnami? W większości przypadków tworzy tabelę zawierającą wszystkie kolumny tabel źródłowych. Każdy wiersz z pierwszej tabeli jest kopiowany do tabeli wynikowej. W takich wierszach wszystkie kolumny należące do drugiej tabeli zawierają puste komórki. W taki sam sposób są kopiowane wiersze drugiej tabeli — wszystkie kolumny właściwe dla pierwszej tabeli pozostają puste. Jest to tzw. symptom podziału danych, pokazany na rysunku 4.1. Power Query nie konsoliduje wartości zawartych w różnych kolumnach, tylko zachowuje oryginalne kolumny z nieciągłymi danymi. Rysunek 4.1 ilustruje również inny symptom niezgodności tabel — brakujące wartości. Polega on na tym, że w tabeli wynikowej umieszczane są wszystkie kolumny pierwszej tabeli, a niezgodne kolumny z drugiej są beztrosko pomijane. W efekcie wiele wierszy w wynikowej tabeli zawiera puste komórki. Dalej w tym rozdziale poznasz dokładniej powyższe symptomy i sposoby radzenia sobie z nimi. Jeżeli zlekceważysz opisane symptomy, pojawią się oczywiste ryzyka. Podzielone i brakujące dane mogą być przyczyną błędów i niedokładności w raportach. Na szczęście wiesz, jak objawiają się symptomy niezgodności tabel i rozumiesz ich wagę. Czas więc, abyś dowiedział się, jak można rozwiązywać tego rodzaju problemy i pomyślnie łączyć tabele. 100 Poleć książkęKup książkę ROZDZIA(cid:146) 4. (cid:132) (cid:146)(cid:107)CZENIE NIEZGODNYCH TABEL RYSUNEK 4.1. Są dwa symptomy niezgodności tabel: podzielone dane i brakujące wartości; oba mogą skutkować błędnymi raportami (cid:109)wiczenie 4.1. Uzgadnianie kolumn: podej(cid:258)cie reaktywne W tym ćwiczeniu nauczysz się uzgadniać nazwy kolumn i unikać symptomów podziału i braku danych opisanych wcześniej w tym rozdziale (patrz rysunek 4.1). W tym ćwiczeniu, demonstrującym problem i jego rozwiązanie, wykorzystasz skoroszyt z danymi firmy Fabryka Przygód. Przeanalizujmy najpierw symptom podziału danych na prostym przykładzie i zastanówmy się, jak można reaktywnie uzgodnić nazwy kolumn. W ćwiczeniu wykorzystasz dwa pliki: (cid:132) R04-01 - akcesoria.xlsx, (cid:132) R04-01 - rowery.xlsx. Skoroszyty te zawierają katalogi produktów dwóch kategorii, takich jak Akcesoria i Rowery. Koszty produktów są zapisane w kolumnie Koszt tabeli Rowery oraz w kolumnie Koszt standardowy w tabeli Akcesoria. Spróbujmy teraz połączyć obie tabele. 1. Utwórz nowy skoroszyt w Excelu lub raport w Power BI Desktop i zaimportuj do niego oba powyższe pliki. 2. Zaznacz zapytanie Akcesoria i użyj przekształcenia Dołącz zapytania jako nowe. 3. Przyjrzyj się w zawartości nowego zapytania Append1 w panelu podglądu. Zwróć uwagę, że zawiera ono zarówno kolumnę Koszt, jak i Koszt standardowy. W wierszach z rowerami pierwsza kolumna zawiera wartości liczbowe, a druga wartości null, natomiast w wierszach z akcesoriami pierwsza kolumna zawiera wartości null, a druga wartości liczbowe. Uwaga. Zapewne zauważyłeś, że połączona tabela nie zawiera informacji o kategorii produktu, zawartej w nazwie pliku i arkusza. W rozdziale 5., „Zachowywanie kontekstu”, dowiesz się, jak zachowywać konteksty tabel. 101 Poleć książkęKup książkę POWER QUERY W EXCELU I POWER BI 4. Zaznacz zapytanie Akcesoria i zmień nazwę kolumny Koszt standardowy na Koszt. Gdy ponownie zaznaczysz zapytanie Append1, okaże się, że po uzgodnieniu nazw kolumn zniknął symptom podziału danych. Opisane tu rozwiązanie jest proste i na pewno będziesz je często stosował do uzgadniania nazw kolumn. Czasami jednak nie będziesz mógł robić tego ręcznie. Załóżmy, że masz dwie tabele, każda zawierająca 100 kolumn. Ręczne zmienianie nazw w opisany tu sposób byłoby powtarzalnym i czasochłonnym zadaniem. Co gorsza, w większości przypadków podczas łączenia tabel zapisanych w folderze ręczna zmiana nazwy kolumn nie jest możliwa, o czym przekonasz się w następnym podrozdziale. (cid:146)(cid:200)czenie niezgodnych tabel zapisanych w folderze Wcześniej w tym rozdziale nauczyłeś się ręcznie uzgadniać nazwy kolumn przed połączeniem tabel. Jeżeli w folderze zapisanych jest wiele tabel z niezgodnymi kolumnami, zadanie staje się jeszcze trudniejsze. Z jednej strony, ręczne uzgadnianie nie jest dobrą praktyką szczególnie wtedy, kiedy plików jest dużo. Z drugiej, przekształcenie polegające na połączeniu plików zapisanych w folderze nie daje możliwości indywidualnego traktowania każdego pliku. Aby sprostać temu wyzwaniu, możesz napisać kod, który zmienia nazwy kolumn u źródła. Załóżmy jednak, że w folderze jest 60 skoroszytów i każdy zawiera tabelę składającą się z 20 kolumn. Należałoby sprawdzić 1200 kolumn pod kątem zgodności nazw i ręcznie je zmienić. To nie jest praktyczna metoda zwłaszcza wtedy, gdy nie jesteś autorem plików, tylko otrzymujesz je regularnie z zewnątrz. W rozdziale 3. nauczyłeś się łączyć tabele zapisane w folderze. Jak już się przekonałeś, wykonując ćwiczenie opisane w tym rozdziale, Power Query wykorzystuje nagłówki pierwszej tabeli do utworzenia kolumn tabeli wynikowej. Podczas łączenia niezgodnych tabel zapisanych w folderze szybko pojawi się symptom braku danych. Następne ćwiczenie demonstruje ten symptom oraz sposób pomyślnego łączenia tabel zapisanych w folderze. (cid:109)wiczenie 4.2. Cz(cid:218)(cid:258)(cid:202) I. Symptom braku warto(cid:258)ci Wróćmy do naszej fikcyjnej firmy Fabryka Przygód. Menedżer każdej z czterech linii produktów prowadzi w skoroszycie Excela własny katalog. Ty, jako szef działu analiz, dostałeś zadanie połączenia wszystkich tabel i przygotowania raportu. Jednak po otwarciu skoroszytów stwierdziłeś, że menedżerowie są kreatywni i zmienili wiele nazw kolumn (patrz rysunek 4.2). Przy próbie połączenia tabel zapisanych w folderze okazało się, że jest to niemożliwe. W tym ćwiczeniu dowiesz się, jak sobie radzić z takimi problemami. RYSUNEK 4.2. Skoroszyty z produktami firmy Fabryka Przygód zawierają kolumny o niezgodnych nazwach 102 Poleć książkęKup książkę 1. Przejrzyj pliki zapisane w folderze C:\Dane\R04\R04-02 - produkty: ROZDZIA(cid:146) 4. (cid:132) (cid:146)(cid:107)CZENIE NIEZGODNYCH TABEL a. R04-02 - akcesoria.xlsx, b. R04-02 - rowery.xlsx, c. R04-02 - komponenty.xlsx, d. R04-02 - ubrania.xlsx. 2. Zwróć uwagę, że nazwy kolumn zbyt różnią się od siebie, aby można było poprawiać indywidualnie każdą z nich. 3. Zaimportuj cztery niezgodne tabele z folderu, aby stwierdzić symptom brakujących wartości. W Excelu: kliknij kartę Dane, a następnie polecenia Pobierz dane/Z pliku/Z folderu. W Power BI Desktop: kliknij ikonę Pobierz dane, a następnie Folder i Połącz. 4. W oknie Folder przejdź do folderu C:\Dane\R04\R04-02 - produkty i kliknij OK. 5. W oknie, które się pojawi (o takiej samej nazwie jak nazwa folderu), kliknij przycisk Połącz, a następnie Połącz i edytuj. 6. W oknie Połącz pliki wybierz w liście Przykładowy plik dowolny skoroszyt, zaznacz Arkusz1 i kliknij OK. Uwaga. W liście Przykładowy plik można wybrać skoroszyt i zastosować jego format do wszystkich plików w folderze. Efektem wyboru są m.in. nazwy kolumn w wynikowej tabeli. Ponieważ każda tabela zawiera kolumny o różnych nazwach, wkrótce zobaczysz symptom brakujących wartości i jego skutki. 7. W oknie edytora przewiń zawartość podglądu, aż zawartość kolumny Source.Name zmieni się z R04-02 - akcesoria.xlsx na R04-02 - komponenty.xlsx. Kolumny Produkt, ID, KosztStandardowy i CenaListowa będą zawierały wartości null, tak jak na rysunku 4.3, które przełożą się na puste wartości w raporcie. RYSUNEK 4.3. Symptom brakujących wartości pojawia się podczas importowania z folderu wielu tabel z niezgodnymi nazwami kolumn Zanim rozwiążesz problem brakujących wartości, otwórz panel Zapytania i zaznacz zapytanie Przekształć przykładowy plik, które w tej książce jest nazywane zapytaniem przykładowym. Zapytanie to zostało utworzone podczas łączenia plików zapisanych w folderze. Gdy je zaznaczysz, zauważysz, że w panelu podglądu dane akcesoriów są pokazywane poprawnie. Rozwiązywanie problemu brakujących wartości należy zawsze zaczynać od zapytania przykładowego. 103 Poleć książkęKup książkę POWER QUERY W EXCELU I POWER BI Wskazówka. Power Query podczas importowania plików z folderu tworzy przykładową funkcję, przykładowe zapytanie, plik zapytania i parametr. Obiekty te są wykorzystywane do łączenia kilku plików na podstawie wybranych danych. Główne przekształcenie każdego pliku jest zaimplementowane w funkcji. Przekształcenia poszczególnych plików dostosowuje się poprzez wprowadzanie zmian w przykładowym zapytaniu. Zmiany te są uwzględniane w funkcji i wprowadzane we wszystkich plikach. Podczas łączenia plików zapisanych w folderze sprawdzaj, jakie zmiany wprowadza zapytanie Przekształć przykładowy plik we wszystkich plikach przed ich połączeniem. (cid:109)wiczenie 4.2. Cz(cid:218)(cid:258)(cid:202) II. Za(cid:239)o(cid:285)enie takiej samej kolejno(cid:258)ci kolumn i ujednolicenie nag(cid:239)ówków Aby rozwiązać problem brakujących wartości, przyjmijmy założenie, że kolumny — choć różnią się nagłówkami — są we wszystkich tabelach ułożone w takiej samej kolejności. W takim przypadku do połączenia tabel będzie można użyć domyślnych nazw kolumn Column1, Column2 itd. Poniższe kroki należy wykonać po krokach od 1. do 6. z części I ćwiczenia 4.2. 8. Zaznacz zapytanie Przekształć przykładowy plik i zmień jego nazwę na Przykładowe produkty. 9. W panelu Zastosowane kroki usuń ostatni krok Nagłówki o podwyższonym poziomie. Kolumny uzyskają nazwy Column1, Column2 itd. Jeżeli wszystkie kolumny we wszystkich tabelach będą miały takie same, ogólne nazwy, wtedy zostaną poprawnie połączone. 10. Kliknij zapytanie R04-02 - produkty i zwróć uwagę na komunikat Expression.Error: Nie można znaleźć kolumny „Produkty” w tabeli. 11. W panelu Zastosowane kroki usuń ostatni krok Zmieniono typ. Teraz połączona tabela nie będzie zawierała brakujących wartości. Możesz się o tym przekonać, przewijając jej zawartość. Wszystkie wartości z tabeli Akcesoria będą dostępne. 12. W karcie Przekształć kliknij ikonę Użyj pierwszego wiersza jako nagłówków, aby zmienić nazwy kolumn. Niestety, połączona tabela zawiera również nagłówki poszczególnych tabel. W rozdziale 3. podobny problem rozwiązałeś za pomocą filtru. Tutaj jednak kolumny mają różne nazwy i trudno określić, które z nich należy zastosować w filtrze. Dlatego przed krokiem użyj indeksu w celu zidentyfikowania wierszy z nagłówkami. 13. W panelu Zapytania kliknij zapytanie Przykładowe produkty. Następnie w karcie Dodaj kolumnę kliknij ikonę Kolumna indeksu. Pojawi się kolumna zawierająca indeksy wierszy. Pierwszy wiersz każdej tabeli, zawierający nagłówki kolumn, ma indeks 0. 14. Wróć do zapytania R04-02 - produkty i usuń nagłówki za pomocą filtru odrzucającego indeks 0. W tym celu kliknij przycisk filtru w nagłówku ostatniej kolumny o nazwie 0. W panelu, który się pojawi, usuń zaznaczenie przy pozycji 0 i kliknij OK. 15. Usuń pierwszą i ostatnią kolumnę (o nazwach odpowiednio R04-02 - akcesoria.xlsx i 0). Aby zapytanie było uniwersalne, zmodyfikuj je w sposób opisany w rozdziale 3., „Łączenie danych z kilku źródeł”. W tym przypadku zastąp statyczne odwołanie do kolumny Source.Name dynamicznym wyrażeniem Table.ColumnName(...){0}. 104 Poleć książkęKup książkę ROZDZIA(cid:146) 4. (cid:132) (cid:146)(cid:107)CZENIE NIEZGODNYCH TABEL W pasku formuły znajduje się następujący kod: = Table.RemoveColumns(# Przefiltrowano wiersze ,{ R04-02 - akcesoria.xlsx , 0 }) Zmień go na następujący: = Table.RemoveColumns(# Przefiltrowano wiersze ,{Table.ColumnNames(# Przefiltrowano (cid:180)wiersze ){0}, 0 }) Teraz możesz załadować połączoną tabelę i rozpocząć analizę danych. Gotowe rozwiązanie jest zapisane w plikach R04-02 - rozwiązanie.xlsx i R04-02 - rozwiązanie.pbix. W kroku 8. zmieniłeś nagłówki kolumn na ogólne Column1, Column2 itd. Mogłeś to zrobić, ponieważ przyjąłeś założenie, że kolumny we wszystkich tabelach są ułożone w takiej samej kolejności. Zastanówmy się jednak, co by się stało, gdyby w jednej z tabel kolejność kolumn była inna, np. w tabeli Rowery zamienione byłyby miejscami kolumny Nazwa i Kod_produktu. Po zastosowaniu opisanej wyżej procedury w wynikowej tabeli powstałby bałagan. Nazwy produktów z tabeli Rowery zostałyby potraktowane jak kody produktów i odwrotnie. Aby temu zapobiec, mógłbyś się umówić z menedżerem działu rowerów, aby nie zmieniał kolejności kolumn w źródłowej tabeli. Jest jednak inne rozwiązanie, opisane dalej w tym rozdziale, polegające na ujednoliceniu nagłówków kolumn przed połączeniem tabel. (cid:109)wiczenie 4.3. Proste ujednolicenie nag(cid:239)ówków za pomoc(cid:200) funkcji Table.TransformColumnNames Automatyczne kojarzenie i zmienianie nazw kolumn w wielu tabelach jest ważną częścią procesu łączenia niezgodnych tabel. Jeżeli takich tabel jest dużo, wtedy ręczne zmienianie nazw wybranych kolumn, tak jak w ćwiczeniu 4.1, jest niepraktycznym rozwiązaniem szczególnie wtedy, gdy plików jest wiele i często się zmieniają. Jeżeli można przyjąć założenie, że kolumny we wszystkich tabelach są ułożone w tej samej kolejności, wtedy sprawdzi się rozwiązanie zaprezentowane w ćwiczeniu 4.2. Jeżeli jednak kolejność kolumn nie jest gwarantowana, wtedy trzeba zastosować bardziej zaawansowany sposób, jakim jest ujednolicenie nazw kolumn. Ujednolicenie nazw kolumn jest to prosta operacja na tekstach. Zazwyczaj, aby uzyskać spójny format tabel, wystarczy wprowadzić niewielkie zmiany we wszystkich niezgodnych kolumnach, np. zastąpić znaki podkreślenia spacjami lub zmienić wszystkie litery na wielkie. Prosta operacja na tekstach, np. zmiana wielkości liter, może być bardzo skutecznym sposobem uzgadniania nazw kolumn. Narzędzie Power Query rozróżnia wielkość liter, zatem np. kolumny o nazwach kolumna1 i Kolumna1 traktuje jako różne. Jeżeli tabele często się zmieniają, np. małe litery w nazwach kolumn są zamieniane na wielkie lub odwrotnie, wtedy ujednolicenie ich wielkości może znacznie zredukować niezgodności. Aby w prosty sposób przekształcić nazwę kolumny, użyj funkcji Table.TransformColumnNames. Jeżeli przykładowo ostatni krok w panelu Zastosowane kroki nazywa się Poprzedni krok, wtedy poniższa formuła zamieni pierwsze litery każdego słowa w nazwie kolumny na wielkie: = Table.TransformColumnNames(# Poprzedni krok , Text.Proper) Natomiast poniższa formuła zamieni wszystkie znaki podkreślenia na spacje: = Table.TransformColumnNames(# Poprzedni krok , each Replacer.ReplaceText(_, _ , )) W tym ćwiczeniu wykorzystasz powyższe formuły do połączenia tabel produktów firmy Fabryka Przygód. 105 Poleć książkęKup książkę POWER QUERY W EXCELU I POWER BI Uwaga. Na tym etapie nie musisz w pełni rozumieć składni opisywanych formuł. Ten temat będzie opisany w rozdziale 9., „Wprowadzenie do języka M”. Na razie musisz jedynie wiedzieć, że funkcje Text.Proper i Replacer.ReplaceText służą do przekształcania tekstu. Obie można stosować wewnątrz funkcji Table.TransformColumnNames. W poprzednim przykładzie funkcja Text.Proper zamieniała pierwsze litery każdego słowa na wielkie, a Replacer.ReplaceText zamieniała wszystkie znaki podane w drugim argumencie na znaki wskazane w trzecim argumencie. 1. Przejrzyj pliki zapisane w folderze C:\Dane\R04\R04-03 - produkty: a. R04-03 - akcesoria.xlsx, b. R04-03 - rowery.xlsx, c. R04-03 - komponenty.xlsx, d. R04-03 - ubrania.xlsx. Pliki te zawierają te same dane, co w ćwiczeniu 4.2, ale zostały w nich wprowadzone dwie zamierzone zmiany. Po pierwsze, inne są nagłówki kolumn. Różnice te można zniwelować, zamieniając pierwsze litery słów na wielkie oraz zastępując znaki podkreślenia spacjami. Po drugie, w tabeli Rowery zamienione są miejscami dwie pierwsze kolumny, przez co nie można ujednolicić ich nagłówków w sposób opisany w ćwiczeniu 4.2. 2. Utwórz nowy skoroszyt w Excelu lub raport w Power BI Desktop i wykonaj poniższe kroki. W Excelu: kliknij kartę Dane, a następnie polecenia Pobierz dane/Z pliku/Z folderu. W Power BI Desktop: kliknij ikonę Pobierz dane, a następnie Folder i Połącz. 3. W oknie Folder przejdź do folderu C:\Dane\R04\R04-03 - produkty i kliknij OK. 4. W oknie, które się pojawi (o takiej samej nazwie jak nazwa folderu), kliknij przycisk Połącz, a następnie Połącz i edytuj. 5. W oknie Połącz pliki wybierz w liście Przykładowy plik dowolny skoroszyt, zaznacz Arkusz1 i kliknij OK. Zgodnie z oczekiwaniami, w edytorze Power Query pojawił się symptom brakujących wartości. 6. Kliknij zapytanie Przekształć przykładowy plik i zmień jego nazwę na Przykładowe produkty. 7. W pasku formuły kliknij przycisk fx (patrz rysunek 4.4). 8. Zwróć uwagę, że w panelu Zastosowane kroki pojawił się nowy krok Niestandardowe1, a w pasku formuły następujący kod: = # Nag(cid:239)ówki o podwy(cid:285)szonym poziomie Krok Nagłówki o podwyższonym poziomie był ostatni na liście, zanim kliknąłeś przycisk fx. Ciąg # Nag(cid:239)ówki o podwy(cid:285)szonym poziomie jest zmienną zawierającą wynik wykonania poprzedniego kroku. Ponieważ wynikiem tym jest tabela z niezgodnymi kolumnami, możesz zmienić wszystkie litery na małe, używając tej zmiennej z funkcjami Table.TransformColumnNames i Text.Lower. W tym celu wpisz następującą formułę: = Table.TransformColumnNames(# Nag(cid:239)ówki o podwy(cid:285)szonym poziomie , Text.Lower) 106 Poleć książkęKup książkę ROZDZIA(cid:146) 4. (cid:132) (cid:146)(cid:107)CZENIE NIEZGODNYCH TABEL RYSUNEK 4.4. Za pomocą przycisku fx można utworzyć niestandardowy krok i zdefiniować formułę nowego przekształcenia modyfikującego poprzedni krok 9. Naciśnij Enter. Zwróć uwagę, że teraz nazwy wszystkich kolumn składają się wyłącznie z małych liter. Gdybyś chciał zamienić pierwsze litery wszystkich słów na wielkie, powinieneś użyć w powyższej formule funkcji Text.Proper zamiast Text.Lower. Uwaga. Jeśli nawet pracujesz na tabelach, których kolumny mają zgodne nazwy i są ułożone we właściwej kolejności, powinieneś stosować powyższe formuły, aby zapobiec sytuacji, w której autor tabeli postanowi zmienić wielkość liter na małe lub wielkie albo ich kombinację. 10. Aby zamienić znaki podkreślenia spacjami, kliknij ponownie w pasku formuły przycisk fx. 11. Zwróć uwagę, że w panelu Zastosowane kroki pojawił się nowy krok Niestandardowe2, a w pasku formuły następujący kod: = Niestandardowe1 Użyj zmiennej Niestandardowe1 z funkcjami Table.TransformColumnNames i Replacer.ReplaceText, aby zamienić znaki podkreślenia na spacje. W tym celu wpisz następującą formułę: = Table.TransformColumnNames(Niestandardowe1, each Replacer.ReplaceText(_, _ , )) 12. Naciśnij Enter. Zwróć uwagę, że w nazwach kolumn znajdują się spacje, a nie znaki podkreślenia. Teraz nagłówki są bardziej czytelne. 13. Zaznacz zapytanie R04-03 - produkty i w panelu Zastosowane kroki usuń ostatni krok Zmieniono typ. Teraz, kiedy wszystkie kolumny zawierają w nagłówkach spacje zamiast znaków podkreślenia, wszystkie tabele zostały połączone poprawnie i zniknął symptom brakujących wartości. Gotowe rozwiązanie jest zapisane w plikach R04-03 - rozwiązanie.xlsx i R04-03 - rozwiązanie.pbix. 107 Poleć książkęKup książkę POWER QUERY W EXCELU I POWER BI Tabela konwersji W ćwiczeniu 4.3 zastosowałeś proste przekształcenie nazw niezgodnych kolumn i zastąpiłeś znaki podkreślenia spacjami. Niestety, w praktyce proste przekształcenia nie wystarczają. Aby zademonstrować taki przypadek, wróćmy do naszej firmy Fabryka Przygód i danych użytych w ćwiczeniu 4.2. Tym razem dodatkowo będą zamienione miejscami dwie kolumny, tak jak w ćwiczeniu 4.3, zatem opisane techniki nie będą działać. W opisanej niżej serii ćwiczeń wykorzystasz dane zapisane w folderze C:\Dane\R04\R04-04 - produkty. Każdy z menedżerów inaczej nazwał kolumnę zawierającą identyfikator produktu: ID, Kod_produktu, Nr_produktu i Kod produktu. Nie możesz więc ujednolicić nagłówków za pomocą prostych przekształceń. Zamiast tego utworzysz tabelę konwersji zawierającą oryginalne i ujednolicone nazwy kolumn. Rysunek 4.5 przedstawia niezgodne kolumny i ich reprezentację w tabeli konwersji. Kolumna Źródło zawiera niezgodną nazwę, która będzie zastąpiona ujednoliconą nazwą w kolumnie Cel. RYSUNEK 4.5. Tabela konwersji zawiera powiązane niezgodne i ujednolicone nazwy kolumn Tworzenie tabeli konwersji może być czasochłonnym zajęciem. Tabela nie musi jednak zawierać wszystkich nazw. Przykładowo na rysunku 4.5 nie ma pary Nazwa (cid:198) Nazwa, ponieważ nie jest potrzebna. Tabela powinna zawierać tylko pary niezgodnych nazw. Jeżeli są setki kolumn, wtedy w tabeli konwersji należy umieścić tylko różniące się nazwy. Wskazówka. Jeżeli tabela konwersji jest tabelą zewnętrzną zapisaną w skoroszycie Excela, można najpierw umieścić w niej kilka niezgodnych nazw kolumn i stopniowo ją uzupełniać w miarę potrzeb. Po utworzeniu zapytania wykorzystującego tabelę konwersji nie potrzeba go modyfikować w celu dalszego ujednolicania kolumn. Zamiast tego wystarczy odpowiednio zmodyfikować tabelę konwersji i odświeżyć raport. Teraz, gdy wiesz już, jak tworzy się tabelę konwersji, nauczysz się ją wykorzystywać do ujednolicania niezgodnych kolumn. Użyjesz do tego celu przekształcenia Scal zapytania dostępnego w karcie Strona główna. Jak pamiętasz z rozdziału 2, „Podstawowe operacje przygotowywania danych”, przekształcenie to służy do łączenia kilku tabel na podstawie takich samych wartości zapisanych w określonych kolumnach. Zanim dowiesz się, jak używać przekształcenia Scal zapytania z tabelą konwersji, zatrzymajmy się na chwilę, aby omówić jeden z największych problemów z ujednolicaniem kolumn. Pod koniec tego rozdziału dowiesz się, jak zastępować nazwy kolumn za pomocą funkcji Table.TransformColumnNames 108 Poleć książkęKup książkę ROZDZIA(cid:146) 4. (cid:132) (cid:146)(cid:107)CZENIE NIEZGODNYCH TABEL opisanej w ćwiczeniu 4.3, funkcji niestandardowej oraz tabeli konwersji. Ponieważ technika ta wymaga głębszej znajomości języka M, wcześniej poznasz trzy sposoby wykorzystujące jedynie interfejs edytora, bez konieczności stosowania zaawansowanych formuł w języku M. Interfejs edytora Power Query jest wyposażony w wiele przekształceń operujących na kolumnach, ale nie na ich nagłówkach. Ćwiczenia 4.4, 4.5 i 4.6 skupiają się na ujednolicaniu nazw kolumn przy użyciu trzech technik wykorzystujących interfejs edytora. Wymagana jest w nich minimalna znajomość języka M. W tych ćwiczeniach nie jest wykorzystywana funkcja Table.TransformColumnNames, ponieważ wymagałaby ona utworzenia niestandardowej funkcji. Ćwiczenia te demonstrują, jak zamiast tego użyć funkcjonalności dostępnych w interfejsie edytora. Wskazówka. Jeżeli poczułeś się pewnie w języku M, spodoba Ci się czwarta technika, czekająca na Ciebie pod koniec rozdziału. Prawdopodobnie uznasz, że ćwiczenie 4.7 jest najlepszym sposobem łączenia niezgodnych tabel i ujednolicania kolumn. Warto jednak, abyś wykonał poniższe ćwiczenia, dzięki którym docenisz potęgę dostępnych funkcjonalności, jak również poznasz ich zalety i wady. Ponadto mogą się one przydać w innych sytuacjach. (cid:109)wiczenie 4.4. Transpozycja z u(cid:285)yciem tabeli konwersji Aby ujednolicić niezgodne kolumny za pomocą tabeli konwersji, musisz najpierw tymczasowo przekształcić nazwy kolumn w jednokolumnową tabelę, scalić z nią tabelę konwersji, zastąpić niezgodne nazwy docelowymi i uzyskanie wyniki z powrotem przekształcić w nagłówki kolumn. W tym ćwiczeniu poznasz pierwszą z wymienionych wyżej technik. Najpierw zaimportuj tabele z folderu, wykonując tę samą sekwencję operacji, jak w ćwiczeniach 4.2 i 4.3. 1. Zaimportuj tabelę konwersji. W Excelu: utwórz nowy skoroszyt, następnie kliknij kartę Dane i polecenia Pobierz dane/ Z pliku/Ze skoroszytu. Wskaż plik R04-04 - tabela konwersji.xlsx. W oknie Nawigator kliknij tabelę Konwersja_nagłówków, a następnie przycisk Przekształć dane. Na koniec kliknij Zamknij i załaduj do oraz zaznacz opcję Utwórz tylko połączenie. W Power BI Desktop: utwórz nowy raport, następnie rozwiń menu Pobierz dane i wybierz Excel. Wskaż plik R04-04 - tabela konwersji.xlsx. W oknie Nawigator kliknij tabelę Konwersja_nagłówków, a następnie przycisk Przekształć dane. W panelu Zapytania kliknij prawym przyciskiem myszy nowe zapytanie i usuń zaznaczenie polecenia Włącz ładowanie. Na koniec kliknij Zamknij i zastosuj. 2. Zaimportuj następujące pliki znajdujące się w folderze C:\Dane\R04\R04-04 - produkty: a. R04-04 - akcesoria.xlsx, b. R04-04 - rowery.xlsx, c. R04-04 - komponenty.xlsx, d. R04-04 - ubrania.xlsx. W Excelu: kliknij kartę Dane, a następnie polecenia Pobierz dane/Z pliku/Z folderu. W Power BI Desktop: kliknij ikonę Pobierz dane, a następnie Folder i Połącz. 3. W oknie Folder przejdź do folderu C:\Dane\R04\R04-04 - produkty i kliknij OK. 109 Poleć książkęKup książkę POWER QUERY W EXCELU I POWER BI 4. W oknie, które się pojawi (o takiej samej nazwie jak nazwa folderu), kliknij przycisk Połącz, a następnie Połącz i edytuj. 5. W oknie Połącz pliki wybierz w liście Przykładowy plik dowolny skoroszyt, zaznacz Arkusz1 i kliknij OK. 6. W panelu Zapytania kliknij zapytanie Przekształć przykładowy plik i zmień jego nazwę na Przykładowe produkty. Jak pamiętasz, jest to przykładowe zapytanie, a więc wprowadzone w nim zmiany będą dotyczyły również zapytania R04-04 - produkty. 7. Zmień nazwę zapytania R04-04 - produkty na Dołączone produkty. Zapisz skoroszyt lub raport, a następnie utwórz jego trzy kopie, które wykorzystasz w ćwiczeniach 4.5, 4.6 i 4.7. Transpozycja Pierwsza technika ujednolicania nazw kolumn polega na użyciu przekształcenia Transponuj dostępnego w kolumnie Przekształć. W jego efekcie każda komórka kolumny X i wiersza Y jest zamieniana na kolumnę Y i wiersz X. W edytorze Power Query o wiele łatwiej manipulować nazwami kolumn, jeżeli są one ułożone pionowo w jednej kolumnie. W tym momencie przydaje się przekształcenie Transponuj. Jednak przy próbie transpozycji tabeli przekonasz się, że nie są zachowywane oryginalne nazwy kolumn. Aby tego uniknąć, musisz anulować użycie pierwszego wiersza tabeli jako nazw kolumn. W tym celu otwórz skoroszyt lub raport, który utworzyłeś w krokach od 1. do 7., i uruchom edytor Power Query. W panelu Zapytania zaznacz zapytanie Przykładowe produkty i wykonaj następujące kroki. 8. W panelu Zastosowane kroki usuń krok Nagłówki o podwyższonym poziomie. Nagłówki kolumn zostaną umieszczone w pierwszym wierszu tabeli. 9. W karcie Przekształć kliknij ikonę Transponuj. Teraz kolumna Column1 zawiera nazwy kolumn, które trzeba ujednolicić. Sekwencja scale(cid:241) Dotarłeś do punktu, w którym scalisz tabelę konwersji z przetransponowaną tabelą i za jednym razem zamienisz niezgodne nazwy kolumn na docelowe. Wykonaj następujące kroki. 10. Zaznacz zapytanie Przykładowe produkty, a następnie w karcie Strona główna kliknij ikonę Scal zapytania. 11. W oknie Scalanie, które się pojawi, kliknij w tabeli Przykładowe produkty kolumnę Column1, a następnie z rozwijanej listy pod tabelą wybierz Konwersja_nagłówków (patrz rysunek 4.6). W tabeli niżej zaznacz kolumnę Źródło. 12. Sprawdź, czy w rozwijanej liście Rodzaj sprzężenia jest wybrana opcja Lewe zewnętrzne (wszystkie z prawej, pasujące z drugiej), a następnie kliknij OK. 13. W ostatniej kolumnie, Konwersja_nagłówków, zawierającej obiekty Table, kliknij przycisk po prawej stronie nagłówka lub zaznacz całą kolumnę i w karcie Przekształć kliknij ikonę Rozwiń. 14. Aby wyodrębnić kolumnę Cel z tabeli Konwersja_nagłówków, usuń w panelu, który się pojawi, zaznaczenie Źródło i kliknij OK. Pojawi się nowa kolumna o nazwie Konwersja_nagłówków.Cel. Kolumna ta zawiera wartość z kolumny Cel, jeżeli wartość w kolumnie Column1 jest taka sama jak w kolumnie Źródło tabeli konwersji, lub jest to wartość null w przeciwnym przypadku. W tym drugim przypadku zos
Pobierz darmowy fragment (pdf)

Gdzie kupić całą publikację:

Power Query w Excelu i Power BI. Zbieranie i przekształcanie danych
Autor:

Opinie na temat publikacji:


Inne popularne pozycje z tej kategorii:


Czytaj również:


Prowadzisz stronę lub blog? Wstaw link do fragmentu tej książki i współpracuj z Cyfroteką: