Przekształcanie układu typu kalendarza w format tabelaryczny w usłudze LuckyTemplates za pomocą Edytora zapytań

Analizowanie danych, które posiadamy, może być trudne, jeśli nie są one odpowiednio ułożone. W tym samouczku wyjaśnię, jak przekształcić powtarzane dane pionowe i poziome w format tabelaryczny. Robimy to, aby ułatwić sobie analizę za pomocą usługi LuckyTemplates. Możesz obejrzeć pełny film tego samouczka na dole tego bloga.

Ten samouczek został stworzony, aby odpowiedzieć na pytanie  członka  . Członek poprosił o sposób, w jaki mógłby przemodelować swoje dane z układu typu kalendarza na format tabelaryczny.

Zanim zaczniemy, najpierw przeanalizujmy dane, którymi dysponujemy.

Tutaj mamy układ danych, w którym dni są ustawione poziomo, a tygodnie są ustawione pionowo. Ten typ układu jest znany większości z nas, ponieważ w ten sposób możemy łatwo zrozumieć dane.

Jednak ten typ układu jest trudniejszy do analizy, ponieważ istnieje wiele kolumn i wierszy, które należy wziąć pod uwagę.

Powiedziawszy to, przekształćmy dane w odpowiedni format tabeli, w którym otrzymamy kolumnę dla dat, kolumnę dla ruchów i inną kolumnę dla nazwisk.

Spis treści

• Czyszczenie danych w celu uzyskania prawidłowego formatu tabelarycznego
• Łamanie stołów
• Identyfikacja listy nowych tabel dla właściwego formatu tabelarycznego
• Określanie, które wartości należą do tabeli
• Grupowanie danych w celu uzyskania odpowiedniego formatu tabelarycznego
• Dołączanie oddzielnych tabel
• Wniosek

Czyszczenie danych w celu uzyskania prawidłowego formatu tabelarycznego

Aby to ułatwić, przekształćmy nasze dane w edytorze Power Query . Ale zanim to zrobimy, upewnijmy się, że pasek formuły jest widoczny na ekranie. Jeśli tak nie jest, przejdź do karty Widok, a następnie zaznacz pole obok paska formuły.

Pierwszym krokiem jest usunięcie zbędnych wartości w naszych danych. Usuńmy pierwszą kolumnę i dwa górne wiersze naszych danych, ponieważ tak naprawdę ich nie potrzebujemy. Aby to zrobić, kliknij kartę Usuń wiersze, kliknij Usuń górne wiersze, wprowadź 2, a następnie naciśnij OK.

A teraz, aby usunąć pierwszą kolumnę, przejdź do zakładki Wybierz kolumny, a następnie odznacz kolumnę 1, odznaczając pole obok niej.

Usuńmy również wiersze o indeksach od 14 do 16, ponieważ są one całkowicie wypełnione tylko wartościami pustymi. Ponownie przejdź do zakładki Usuń wiersze, a następnie kliknij Usuń puste wiersze .

Innym problemem, który mamy, jest to, że wartości dat za każdym razem pomijają kolumnę. Po datach zawsze występuje wartość null, jak zaznaczono poniżej. Dzieje się tak nie tylko w górnym rzędzie, ale także w rzędach poniżej.

Aby wypełnić te luki, najpierw przetransponujmy naszą tabelę danych, przechodząc do  karty Przekształć , a następnie klikając przycisk Transponuj.

Stół powinien teraz wyglądać tak, jakby został przewrócony na bok po wykonaniu akcji.

Następnie zaznacz wszystkie kolumny zawierające daty, kliknij prawym przyciskiem myszy nagłówek kolumny, a następnie wybierz opcję Wypełnij w dół.

Powinno to zduplikować daty i wypełnić luki w wybranych kolumnach.

Łamanie stołów

Patrząc na dane, które mamy teraz, widzimy, że istnieją cztery tabele sąsiadujące ze sobą. Kolumny od 1 do 7 tworzą jedną tabelę, kolumny od 8 do 13 tworzą drugą tabelę i tak dalej.

