Gjøre kalendertypeoppsett til tabellformat i LuckyTemplates ved hjelp av Query Editor

Å analysere dataene vi har kan være vanskelig hvis de ikke er riktig ordnet. I denne opplæringen vil jeg forklare hvordan du transformerer vertikale og horisontale gjentatte data til et tabellformat. Vi gjør dette slik at vi lettere kan analysere det ved hjelp av LuckyTemplates. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Denne opplæringen ble laget for å ta opp spørsmålet om et medlem. Medlemmet ba om en måte han kunne omforme dataene sine fra et kalendertypeoppsett til et tabellformat.

Før vi starter, la oss først undersøke dataene vi har.

Her har vi et dataoppsett der dagene er satt opp horisontalt, og ukene er satt opp vertikalt. Denne typen arrangement er kjent for de fleste av oss siden dette er måten vi enkelt kan forstå dataene på.

Imidlertid er denne typen layout vanskeligere å analysere siden det er mange kolonner og rader å ta hensyn til.

Når det er sagt, la oss transformere dataene til et riktig tabellformat der vi ender opp med en kolonne for datoer, en kolonne for bevegelsene og en annen kolonne for navnene.

Innholdsfortegnelse

• Rydde opp i data for et riktig tabellformat
• Breaking The Tables
• Identifisere listen for de nye tabellene for et riktig tabellformat
• Bestemme hvilke verdier som hører hjemme i tabellen
• Gruppering av data for et riktig tabellformat
• Legge til de separate tabellene
• Konklusjon

Rydde opp i data for et riktig tabellformat

For å gjøre ting enklere, la oss transformere dataene våre inne i. Men før vi gjør det, la oss sørge for at Formula Bar er synlig på skjermen din. Hvis den ikke er det, gå til Vis-fanen og merk av i boksen ved siden av formellinjen.

Det første trinnet er å fjerne de unødvendige verdiene i dataene våre. La oss fjerne den første kolonnen og de to øverste radene med dataene våre siden vi egentlig ikke trenger dem. For å gjøre det, klikk på Fjern rader-fanen, klikk Fjern øverste rader, skriv inn 2, og trykk deretter OK.

Og nå for å fjerne den første kolonnen, gå til fanen Velg kolonner, og fjern deretter merket for kolonne 1 ved å fjerne merket i boksen ved siden av.

La oss også fjerne radindeks 14 til 16 siden de er fullstendig fylt med bare nullverdier. Igjen, gå til Fjern rader- fanen og klikk deretter Fjern tomme rader .

Et annet problem vi har er at datoverdiene hopper over en kolonne hver gang. Datoene er alltid etterfulgt av en nullverdi som fremhevet nedenfor. Dette skjer ikke bare på den øverste raden, men på radene under også.

For å fylle disse hullene, la oss først transponere datatabellen vår ved å gå til  Transform- fanen og deretter klikke Transponer-knappen.

Bordet skal nå se ut som det har blitt snudd på siden etter å ha utført handlingen.

Velg deretter alle kolonnene som inneholder datoene, høyreklikk på kolonneoverskriften, og velg deretter Fyll ned.

Dette bør duplisere datoene og fylle hullene i de valgte kolonnene.

Breaking The Tables

Når vi ser på dataene vi nå har, ser vi at det er fire tabeller ved siden av hverandre. Kolonne 1 til 7 danner én tabell, kolonne 8 til 13 utgjør en annen tabell, og så videre.

For å legge disse til en enkelt tabell, må vi utføre en grunnleggende unpivot- operasjon for å transformere den til et riktig tabellformat. Men siden bordene ligger ved siden av hverandre, kan vi ikke bare gjøre det. Vi må bryte dette bordet fra hverandre og sy dem sammen igjen til mindre bord.

For å gjøre det, lager vi den første formelen for denne opplæringen. La oss klikke på fx- knappen og deretter legge til formelen vi skal bruke. Vi skal bruke Table.ToColumns -operasjonen. Dette oppretter en liste over nestede lister med kolonneverdier fra en tabell. Hver kolonne blir omgjort til en liste.

Dette er hvordan jeg har satt opp formelen.

Formelen lager en liste over kolonnene som vi tidligere har. Den første verdien av listen inneholder kolonne 1, den andre verdien inneholder kolonne 2, og så videre.

Identifisere listen for de nye tabellene for et riktig tabellformat

Nå bør vi identifisere hvilke verdier på listen som inneholder datoverdier. Når vi vet hvor disse datoene er, kan vi bestemme hvor hver av våre separate tabeller starter eller slutter.

For å gjøre det, la oss først konvertere listen tilbake til en tabell ved å klikke på Til tabell- knappen i øvre venstre hjørne.

Legg deretter til en indekskolonne ved siden av.

Det neste vi skal gjøre er å legge til en annen kolonne ved siden av indekskolonnen. Denne kolonnen skal hjelpe oss med å finne ut hvilke verdier på Kolonne1 som inneholder datoverdiene. La oss gi denne kolonnen navn Index2.

For å gjøre det, skal vi inkludere en formel i Indeks2, som returnerer indeksverdien til Kolonne1 hvis den inneholder en dato.

