Poznawanie funkcji pobierania i przekształcania programu Excel

Podsumowanie wykonawcze

W dobie jezior danych i baz danych w skali petabajtów szokujące jest to, jak często wciąż otrzymuję dane w postaci plików CSV, tekstowych i Excel. Podczas gdy współczesna analityka koncentruje się na przełomowych postępach w algorytmach uczenia maszynowego, codzienna żmudna analiza danych jest nadal ręcznym procesem wyszukiwania, kompilowania i kłócenia się z różnymi typami danych.

Dla analityka finansowego dane często przychodzą w postaci arkusza kalkulacyjnego Excel, ale równie często są to zrzuty danych do pliku CSV lub zapytanie do bazy danych SQL. Czasami dane są ułożone w mylący układ lub nie zawierają wszystkich elementów wymaganych do analizy. Czas spędzony na przeszukiwaniu tych danych jest cennym czasem straconym dla analityka, jednak czasami to zadanie jest akceptowane jako zło konieczne, które należy tolerować.

Co robi Get & Transform?

Rozwiązanie tego powszechnego problemu jest w rzeczywistości dość przystępne: programy Excel i Power BI mają cały zestaw narzędzi do przekształcania danych, o których wie niewielu użytkowników, o nazwie Pobierz i przekształć (wcześniej znany jako Power Query). Dzięki wbudowanej funkcji wyodrębniania, przekształcania i ładowania (ETL) analitycy finansowi mogą bezproblemowo łączyć się ze źródłami danych i szybciej uzyskiwać szczegółowe informacje.

Gdy zbieramy dane do załadowania do programu Excel lub Power BI, zwykle musimy dokonać pewnych przekształceń danych. Niektóre przykłady manipulacji danymi obejmują:

• usuwanie kolumn,
• Filtrowanie danych,
• Grupowanie danych,
• Przestawianie/odkręcanie danych,
• Dzielenie ciągów na podciągi,
• Wyodrębnianie słów kluczowych z ciągów,
• Dołączanie wierszy z innej tabeli i
• Łączenie dwóch tabel wymiarów.

Na poniższym diagramie widzimy, że funkcja Pobierz i przekształć spełnia tę żmudną rolę polegającą na wstępnym przetwarzaniu danych przed ich załadowaniem.

Dlaczego powinieneś używać Get & Transform?

Dlaczego warto nauczyć się korzystać z Get & Transform? Cóż, kiedy patrzę na to, do czego osobiście wykorzystałem tę funkcjonalność, zaoferowano mi plastyczny zestaw narzędzi do:

• Ładowanie całego folderu plików tekstowych do jednej tabeli danych
• Konwertowanie wyeksportowanych plików księgowych do czytelnego układu
• Ładowanie milionów wierszy sprzedaży bezpośrednio do dodatku Power Pivot
• Grupowanie danych dziennych w łatwe do zarządzania wyniki miesięczne przed zaimportowaniem ich do programu Excel
• Łączenie danych z innej tabeli poprzez łączenie na pasujących kolumnach

Ogólnie rzecz biorąc, gdy otrzymam nowe dane, zbadam je za pomocą funkcji Pobierz i przekształć przed załadowaniem ich do dodatku Power Pivot. To pozwala mi zobaczyć, jakie przekształcenia mogą być konieczne, i szybko wykonać pewne przestawienia i grupować dane w celu sformułowania struktury do analizy. W wielu przypadkach na tym etapie stwierdzę, że potrzebuję więcej danych lub są problemy z danymi. Korzystając z platformy opartej na Excelu, mogę szybko iterować z moim źródłem danych, aby znaleźć te anomalie danych.

Ostatecznie decyzja o pozostaniu w Excelu lub przeniesieniu analizy danych na inną platformę będzie zależała od odbiorców oraz powtarzalności i dystrybucji analizy. Jeśli moi klienci używają tylko programu Excel, prawie zawsze będę używał funkcji Pobierz i przekształć do ładowania danych, Power Pivot do przeprowadzenia analizy i programu Excel do tworzenia tabel przestawnych i wykresów. Dla klienta będzie to bezproblemowe, ponieważ wszystko jest zawarte w programie Excel.

Jeśli jednak mój klient:

1. chce użyć innego narzędzia do wizualizacji,
2. Ma wielu użytkowników, którzy będą odświeżać dane, lub
3. Konieczność zastosowania modeli uczenia maszynowego,