Aby dołączyć je do jednej tabeli, musimy wykonać podstawową operację unpivot , aby przekształcić ją w odpowiedni format tabelaryczny. Ale ponieważ stoły sąsiadują ze sobą, nie możemy tego po prostu zrobić. Musimy rozebrać ten stół i zszyć go z powrotem w mniejsze stoliki.

W tym celu utworzymy pierwszą formułę dla tego samouczka. Kliknijmy przycisk fx , a następnie dodajmy formułę, której będziemy używać. Zamierzamy użyć operacji Table.ToColumns . Spowoduje to utworzenie listy zagnieżdżonych list wartości kolumn z tabeli. Każda kolumna zostanie przekształcona w listę.

W ten sposób ustawiłem formułę.

Formuła tworzy listę kolumn, które mamy wcześniej. Pierwsza wartość listy zawiera kolumnę 1, druga wartość zawiera kolumnę 2 i tak dalej.

Identyfikacja listy nowych tabel dla właściwego formatu tabelarycznego

Teraz powinniśmy określić, które wartości na liście zawierają wartości dat. Kiedy już wiemy, gdzie znajdują się te daty, możemy określić, gdzie zaczyna się lub kończy każda z naszych oddzielnych tabel.

Aby to zrobić, najpierw przekształćmy listę z powrotem w tabelę, klikając przycisk Do tabeli w lewym górnym rogu.

Następnie dodaj obok niego kolumnę indeksu.

Następną rzeczą, którą zamierzamy zrobić, jest dodanie kolejnej kolumny obok kolumny indeksu. Ta kolumna powinna nam pomóc określić, które wartości w Kolumnie 1 zawierają wartości dat. Nazwijmy tę kolumnę Index2.

Aby to zrobić, włączymy formułę w Indeksie2, która zwraca wartość indeksu Kolumny1, jeśli zawiera ona datę.

Zamierzamy użyć , a następnie odwołać się do Kolumny1. Nawiasy będą służyć jako operator; dodanie 0 w tych nawiasach oznacza, że ​​otrzymamy pierwszą wartość z listy; wtedy ustalamy, czy to randka. Jeśli tak, chcę, aby formuła zwróciła numer indeksu; jeśli tak nie jest, nie chcę, aby w ogóle zwracał jakąkolwiek wartość.

Ponieważ pierwsza wartość w Kolumnie 1 zawiera datę, pierwsza wartość w Indeksie 2 powinna zawierać swój numer indeksu 0. Ponadto, ponieważ druga wartość w Kolumnie 1 nie zawiera daty, druga wartość w Indeksie 2 powinna zawierać tylko wartość pustą.

W ten sposób skonfigurowałem formułę dla Index2. Upewnij się tylko, że w formule nie ma błędów składniowych.

Po wykonaniu tej czynności nasza kolumna Index2 powinna mieć teraz indeks wartości w kolumnie 1, która zawiera datę. Będzie również zawierał pewne wartości puste w wyniku skonfigurowanej przez nas formuły.

Określanie, które wartości należą do tabeli

Teraz te wartości indeksu wewnątrz Indeksu2 wskazują początek i koniec oddzielnych tabel, które mamy. Oznacza to, że 0 oznacza początek pierwszej tabeli, 7 oznacza koniec pierwszej tabeli, początek drugiej tabeli i tak dalej.

Aby pomóc nam zidentyfikować, które wartości w Kolumnie 1 należą do tej samej tabeli, zastosujmy akcję wypełnienia w naszej nowej kolumnie.

Po wykonaniu akcji wartości null należy teraz zastąpić zerami, siódemkami, trzynastkami i dwudziestkami. Liczby te wskazują, że należą do tej samej tabeli. Oznacza to, że zera tworzą jedną tabelę, siódemki tworzą drugą i tak dalej.

Grupowanie danych w celu uzyskania odpowiedniego formatu tabelarycznego

Ponieważ wiemy już, które wartości w Kolumnie 1 należą do tej samej tabeli, zgrupujmy je teraz. Aby to zrobić, użyjemy operacji Grupuj według.

