Ratkaise puuttuvat valuuttakurssit Power Queryllä LuckyTemplatesissa

Tässä opetusohjelmassa opit käyttämään Power Queryä valuuttamuunnoksiin LuckyTemplatesissa. Voit katsoa tämän opetusohjelman koko videon tämän blogin alaosasta.

Tässä blogissa käsitelty menetelmä auttaa sinua ratkaisemaan ongelmia valuuttakurssitaulukossasi käyttämällä viimeistä saatavilla olevaa kurssia.

Ymmärrät, kuinka asiat selviävät, kun valuuttakurssitaulukossa ei ole valuuttakurssia joka päivä.

Sisällysluettelo

• Puuttuvien tietojen tunnistaminen
• Eri valuuttojen erottaminen
• Puuttuvien hintojen täyttäminen
• Kaikkien valuuttojen yhdistäminen
• Suhteiden luominen tietomallissa
• DAX- ja Power Query -menetelmien vertailu
• Johtopäätös

Puuttuvien tietojen tunnistaminen

Tämä ongelma voi ilmetä, jos Päivämäärä-taulukossa ei ole tietoja lomien tai viikonloppujen osalta.

Jos sinulla ei ole kurssia jonakin päivänä etkä ota sitä huomioon, kokonaismyyntiä koskevia tietoja ei muunneta.

Jos katsot tätä valuuttakurssitaulukkoa, siinä on tammikuun 2016 kurssit euroina.

Mutta huomaa, että 27. ja 28. päivä puuttuvat, eikä niillä ole korkoa. Joten jos siirryt matriisiin, näet, että 27:lle ja 28:lle ei ole hintoja.

Valuuttakurssi puuttuva mitta on yksinkertaisesti summa Fakta-valuuttakurssit -taulukosta.

Taulukon kokonaissumma 26,6983 on merkityksetön. Se on vain kaikkien sen yläpuolella olevien valuuttakurssien summa, jota sinun ei tarvitse laskea yhteen, koska ne ovat mitä ne ovat kuluvan päivän alapuolella.

Nyt tämä voidaan korjatakoska tämä on enemmän tietojen mallinnusongelma, johon liittyy kyselyitä ja kokoamista. Tämä voidaan tehdä myös DAX:lla, mutta se on helpompaa ja nopeampaa LuckyTemplatesin Power Queryn avulla.

Napsauta siis Muunna tiedot ja avaa sitten Power Query -ikkuna.

Tämä on taulukko, josta puuttuvat tiedot. Voit myös nähdä tästä valuuttakurssitaulukosta, että se sisältää euroja, puntia ja dollareita.

Ja sitten tämä on finaalipöytä, jolla on arvot 27. ja 28. tammikuuta. Tämä on taulukko tai tulos, joka sinulla pitäisi olla.

Tuo tietosi mistä tahansa lähteestä ja muuta vain tietotyyppiä.

Eri valuuttojen erottaminen

Seuraavaksi, jos sinulla on useampi kuin yksi valuutta, on tärkeää erottaa ja ryhmitellä ne niiden merkkien mukaan.

Kun ryhmittelet ne, muista ryhmitellä ne kaikkiin riveihin, jotta jos napsautat esimerkiksi Eurot, se näyttää valuuttakurssit vain euroina.

Voit myös nähdä, että täältä puuttuu päiviä sekä punnan valuutta.

LuckyTemplatesissa on tämä asia nimeltä Invoke Custom Function . Tämä on toiminto, jossa on FillMissingRates.

Jos napsautat euroa, näet täydellisen taulukon, joka sisältää puuttuvat tiedot, jotka näit laajennetussa taulukossa. Se on lajiteltu vanhimmasta uusimpaan.

Selvitä, mitä mukautetut toiminnot tekevät. Jos napsautat Invoke Custom Function -toiminnon lähellä olevaa rataskuvaketta, näet sarakkeen nimen ja lähteen, joka hakee Kaikki-saraketta.

Voit tehdä tämän aloittamalla perustaulukon. Tee muunnokset, saavuta haluamasi tulos ja käytä sitä sitten funktion rakentamiseen. Se on järkevämpää kuin yrittää tehdä se taulukossa, jossa sinulla on kaikki muut alitaulukot.

Jos esimerkiksi haluat keskittyä euroon, suodata se tickeriin.

Jos haluat selvittää, mitkä päivät puuttuvat, yhdistä se päivämäärätaulukkoon.

Tässä on perussuodatettu valuuttataulukko ja DimDate-taulukko:

Join Kindissä voit käyttää Full Outer -toimintoa tuodaksesi kaikki rivit molemmista taulukoista. Tämä näyttää, mitä puuttuu ja vastaavat hinnat.

Kun yhdistät sen, saat täyden taulukon alitaulukoksi.

Lajittele sen jälkeen rivit ja laajenna DimDate niin, että se sisältää vain Päivämäärä-sarakkeen.

Nyt voit nähdä, että kaikki on kohdillaan. Ja kun lajittelet rivit vanhimmasta uusimpaan, näet myös kaksi puuttuvaa päivää nolla-arvoilla.

Puuttuvien hintojen täyttäminen

On tärkeää, että ne ovat tässä järjestyksessä, koska täytät kaikki tyhjät kohdat kaikissa sarakkeissa paitsi Date.1.

Kun olet täyttänyt sen, näet, että nolla-arvot ovat muuttuneet euroiksi ja viimeiseksi saatavilla olevaksi kurssiksi.

Poista seuraavaksi taulukosta tarpeettomat sarakkeet, kuten Päivämäärä-sarake.

