Vytváranie hodnôt pre každý dátum pomocou vzorca Power Query

V tomto návode sa naučíte, ako vytvárať hodnoty pre každý deň pomocou vzorca v LuckyTemplates.

Vytváranie hodnôt pre dátumy je dôležitá technika, ktorú sa treba naučiť v LuckyTemplates. Môže vám poskytnúť údaje, ktoré chcete vidieť počas dní vo vašom modeli. Táto technika pokrýva aj medzery medzi stĺpmi.

Je to skvelý nástroj, ktorý môžete použiť, keď v tabuľkách dátumov chýbajú informácie.

Tu je podrobný návod, ako na to: Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Obsah

• Nastavte ďalšie hodnoty
• Vytvorte podmienku filtra pre ID
• Pridajte podmienku filtra pre dátumy
• Skontrolujte a zoraďte stĺpec dátumu
• Použite funkciu Table.FirstN
• Získajte denný rozdiel medzi 2 hodnotami dátumu
• Určte hmotnostný krok
• Vyplňte medzeru
• Získajte odhad hmotnosti dátumov
• Záver

Nastavte ďalšie hodnoty

Pre množiny údajov, ktoré obsahujú ID, dátumy a váhy, musíte vyplniť medzeru medzi týmito meraniami. Dá sa to dosiahnuť vytvorením rozsahu dátumov, aby sa rozdiel rozložil na dni.

Ak to chcete urobiť, prejdite do Power Query a zobrazte svoju tabuľku.

Musíte nahrať kópiu svojich údajov do pamäte. Aby sa tak stalo, musíte použiť meranie Table.Buffer .

Ak kliknete na TableBuffer, uvidíte, čo meranie robí.

Potom sa vráťte do svojej množiny údajov a kliknite na tlačidlo fx na paneli vzorcov. Do miery musíte pridať Table.Buffer.

Potom tento krok premenujte na BufferedTable .

Potom pridajte vlastný stĺpec.

Vytvorte podmienku filtra pre ID

V tomto vzorci Power Query zmeňte odkazovaný krok z BufferedTable na RenameColumns , pretože BufferedTable by sa mala skontrolovať ako prvá.

Na kontrolu BufferedTable použite Table.SelectRows , ktorá vráti tabuľku, ktorá zodpovedá vybratej podmienke.

Takže pre každý výraz vo vzorci použite Table.SelectRows a potom použite BufferedTable.

Ako podmienku filtra použite funkciu vlastného konca a zadajte BT pre tabuľku s vyrovnávacou pamäťou . Skontrolujte stĺpec ID, ak sa zhoduje s ID z aktuálneho záznamu. Potom pristupujte k aktuálnemu záznamu pomocou podčiarkovníka ako operátora prístupu a potom odkazujte na rovnaký stĺpec ID.

Získate tak filtrovanú tabuľku, ktorá obsahuje iba ID, ktoré sa zhodujú s aktuálnym záznamom. 

Pridajte podmienku filtra pre dátumy

Musíte filtrovať stĺpec dátumu, pretože potrebujete iba dátumy, ktoré sa rovnajú aktuálnemu záznamu.

Pomocou týchto funkcií skontrolujte stĺpec dátumu BT:

Tento stĺpec musí byť väčší alebo rovný dátumu z aktuálneho záznamu.

Vďaka tomu bude tabuľka obsahovať dva riadky namiesto troch:

Skontrolujte a zoraďte stĺpec dátumu

Musíte skontrolovať, či je zoradenie stĺpca dátumu správne pomocou Table.Sort . Funkcia Table.Sort triedi tabuľku podľa jedného alebo viacerých stĺpcov v závislosti od požadovaných kritérií.

Syntax v tomto príklade je dosť komplikovaná, takže musíte urobiť vŕtačku nižšie v predmete tabuľky.

Musíte kliknúť pravým tlačidlom myši na tabuľku a pridať ju ako nový dotaz.

Kliknutím získate vlastný dotaz, ktorý obsahuje túto tabuľku.

Potom kliknite na stĺpec dátumu a vyberte možnosť Zoradiť vzostupne.

Skopírujte celý vzorec:

Vráťte sa k svojmu hlavnému dotazu a prilepte ho do riadka vzorcov:

Musíte odstrániť referenciu Table.Sort, pretože tabuľka, ktorá musí odovzdať parameter, by mala byť výsledkom z Table.SelectRows.