Vi skal bruke , og deretter referere til Kolonne1. Brakettene vil tjene som operatør; å legge til 0 mellom disse parentesene betyr at vi får den første verdien fra listen; så avgjør vi om det er en date. Hvis det er det, vil jeg at formelen skal returnere indeksnummeret; hvis det ikke er det, vil jeg ikke at det skal returnere noen verdi i det hele tatt.

Siden den første verdien i Kolonne1 inneholder en dato, bør den første verdien i Indeks2 inneholde indeksnummeret 0. Siden den andre verdien i Kolonne1 ikke inneholder en dato, bør den andre verdien i Indeks2 bare inneholde en nullverdi.

Dette er hvordan jeg har satt opp formelen for Index2. Bare sørg for at det ikke er syntaksfeil i formelen din.

Etter å ha gjort det, bør Indeks2-kolonnen vår nå ha indeksen til verdiene i Kolonne1 som inneholder datoen. Den vil også inkludere noen nullverdier som et resultat av formelen vi har satt opp.

Bestemme hvilke verdier som hører hjemme i tabellen

Nå indikerer disse indeksverdiene i Index2 begynnelsen og slutten av de separate tabellene vi har. Det vil si at 0 markerer starten på det første bordet, 7 markerer slutten på det første bordet, og starten på det andre bordet, og så videre.

For å hjelpe oss med å identifisere hvilke verdier i kolonne 1 som hører hjemme i samme tabell, la oss bruke en utfyllingshandling i vår nye kolonne.

Etter å ha utført handlingen, skal nullverdiene nå erstattes av nuller, syvere, trettene og tjue. Disse tallene indikerer at de hører hjemme i samme tabell. Det vil si at nullene danner en tabell, sjuerne danner en annen tabell, og så videre.

Gruppering av data for et riktig tabellformat

Siden vi allerede vet hvilke verdier i Kolonne1 som hører til i samme tabell, la oss nå gruppere dem sammen. For å gjøre det, vil vi bruke Group By-operasjonen.

Ved å gjøre det ender vi nå opp med bare 4 verdier i Index2-kolonnen vår. Legg merke til at en annen kolonne kalt Count er ved siden av Index2-kolonnen vår. Denne kolonnen teller antall rader i hver av våre verdier i Indeks2-kolonnen.

Men det er egentlig ikke dette vi ønsker å gjøre. Det vi må gjøre er å lage en ny tabell ved å bruke kolonnene vi har.

For å gjøre det, skal vi bruke Table.FromColumns -operasjonen, referer til kolonnen som inneholder listeverdiene våre som er Column1, og endre deretter returtypen til en tabell.

Det er viktig å sikre at vi har referert til riktig kolonne i formelen vår. Sjekk om Kolonne1 virkelig inneholder listeverdiene.

Dette syr alle verdiene i Kolonne1 med en Indeks2-verdi på 0 i én tabell, de med Indeks2-verdien på 7 til en annen tabell, og så videre.

Vi sjekket hver post til verdi 0, og hentet deretter listeverdien fra kolonne1. Til slutt satt vi sammen disse listene for å danne en enkelt tabell ved å bruke denne M-koden.

Vi tok tak i de første 7 kolonnene og sydde dem til et enkelt bord, tok tak i de neste kolonnene, sydde dem til et annet bord, og så videre.

Legge til de separate tabellene

Nå som vi allerede har gruppert dem sammen, la oss prøve å legge til alle tabellene ved å legge til et nytt trinn. Igjen, la oss trykke på fx- knappen for å legge til en ny formel.

Table.Combine - operasjonen slår sammen en liste over tabeller, og oppretter deretter én enkelt tabell av dem. Her skal vi referere til kolonnen som inneholder listen over tabeller, som er Count-kolonnen.

Vi har nå en tabell der alle nestede tabeller legges tilbake til en stor enkelt tabell.

Etter å ha lagt til alle tabellene, kan vi nå utføre de siste trinnene for å transformere dataene våre til et riktig tabellformat. Velg de to første kolonnene i tabellen, og klikk deretter på Unpivot Other Columns- knappen i Transform-fanen.

Etter det fjerner du attributtkolonnen siden vi egentlig ikke trenger den. Gå til Velg kolonner , og fjern deretter merket for attributtkolonnen.

Deretter angir du til slutt de riktige typene for alle kolonnene og merker dem riktig.

Dobbeltklikk på kolonneoverskriften til hver kolonne for å endre navn tilsvarende.

For den første kolonnen endrer du typen til dato siden den inneholder datoverdiene våre.

Deretter velger du den andre og tredje kolonnen, og setter typene til tekst.

Ferdig! Slik skal vårt riktige tabellformat se ut.

Konklusjon

I denne opplæringen transformerte vi noen data, som ble presentert i en kalendertypelayout, til et riktig tabellformat. Vi ryddet først dataene våre, delte dem opp i mindre tabeller, grupperte tabellene som hører sammen, og så la vi alle sammen til slutt.

Denne opplæringen gir deg mye kunnskap om hvordan du kan modellere dataene dine riktig for enklere analyse.

Å forstå konseptene som ble brukt her er svært viktig siden de også kan brukes på mange andre måter.

Hvis konseptene fortsatt ikke er klare for deg, kan det hjelpe deg å sjekke lenkene nedenfor.

Nyt å jobbe gjennom denne!

Melissa