Riešenie chýbajúcich kurzov mien pomocou Power Query v LuckyTemplates

V tomto návode sa naučíte používať Power Query na prevody mien v LuckyTemplates. Celé video tohto návodu si môžete pozrieť v spodnej časti tohto blogu.

Metóda, o ktorej sa hovorí v tomto blogu, vám pomôže vyriešiť problémy v tabuľke kurzov mien pomocou posledného dostupného kurzu.

Pochopíte, ako to vyriešiť, keď tabuľka kurzov meny nemá kurz meny každý jeden deň.

Obsah

• Identifikácia chýbajúcich údajov
• Oddelenie rôznych mien
• Vyplnenie chýbajúcich sadzieb
• Zlúčenie všetkých mien
• Vytváranie vzťahov v dátovom modeli
• Porovnanie metód DAX a Power Query
• Záver

Identifikácia chýbajúcich údajov

Tento problém sa môže vyskytnúť, ak tabuľka Dátum neobsahuje údaje pre sviatky alebo víkendy.

Ak v ktorýkoľvek deň nemáte sadzbu a nezohľadňujete ju, metriky ako celkový predaj sa neprevedú.

Ak sa pozriete na túto tabuľku menových kurzov, sú v nej uvedené kurzy v eurách za január 2016.

Všimnite si však, že chýbajú 27. a 28. deň a nemajú uvedenú sadzbu. Ak teda prejdete na maticu, môžete vidieť, že pre 27 a 28 neexistujú žiadne sadzby.

Kurz meny s chýbajúcou mierou je jednoducho súčtom tabuľky Skutočné kurzy mien.

Celková suma 26,6983 v tabuľke je nezmyselná. Je to len súčet všetkých menových kurzov nad ním, ktoré nemusíte sčítať, pretože sú také, aké sú v aktuálny deň.

Teraz sa to dá opraviťpretože ide skôr o problém modelovania údajov, ktorý zahŕňa dotazy a agregáciu. Dá sa to urobiť aj pomocou jazyka DAX, ale s Power Query v LuckyTemplates je to jednoduchšie a rýchlejšie.

Kliknite teda na položku Transformovať údaje a potom otvorte okno Power Query.

Toto je tabuľka s chýbajúcimi údajmi. V tejto tabuľke kurzov mien môžete tiež vidieť, že obsahuje eurá, libry a doláre.

A potom toto je konečná tabuľka, ktorá má hodnoty 27. a 28. januára. Toto je tabuľka alebo výstup, ktorý by ste mali mať.

Importujte svoje údaje z akéhokoľvek zdroja, z ktorého ich získavate, a jednoducho zmeňte typ údajov.

Oddelenie rôznych mien

Ďalej, ak máte viac ako jednu menu, je dôležité ich oddeliť a zoskupiť podľa ich tickera.

Keď ich zoskupujete, uistite sa, že ste ich zoskupili do všetkých riadkov, takže ak napríklad kliknete na Euro, zobrazia sa len kurzy mien v eurách.

Môžete tiež vidieť, že tu chýbajú dni, ako aj mena libra.

V LuckyTemplates je táto vec nazývaná Invoke Custom Function . Toto je funkcia s FillMissingRates.

Ak kliknete na Euro, zobrazí sa úplná tabuľka, ktorá obsahuje chýbajúce údaje, ktoré ste videli v rozbalenej tabuľke. Sadzby sú zoradené od najstarších po najnovšie.

Zistite, čo robia vlastné funkcie. Ak kliknete na ikonu ozubeného kolieska v blízkosti Vyvolať vlastnú funkciu, v stĺpci Všetko uvidíte názov stĺpca a zdroj, ktorý sa sťahuje.

Ak to chcete urobiť, začnite so základnou tabuľkou. Vykonajte transformácie, získajte požadovaný výsledok a potom ho použite na vytvorenie funkcie. Dáva to väčší zmysel, ako sa to snažiť urobiť v tabuľke, kde máte všetky ostatné podtabuľky.

Ak sa napríklad chcete zamerať na euro, vyfiltrujte ho až na ticker.

Ak chcete zistiť, ktoré dni chýbajú, zlúčte ich s tabuľkou dátumov.

Tu je základná filtrovaná menová tabuľka a tabuľka DimDate:

V prípade typu Join môžete použiť Full Outer na prinesenie všetkých riadkov z oboch stolov. To vám ukáže, čo chýba, a zodpovedajúce sadzby.

Keď ju zlúčite, získate celú tabuľku ako podtabuľku.

Potom zoraďte riadky a potom rozbaľte DimDate tak, aby obsahoval iba stĺpec Dátum.

Teraz môžete vidieť, že všetko je zosúladené. A keď zoradíte riadky od najstarších po najnovšie, môžete vidieť aj dva chýbajúce dni s hodnotami null.

Vyplnenie chýbajúcich sadzieb

Je dôležité, aby boli v tomto poradí, pretože vyplníte všetky hodnoty null vo všetkých stĺpcoch okrem Date.1.

Po jeho vyplnení uvidíte, že nulové hodnoty sa zmenili na euro a posledný dostupný kurz.

Potom z tabuľky odstráňte nepotrebné stĺpce, ako je stĺpec Dátum.

Stĺpec Date.1 už obsahuje dátumy, takže stĺpec Date nie je potrebný. Potom nastavte Crossrate na Decimal Number.

