Tabela wymiarów w pliku tekstowym: rozwiązanie Power Query

W tym poście na blogu omówię problem nr 2 z trwającego problemu tygodnia na , gdzie dyskutowaliśmy, jak uporządkować niechlujne dane w czystą tabelę wymiarów. To druga iteracja tej nowej inicjatywy, którą organizujemy w LuckyTemplates. Jestem szczególnie pasjonatem tej serii, ponieważ daje każdemu szansę na regularne ćwiczenia. Możesz obejrzeć pełny film z tego samouczka na dole tego bloga.

W pierwszą środę każdego miesiąca odbywa się wyzwanie DAX , aw trzecią środę – wyzwanie dotyczące zapytań zaawansowanych .

To świetna okazja, aby eksplorować, odkrywać i uczyć się nowych rzeczy na temat tych języków, które musisz wykorzystać w usłudze LuckyTemplates.

Na forum LuckyTemplates znajdziesz kategorię dotyczącą problemu tygodnia.

Główny nacisk kładziony jest na proces, a nie na wynik.

Jeśli uda ci się rozbić problem na mniejsze części, będziesz w stanie rozwiązać każdy napotkany problem.

Melissa de Korte

Brian mówił wcześniej o technice zwanej debugowaniem gumowej kaczki . Jeśli to przegapiliście, koniecznie obejrzyjcie jego wideo. Może ci pomóc, gdy utkniesz w tych wyzwaniach.

Najpierw przeanalizujmy zadanie. Mamy niechlujny plik tekstowy, który musimy przekształcić w odpowiednią tabelę wymiarów.

Teraz, kiedy patrzę na takie dane, w większości chodzi o czyszczenie tekstu, usuwanie niechcianych znaków, przycinanie, pisanie wielkimi literami i tak dalej.

Ale musimy również pobrać wszystkie wiersze z danych tego stosu i przekształcić je w pojedynczą linię dla każdego kraju. Nazywam to danymi stosu, ponieważ wszystkie nazwy pól są powtarzane w jednej kolumnie dla każdego kraju.

Mam pewne doświadczenie w programie Excel, a jedną z jego najpotężniejszych funkcji są moim zdaniem tabele przestawne .

Spis treści

• Tabele przestawne w Excelu
• Używanie Power Query do tworzenia tabeli wymiarów
• Niestandardowa funkcja czyszczenia tekstu
• Obracanie danych
• Wniosek

Tabele przestawne w Excelu

Tabele przestawne pozwalają mi przeglądać dane segment po segmencie. W zależności od tego, co umieścisz w sekcji wierszy, tabela przestawna skondensuje każde wystąpienie tego segmentu w jedną linię.

Następnie możesz podzielić to jeszcze bardziej, przeciągając pola do sekcji kolumn.

Stworzyłem przykład, jak to wygląda. Tutaj mamy prawie te same dane, które mieliśmy w pliku tekstowym.

Na następnej stronie utworzyłem pustą tabelę przestawną.

To, czego ci nie pokazałem, to to, że mam tutaj trzy kolumny zamiast dwóch, które już ci pokazałem.

Przeciągnę segment do wierszy.

Ponieważ kolumna 1 zawierała nazwy pól, przeciągnę ją do sekcji kolumn.

Kolumna 2 zawierała wszystkie wartości pól, więc przeciągnę je do sekcji wartości.

Widzimy tutaj, że tabele przestawne nie obsługują ciągów tekstowych. Zlicza je, ale pokazuje, że mamy jedną wartość dla każdego pola.

Teraz spójrzmy na ten segment, który utworzyłem. Wrócę więc do danych i odkryję moją kolumnę. Widać, że jest to tylko numer indeksu, identyfikujący każdy z oddzielnych bloków danych, które wciąż są ułożone jeden na drugim.

Tak więc dla mnie kluczową transformacją będzie obrót danych, aby przywrócić je do tego formatu tabelarycznego.

Na forum widziałem inne sposoby radzenia sobie z tym. Istnieją inne sposoby, aby przejść do formatu tabelarycznego poza użyciem akcji przestawnej i działają one równie dobrze. Jeśli Cię to interesuje, wejdź na forum i zacznij odkrywać.

Używanie Power Query do tworzenia tabeli wymiarów

Przejdźmy do zapytania o moc i przejrzyjmy moje rozwiązanie.

Osobiście uważam, że interfejs użytkownika wykonuje świetną robotę, pisząc dla nas większość kodu M. Dlatego staram się projektować zapytania przy użyciu interfejsu użytkownika w jak największym stopniu.

Gdy zapytanie zrobi to, czego potrzebowałem, przejdę do zaawansowanego edytora i sprawdzę kod M, aby zobaczyć, czy mogę go zmodyfikować. Zobaczmy, jak to wygląda.

