Waarden creëren voor elke datum met behulp van de Power Query-formule

In deze zelfstudie leert u hoe u waarden voor elke dag maakt met behulp van de Power Query- formule in LuckyTemplates.

Het maken van waarden voor datums is een belangrijke techniek om te leren in LuckyTemplates. Het kan u de gegevens geven die u gedurende de dagen in uw model wilt zien. Deze techniek dekt ook de openingen tussen kolommen.

Dit is een geweldig hulpmiddel om te gebruiken wanneer uw datumtabellen informatie missen.

Hier is een stapsgewijze handleiding om dit te doen: Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

Inhoudsopgave

• Stel aanvullende waarden in
• Maak de filtervoorwaarde voor de ID's
• Voeg de filtervoorwaarde voor de datums toe
• Controleer en sorteer de datumkolom
• Gebruik de functie Table.FirstN
• Krijg het dagverschil tussen 2 datumwaarden
• Bepaal een gewichtstap
• Vul de leegte
• Krijg de gewichtsprojectie van datums
• Conclusie

Stel aanvullende waarden in

Voor datasets die ID's, datums en gewichten bevatten, moet u het gat tussen die metingen opvullen. Dit kan door een datumbereik aan te maken om het verschil over de dagen te spreiden.

Ga hiervoor naar Power Query om uw tabel te bekijken.

U moet een kopie van uw gegevens in het geheugen laden. Om dat te laten gebeuren, moet u de Table.Buffer- maatstaf gebruiken.

Als u op TableBuffer klikt, ziet u wat de meting doet.

Ga vervolgens terug naar uw dataset en klik op fx in de formulebalk. U moet de Table.Buffer in de maat toevoegen.

Wijzig de naam van deze stap vervolgens in BufferedTable .

Voeg vervolgens een aangepaste kolom toe.

Maak de filtervoorwaarde voor de ID's

Wijzig in deze Power Query-formule de stap waarnaar wordt verwezen van BufferedTable in RenameColumns omdat BufferedTable eerst moet worden gecontroleerd.

Om de BufferedTable te controleren, gebruikt u Table.SelectRows die een tabel retourneert die overeenkomt met de geselecteerde voorwaarde.

Gebruik dus voor elke uitdrukking in de formule Table.SelectRows en gebruik vervolgens BufferedTable.

Gebruik als filtervoorwaarde een aangepaste eindfunctie en voer BT in voor Buffered Table . Controleer de kolom ID of deze gelijk is aan de ID van het huidige record. Open vervolgens het huidige record door het onderstrepingsteken als toegangsoperator te gebruiken en verwijs vervolgens naar dezelfde ID-kolom.

Dit geeft u een gefilterde tabel die alleen de ID's bevat die overeenkomen met het huidige record. 

Voeg de filtervoorwaarde voor de datums toe

U moet de datumkolom filteren omdat u alleen de datums nodig hebt die gelijk zijn aan het huidige record.

Gebruik deze functies om de BT-datumkolom te controleren:

Deze kolom moet groter zijn dan of gelijk zijn aan de datum van het huidige record.

Hiermee bevat de tabel dan twee rijen in plaats van drie:

Controleer en sorteer de datumkolom

U moet controleren of de sortering van de datumkolom correct is met behulp van Table.Sort . De functie Table.Sort sorteert een tabel op een of meer kolommen, afhankelijk van de gewenste criteria.

De syntaxis in dit voorbeeld is behoorlijk gecompliceerd, dus u moet een downdrill maken op een tabelonderwerp.

U moet met de rechtermuisknop op een tabel klikken en deze als een nieuwe query toevoegen.

Als u hierop klikt, krijgt u een aangepaste query die die tabel bevat.

Klik vervolgens op de datumkolom en kies Oplopend sorteren.

Kopieer de hele formule:

Ga terug naar uw hoofdquery en plak deze in de formulebalk:

U moet de verwijzing van Table.Sort verwijderen omdat de tabel die de parameter moet doorgeven, het resultaat moet zijn van de Table.SelectRows.

