Večný prázdninový kalendár – POTW #12 (riešenie Power Query)

V tomto blogovom príspevku preskúmame moje riešenie problému . Pre túto výzvu sme znovu navštívili prvú výzvu Problém týždňa od LuckyTemplates, kde sme vytvorili večný kalendár sviatkov zo zmesi konkrétnych a relatívnych dátumov. Tentokrát však budeme používať iba Power Query . Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Ak ste uviazli v samotnom rozdelení problému, môžete ho znova navštíviť a zistiť, či v niektorom z prístupov existujú prvky, ktoré vám môžu pomôcť s touto výzvou.

Pri čítaní príspevkov som videl zmes ľudí, ktorí využívajú používateľské rozhranie a píšu vlastné funkcie na vytvorenie večného kalendára. Bolo príjemné analyzovať každý príspevok a ak sa chcete inšpirovať, odporúčam vám urobiť to isté.

Keď som sám pracoval na tomto scenári, nakoniec som vytvoril viacero riešení. Ten, s ktorým som išiel, najlepšie ilustruje, ako môžete problém nielen rozložiť na menšie časti , ale aj ich zviditeľniť . Je to veľmi užitočné, ak narazíte na chybu alebo neočakávané výsledky.

Obsah

• Východiskový bod pre vytvorenie večného kalendára
• Získanie rokov z tabuľky dátumov
• Získanie čísla zo stĺpca Deň
• Vyvolanie vlastnej funkcie pre večný kalendár sviatkov
• Vytvorenie zoznamu dátumov
• Aplikácia podmienenej logiky
• Preskúmanie M kódu pre večný prázdninový kalendár
• Záver

Východiskový bod pre vytvorenie večného kalendára

Tento raw sviatočný stôl je naším východiskovým bodom. Na zostavenie dátumu potrebujeme rok, mesiac a deň pre konkrétne dátumy. Chýba už len rok.

Ale pre relatívne dátumy musíme urobiť trochu viac práce. Na začiatok musíme rozdeliť stĺpec dňa na číslo a deň v týždni. Pozrime sa na tieto kroky.

Získanie rokov z tabuľky dátumov

Aby bolo toto riešenie dynamické, získam roky z tabuľky dátumov . Na riadku vzorcov môžete vidieť, že som použil projekciu na získanie tabuľky s jedným stĺpcom z mojej tabuľky dátumov. Toto bude samozrejme obsahovať duplikáty. Aby som ich odstránil, pridal som Table.Distinct .

Keď kliknem na stranu na biele miesto vedľa ktorejkoľvek z týchto tabuliek, zobrazí sa vám aj zoznam s rôznymi rokmi prítomnými v mojom dotaze na tabuľku dátumov.

Dostali sme názov mesiaca , ale v skutočnosti budeme potrebovať číslo mesiaca . Teraz, keď zreťazíme rok s názvom mesiaca, môžeme použiť funkciu Date.From na vrátenie prvého mesiaca z daného mesiaca ako hodnoty dátumu.

Pridaním dátumu.mesiac nám zostane len číslo mesiaca pre daný konkrétny dátum. Pre január vráti 1. Pre február vráti 2 atď.

Získanie čísla zo stĺpca Deň

Aby sme získali číslo zo stĺpca dňa, rozhodli sme sa stĺpec nerozdeliť a namiesto toho extrahovať hodnotu. Ako môžete vidieť na riadku vzorcov, použili sme Text.BeforeDelimiter a použili sme tento priestor na identifikáciu pravej a ľavej časti tohto textového reťazca.

Ak nie je miesto, chceme vrátiť všetko, čo zostáva v stĺpci deň. Takže pre prvý záznam to vráti 1. Pre druhý záznam, ktorý obsahuje túto medzeru, vráti prvú časť textového reťazca, slovo Third .

Na získanie názvu dátumu použijeme podobný prístup, ale použijeme funkciu List.PositionOf . Toto vráti nulový posun hodnoty v zozname odovzdanej ako prvý argument a vráti -1, ak sa hodnota v tomto zozname neobjaví.

Pre prvý záznam prechádza hodnotou 1, ktorá sa nenachádza v našom zozname. Preto vracia hodnotu -1. Pre druhý záznam vráti hodnotu Monday .

Odovzdá to našej funkcii a vráti 1, pretože je to druhá položka v našom zozname. Ďalej nahradíme všetky hodnoty -1 hodnotou null .

V tomto bode sme vytvorili všetky stavebné bloky, ktoré potrebujeme na vyvolanie vlastnej funkcie.