Następnie użyję Get & Transform wyłącznie do początkowej eksploracji danych, a następnie przeniosę ciężkie podnoszenie do R.

Jak uzyskać dostęp do pobierania i przekształcania w programie Excel lub Power BI

W poprzednich wersjach programu Excel dodatek Power Query był dodatkiem, który można było zainstalować w celu ułatwienia obsługi funkcji ETL. Jednak w programach Excel 2016 i Power BI narzędzia te są ściślej zintegrowane. W programie Excel 2016 można uzyskać do nich dostęp za pośrednictwem karty Dane , a następnie sekcji Pobierz i przekształć dane .

W Power BI funkcjonalność znajduje się na karcie Strona główna w sekcji Dane zewnętrzne .

W tym artykule moje przykłady mają miejsce w Power BI, ale interfejs jest prawie identyczny jak w Excelu. Wskażę różnice, kiedy się pojawią, więc tutorial powinien mieć sens dla obu typów użytkowników.

1. Ładowanie plików CSV

Aby pomóc w tym samouczku, stworzyłem kilka przykładów danych sprzedaży dla fikcyjnego sprzedawcy, który sprzedaje sprzęt i odzież outdoorową. W każdym z tych przykładów dane będą tworzone na różne sposoby, aby zademonstrować realistyczne metody zrzutów danych.

Jako pierwszy przykład zobaczymy dane przedstawione jako duży zrzut danych do pliku CSV. Czynnikiem komplikującym jest to, że dane są prezentowane w wielu kolumnach reprezentujących różne sklepy. Idealnie chcielibyśmy zaimportować i przekształcić dane w bardziej użyteczny układ.

Poniżej znajduje się zrzut ekranu przedstawiający surowy plik CSV:

Dlaczego mielibyśmy to zmienić? Aby wykorzystać możliwości relacji, które są możliwe w tych aplikacjach. Zobaczymy to w dalszej części dyskusji.

Na razie załóżmy, że musimy postrzegać dane jako strukturę „węższą i wyższą”, a nie „szerszą i krótszą”. Pierwszym krokiem jest załadowanie pliku CSV; następnie zaczniemy „odkręcać” dane.

Jak widać, ostateczna struktura danych jest węższa od danych początkowych i dużo dłuższa. Inną kwestią jest to, że gdy klikamy różne akcje, narzędzie po prawej stronie generuje listę zastosowanych kroków użytych do zbudowania zapytania. Ważne jest, aby zrozumieć, że dzieje się to w tle, ponieważ zostanie to omówione później.

W większości przypadków funkcja Pobierz i przekształcaj wygląda i zachowuje się podobnie w usługach Power BI i Excel. Jednak w programie Excel po kliknięciu przycisku Zamknij i wczytaj pojawia się jeden dodatkowy monit. Na poniższym rysunku możemy przełączać się między tym, czy chcemy załadować dane do:

1. Tabela w Excelu,
2. Tabela przestawna utworzona na podstawie danych,
3. Wykres przestawny utworzony na podstawie danych lub
4. „Tylko twórz połączenie”.

Ponadto mamy również możliwość dodania tych danych do modelu danych . Zaznaczenie tego pola powoduje ładowanie danych do tabeli dodatku Power Pivot. Jeśli zamierzamy analizować dane w Power Pivot, radzę wybrać opcję Tylko utwórz połączenie , a następnie upewnić się, że zaznaczona jest opcja Dodaj te dane do modelu danych . Jeśli dane mieszczą się w limicie wierszy programu Excel, a my wolimy przeprowadzić analizę w programie Excel, wystarczy wybrać opcję Tabela .

W następnym klipie zobaczymy, że powodem, dla którego sformatowaliśmy dane tak, aby były długie i wąskie, jest to, że jesteśmy w stanie analizować sprzedaż nie tylko według sklepu, ale także według regionu i stanu. Aby wykonać to zadanie, zaimportujemy tabelę, która mapuje każdy sklep na region i stan. Poniżej zobaczymy, że możemy szybko tworzyć raporty pokazujące sprzedaż według tych różnych grup.

Możesz sobie wyobrazić, jak tego typu możliwości transformacji danych w programie Excel lub Power BI można skutecznie zastosować w każdym przypadku, w którym mamy dynamiczne grupy danych, takie jak:

