fbpx
Power Query - inna metoda na głoda

O tym, że w danych jest mnóstwo informacji o firmie nie trzeba prawdopodobnie nikogo przekonywać.

Skąd firma może takie dane pozyskać:

  • dane wewnętrzne z systemów wewnętrznych (system finansowy, system zakupowy)
  • dane zewnętrzne od dostawców towarów i usług dla naszej firmy.

No fajnie mamy te dane i co z tego, skoro nie możemy ich użyć, ponieważ są one:

  • niekompletne;
  • brudne (np.: 1 dostawca jest pod różnymi nazwami i różnymi ID dostawcy, miasta raz są pisane tylko kapitalikami, a raz tylko małymi literami);
  • nie do połączenia – integracja danych z systemu księgowego i zakupowego, na przykład po ID Zamówienia, przy użyciu „wyszukaj.pionowo” to nie lada wyzwanie, a komputer analityka często zawiesza się, ponieważ danych jest tak dużo;
  • przysyłane do nas, na przykład od dostawców, co miesiąc i trzeba je doklejać do istniejącego rejestru w Excelu i to trwa godzinami, dniami;
  • w różnych formatach – np.: operatorów kart paliwowych mamy 3 i każdy z nich udostępnia dane z tankowań, a kolumny są w innej kolejności;
  • obrabiane ręcznie – sklejanie danych ręcznie niesie ryzyko dodania tych samych danych 2 razy i jest problem.

Wszytko to co jest napisane powyżej to prawda, jeżeli używa się niewłaściwych narzędzi do zbierania i przetwarzania danych.

Sposobem na unikniecie powyższego jest wykorzystanie Power Query. Jest to darmowy dodatek do Excel-a dzięki któremu dzieje się magia w procesie pobierania, zbierania i transformacji danych (ang. ETL, Extract Transform and Load).

Power Query (PQ) w Excelu umożliwia pobierania danych bezpośrednio z:

  • baz danych i hurtowni danych,
  • baz danych MS Access,
  • stron internetowych (np.: kursy walut),
  • inne.

PQ umożliwia również, łączenie danych z plików xls, csv, txt bez konieczności wklejania ich do jednego pliku. Po przeprowadzeniu odpowiedniej operacji tj.: „pobierz dane z folderu” i wskazaniu w jakiego folderu PQ ma pobierać i łączyć dane, po każdym kolejnym dodaniu do folderu pliku z nowymi danymi nowe informacje zostaną automatycznie dodane do wcześniejszych.

Ile czasu do tej pory spędzałeś na łączenie takich danych?

Ok, dane mamy już pobrane do jednej tabeli/zestawu danych, ale żeby zacząć je skutecznie analizować musimy je „oczyścić”. Najprościej mówiąc, trzeba sprawić, aby w każdej kolumnie były takie same typy danych tj.: kwoty (to liczby, lub waluty), data zapisana w odpowiednim formacie dla dat, ilość zatankowanego paliwa w litrach, a nazwy dostawców, klientów, miast, usług zawsze były tak samo napisane (np.: zawsze „Dostawca1”, a nie czasem „dostawca 1” lub „DOSTAWCA1”, a Warszawa zawsze „Warszawa”, a nie „Warszawa Jerozolimskie”, czy „W-wa”). Czasami system generuje raport, który zawiera zduplikowane informacje, wiersze, zaś ich ręczne usuwanie jest żmudne.

Cały proces czyszczenia wykonamy w Power Query. To narzędzie posiada rozbudowane funkcjonalności dostosowywania danych do naszych wymagań raportowych.

Ważne: Power Query podczas transformowania danych nie modyfikuje danych źródłowych, czyli zawsze można sprawdzić jak nasze dane zostały poprawione i które dane zostały poprawione.

Np.: jednym kliknięciem zamienimy 1 literę imienia na wielką, a pozostałe na małe, we wszystkich imionach, choćby były ich tysiące. Jeżeli nie mamy daty wystawienia dokumentu, ale stanowi ona część numeru dokumentu, to możemy wykonać procedurę podziału i zamiany takiego numeru dokumentu, aby pozyskać osobno numer, a osobno datę. Inny przykład: w nazwie produktu jest ID produktu i jego nazwa – tu również możemy podzielić ten ciąg znaków na 2 rozdzielne („ID produktu” i „Nazwa produktu”). Możliwości czyszczenia i poprawiania danych jest tak wiele, że nie sposób ich opisać w tym artykule. Zaryzykuję stwierdzenie, że PQ nie ma ograniczeń w tym zakresie.

Mając pobrane i posklejane w pojedyncze zestawy dane, koniecznie musimy jakoś te dane połączyć, aby robić analizy wielowymiarowe. Chodzi o zrobienie działania jak „wyszukaj.pionowo” lub „vlookup”, ale bez używania tych funkcji w Excelu. Przy pracy na dużych zestawach danych wykorzystanie tych tradycyjnych funkcji powoduje, że rozmiar/waga pliku bardzo mocno wzrasta, a sama wydajność działania pliku i pracy na nim drastycznie spada, często do zera.

W takim przypadku należy połączyć zestawy danych relacjami, tak jak w relacyjnych bazach danych. Np.: jeżeli w danych z systemu zakupowego mamy nr zamówienia i w danych z systemu księgowego mamy nr zamówienia, to możemy połączyć te 2 zestawy danych relacją, bez konieczności stosowania „wyszukaj.pionowo”. Jak, z kolei, mamy nr faktury w systemie księgowym i również w raporcie od dostawcy mamy numer faktury, to możemy połączyć kolejną relacją te 2 zestawy danych i dzięki temu automatycznie mamy połączone 3 zestawy danych:

  1. z systemu księgowego,
  2. z systemu zakupowego,
  3. raporty od dostawców.

Takie relacje tworzymy w dodatku Power Pivot (PP), który tak samo jak Power Query, jest darmowym dodatkiem i każdy użytkownik posiadający licencję na Excel 2010 lub wyższy, może sobie go doinstalować.

Kolejny etap to określenie wskaźników i miar jakie nasz model będzie liczył automatycznie przy każdym uruchomieniu. Ale o tym napiszę w kolejnym artykule poświęconym dodatkowi Power Pivot.

Podsumowując, aby przygotować analizę różnych danych z różnych źródeł nie trzeba już żmudnie łączyć ze sobą mało wydajnych funkcji tradycyjnego Excela, ale też nie trzeba inwestować w drogie rozwiązania ETL. Na początek wystarczy ExcelBI (Excel+Power Query+Power Pivot). Jeżeli wykorzystanie tych narzędzi przyniesie wartość firmie, a na pewno przyniesie, to można myśleć o rozwiązaniach bardziej wydajnych ale o tym w kolejnych publikacjach.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *