Premena rozloženia typu kalendára na tabuľkový formát v LuckyTemplates pomocou editora dotazov

Analýza údajov, ktoré máme, môže byť náročná, ak nie sú správne usporiadané. V tomto návode vysvetlím, ako transformovať vertikálne a horizontálne opakované dáta do tabuľkového formátu. Robíme to preto, aby sme to mohli jednoduchšie analyzovať pomocou LuckyTemplates. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Tento tutoriál bol vytvorený s cieľom vyriešiť otázku člena. Člen požiadal o spôsob, akým by mohol prerobiť svoje údaje z rozloženia typu kalendára do tabuľkového formátu.

Skôr než začneme, najprv preskúmame údaje, ktoré máme.

Tu máme rozloženie údajov, v ktorom sú dni nastavené horizontálne a týždne sú nastavené vertikálne. Tento typ usporiadania pozná väčšina z nás, pretože je to spôsob, akým môžeme dátam ľahko porozumieť.

Tento typ rozloženia je však ťažšie analyzovať, pretože je potrebné vziať do úvahy veľa stĺpcov a riadkov.

S tým, čo bolo povedané, transformujme údaje do správneho formátu tabuľky, kde skončíme so stĺpcom pre dátumy, stĺpcom pre pohyby a ďalším stĺpcom pre mená.

Obsah

• Čistenie údajov pre správny tabuľkový formát
• Breaking The Tables
• Identifikácia zoznamu pre nové tabuľky pre správny tabuľkový formát
• Určenie, ktoré hodnoty patria do tabuľky
• Zoskupovanie údajov pre správny tabuľkový formát
• Pridanie samostatných tabuliek
• Záver

Čistenie údajov pre správny tabuľkový formát

Aby sme to uľahčili, transformujme naše údaje vo vnútri. Predtým, ako to urobíme, sa však presvedčte, že na obrazovke je viditeľný riadok vzorcov . Ak nie je, prejdite na kartu Zobraziť a začiarknite políčko vedľa riadku vzorcov.

Prvým krokom je odstránenie nepotrebných hodnôt v našich údajoch. Odstránime prvý stĺpec a dva horné riadky našich údajov, pretože ich v skutočnosti nepotrebujeme. Ak to chcete urobiť, kliknite na kartu Odstrániť riadky, kliknite na položku Odstrániť najvyššie riadky, zadajte 2 a stlačte OK.

A teraz, ak chcete odstrániť prvý stĺpec, prejdite na kartu Vybrať stĺpce a potom zrušte označenie stĺpca 1 zrušením začiarknutia políčka vedľa neho.

Odstránime tiež indexy riadkov 14 až 16, pretože sú úplne vyplnené iba hodnotami null. Opäť prejdite na kartu Odstrániť riadky a potom kliknite na položku Odstrániť prázdne riadky .

Ďalším problémom, ktorý máme, je, že hodnoty dátumu zakaždým preskočia stĺpec. Po dátumoch vždy nasleduje nulová hodnota, ako je zvýraznené nižšie. To sa deje nielen v hornom riadku, ale aj v riadkoch nižšie.

Aby sme zaplnili tieto medzery, najprv transponujme našu tabuľku údajov tak, že prejdeme na  kartu Transformácia a potom klikneme na tlačidlo Transponovať.

Stôl by teraz mal po vykonaní akcie vyzerať ako prevrátený na bok.

Ďalej vyberte všetky stĺpce, ktoré obsahujú dátumy, kliknite pravým tlačidlom myši na hlavičku stĺpca a vyberte Vyplniť.

To by malo duplikovať dátumy a vyplniť medzery vo vybratých stĺpcoch.

Breaking The Tables

Pri pohľade na údaje, ktoré teraz máme, vidíme, že vedľa seba sú štyri tabuľky. Stĺpce 1 až 7 tvoria jednu tabuľku, stĺpce 8 až 13 ďalšiu tabuľku atď.

Aby sme ich pridali do jednej tabuľky, musíme vykonať základnú operáciu unpivot , aby sme ju transformovali do správneho tabuľkového formátu. Ale keďže stoly sú vedľa seba, nemôžeme to jednoducho urobiť. Tento stôl musíme rozbiť a spojiť ich späť do menších stolov.

Aby sme to dosiahli, vytvoríme prvý vzorec pre tento tutoriál. Kliknite na tlačidlo fx a potom pridajte vzorec, ktorý budeme používať. Použijeme operáciu Table.ToColumns . Tým sa vytvorí zoznam vnorených zoznamov hodnôt stĺpcov z tabuľky. Každý stĺpec sa zmení na zoznam.

Takto som nastavil vzorec.

Vzorec vytvorí zoznam stĺpcov, ktoré sme predtým mali. Prvá hodnota zoznamu obsahuje stĺpec 1, druhá hodnota obsahuje stĺpec 2 atď.

Identifikácia zoznamu pre nové tabuľky pre správny tabuľkový formát

Teraz by sme mali identifikovať, ktoré hodnoty v zozname obsahujú hodnoty dátumu. Keď vieme, kde sa tieto dátumy nachádzajú, môžeme určiť, kde začína alebo končí každá z našich samostatných tabuliek.

Ak to chcete urobiť, najprv preveďte zoznam späť na tabuľku kliknutím na tlačidlo Do tabuľky v ľavom hornom rohu.

Potom vedľa neho pridajte indexový stĺpec.

Ďalšia vec, ktorú urobíme, je pridať ďalší stĺpec vedľa indexového stĺpca. Tento stĺpec by nám mal pomôcť určiť, ktoré hodnoty v stĺpci 1 obsahujú hodnoty dátumu. Nazvime tento stĺpec Index2.

Aby sme to dosiahli, začleníme do Index2 vzorec, ktorý vráti hodnotu indexu stĺpca1, ak obsahuje dátum.

Budeme používať , a potom odkazovať na stĺpec1. Zátvorky budú slúžiť ako operátor; pridanie 0 medzi tieto zátvorky znamená, že dostaneme prvú hodnotu zo zoznamu; potom určíme, či je to dátum. Ak áno, chcem, aby vzorec vrátil indexové číslo; ak nie je, nechcem, aby vracal vôbec žiadnu hodnotu.

Keďže prvá hodnota v Stĺpci1 obsahuje dátum, prvá hodnota v Indexe2 by mala obsahovať indexové číslo 0. Keďže druhá hodnota v Stĺpci1 neobsahuje dátum, potom by mala druhá hodnota v Indexe2 obsahovať iba nulovú hodnotu.

Takto som nastavil vzorec pre Index2. Len sa uistite, že vo vzorci nie sú žiadne syntaktické chyby.

Potom by náš stĺpec Index2 mal mať index hodnôt v stĺpci 1, ktorý obsahuje dátum. Bude tiež obsahovať niektoré hodnoty null ako výsledok vzorca, ktorý sme nastavili.

Určenie, ktoré hodnoty patria do tabuľky

Teraz tieto hodnoty indexu v Index2 označujú začiatok a koniec jednotlivých tabuliek, ktoré máme. To znamená, že 0 označuje začiatok prvého stola, 7 koniec prvého stola a začiatok druhého stola atď.

Ak nám chcete pomôcť identifikovať, ktoré hodnoty v stĺpci 1 patria do tej istej tabuľky, použite v našom novom stĺpci akciu vyplnenia .

Po vykonaní akcie by teraz mali byť nulové hodnoty nahradené nulami, sedmičkami, trinástimi a dvadsiatimi. Tieto čísla označujú, že patria do tej istej tabuľky. To znamená, že nuly tvoria jednu tabuľku, sedmičky ďalšiu tabuľku atď.

Zoskupovanie údajov pre správny tabuľkový formát

Keďže už vieme, ktoré hodnoty v stĺpci 1 patria do rovnakej tabuľky, poďme ich teraz zoskupiť. Na tento účel použijeme operáciu Zoskupiť podľa.

Ak to urobíme, teraz máme v stĺpci Index2 iba 4 hodnoty. Všimnite si, že vedľa nášho stĺpca Index2 je ďalší stĺpec s názvom Počet. Tento stĺpec počíta počet riadkov v každej z našich hodnôt v stĺpci Index2.

Toto však v skutočnosti nie je to, čo chceme robiť. Čo musíme urobiť, je vytvoriť novú tabuľku pomocou stĺpcov, ktoré máme.

Aby sme to dosiahli, použijeme operáciu Table.FromColumns , odkážeme na stĺpec, ktorý obsahuje hodnoty nášho zoznamu, ktorým je Column1, a potom zmeníme návratový typ na tabuľku.

Je dôležité zabezpečiť, aby sme v našom vzorci odkázali na správny stĺpec. Skontrolujte, či stĺpec1 skutočne obsahuje hodnoty zoznamov.

Tým sa spoja všetky hodnoty v stĺpci 1 s hodnotou Index2 0 do jednej tabuľky, hodnoty s hodnotou Index2 7 do inej tabuľky atď.

Skontrolovali sme každý záznam na hodnotu 0 a potom sme získali hodnotu zoznamu zo stĺpca 1. Nakoniec sme tieto zoznamy spojili do jednej tabuľky pomocou tohto M kódu.

Chytili sme prvých 7 stĺpcov a zošívali ich do jednej tabuľky, chytili sme niekoľko ďalších stĺpcov, prišívali sme ich k ďalšej tabuľke atď.

Pridanie samostatných tabuliek

Teraz, keď sme ich už zoskupili, skúsme pridať všetky tabuľky pridaním ďalšieho kroku. Opäť stlačte tlačidlo fx na pridanie nového vzorca.

Operácia Table.Combine zlúči zoznam tabuliek a potom z nich vytvorí jednu tabuľku. Tu sa budeme odvolávať na stĺpec, ktorý obsahuje náš zoznam tabuliek, čo je stĺpec Počet.

Teraz máme tabuľku, kde sú všetky vnorené tabuľky pripojené späť do jednej veľkej tabuľky.

Po pripojení všetkých tabuliek teraz môžeme vykonať niekoľko posledných krokov pri transformácii našich údajov do správneho tabuľkového formátu. Vyberte prvé dva stĺpce tabuľky a potom kliknite na tlačidlo Unpivot Other Columns na karte Transform.

Potom odstráňte stĺpec atribútu, pretože ho skutočne nepotrebujeme. Prejdite na položku Vybrať stĺpce a potom zrušte výber stĺpca atribútov.

Potom nakoniec nastavte správne typy všetkých stĺpcov a správne ich označte.

Dvakrát kliknite na hlavičku každého stĺpca, aby ste zodpovedajúcim spôsobom zmenili jeho názov.

Pre prvý stĺpec zmeňte jeho typ na dátum, pretože obsahuje naše hodnoty dátumu.

Potom vyberte druhý a tretí stĺpec a nastavte ich typy na text.

Všetko hotové! Takto by mal vyzerať náš správny tabuľkový formát.

Záver

V tomto návode sme transformovali niektoré údaje, ktoré boli prezentované v rozložení typu kalendára, do správneho tabuľkového formátu. Najprv sme vyčistili naše údaje, rozdelili ich do menších tabuliek, zoskupili tabuľky, ktoré k sebe patria, a nakoniec sme ich všetky pripojili.

Tento tutoriál vám ponúka veľa vedomostí o tom, ako správne modelovať údaje pre jednoduchšiu analýzu.

Pochopenie pojmov, ktoré tu boli použité, je veľmi dôležité, pretože sa dajú použiť aj mnohými inými spôsobmi.

Ak vám pojmy stále nie sú jasné, môže vám pomôcť kontrola odkazov nižšie.

Užite si prácu cez tento!

Melissa