Päivämäärä.1-sarakkeessa on jo päivämäärät, joten Päivämäärä-saraketta ei tarvita. Aseta sitten Crossrate-arvoksi desimaaliluku.

Järjestä ja nimeä lopuksi sarakkeet uudelleen ja varmista, että tietotyypit on määritetty.

Sinulla on nyt täydellinen valuuttakurssitaulukko.

Tämä toimii kuitenkin vain tässä taulukossa. Sinun on löydettävä tapa soveltaa samaa logiikkaa taulukkoon, jossa valuutat on ryhmitelty.

Kaikkien valuuttojen yhdistäminen

Jos sinulla on eri valuuttoja, on vaikea ylläpitää erilaisia ​​kurssitaulukoita. Joten sinun on yhdistettävä ne yhdeksi valuuttataulukoksi.

Aloita ottamalla olemassa oleva taulukko ja luomalla funktio napsauttamalla Taulukko ja valitsemalla Luo funktio . Syötä seuraavaksi funktion nimi.

Siirry sen jälkeen Lisäeditoriin . Poista laskelmat, joita et tarvitse. Tässä tapauksessa poista lähde, suodata ja muuta syntaksia.

Nimeä seuraavaksi parametri Lähde ja syötä sitten Let. Koska Suodatetut rivit poistettiin, muuta Yhdistetyt kyselyt -syntaksin arvoksi Lähde. Poista sen jälkeen lähde syntaksin lopusta ja napsauta sitten Valmis.

Siirry nyt kohtaan Lisää sarake ja napsauta Kutsu mukautettua toimintoa. Syötä sarakkeen nimi, kuten Kaikki tiedot. Valitse funktiokyselyä varten aiemmin luotu funktio. Valitse lähteeksi Kaikki ja napsauta sitten OK.

Seuraava asia on poistaa kaikki sarakkeet paitsi Kaikki tiedot. Laajenna sitten kyseinen sarake ja poista valinta "Käytä alkuperäistä sarakkeen nimeä etuliitteenä" -asetus. Napsauta sen jälkeen OK ja muuta saraketyyppejä.

Aseta Date-, Ticker- ja Crossrate-sarakkeiden tyypiksi päivämäärä, teksti ja desimaali. Kun olet valmis, napsauta Sulje ja käytä.

Saat nyt yhdistetyn valuuttakurssitaulukon ilman puuttuvia päivämääriä ja kursseja.

Suhteiden luominen tietomallissa

Tietomallissasi näet FactCurrencyRates- taulukon. Luo nyt suhde, Ticker to Ticker ja Date to Date, tämän taulukon ja DimCurrencyRates- ja DimDate-taulukoiden kanssa.

Vedä matriisiin Valuuttakurssi ei puuttuu nähdäksesi, että siinä on nyt 27. ja 28. kurssit tehty Power Queryssä.

Viimeisin raportoitu valuuttakurssi -sarake näyttää myös puuttuvien päivien kurssit. Se näyttää samat hinnat ja arvot. Mutta ero niiden välillä on, että tämä sarake luodaan DAXissa.

Tämä on kyseisen sarakkeen mitta ja syntaksi. Se ei ole niin yksinkertaista kuin vain toiminnon käyttäminen Power Queryssa.

Kun katsot taulukkoa, näet Converted Sales No Missing -sarakkeen. Se näyttää myynnit, jotka on muunnettu käyttämällä Valuuttakurssi ei puuttuvia -saraketta. Toinen sarake, jossa on muunnettu myynti, käyttää saraketta, joka on tehty DAX:ssa.

Muunnetun myynnin saamiseen käytetty funktio on. Jos katsot kyseisen sarakkeen mittaa, näet, että SUMX menee DimDate-taulukon yli.

DAX- ja Power Query -menetelmien vertailu

Voit itse asiassa verrata molempia muunnettuja myyntisarakkeita, joissa käytettiin DAXia ja Power Queryä LuckyTemplatesissa.

Voit tehdä tämän poistamalla kaikki sarakkeet paitsi muunnetut myyntisarakkeet. Käynnistä sitten suorituskyvyn analysaattori ja napsauta Aloita tallennus.

Avaa seuraavaksi DAX Studio. Se on sovellus, joka näyttää kuinka ja miksi asiat toimivat mallissasi. Kopioi sen jälkeen matriisi kysely ja liitä se studioon.

Muuta vertailua varten yksi niistä ensin kommentiksi, jotta se ei häiritse toista testattavaa saraketta.

Lataa sitten Palvelimen ajoitukset ja kyselysuunnitelma. Koska vertailet suorituskykyä, tyhjennä välimuisti ennen vertailun suorittamista.

Testin suorittamisen jälkeen voit nähdä, että DAXia käyttävä sarake sai paljon skannauksia ja sen kokonaisaika on 71 millisekuntia.

Suorita nyt toinen sarake, joka käytti Power-kyselyä. Mutta ensin muuta syntaksin toinen sarake kommentiksi.

Voit nähdä, että sarake oli käynnissä vain 25 millisekuntia ja siinä on vain 7 skannausta.

Näet selvästi, kumpi toimii nopeammin ja paremmin DAX- ja Power Query -menetelmien välillä.

Johtopäätös

Power Query -menetelmän käytön etu LuckyTemplatesissa on, että tiedot on jo tallennettu. Joten mitta voi löytää koron ja tuoda sen sitten esiin yksinkertaisten funktioiden avulla.

Toinen asia tässä menetelmässä on, että se ei hidastu, jos laskenta muuttuu monimutkaiseksi.

Tämä toimii kuitenkin vain, jos pyytämääsi dataa ei tarvitse muuntaa lennossa. Jos näin on, sinun on käytettävä DAXia.

Kaikki parhaat,