Forvandling af kalendertypelayout til tabelformat i LuckyTemplates ved hjælp af Query Editor

Det kan være svært at analysere de data, vi har, hvis de ikke er korrekt arrangeret. I denne tutorial vil jeg forklare, hvordan man transformerer lodrette og vandrette gentagne data til et tabelformat. Vi gør dette, så vi kan have nemmere ved at analysere det ved hjælp af LuckyTemplates. Du kan se den fulde video af denne tutorial nederst på denne blog.

Denne tutorial blev lavet for at løse spørgsmålet om et medlem. Medlemmet bad om en måde, hvorpå han kunne omdanne sine data fra et kalendertypelayout til et tabelformat.

Før vi starter, lad os først undersøge de data, vi har.

Her har vi et datalayout, hvor dagene er sat op vandret, og ugerne er sat op vertikalt. Denne type arrangement er velkendt for de fleste af os, da det er den måde, vi nemt kan forstå dataene på.

Denne type layout er dog sværere at analysere, da der er mange kolonner og rækker, der skal tages i betragtning.

Når det er sagt, lad os omdanne dataene til et ordentligt tabelformat, hvor vi ender med en kolonne for datoer, en kolonne for bevægelserne og en anden kolonne for navnene.

Indholdsfortegnelse

• Oprydning af data til et korrekt tabelformat
• Breaking The Tables
• Identifikation af listen for de nye tabeller til et korrekt tabelformat
• Bestemmelse af, hvilke værdier der hører til i tabellen
• Gruppering af data til et korrekt tabelformat
• Tilføjelse af de separate tabeller
• Konklusion

Oprydning af data til et korrekt tabelformat

For at gøre tingene lettere, lad os transformere vores data inde i. Men før vi gør det, lad os sørge for, at Formula Bar er synlig på din skærm. Hvis det ikke er det, skal du gå til fanen Vis og derefter markere afkrydsningsfeltet ved siden af ​​formellinjen.

Det første skridt er at fjerne de unødvendige værdier i vores data. Lad os fjerne den første kolonne og de to øverste rækker af vores data, da vi ikke rigtig har brug for dem. For at gøre det skal du klikke på fanen Fjern rækker, klikke på Fjern øverste rækker, indtaste 2 og derefter trykke på OK.

Og nu for at fjerne den første kolonne, gå til fanen Vælg kolonner, og fravælg derefter kolonne 1 ved at fjerne markeringen i feltet ved siden af.

Lad os også fjerne række indeks 14 til 16, da de er fuldstændigt fyldt med kun nul-værdier. Gå igen til fanen Fjern rækker , og klik derefter på Fjern tomme rækker .

Et andet problem, vi har, er, at datoværdierne springer en kolonne over hver gang. Datoerne efterfølges altid af en nulværdi som fremhævet nedenfor. Dette sker ikke kun på den øverste række, men også på rækkerne nedenfor.

For at udfylde disse huller, lad os først transponere vores datatabel ved at gå til  fanen Transformer og derefter klikke på knappen Transponer.

Bordet skulle nu se ud som om det er blevet vendt om på siden efter at have udført handlingen.

Vælg derefter alle de kolonner, der indeholder datoerne, højreklik på kolonneoverskriften, og vælg derefter Udfyld ned.

Dette bør duplikere datoerne og udfylde hullerne i de valgte kolonner.

Breaking The Tables

Ser vi på de data, vi nu har, ser vi, at der er fire tabeller, der støder op til hinanden. Kolonne 1 til 7 danner én tabel, kolonne 8 til 13 danner en anden tabel, og så videre.

For at tilføje disse til en enkelt tabel, skal vi udføre en grundlæggende unpivot- operation for at transformere den til et korrekt tabelformat. Men da bordene støder op til hinanden, kan vi ikke bare gøre det. Vi er nødt til at bryde dette bord ad og sy dem sammen igen til mindre borde.

For at gøre det opretter vi den første formel til denne øvelse. Lad os klikke på fx- knappen og derefter tilføje den formel, vi skal bruge. Vi kommer til at bruge Table.ToColumns -operationen. Dette opretter en liste over indlejrede lister med kolonneværdier fra en tabel. Hver kolonne bliver omdannet til en liste.

Sådan har jeg sat formlen op.

Formlen opretter en liste over de kolonner, som vi tidligere har. Den første værdi på listen indeholder kolonne 1, den anden værdi indeholder kolonne 2, og så videre.

Identifikation af listen for de nye tabeller til et korrekt tabelformat

Nu bør vi identificere, hvilke værdier på listen der indeholder datoværdier. Når vi ved, hvor disse datoer er placeret, kan vi bestemme, hvor hver af vores separate tabeller starter eller slutter.

For at gøre det, lad os først konvertere listen tilbage til en tabel ved at klikke på knappen Til tabel i øverste venstre hjørne.

Tilføj derefter en indekskolonne ved siden af.

Den næste ting, vi skal gøre, er at tilføje endnu en kolonne ved siden af ​​indekskolonnen. Denne kolonne skal hjælpe os med at bestemme, hvilke værdier på Kolonne1 der indeholder datoværdierne. Lad os navngive denne kolonne Index2.

For at gøre det vil vi inkorporere en formel i Indeks2, som returnerer indeksværdien for Kolonne1, hvis den indeholder en dato.