Als u klaar bent, voegt u de sorteervoorwaarden weer toe. Na het toepassen van de wijzigingen is uw tabel nu correct gesorteerd op datum.

Gebruik de functie Table.FirstN

Voor deze stap heeft u slechts 2 records nodig in plaats van 3.

Ga terug naar de aangepaste query om dit op te lossen. Klik op Rijen behouden en vervolgens op Bovenste rijen behouden.

Voer 2 in de lege ruimte in voor het aantal rijen.

U zult merken dat de functie Table.FirstN in de formulebalk verschijnt. Table.FirstN retourneert de eerste rij(en) van de tabel, afhankelijk van de waarde van countOrCondition .

Voeg die functie in, samen met het aantal rijen dat u nodig hebt in de hoofdquery. Voeg aan het eind de functietypetabel toe.

Elk tabelobject in de set heeft nu 1 of 2 rijen.

Krijg het dagverschil tussen 2 datumwaarden

De volgende stap is om het aantal dagen tussen de datumwaarden te krijgen en de datum te extraheren die op de tweede record stond.

Begin met het toevoegen van een aangepaste kolom. Noem het Dagen . Volg daarna deze Power Query-formule:

De syntaxis zorgt ervoor dat er 2 records in een tabel staan. Als er maar 1 is, is het dagverschil 0 omdat er geen volgend record is. Als er 2 zijn, kunt u het verschil uit de datumwaarden halen.

Voeg vervolgens het typenummer toe aan de formule zoals deze.

Zoals u kunt zien, krijgt u een verschil van 385 dagen van 14 september 2017 tot 4 oktober 2018.

Bepaal een gewichtstap

Vervolgens moet u berekenen wat het gewicht voor elke dag zou moeten zijn.

U moet opnieuw een aangepaste kolom toevoegen, deze WeightStep noemen en deze Power Query-formule kopiëren:

De syntaxis controleert of de tabel meer dan 1 rij heeft, trekt het gewicht af van het huidige record en deelt dat verschil door het aantal dagen.

Voeg opnieuw het typenummer toe aan de formule, zodat u het gewicht van elke dag kunt zien.

Vul de leegte

Nu u het aantal dagen heeft, kunt u een lijst met datums maken om het gat in de dataset op te vullen.

Voeg eerst nog een aangepaste kolom toe en noem deze DateKey . Volg daarna deze Power Query-formule:

De formule controleert of er een datumverschil is. Als dat het geval is, wordt er een lijst gemaakt met datums tussen de startdatum die afkomstig is van het huidige record, en wordt deze uitgebreid met het aantal dagen. Maar als er geen verschillen zijn, wordt de datum geretourneerd.

Dit is het resultaat:

De tabelonderwerpen die geen verschillen hadden, kregen de datum terug; de tafels die dat wel deden, kregen een lijst met data.

Als u de DateKey uitbreidt naar nieuwe rijen, kunt u de records van alle dagen zien.

Krijg de gewichtsprojectie van datums

Nadat u een datumlijst hebt gemaakt, moet u een gewichtsprojectie voor de datums krijgen.

Voeg eerst een aangepaste kolom toe en noem deze WeightProjection . Voer vervolgens deze Power Query-formule in:

De syntaxis haalt de gewichtswaarde op, telt deze op bij de DateKey-waarde, trekt deze af van de datumwaarde en vermenigvuldigt deze met de WeightStep.

Voeg nogmaals het typenummer toe aan het einde van de formule.

Je hebt nu de gewichtsprojectie voor elke datum.

Verwijder de hulpkolommen: Custom, Days en WeightStep met behulp van de onderstaande formule en wijzig de notatie van de DateKey-kolom in Date.

Conclusie

In deze zelfstudie hebt u geleerd hoe u de hiaten in uw dataset kunt opvullen door waarden in elke datumlijst te maken.

Deze datumwaarden helpen u waardevolle inzichten uit uw model te halen. Als uw tabellen informatie missen, kunt u deze techniek gebruiken om ze uitgebreider te maken.

Er kunnen essentiële gegevens in uw tabel staan ​​die u weglaat, dus maximaliseer altijd elke rij en kolom.

Al het beste,

Melissa