To jest moja grupa podstawowa zbudowana przy użyciu interfejsu użytkownika.

Możesz zobaczyć, że w zastosowanych krokach po prawej stronie jest wiele kroków.

To samo w sobie nie stanowi problemu, ale wystarczy spojrzeć na te kroki, aby zobaczyć, że istnieje wiele przekształceń, które można zgrupować.

Otwórzmy zaawansowany edytor.

Widzimy, że to zapytanie ma 31 kroków.

Dodałem również kilka komentarzy do tego samego zapytania zawierającego 31 kroków, ale podzieliłem je na sekcje.

Pierwszą rzeczą, którą zrobiłem, było usunięcie kroku typu zmiany. Sugeruję utworzenie niestandardowej funkcji, aby wykonać wszystkie te kroki transformacji tekstu.

Jest tu wiele komentarzy, ale są tylko dwie rzeczy, które chcę podkreślić w tym poście na blogu. Pierwsza to niestandardowa funkcja służąca do czyszczenia tekstu .

Drugi to krok przestawny w celu przekształcenia tego typu danych z powrotem w odpowiedni format tabelaryczny .

Niestandardowa funkcja czyszczenia tekstu

Wróćmy do wczesnych etapów budowania mojego zapytania, gdzie miałem wszystkie te pogrupowane kroki, aby wyczyścić te teksty: kolumna 1 i kolumna scalona.

Dodałem również dodatkową kolumnę niestandardową. Jego jedynym celem jest zbudowanie mojej niestandardowej funkcji czyszczenia tekstu . Wywołałem to w scalonej kolumnie.

W ten sposób nie muszę pisać funkcji za jednym razem, ale buduję ją stopniowo krok po kroku, dodając nową transformację po przejrzeniu wyników z poprzedniego kroku.

Spójrzmy na kod M dla funkcji czyszczenia tekstu.

Jak widać, mam kilka kroków. Kiedy budowałem tę funkcję tekstową, przechodziłem tam iz powrotem między zapytaniami, aby spojrzeć na wyniki, aby zobaczyć, co zbudować, a co poprawić w następnej kolejności.

Z tym wynikiem wykonałem wszystkie przekształcenia, których potrzebowałem. Niektóre z funkcji M, których tutaj użyłem, zostały udostępnione przez interfejs użytkownika podczas tworzenia pierwszego zapytania, na przykład Text. Funkcja przycinania. Jednak inne używane funkcje nie były.

Jeśli ich nie znasz, możesz wyszukać wszystkie funkcje M w internetowym przewodniku po formułach M. To jest link , do którego musisz się udać.

Znajdziesz sekcję poświęconą zapytaniom o moc i funkcjom.

Jeśli przewiniesz w dół, znajdziesz sekcję dotyczącą funkcji tekstowych, a każda sekcja zaczyna się od przeglądu. Istnieje lista wszystkich funkcji tekstowych wewnątrz zapytania potęgowego i języka formuły M.

Jeśli szukasz konkretnej transformacji, możesz to sprawdzić tutaj.

Obracanie danych

Drugą częścią, którą chciałem podkreślić podczas tworzenia tej tabeli wymiarów, jest obracanie samych danych. Przyjrzyjmy się temu również bliżej.

Zacząłem od dodania pliku index. Zaktualizowałem ten indeks, aby prawidłowo segmentować bloki danych. Zrobiłem to, zwracając numer indeksu dla każdej linii, w której w kolumnie 1 znajdował się kraj tekstowy, a następnie wypełniłem tę wartość.

Wszystko, co musimy zrobić, to obrócić same dane. Na karcie przekształcania znajdziesz kolumnę przestawną. Po wybraniu kolumny 1 kliknij opcję Kolumna przestawna.

Użyje wartości kolumny 1 jako nowej nazwy kolumny. Chce również wiedzieć, gdzie znajdują się wartości dla tych nazw pól. Są one w naszej połączonej kolumnie.

Jeśli program Excel radzi sobie z wartościami tekstowymi, Power Query może to zrobić również ze względu na zaawansowane ustawienia opcji. Wszystko, co musimy zrobić, to wybrać Nie agreguj , aby mógł obsługiwać wartości tekstowe.

Po kliknięciu OK widzimy, że nasze dane zostały obrócone.

Wniosek

Powyższy obraz to zapytanie z wynikiem końcowym. Mam nadzieję, że spodobało Ci się, jak przekształciliśmy chaotyczne dane w dostarczonym pliku tekstowym i przekształciliśmy je w przejrzystą tabelę wymiarów , która nadaje się do analizy.

Jeśli podobał Ci się ten post na blogu, zasubskrybuj kanał LuckyTemplates, aby nie przegapić żadnych nowych treści.

Mam nadzieję, że zobaczymy się wszyscy w przyszłych wyzwaniach Problemu Tygodnia.

Melisa