• Zestawianie danych dziennych na tygodnie, miesiące i kwartały;
• Grupowanie sprzedawców w działy i regiony; lub
• Mapowanie jednostek SKU na typy produktów.

Chociaż ten artykuł dotyczy plików CSV i innych plików programu Excel, Get & Transform zajmuje się szeroką gamą typów danych. Po utworzeniu zapytania można je odświeżyć w miarę zmian danych.

2. Obsługa ciągów tekstowych

Aby zademonstrować zdolność Get & Transform do manipulowania ciągami, stworzyłem inny zestaw danych, który naśladuje plik tekstowy pokazujący transakcje księgowe z księgi głównej (GL) firmy.

Zauważ, że numer konta i nazwa pojawiają się w tym samym ciągu? W Power BI możemy bez wysiłku przeanalizować numer konta i nazwę w osobnych polach.

Na tym filmie widać, że po podzieleniu kolumny narzędzie odgadło, że nowa lewa strona pola Konto powinna być liczbą i tworzy krok „Zmieniony typ1”. Ponieważ ostatecznie chcemy, aby to pole było ciągiem, możemy przejść dalej i ręcznie usunąć krok pod zastosowanymi krokami.

Następnie z tych samych danych tworzymy plan kont z mapowaniem do kategorii kont.

Dlaczego mielibyśmy wykonywać te wszystkie kroki, aby zmapować kilka numerów kont? Prawdziwa księga główna może składać się z setek, a nawet tysięcy kont. To szybkie zapytanie mapujące, jak pokazaliśmy, skalowałoby się do tego poziomu bez dodatkowej pracy.

3. Praca z różnymi źródłami danych

Pobierz i przekształć obsługuje wiele różnych źródeł danych. Chociaż nie jest to wyczerpująca lista, poniżej przedstawiamy kilka przykładów:

Osobiście wypróbowałem tylko około połowy połączeń z powyższej listy. Każde z użytych przeze mnie złączy było dość solidne; Przeszedłem od surowych danych do insightów bez uciążliwego nakładu pracy. Co równie ważne, służy jako walidator między różnymi źródłami danych, zapewniając, że końcowe wyniki mają znormalizowany poziom kontroli jakości.

4. Personalizacja kodu za pomocą języka M

W tle Get & Transform generuje kod za każdym razem, gdy klikamy przycisk w narzędziu lub dokonujemy wyboru. Poniżej znajduje się przykład, w jaki sposób możesz uzyskać dostęp do kodu utworzonego przez nas zapytania mapowania konta:

Kod używa języka funkcjonalnego o nazwie M, który generuje się automatycznie dla podstawowych przypadków użycia. Jednak w przypadku bardziej skomplikowanej wymiany danych możemy edytować i pisać własny kod. W większości przypadków będę wprowadzał tylko drobne modyfikacje tego kodu. W bardziej skomplikowanych przekształceniach mogę napisać większość kodu od zera, aby postawić tymczasowe tabele lub wykonać bardziej skomplikowane sprzężenia.

Granice Get & Transform

Excel ma tendencję do osiągania swoich granic, gdy próbujesz wyeksportować więcej niż milion wierszy. W przypadkach, w których przekształciłem miliony wierszy za pomocą funkcji Get & Transform, jedynym sposobem na wysłanie niezgrupowanych wierszy jest żmudne hacki lub obejścia. Odkryłem również, że zapytania typu Pobierz i przekształć mogą być niestabilne przy wdrażaniu dla wielu użytkowników, zwłaszcza jeśli używasz wielu źródeł danych i złączeń. W takich przypadkach zawsze będę używał R do wdrażania duplikatów danych. Wreszcie program Excel nie jest przeznaczony do bardziej zaawansowanego modelowania danych. Możesz dość szybko wykonać regresję liniową, ale poza tym będziesz musiał użyć bardziej rygorystycznej platformy.

Powiedziawszy to wszystko, uważam, że Excel jest tym, z czym większość moich klientów czuje się najbardziej komfortowo. Excel jest nadal najważniejszym narzędziem w arsenale analityka finansowego. Dzięki włączeniu funkcji Pobierz i przekształć programy Excel i Power BI stają się jeszcze bardziej zaawansowane dzięki szerokiemu zakresowi źródeł danych, które mogą zaakceptować.