Förvandla kalendertypslayout till tabellformat i LuckyTemplates med hjälp av frågeredigeraren

Att analysera de data som vi har kan vara svårt om det inte är korrekt ordnat. I den här handledningen kommer jag att förklara hur man omvandlar vertikala och horisontella upprepade data till ett tabellformat. Vi gör detta så att vi lättare kan analysera det med hjälp av LuckyTemplates. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Denna handledning gjordes för att ta itu med frågan om en medlem. Medlemmen bad om ett sätt på vilket han kunde omforma sina data från en kalendertyplayout till ett tabellformat.

Innan vi börjar, låt oss först undersöka de data vi har.

Här har vi en datalayout där dagarna ställs in horisontellt och veckorna ställs in vertikalt. Denna typ av arrangemang är bekant för de flesta av oss eftersom det är så vi enkelt kan förstå data.

Den här typen av layout är dock svårare att analysera eftersom det finns många kolumner och rader att ta hänsyn till.

Med det sagt, låt oss omvandla data till ett korrekt tabellformat där vi slutar med en kolumn för datum, en kolumn för rörelserna och en annan kolumn för namnen.

Innehållsförteckning

• Rensa upp data för ett korrekt tabellformat
• Breaking The Tables
• Identifiera listan för de nya tabellerna för ett korrekt tabellformat
• Bestämma vilka värden som hör hemma i tabellen
• Gruppera data för ett korrekt tabellformat
• Bifoga de separata tabellerna
• Slutsats

Rensa upp data för ett korrekt tabellformat

För att göra saker enklare, låt oss omvandla vår data inuti. Men innan vi gör det, låt oss se till att Formula Bar är synlig på din skärm. Om det inte är det, gå till fliken Visa och markera sedan rutan bredvid formelfältet.

Det första steget är att ta bort de onödiga värdena i vår data. Låt oss ta bort den första kolumnen och de två översta raderna av vår data eftersom vi egentligen inte behöver dem. För att göra det klickar du på fliken Ta bort rader, klickar på Ta bort översta rader, anger 2 och trycker sedan på OK.

Och nu för att ta bort den första kolumnen, gå till fliken Välj kolumner och avmarkera sedan kolumn 1 genom att avmarkera rutan bredvid den.

Låt oss också ta bort raderna index 14 till 16 eftersom de är helt fyllda med bara nollvärden. Återigen, gå till fliken Ta bort rader och klicka sedan på Ta bort tomma rader .

Ett annat problem som vi har är att datumvärdena hoppar över en kolumn varje gång. Datumen följs alltid av ett nollvärde som markerats nedan. Detta händer inte bara på den översta raden utan även på raderna under.

För att fylla dessa luckor, låt oss först transponera vår datatabell genom att gå till  Transform- fliken och sedan klicka på Transpose-knappen.

Bordet ska nu se ut som om det har vänts på sidan efter att ha utfört åtgärden.

Välj sedan alla kolumner som innehåller datumen, högerklicka på kolumnrubriken och välj sedan Fyll ner.

Detta bör duplicera datumen och fylla luckorna i de valda kolumnerna.

Breaking The Tables

När vi tittar på de data vi nu har ser vi att det finns fyra tabeller bredvid varandra. Kolumnerna 1 till 7 utgör en tabell, kolumnerna 8 till 13 utgör en annan tabell, och så vidare.

För att lägga till dessa i en enda tabell måste vi utföra en grundläggande unpivot- operation för att omvandla den till ett korrekt tabellformat. Men eftersom borden ligger bredvid varandra kan vi inte bara göra det. Vi måste bryta isär det här bordet och sy ihop dem igen till mindre bord.

För att göra det skapar vi den första formeln för denna handledning. Låt oss klicka på fx- knappen och lägg sedan till formeln som vi ska använda. Vi kommer att använda operationen Table.ToColumns . Detta skapar en lista med kapslade listor med kolumnvärden från en tabell. Varje kolumn kommer att omvandlas till en lista.

Så här har jag satt upp formeln.

Formeln skapar en lista över de kolumner som vi tidigare har. Det första värdet i listan innehåller kolumn 1, det andra värdet innehåller kolumn 2 och så vidare.

Identifiera listan för de nya tabellerna för ett korrekt tabellformat

Nu bör vi identifiera vilka värden på listan som innehåller datumvärden. När vi väl vet var dessa datum finns kan vi bestämma var var och en av våra separata bord börjar eller slutar.

För att göra det, låt oss först konvertera listan tillbaka till en tabell genom att klicka på knappen Till tabell i det övre vänstra hörnet.

Lägg sedan till en indexkolumn bredvid den.

Nästa sak vi ska göra är att lägga till ytterligare en kolumn bredvid indexkolumnen. Den här kolumnen bör hjälpa oss att avgöra vilka värden på kolumn1 som innehåller datumvärdena. Låt oss namnge denna kolumn Index2.

För att göra det kommer vi att införliva en formel i Index2, som returnerar indexvärdet för Kolumn1 om den innehåller ett datum.