Vyvolanie vlastnej funkcie pre večný kalendár sviatkov

V riadku vzorcov vidíme, že funkcia má štyri argumenty: rok , mesiac , číslicu a deň v týždni .

Ale ako určuje hodnotu dátumu? Znova použijeme List.PositionOf na získanie číselnej hodnoty pre číslovku. Máme zoznam obsahujúci prvé, druhé, tretie a štvrté. Keď odovzdáme číslovku List.PositionOf , vráti offset založený na nule.

Opäť platí, že ak hodnota nie je prítomná, vráti -1. Takže pre prvý záznam odovzdávame hodnotu 1. 1 sa v tomto zozname nenachádza, a preto vracia hodnotu -1. Pre druhý záznam prechádzame hodnotou Tretia, čo je tretia položka v našom zozname. Tým sa vráti hodnota 2.

Vytvorenie zoznamu dátumov

Teraz si vytvoríme zoznam dátumov. Určíme počiatočný dátum pre daný mesiac, určíme počet dní v danom mesiaci a pomocou List.Dates vytvoríme zoznam obsahujúci všetky dni v danom mesiaci.

Ďalej použijeme List.Select , aby sme zachovali iba tie dátumy, ktoré sú v konkrétny deň v týždni.

Keď klikneme na stranu vedľa niektorého z týchto zoznamov, vidíme, že obsahuje iba tieto štyri položky.

Aplikácia podmienenej logiky

Nakoniec použijeme podmienenú logiku na vytvorenie alebo načítanie dátumu. Ak je deň v týždni null , čo znamená, že máme konkrétny dátum, na vytvorenie hodnoty dátumu môžeme použiť vlastný #date .

Ak sa číslica rovná poslednej, potom môžeme použiť funkciu List.Reverse na obrátenie poradia v zozname a extrahovanie prvej hodnoty.

Ak chceme zmeniť toto vzostupné poradie na zostupné, vždy môžeme extrahovať posledný dátum z tohto rozsahu dátumov. Nezáleží na tom, či máme v zozname štyri alebo päť prvkov. Obrátením objednávky a vytiahnutím prvej položky máme vždy posledný dátum.

Pre všetky ostatné prípady používame List.Skip .

Hodnotu môžeme extrahovať na základe tejto pozície v zozname. Pre Deň vďakyvzdania je číslo štvrté a štvrté vracia 3. Tým sa preskočia prvé tri hodnoty v zozname a vždy sa vráti štvrtá položka.

Dajme si to všetko dokopy a prejdime k dotazu na riešenie. Pre krok zdroja voláme tabuľku dátumov sviatkov RAW. Ďalej pridáme stĺpec, ktorý vráti zoznam s dátumami.

Preskúmanie M kódu pre večný prázdninový kalendár

Skontrolujeme M kód pre stĺpec. Pre každý riadok v tabuľke sme vnorili tabuľku obsahujúcu odlišné roky z tabuľky dátumov. Potom sme transformovali hodnoty v tomto stĺpci roku volaním vlastnej funkcie.

Samozrejme, aby sme mohli vyvolať vlastnú funkciu, musíme najprv urobiť nejaké nastavenie. Musíme získať číslo mesiaca , číselnú hodnotu a samozrejme deň v týždni . Potom sme z tejto vnorenej tabuľky držali zoznam s dátumami.

Keď klikneme na stranu do bieleho priestoru, zobrazí sa náhľad tohto zoznamu nižšie.

Ďalším krokom je rozbaliť tento zoznam a pridať CelebratedOnDate .

Toto je logika, ktorú sme použili pri tomto výpočte: ak je dátum v sobotu, potom odpočítame deň, aby sme pristáli v piatok. Ak je nedeľa, potom pridáme deň, kedy pristaneme v pondelok. Ak nie je sobota alebo nedeľa, potom chceme nulu.

Nakoniec posledným krokom je zmena typov.

Záver

Len rýchle pripomenutieje prebiehajúca séria, ktorá sa koná každú 1. a 3. stredu na fóre LuckyTemplates. Problém zverejnený v 1. týždni vyžaduje riešenie založené na jazyku DAX, zatiaľ čo problém z 3. týždňa vyžaduje riešenie založené na Power Query.

Ak sa vám páčil obsah tejto UMÝVAČKY, prihláste sa na odber televízneho kanála LuckyTemplates a získajte ďalšie informácie. Neustále nám vychádza obrovské množstvo obsahu od mňa a od mnohých tvorcov obsahu, ktorí sa všetci venujú zlepšovaniu spôsobu, akým používate LuckyTemplates a Power Platform.

Melissa