Oprettelse af værdier for hver dato ved hjælp af Power Query Formula

I denne øvelse lærer du, hvordan du opretter værdier for hver dag ved hjælp af formel i LuckyTemplates.

At skabe værdier for datoer er en vigtig teknik at lære i LuckyTemplates. Det kan give dig de data, du ønsker at se gennem dagene i din model. Denne teknik dækker også hullerne mellem søjlerne.

Dette er et fantastisk værktøj at bruge, når dine datotabeller mangler information.

Her er en trin-for-trin guide til, hvordan du gør det: Du kan se den fulde video af denne vejledning nederst på denne blog.

Indholdsfortegnelse

• Indstil yderligere værdier
• Opret filterbetingelsen for ID'erne
• Tilføj filterbetingelsen for datoerne
• Kontroller og sorter datokolonnen
• Brug funktionen Table.FirstN
• Få dagsforskellen mellem 2 datoværdier
• Bestem et vægttrin
• Udfyld hullet
• Få vægtfremskrivning af datoer
• Konklusion

Indstil yderligere værdier

For datasæt, der indeholder id'er, datoer og vægte, skal du udfylde hullet mellem disse målinger. Dette kan gøres ved at oprette et datointerval for at sprede forskellen over dagene.

For at gøre det skal du gå til Power Query for at se din tabel.

Du skal indlæse en kopi af dine data i hukommelsen. For at det kan ske, skal du bruge Table.Buffer- målet.

Hvis du klikker på TableBuffer, vil du se, hvad målingen gør.

Gå derefter tilbage til dit datasæt og klik på fx på formellinjen. Du skal tilføje Table.Buffer i målingen.

Omdøb derefter dette trin til BufferedTable .

Tilføj derefter en brugerdefineret kolonne.

Opret filterbetingelsen for ID'erne

I denne Power Query-formel skal du ændre det refererede trin fra BufferedTable til RenameColumns , fordi BufferedTable skal kontrolleres først.

For at kontrollere BufferedTable skal du bruge Table.SelectRows , som returnerer en tabel, der matcher den valgte betingelse.

Så for hvert udtryk i formlen skal du bruge Table.SelectRows og derefter bruge BufferedTable.

Som en filterbetingelse skal du bruge en brugerdefineret slutfunktion og indtaste BT for Buffered Table . Tjek ID-kolonnen, hvis den er lig med ID'et fra den aktuelle post. Derefter skal du få adgang til den aktuelle post ved at bruge understregningen som adgangsoperatør, og derefter referere til den samme ID-kolonne.

Dette vil give dig en filtreret tabel, der kun indeholder de ID'er, der matcher den aktuelle post. 

Tilføj filterbetingelsen for datoerne

Du skal filtrere datokolonnen, fordi du kun har brug for de datoer, der er lig med den aktuelle post.

Brug disse funktioner til at kontrollere BT-datokolonnen:

Denne kolonne skal være større end eller lig med datoen fra den aktuelle post.

Med dette vil tabellen så indeholde to rækker i stedet for tre:

Kontroller og sorter datokolonnen

Du skal tjekke om sorteringen af ​​datokolonnen er korrekt ved hjælp af Table.Sort . Funktionen Table.Sort sorterer en tabel på en eller flere kolonner afhængigt af de kriterier du ønsker.

Syntaksen i dette eksempel er ret kompliceret, så du skal lave en nedboring på et tabelemne.

Du skal højreklikke på en tabel og tilføje den som en ny forespørgsel.

Hvis du klikker på det, får du en tilpasset forespørgsel, der indeholder denne tabel.

Klik derefter på datokolonnen og vælg Sorter stigende.

Kopier hele formlen:

Gå tilbage til din hovedforespørgsel og indsæt den i formellinjen:

Du skal fjerne referencen til Table.Sort, fordi den tabel, der skal passere parameteren, skal være resultatet fra Table.SelectRows.