Nakoniec zmeňte poradie a premenujte stĺpce a uistite sa, že máte nastavené typy údajov.

Teraz máte kompletnú tabuľku menových kurzov.

Toto však funguje len pre túto tabuľku. Musíte nájsť spôsob, ako použiť rovnakú logiku na tabuľku, v ktorej sú meny zoskupené.

Zlúčenie všetkých mien

Ak máte rôzne meny, je ťažké udržiavať rôzne cenníky. Takže ich musíte zlúčiť, aby ste vytvorili jednu menovú tabuľku.

Ak chcete začať, vezmite svoj existujúci stôl a vytvorte funkciu kliknutím na položku Tabuľka a výberom možnosti Vytvoriť funkciu . Ďalej zadajte názov funkcie.

Potom prejdite na Pokročilý editor . Odstráňte výpočty, ktoré nepotrebujete. V takom prípade sa zbavte zdroja, filtra a zmeňte syntax.

Ďalej pomenujte svoj parameter Source a potom zadajte Let. Keďže boli filtrované riadky odstránené, zmeňte hodnotu v syntaxi zlúčených dotazov na Zdroj. Potom odstráňte zdroj na konci syntaxe a kliknite na tlačidlo Hotovo.

Teraz prejdite na položku Pridať stĺpec a kliknite na položku Vyvolať vlastnú funkciu. Zadajte názov stĺpca, napríklad Všetky údaje. Pre dotaz funkcie vyberte funkciu, ktorá bola vytvorená skôr. Ako zdroj vyberte Všetko a potom kliknite na tlačidlo OK.

Ďalšia vec, ktorú musíte urobiť, je odstrániť všetky stĺpce okrem všetkých údajov. Potom rozbaľte tento stĺpec a zrušte začiarknutie políčka „Použiť pôvodný názov stĺpca ako predponu“. Potom kliknite na tlačidlo OK a zmeňte typy stĺpcov.

Nastavte typ stĺpcov Dátum, Ticker a Krížová rýchlosť na Dátum, Text a Desatinné. Po dokončení kliknite na tlačidlo Zavrieť a použiť.

Teraz získate tabuľku kombinovaných kurzov meny bez chýbajúcich dátumov a kurzov.

Vytváranie vzťahov v dátovom modeli

Vo svojom dátovom modeli môžete vidieť tabuľku FactCurrencyRates . Teraz vytvorte vzťah, Ticker to Ticker a Date to Date, s touto tabuľkou a tabuľkou DimCurrencyRates a DimDate.

Vo svojej matici vytiahnite kurz meny No Missing , aby ste videli, že teraz má kurzy pre 27. a 28. deň, všetko vykonané v Power Query.

Stĺpec Posledný nahlásený kurz meny zobrazuje aj kurzy chýbajúcich dní. Zobrazuje rovnaké sadzby a hodnoty. Rozdiel medzi nimi je však v tom, že tento stĺpec je vytvorený v jazyku DAX.

Toto je miera a syntax tohto stĺpca. Nie je to také jednoduché ako použitie funkcie v Power Query.

Pri pohľade na tabuľku uvidíte stĺpec Konvertovaný predaj bez chýbajúceho. Zobrazuje predaje, ktoré sú prevedené pomocou stĺpca Kurz meny, ktorý chýba. Druhý stĺpec s konvertovanými predajmi používa stĺpec, ktorý bol vytvorený v jazyku DAX.

Funkcia použitá pri získavaní prevedených predajov je. Ak sa pozriete na mieru pre tento stĺpec, môžete vidieť, že SUMX prechádza cez tabuľku DimDate.

Porovnanie metód DAX a Power Query

V LuckyTemplates môžete skutočne porovnať konvertované stĺpce predaja, ktoré používali DAX a Power Query.

Ak to chcete urobiť, zbavte sa všetkých stĺpcov okrem konvertovaných stĺpcov predaja. Potom spustite analyzátor výkonu a kliknite na tlačidlo Spustiť nahrávanie.

Potom otvorte DAX Studio. Je to aplikácia, ktorá vám ukáže, ako a prečo veci fungujú vo vašom modeli. Potom skopírujte dotaz vašej matrice a vložte ho do štúdia.

Pre porovnanie urobte najprv komentár z jedného z nich, aby nezasahoval do druhého testovaného stĺpca.

Potom načítajte plán časovania servera a plán dotazov. Keďže porovnávate výkony, pred spustením porovnávania najprv vymažte vyrovnávaciu pamäť.

Po vykonaní testu môžete vidieť, že stĺpec, ktorý používal DAX, dostal veľa skenov a má celkový čas 71 milisekúnd.

Teraz spustite druhý stĺpec, ktorý používal dotaz Power. Najprv však premeňte druhý stĺpec v syntaxi na komentár.

Môžete vidieť, že stĺpec bežal iba 25 milisekúnd a má iba 7 skenov.

Môžete jasne vidieť, ktorá z metód DAX a Power Query funguje rýchlejšie a lepšie.

Záver

Výhodou použitia metódy Power Query v LuckyTemplates je, že údaje sú už uložené. Takže miera môže nájsť sadzbu a potom ju zobraziť pomocou jednoduchých funkcií.

Ďalšou vecou tejto metódy je, že sa nespomalí, ak sa výpočet stane zložitým.

Toto však funguje iba vtedy, ak údaje, ktoré požadujete, nemusia byť konvertované za chodu. Ak áno, musíte použiť DAX.

Všetko najlepšie,