W ten sposób otrzymamy teraz tylko 4 wartości w naszej kolumnie Index2. Zauważ, że obok naszej kolumny Index2 znajduje się inna kolumna o nazwie Count. Ta kolumna zlicza liczbę wierszy w każdej z naszych wartości w kolumnie Index2.

Jednak tak naprawdę nie to chcemy robić. To, co musimy zrobić, to utworzyć nową tabelę przy użyciu kolumn, które mamy.

Aby to zrobić, użyjemy operacji Table.FromColumns , odwołamy się do kolumny zawierającej nasze wartości listy, czyli Column1, a następnie zmienimy zwracany typ na tabelę.

Ważne jest, aby upewnić się, że odwołujemy się do właściwej kolumny w naszej formule. Sprawdź, czy Kolumna1 naprawdę zawiera wartości list.

Spowoduje to zszycie wszystkich wartości w Kolumnie 1 z wartością Indeksu2 równą 0 w jednej tabeli, wartości z wartością Indeksu2 równą 7 w innej tabeli i tak dalej.

Sprawdziliśmy każdy rekord do wartości 0, a następnie pobraliśmy wartość listy z kolumny 1. Na koniec połączyliśmy te listy, aby utworzyć pojedynczą tabelę przy użyciu tego kodu M.

Chwyciliśmy pierwsze 7 kolumn i zszyliśmy je w jedną tabelę, złapaliśmy kilka następnych kolumn, zszyliśmy je z inną tabelą i tak dalej.

Dołączanie oddzielnych tabel

Teraz, gdy już je pogrupowaliśmy, spróbujmy dołączyć wszystkie tabele, dodając kolejny krok. Ponownie naciśnijmy przycisk fx , aby dodać nową formułę.

Operacja Table.Combine scala listę tabel, a następnie tworzy z nich jedną pojedynczą tabelę. W tym miejscu odwołujemy się do kolumny zawierającej naszą listę tabel, czyli do kolumny Count.

Mamy teraz tabelę, w której wszystkie zagnieżdżone tabele są ponownie dołączane do jednej dużej pojedynczej tabeli.

Po dołączeniu wszystkich tabel możemy teraz wykonać kilka ostatnich kroków przekształcania naszych danych do odpowiedniego formatu tabelarycznego. Wybierz pierwsze dwie kolumny tabeli, a następnie kliknij przycisk Odwróć inne kolumny na karcie Przekształć.

Następnie usuń kolumnę atrybutów, ponieważ tak naprawdę jej nie potrzebujemy. Przejdź do opcji Wybierz kolumny , a następnie usuń zaznaczenie kolumny atrybutu.

Na koniec ustaw poprawne typy wszystkich kolumn i odpowiednio je oznacz.

Kliknij dwukrotnie nagłówek każdej kolumny, aby odpowiednio zmienić jej nazwę.

W przypadku pierwszej kolumny zmień jej typ na datę, ponieważ zawiera ona nasze wartości dat.

Następnie zaznacz drugą i trzecią kolumnę i ustaw ich typ na tekst.

Wszystko gotowe! Tak powinien wyglądać nasz właściwy format tabelaryczny.

Wniosek

W tym samouczku przekształciliśmy niektóre dane, które zostały przedstawione w układzie typu kalendarz, do odpowiedniego formatu tabelarycznego. Najpierw wyczyściliśmy nasze dane, podzieliliśmy je na mniejsze tabele, pogrupowaliśmy tabele, które należą do siebie, a na koniec dodaliśmy je wszystkie.

Ten samouczek oferuje dużą wiedzę na temat prawidłowego modelowania danych w celu łatwiejszej analizy.

Zrozumienie użytych tutaj pojęć jest bardzo ważne, ponieważ można je zastosować na wiele innych sposobów.

Jeśli pojęcia nadal nie są dla Ciebie jasne, skorzystaj z poniższych linków, które mogą Ci pomóc.

Miłej pracy z tym!

Melisa