Po dokončení pridajte podmienky zoradenia späť. Po použití zmien je teraz vaša tabuľka správne zoradená podľa dátumu.

Použite funkciu Table.FirstN

Na tento krok potrebujete iba 2 záznamy namiesto 3.

Ak to chcete vyriešiť, vráťte sa na vlastný dotaz. Kliknite na Ponechať riadky a potom na Ponechať najvyššie riadky.

Zadajte 2 do prázdneho poľa pre počet riadkov.

Všimnite si, že funkcia Table.FirstN sa zobrazí v riadku vzorcov. Table.FirstN vráti prvý riadok(y) tabuľky v závislosti od hodnoty countOrCondition .

Vložte túto funkciu spolu s počtom riadkov, ktoré potrebujete v hlavnom dotaze. Na koniec uveďte tabuľku typov funkcií.

Každý objekt tabuľky v sade bude mať teraz buď 1 alebo 2 riadky.

Získajte denný rozdiel medzi 2 hodnotami dátumu

Ďalším krokom je získať počet dní medzi hodnotami dátumu a extrahovať dátum, ktorý bol v druhom zázname.

Začnite pridaním vlastného stĺpca. Pomenujte to Dni . Potom postupujte podľa tohto vzorca Power Query:

Syntax zabezpečuje, že v tabuľke sú 2 záznamy. Ak je len 1, denný rozdiel je 0, pretože nemá žiadny ďalší záznam. Ak sú 2, môžete získať rozdiel z hodnôt dátumu.

Ďalej pridajte číslo typu do vzorca takto.

Ako vidíte, od 14. septembra 2017 do 4. októbra 2018 získate 385-dňový rozdiel.

Určte hmotnostný krok

Ďalej musíte vypočítať, aká by mala byť hmotnosť na každý deň.

Musíte znova pridať vlastný stĺpec, pomenovať ho WeightStep a skopírovať tento vzorec Power Query:

Syntax skontroluje, či tabuľka má viac ako 1 riadok, odčíta váhu od aktuálneho záznamu a vydelí tento rozdiel počtom dní.

Opäť pridajte do vzorca číslo typu , aby ste videli hmotnosť každého dňa.

Vyplňte medzeru

Teraz, keď máte počet dní, môžete vytvoriť zoznam dátumov na vyplnenie medzery v množine údajov.

Najprv pridajte ďalší vlastný stĺpec a nazvite ho DateKey . Potom postupujte podľa tohto vzorca Power Query:

Vzorec kontroluje, či existuje rozdiel v dátumoch. Ak existuje, vytvorí zoznam dátumov medzi počiatočným dátumom, ktorý je z aktuálneho záznamu, a rozšíri ho o počet dní. Ak však nie sú žiadne rozdiely, vráti dátum.

Toto je výsledok:

Tabuľkovým subjektom, ktoré nemali žiadne rozdiely, sa vrátil dátum; tabuľky, ktoré to urobili, dostali zoznam dátumov.

Ak rozbalíte DateKey do nových riadkov, uvidíte záznamy všetkých dní.

Získajte odhad hmotnosti dátumov

Po vytvorení zoznamu dátumov musíte získať projekciu hmotnosti pre dátumy.

Najprv pridajte vlastný stĺpec a pomenujte ho WeightProjection . Potom zadajte tento vzorec Power Query:

Syntax získa hodnotu váhy, pridá ju k hodnote DateKey, odčíta ju od hodnoty dátumu a vynásobí ju krokom WeightStep.

Opäť pridajte číslo typu na koniec vzorca.

Teraz máte projekciu hmotnosti pre každý dátum.

Odstráňte pomocné stĺpce: Custom, Days a WeightStep pomocou nižšie uvedeného vzorca a zmeňte formát stĺpca DateKey na Date.

Záver

V tomto návode ste sa naučili, ako vyplniť medzery v množine údajov vytvorením hodnôt v každom zozname dátumov.

Tieto hodnoty dátumu vám pomôžu získať cenné informácie z vášho modelu. Ak vašim tabuľkám chýbajú informácie, môžete použiť túto techniku, aby boli komplexnejšie.

V tabuľke môžu byť dôležité údaje, ktoré vynechávate, preto vždy maximalizujte každý riadok a stĺpec.

Všetko najlepšie,

Melissa