Skapa värden för varje datum med hjälp av Power Query Formel

I den här handledningen får du lära dig hur du skapar värden för varje dag med hjälp av formeln i LuckyTemplates.

Att skapa värden för datum är en viktig teknik att lära sig i LuckyTemplates. Det kan ge dig den data du vill se under dagarna i din modell. Denna teknik täcker även luckorna mellan kolumnerna.

Det här är ett bra verktyg att använda när dina datumtabeller saknar information.

Här är en steg-för-steg-guide om hur du gör det: Du kan titta på hela videon med den här handledningen längst ner på den här bloggen.

Innehållsförteckning

• Ange ytterligare värden
• Skapa filtervillkoret för ID:n
• Lägg till filtervillkoret för datumen
• Kontrollera och sortera datumkolumnen
• Använd funktionen Table.FirstN
• Få dagsskillnaden mellan 2 datumvärden
• Bestäm ett viktsteg
• Fyll luckan
• Få viktprognosen för datum
• Slutsats

Ange ytterligare värden

För datamängder som innehåller ID, datum och vikter måste du fylla gapet mellan dessa måtten. Detta kan göras genom att skapa ett datumintervall för att sprida skillnaden över dagarna.

För att göra det, gå till Power Query för att se din tabell.

Du måste ladda en kopia av dina data till minnet. För att det ska hända måste du använda måttet Table.Buffer .

Om du klickar på TableBuffer ser du vad måttet gör.

Gå sedan tillbaka till din datamängd och klicka på fx i formelfältet. Du måste lägga till Table.Buffer i måttet.

Byt sedan namn på det här steget till BufferedTable .

Lägg sedan till en anpassad kolumn.

Skapa filtervillkoret för ID:n

I denna Power Query-formel ändrar du det refererade steget från BufferedTable till RenameColumns eftersom BufferedTable bör kontrolleras först.

För att kontrollera BufferedTable, använd Table.SelectRows som returnerar en tabell som matchar det valda villkoret.

Så för varje uttryck i formeln, använd Table.SelectRows och använd sedan BufferedTable.

Som ett filtervillkor, använd en anpassad slutfunktion och ange BT för Buffered Table . Kontrollera ID-kolumnen om den är lika med ID från den aktuella posten. Gå sedan till den aktuella posten genom att använda understrecket som åtkomstoperatör och referera sedan till samma ID-kolumn.

Detta kommer att ge dig en filtrerad tabell som bara innehåller de ID:n som matchar den aktuella posten. 

Lägg till filtervillkoret för datumen

Du måste filtrera datumkolumnen eftersom du bara behöver de datum som är lika med den aktuella posten.

Använd dessa funktioner för att kontrollera BT-datumkolumnen:

Denna kolumn måste vara större än eller lika med datumet från den aktuella posten.

Med detta kommer tabellen då att innehålla två rader istället för tre:

Kontrollera och sortera datumkolumnen

Du måste kontrollera om sorteringen av datumkolumnen är korrekt med Table.Sort . Funktionen Table.Sort sorterar en tabell på en eller flera kolumner beroende på vilka kriterier du vill ha.

Syntaxen i detta exempel är ganska komplicerad så du måste göra en nedborrning på ett tabellämne.

Du måste högerklicka på en tabell och lägga till den som en ny fråga.

Om du klickar på det får du en anpassad fråga som innehåller den tabellen.

Klicka sedan på datumkolumnen och välj Sortera stigande.

Kopiera hela formeln:

Gå tillbaka till din huvudfråga och klistra in den i formelfältet:

Du måste ta bort referensen till Table.Sort eftersom tabellen som måste skicka parametern bör vara resultatet från Table.SelectRows.

När du är klar lägger du tillbaka sorteringsvillkoren. Efter att ha tillämpat ändringarna är din tabell nu sorterad korrekt efter datum.

Använd funktionen Table.FirstN

För detta steg behöver du bara 2 poster istället för 3.

För att åtgärda detta, gå tillbaka till den anpassade frågan. Klicka på Behåll rader och sedan på Behåll översta rader.

Ange 2 i blanketten för antalet rader.

Du kommer att märka att funktionen Table.FirstN visas i formelfältet. Table.FirstN returnerar den första raden/raderna i tabellen beroende på värdet på countOrCondition .

Infoga den funktionen, tillsammans med antalet rader du behöver i huvudfrågan. Inkludera funktionstyptabellen i slutet.

Varje tabellobjekt i uppsättningen kommer nu att ha antingen 1 eller 2 rader.

Få dagsskillnaden mellan 2 datumvärden

Nästa steg är att få antalet dagar mellan datumvärdena och extrahera datumet som fanns på den andra posten.

Börja med att lägga till en anpassad kolumn. Name it Days . Följ sedan denna Power Query-formel:

Syntaxen ser till att det finns 2 poster i en tabell. Om det bara finns 1 är dagsskillnaden 0 eftersom den inte har något nästa rekord. Om det finns 2 kan du få skillnaden från datumvärdena.

Lägg sedan till typnummer i formeln så här.

Som du kan se får du 385 dagars skillnad från 14 september 2017 till 4 oktober 2018.

Bestäm ett viktsteg

Därefter måste du räkna ut vad vikten för varje dag ska vara.

Du måste lägga till en anpassad kolumn igen, namnge den WeightStep och kopiera denna Power Query-formel:

Syntaxen kontrollerar om tabellen har mer än 1 rad, subtraherar vikten från den aktuella posten och dividerar skillnaden med antalet dagar.

Återigen, lägg till typnummer i formeln så att du kan se vikten för varje dag.

Fyll luckan

Nu när du har antalet dagar kan du skapa en lista med datum för att fylla luckan i datamängden.

Lägg först till en annan anpassad kolumn och kalla den DateKey . Följ sedan denna Power Query-formel:

Formeln kontrollerar om det finns en datumskillnad. Om det finns, skapar den en lista med datum mellan startdatumet som kommer från den aktuella posten och utökar den med antalet dagar. Men om det inte finns några skillnader returnerar den datumet.

Detta är resultatet:

De tabellämnen som inte hade några skillnader fick sitt datum tillbaka; tabellerna som gjorde det, fick en lista med datum.

Om du utökar DateKey till nya rader kan du se posterna för alla dagar.

Få viktprognosen för datum

Efter att ha skapat en datumlista måste du få en viktprojektion för datumen.

Lägg först till en anpassad kolumn och döp den till WeightProjection . Mata sedan in denna Power Query-formel:

Syntaxen hämtar viktvärdet, lägger till det till DateKey-värdet, subtraherar det från datumvärdet och multiplicerar det med WeightStep.

Återigen, lägg till typnummer i slutet av formeln.

Du har nu viktprognosen för varje datum.

Ta bort hjälpkolumnerna: Custom, Days och WeightStep genom att använda formeln nedan och ändra formatet på kolumnen DateKey till Date.

Slutsats

I den här handledningen lärde du dig hur du fyller luckorna i din datamängd genom att skapa värden i varje datumlista.

Dessa datumvärden hjälper dig att få värdefulla insikter från din modell. Om dina tabeller saknar information kan du använda den här tekniken för att göra dem mer heltäckande.

Det kan finnas viktiga data i din tabell som du utelämnar, så maximera alltid varje rad och kolumn.

Med vänliga hälsningar,

Melissa