Kalenderlayouter till tabellformat med hjälp av Power Query

I den här handledningen får du lära dig hur du förvandlar layouter av flera kalendertyper till ett tabellformat med hjälp av Power Query i LuckyTemplates. Du kommer att lära dig hur du transformerar flera filer istället för en enda fil från en mapp. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Denna handledning kommer också att lära dig hur du fyller luckor i dina data och hur du skördar dem i LuckyTemplates.

Innehållsförteckning

• Förbereder filerna och layouterna i Power Query
• Formulera beräkningar i Power Query
• Fyller luckorna i datumen
• Gruppera separerade tabeller
• Slutsats

Förbereder filerna och layouterna i Power Query

Detta är den typiska layouten för filerna och veckorna som anges horisontellt och vertikalt. Från fil till fil kan antalet rader variera på grund av variationen i antalet rader.

Gå nu till Power Query och hämta filerna. Välj Ny källa och klicka på Mer. Klicka sedan på Alla och välj Mapp.

Välj Parameter och tryck på OK.

Det kommer att visa dig filerna i mappen. Du måste samla alla filer i en enda resultatfråga. Genom att klicka på Kombinera och transformera laddas data och skapa ett antal frågor.

Välj sedan valfri fil i mappen som en provfil och välj ett objekt som du vill starta.

När du tittar på Query1 kan du se data från både File1 och File2.

Formulera beräkningar i Power Query

Dataformatet i Query1 är fortfarande felaktigt. Du kan ändra det genom frågan Transform Sample File.

Om du ändrar formatet på den frågan till en tabellform kommer den att fungera som en ritning för alla filer i mappen.

Ta bort de två första raderna och kolumn1.

Fyll sedan luckorna inuti bordet. Gå till fliken Transformera och välj Transponera. Välj kolumnerna som innehåller datum och klicka på rubriken. Klicka på Fyll och välj Ner.

Kolumnerna med datum i formelfältet betyder att om det finns ett varierande antal rader varje vecka, kommer resultaten inte att finnas i dessa kolumner. Så, ta bort ifyllda steg i avsnittet Tillämpade steg.

Varje grupp av tabeller börjar med en kolumn som innehåller datum. Du måste utföra grundläggande operationer för att få dem tillbaka till ett tabellformat.

Lägg till ett manuellt steg genom att trycka på f(x) i formelfältet. Mata sedan in Table.ToColumns . Detta förvandlar varje kolumn till en lista som innehåller listor.

Förvandla listan till en tabell och lägg till ett indexnummer. Du kan ändra från 0 eller 1.

Fyller luckorna i datumen

Det finns fortfarande luckor i datumen som du behöver fylla i. Men fyllningsåtgärden fungerar inte för listor. Du måste förvandla den här listan till en tabell.

Välj kolumn Index och klicka på Gruppera efter. Ersätt Count to ToTable för att ändra kolumnnamnet. Ändra RowCount till FromColumns och peka på Column1 där listorna placeras. Förvandla den sedan till en typtabell.

Du kan nu se att listan förvandlas till en tabell med endast en kolumn.

Utför nu fyllningsoperationen för varje tabell. Lägg först till en anpassad kolumn och kalla den FillDown .

Du måste komma åt det första värdet i den första kolumnen i tabellen så för formeln, använd IF . Referera till ToTable-kolumnen som innehåller tabellen. Ange kolumn1 som den kolumn du vill referera till. Använd sedan null mellan dem för att hämta det första värdet från listan.

Kontrollera sedan om det är ett datum. Om det är det, fyll den kolumnen ner med Table.Filldown. Inuti den funktionen anger du ToTable och Column1. Men om det inte är ett datum, skriv in ToTable för att returnera hela tabellen.

Detta ger dig en ny kolumn med tabeller ifyllda.

Hitta var kolumndatumen är placerade och dela upp dem i separata tabeller. För att göra det måste du göra om tabellerna till en lista med kolumner.

Efter funktionerna Table.FillDown och Else i formelfältet hänvisar du till Kolumn1.

Detta kommer att förvandla dina tabeller till listor med uppsättning datum.

Förvandla sedan listan tillbaka till en tabell genom att uppdatera din indexkolumn. Kom ihåg att delningsplatsen är där datumkolumnens position är. Så lägg till en anpassad kolumn och kalla den Index2 . Kontrollera sedan om listan innehåller datum.

Använd IF och mata in FillDown. Du behöver bara kontrollera det första värdet på listan. Om det är ett datum, returnera Index. Om inte, mata in null . Fyll sedan kolumnen neråt.

Gruppera separerade tabeller

Använd Gruppera efter i kolumnen Index2. Detta grupperar tabellerna med samma Index2-värden till en.

Du kommer inte att räkna raderna. Så ändra RowCount till FromColumns och lägg till FillDown-kolumnen. Ändra sedan typen till tabell.

Om du klickar på en tabell ser du kolumnerna i den.

Därefter måste du lägga till alla separata tabeller tillsammans till en enda tabell. Skapa ett manuellt steg och använd Table.Combine. Referera sedan till kolumnen Antal inuti den.

Välj Kolumn1 och Kolumn2. Klicka på Transformera och välj Unpivot Other Columns. Det kommer då att visa dig 4 kolumner.

Ta bort Attribut-kolumnen eftersom den inte behövs. Ändra sedan kolumnernas namn till Datum , Rörelser och Namn . Ändra datatypen för kolumnen Datum till Datum och kolumnen Namn till Text.

Du har nu uppdaterat transformationerna till transformexempelfilen.

Om du går till Fråga1 kan du se att kolumn1 i tabellen inte kan hittas. Så ta bort steget Ändrad typ i avsnittet Tillämpade steg.

Ändra slutligen kolumnernas datatyper. Ställ in datatypen för kolumnen Datum till Datum. Ställ sedan in datatypen för kolumnerna Rörelser och Namn till Text.

Du har nu uppdaterat och ändrat datamodellens format till en tabellform.

Slutsats

I din datarapport måste du fylla luckorna i din datumtabell genom att omvandla kalenderlayouter till en tabellform.

Du kan transformera flera kolumner med Power Query i LuckyTemplates. Detta kommer att ge din rapport korrekta och värdefulla insikter att skörda.

Med vänliga hälsningar,

Melissa