Vi vil bruge , og derefter referere til Kolonne1. Beslagene vil tjene som operatør; Tilføjelse af 0 mellem disse parenteser betyder, at vi får den første værdi fra listen; så afgør vi, om det er en dato. Hvis det er, vil jeg have formlen til at returnere indeksnummeret; hvis det ikke er, vil jeg ikke have, at det returnerer nogen værdi overhovedet.

Da den første værdi i Kolonne1 indeholder en dato, skal den første værdi i Indeks2 indeholde dets indeksnummer 0. Da den anden værdi i Kolonne1 ikke indeholder en dato, skal den anden værdi i Indeks2 kun indeholde en nulværdi.

Sådan har jeg opsat formlen for Index2. Bare sørg for, at der ikke er nogen syntaksfejl i din formel.

Efter at have gjort det, skulle vores Indeks2-kolonne nu have indekset for værdierne i Kolonne1, der indeholder datoen. Det vil også inkludere nogle null-værdier som et resultat af den formel, vi har sat op.

Bestemmelse af, hvilke værdier der hører til i tabellen

Nu angiver disse indeksværdier inde i Index2 starten og slutningen af ​​de separate tabeller, vi har. Det vil sige, 0 markerer starten på det første bord, 7 markerer slutningen af ​​det første bord, og starten på det andet bord og så videre.

For at hjælpe os med at identificere, hvilke værdier i Kolonne1 der hører til i den samme tabel, lad os anvende en udfyldningshandling i vores nye kolonne.

Efter at have udført handlingen, skal nulværdierne nu erstattes af nuller, syvere, trettene og tyvere. Disse tal angiver, at de hører til i samme tabel. Det vil sige, at nullerne danner en tabel, syvtallene danner en anden tabel, og så videre.

Gruppering af data til et korrekt tabelformat

Da vi allerede ved, hvilke værdier i Kolonne1 der hører til i samme tabel, lad os nu gruppere dem sammen. For at gøre det vil vi bruge Group By-operationen.

Ved at gøre det ender vi nu med kun 4 værdier i vores Index2 kolonne. Bemærk, at en anden kolonne ved navn Count er ved siden af ​​vores Index2-kolonne. Denne kolonne tæller antallet af rækker i hver af vores værdier i kolonnen Index2.

Det er dog ikke rigtig det, vi ønsker at gøre. Det, vi skal gøre, er at danne en ny tabel ved hjælp af de kolonner, vi har.

For at gøre det skal vi bruge Table.FromColumns -operationen, henvise til kolonnen, der indeholder vores listeværdier, som er Column1, og derefter ændre returtypen til en tabel.

Det er vigtigt at sikre, at vi har henvist til den korrekte kolonne i vores formel. Tjek, om Kolonne1 virkelig indeholder listeværdierne.

Dette syr alle værdierne i Kolonne1 med en Indeks2-værdi på 0 i én tabel, dem med Indeks2-værdien på 7 i en anden tabel, og så videre.

Vi kontrollerede hver post til værdien 0 og hentede derefter listeværdien fra kolonne1. Til sidst sammensatte vi disse lister for at danne en enkelt tabel ved hjælp af denne M-kode.

Vi greb de første 7 kolonner og syede dem til et enkelt bord, tog fat i de næste par kolonner, syede dem til et andet bord, og så videre.

Tilføjelse af de separate tabeller

Nu hvor vi allerede har grupperet dem sammen, lad os prøve at tilføje alle tabellerne ved at tilføje endnu et trin. Igen, lad os trykke på fx- knappen for at tilføje en ny formel.

Operationen Table.Combine fletter en liste over tabeller og opretter derefter en enkelt tabel ud af dem. Her skal vi referere til kolonnen, der indeholder vores liste over tabeller, som er kolonnen Antal.

Vi har nu en tabel, hvor alle de indlejrede tabeller er tilføjet tilbage til en stor enkelt tabel.

Efter at have tilføjet alle tabellerne, kan vi nu udføre de sidste par trin i at transformere vores data til et korrekt tabelformat. Vælg de første to kolonner i din tabel, og klik derefter på knappen Fjern pivot andre kolonner på fanen Transformer.

Fjern derefter attributkolonnen, da vi ikke rigtig har brug for den. Gå til Vælg kolonner , og fravælg derefter attributkolonnen.

Til sidst skal du indstille de korrekte typer af alle kolonnerne og mærke dem korrekt.

Dobbeltklik på kolonneoverskriften for hver kolonne for at ændre deres navn i overensstemmelse hermed.

For den første kolonne skal du ændre dens type til dato, da den indeholder vores datoværdier.

Vælg derefter anden og tredje kolonne, og indstil deres typer til tekst.

Helt færdig! Sådan skal vores rigtige tabelformat se ud.

Konklusion

I denne tutorial har vi transformeret nogle data, som blev præsenteret i et kalendertypelayout, til et korrekt tabelformat. Vi rensede først vores data, opdelte dem i mindre tabeller, grupperede de tabeller, der hører sammen, og tilføjede dem alle sammen til sidst.

Denne tutorial giver dig en masse viden om, hvordan du korrekt modellerer dine data for lettere analyse.

Det er meget vigtigt at forstå de begreber, der blev brugt her, da de også kan anvendes på mange andre måder.

Hvis begreberne stadig ikke er klare for dig, kan det måske hjælpe dig at tjekke nedenstående links.

Nyd at arbejde igennem denne!

Melissa