Når du er færdig, skal du tilføje sorteringsbetingelserne tilbage. Efter at du har anvendt ændringerne, er din tabel nu sorteret korrekt efter dato.

Brug funktionen Table.FirstN

Til dette trin har du kun brug for 2 poster i stedet for 3.

For at rette dette skal du gå tilbage til den tilpassede forespørgsel. Klik på Behold rækker og derefter Behold øverste rækker.

Indtast 2 i det tomme felt for antallet af rækker.

Du vil bemærke, at funktionen Table.FirstN vises i formellinjen. Table.FirstN returnerer den eller de første rækker i tabellen afhængigt af værdien af ​​countOrCondition .

Indsæt den funktion sammen med antallet af rækker, du har brug for i hovedforespørgslen. Medtag funktionstypetabellen til sidst.

Hvert tabelobjekt i sættet vil nu have enten 1 eller 2 rækker.

Få dagsforskellen mellem 2 datoværdier

Det næste trin er at få antallet af dage mellem datoværdierne og udtrække den dato, der var på den anden post.

Start med at tilføje en brugerdefineret kolonne. Name it Days . Følg derefter denne Power Query-formel:

Syntaksen sørger for, at der er 2 poster i en tabel. Hvis der kun er 1, er dagsforskellen 0, fordi den ikke har nogen næste rekord. Hvis der er 2, kan du få forskellen fra datoværdierne.

Tilføj derefter typenummer i formlen som denne.

Som du kan se, får du 385 dages forskel fra 14. september 2017 til 4. oktober 2018.

Bestem et vægttrin

Dernæst skal du beregne, hvad vægten for hver dag skal være.

Du skal tilføje en brugerdefineret kolonne igen, navngive den WeightStep og kopiere denne Power Query-formel:

Syntaksen kontrollerer, om tabellen har mere end 1 række, trækker vægten fra den aktuelle post og dividerer forskellen med antallet af dage.

Tilføj igen typenummer i formlen, så du kan se vægten af ​​hver dag.

Udfyld hullet

Nu hvor du har antallet af dage, kan du oprette en liste over datoer for at udfylde hullet i datasættet.

Tilføj først en anden brugerdefineret kolonne og kald den DateKey . Følg derefter denne Power Query-formel:

Formlen tjekker, om der er en datoforskel. Hvis der er, opretter den en liste over datoer mellem startdatoen, som er fra den aktuelle post, og udvider den med antallet af dage. Men hvis der ikke er nogen forskelle, returnerer den datoen.

Dette er resultatet:

De tabelemner, der ikke havde nogen forskelle, fik sin dato returneret; tabellerne, der gjorde, fik en liste over datoer.

Hvis du udvider DateKey til nye rækker, kan du se registreringerne for alle dagene.

Få vægtfremskrivning af datoer

Når du har oprettet en datoliste, skal du have en vægtfremskrivning for datoerne.

Tilføj først en brugerdefineret kolonne, og giv den navnet WeightProjection . Indtast derefter denne Power Query-formel:

Syntaksen henter vægtværdien, føjer den til DateKey-værdien, trækker den fra datoværdien og multiplicerer den med WeightStep.

Tilføj igen typenummer til slutningen af ​​formlen.

Du har nu vægtfremskrivningen for hver dato.

Fjern hjælpekolonnerne: Custom, Days og WeightStep ved at bruge formlen nedenfor, og skift formatet på DateKey-kolonnen til Dato.

Konklusion

I dette selvstudie lærte du, hvordan du udfylder hullerne i dit datasæt ved at oprette værdier i hver datoliste.

Disse datoværdier hjælper dig med at få værdifuld indsigt fra din model. Hvis dine tabeller mangler information, kan du bruge denne teknik til at gøre dem mere omfattende.

Der kan være væsentlige data i din tabel, som du udelader, så maksimer altid hver række og kolonne.

Alt det bedste,

Melissa