Vi kommer att använda , och sedan referera till kolumn1. Fästena kommer att fungera som operatör; lägga till 0 mellan dessa parenteser betyder att vi får det första värdet från listan; sedan avgör vi om det är ett datum. Om det är så vill jag att formeln ska returnera indexnumret; om det inte är det vill jag inte att det ska ge något värde alls.

Eftersom det första värdet i Kolumn1 innehåller ett datum, bör det första värdet i Index2 innehålla dess indexnummer 0. Dessutom, eftersom det andra värdet i Kolumn1 inte innehåller ett datum, bör det andra värdet i Index2 bara innehålla ett nullvärde.

Så här har jag satt upp formeln för Index2. Se bara till att det inte finns några syntaxfel i din formel.

Efter att ha gjort det, bör vår kolumn Index2 nu ha indexet för värdena i kolumn1 som innehåller datumet. Det kommer också att inkludera några nollvärden som ett resultat av formeln som vi har ställt in.

Bestämma vilka värden som hör hemma i tabellen

Nu indikerar dessa indexvärden inuti Index2 början och slutet av de separata tabellerna som vi har. Det vill säga, 0 markerar början på det första bordet, 7 markerar slutet på det första bordet, och början på det andra bordet, och så vidare.

För att hjälpa oss att identifiera vilka värden i kolumn1 som hör till samma tabell, låt oss tillämpa en fyllningsåtgärd i vår nya kolumn.

Efter att ha utfört åtgärden ska null-värdena nu ersättas med nollor, sjuor, tretton och tjugo. Dessa siffror indikerar att de hör hemma i samma tabell. Det vill säga, nollorna bildar en tabell, sjuorna bildar en annan tabell och så vidare.

Gruppera data för ett korrekt tabellformat

Eftersom vi redan vet vilka värden i kolumn1 som hör till samma tabell, låt oss nu gruppera dem. För att göra det kommer vi att använda funktionen Group By.

Genom att göra det slutar vi nu med endast 4 värden i vår Index2-kolumn. Lägg märke till att en annan kolumn med namnet Count finns bredvid vår Index2-kolumn. Den här kolumnen räknar antalet rader i vart och ett av våra värden i kolumnen Index2.

Det är dock inte riktigt vad vi vill göra. Vad vi behöver göra är att skapa en ny tabell med hjälp av de kolumner vi har.

För att göra det kommer vi att använda Table.FromColumns -operationen, referera till kolumnen som innehåller våra listvärden som är Column1, och ändra sedan returtypen till en tabell.

Det är viktigt att se till att vi har refererat till rätt kolumn i vår formel. Kontrollera om kolumn1 verkligen innehåller listvärdena.

Detta sammanfogar alla värden i kolumn1 med ett Index2-värde på 0 till en tabell, de med Index2-värdet på 7 till en annan tabell, och så vidare.

Vi kontrollerade varje post till värdet 0 och hämtade sedan listvärdet från kolumn1. Slutligen sammanfogade vi dessa listor för att bilda en enda tabell med den här M-koden.

Vi tog tag i de första 7 kolumnerna och sydde ihop dem till ett enda bord, tog tag i de nästa kolumnerna, sydde ihop dem till ett annat bord och så vidare.

Bifoga de separata tabellerna

Nu när vi redan har grupperat dem tillsammans, låt oss försöka lägga till alla tabeller genom att lägga till ytterligare ett steg. Återigen, låt oss trycka på fx- knappen för att lägga till en ny formel.

Operationen Table.Combine slår samman en lista med tabeller och skapar sedan en enda tabell av dem. Här kommer vi att referera till kolumnen som innehåller vår lista med tabeller, som är kolumnen Räkna.

Vi har nu en tabell där alla kapslade tabeller läggs tillbaka till en enda stor tabell.

Efter att ha lagt till alla tabeller kan vi nu utföra de sista stegen för att omvandla vår data till ett korrekt tabellformat. Välj de två första kolumnerna i din tabell och klicka sedan på knappen Unpivot Other Columns på fliken Transform.

Ta sedan bort attributkolumnen eftersom vi egentligen inte behöver den. Gå till Välj kolumner och avmarkera sedan attributkolumnen.

Ställ sedan in rätt typ av alla kolumner och märk dem ordentligt.

Dubbelklicka på kolumnrubriken för varje kolumn för att ändra deras namn.

För den första kolumnen ändrar du dess typ till datum eftersom den innehåller våra datumvärden.

Välj sedan den andra och tredje kolumnen och ställ in deras typer till text.

Helt klar! Så här ska vårt rätta tabellformat se ut.

Slutsats

I den här handledningen omvandlade vi en del data, som presenterades i en layout av kalendertyp, till ett korrekt tabellformat. Vi rengjorde först vår data, bröt upp den i mindre tabeller, grupperade tabellerna som hör ihop och la sedan till dem alla till slut.

Denna handledning ger dig mycket kunskap om hur du korrekt modellerar dina data för enklare analys.

Att förstå begreppen som användes här är mycket viktigt eftersom de också kan tillämpas på många andra sätt.

Om begreppen fortfarande inte är tydliga för dig kan det hjälpa dig att kolla länkarna nedan.

Njut av att arbeta igenom detta!

Melissa