Rezolvarea ratelor valutare lipsă cu Power Query în LuckyTemplates

În acest tutorial, veți învăța cum să utilizați Power Query pentru conversiile valutare în LuckyTemplates. Puteți viziona videoclipul complet al acestui tutorial în partea de jos a acestui blog.

Metoda discutată în acest blog vă va ajuta să rezolvați problemele din tabelul de curs valutar folosind ultimul curs disponibil.

Veți înțelege cum să rezolvați lucrurile atunci când tabelul de cursuri valutare nu are un curs valutar în fiecare zi.

Cuprins

• Identificarea datelor lipsă
• Separarea diferitelor valute
• Completarea ratelor lipsă
• Fuzionarea tuturor monedelor
• Crearea de relații în modelul de date
• Compararea metodelor DAX și Power Query
• Concluzie

Identificarea datelor lipsă

Această problemă poate apărea dacă tabelul Date nu are date pentru sărbători sau weekenduri.

Dacă nu aveți o rată în nicio zi și nu țineți cont de aceasta, valori precum Vânzările totale nu vor fi convertite.

Dacă aruncați o privire la acest tabel de cursuri valutare, acesta are cursuri în euro pentru ianuarie 2016.

Dar observați că zilele 27 și 28 lipsesc și nu au tarif. Deci, dacă mergi la matrice, poți vedea că nu există tarife pentru 27 și 28.

Rata valutară cu măsura lipsă este pur și simplu o sumă a tabelului Rate valutare reale.

Totalul de 26,6983 din tabel este lipsit de sens. Este doar suma tuturor cursurilor valutare de deasupra acesteia, pe care nu trebuie să le adunați, deoarece acestea sunt ceea ce sunt în ziua curentă.

Acum, acest lucru poate fi remediat în Power Query , deoarece aceasta este mai mult o problemă de modelare a datelor care implică interogări și agregare. Acest lucru se poate face și cu DAX, dar este mai ușor și mai rapid cu Power Query în LuckyTemplates.

Așadar, faceți clic pe Transformare date , apoi deschideți fereastra Power Query.

Acesta este tabelul cu datele lipsă. De asemenea, puteți vedea în acest tabel de cursuri valutare că conține euro, lire sterline și dolari.

Și apoi acesta este masa finală care are valori pe 27 și 28 ianuarie. Acesta este tabelul sau ieșirea pe care ar trebui să-l aveți.

Importați-vă datele din orice sursă de la care le obțineți și doar schimbați tipul de date.

Separarea diferitelor valute

În continuare, dacă aveți mai multe monede, este important să le separați și să le grupați după simbolul lor.

Când le grupați, asigurați-vă că le grupați în Toate rândurile, astfel încât, dacă faceți clic pe euro, de exemplu, va afișa doar cursurile valutare în euro.

De asemenea, puteți vedea că aici lipsesc zile, precum și moneda lire sterline.

Există acest lucru numit Invoke Custom Function în LuckyTemplates. Aceasta este o funcție cu FillMissingRates.

Dacă faceți clic pe Euro, veți vedea un tabel complet care conține datele lipsă pe care le-ați văzut în tabelul extins. Este sortat de la cele mai vechi la cele mai noi tarife.

Aflați ce fac funcțiile personalizate. Dacă faceți clic pe pictograma roată din apropierea funcției personalizate Invoke, puteți vedea numele coloanei și sursa care trage în coloana Toate.

Pentru a face asta, începeți cu un tabel de bază. Faceți transformările, obțineți rezultatul dorit și apoi utilizați-l pentru a construi funcția. Are mai mult sens decât să încerci să o faci în tabelul în care ai toate celelalte sub-tabele.

De exemplu, dacă doriți să vă concentrați pe euro, filtrați-l până la ticker.

Pentru a afla ce zile lipsesc, îmbinați-l cu tabelul de date.

Iată un tabel valutar de bază filtrat în jos și un tabel DimDate:

Pentru Join Kind, puteți folosi Full Outer pentru a aduce toate rândurile de la ambele tabele. Aceasta vă arată ce lipsește și tarifele care se potrivesc.

Când îl îmbinați, vă va oferi un tabel complet ca subtabel.

După aceea, sortați rândurile și apoi extindeți DimDate pentru a include numai coloana Date.

Acum, puteți vedea că totul este potrivit. Și când sortați rândurile de la cel mai vechi la cel mai nou, puteți vedea și cele două zile lipsă cu valori nule.

Completarea ratelor lipsă

Este important ca acestea să fie în această ordine, deoarece veți completa toate valorile nule din toate coloanele, cu excepția Datei.1.

După ce îl completați, puteți vedea că valorile nule s-au schimbat în euro și ultima rată disponibilă.

Apoi, eliminați din tabel coloanele inutile, cum ar fi coloana Data.

Coloana Date.1 are deja datele, deci nu este necesară coloana Date. Apoi, setați Crossrate la Număr zecimal.

În cele din urmă, reordonați și redenumiți coloanele și asigurați-vă că tipurile de date sunt setate.

Acum aveți un tabel complet al ratelor valutare.

Cu toate acestea, acest lucru funcționează numai pentru acest tabel. Trebuie să găsiți o modalitate de a aplica aceeași logică unui tabel în care sunt grupate monedele.

Fuzionarea tuturor monedelor

Dacă aveți valute diferite, este dificil să mențineți tabele de rate diferite. Deci, trebuie să le îmbinați pentru a crea un singur tabel valutar.

Pentru a începe, luați tabelul existent și creați o funcție făcând clic pe Tabel și selectând Creare funcție . Apoi, introduceți un nume de funcție.

După aceea, accesați Editorul avansat . Eliminați calculele de care nu veți avea nevoie. În acest caz, scăpați de sursă, filtrați și modificați sintaxa.

Apoi, denumiți parametrul Sursă și apoi introduceți Let. Deoarece rândurile filtrate au fost eliminate, modificați valoarea din sintaxa Merged Queries la Sursă. După aceea, ștergeți Sursa de la sfârșitul sintaxei și apoi faceți clic pe Efectuat.

Acum, accesați Adăugare coloană, faceți clic pe Invocare funcție personalizată. Introduceți un nume de coloană, cum ar fi Toate datele. Pentru interogarea funcției, selectați funcția care a fost creată mai devreme. Alegeți Toate ca sursă, apoi faceți clic pe OK.

Următorul lucru de făcut este să eliminați toate coloanele, cu excepția tuturor datelor. Apoi, extindeți acea coloană și debifați setarea „Utilizați numele coloanei originale ca prefix”. După aceea, faceți clic pe OK și modificați tipurile de coloane.

Setați tipul coloanelor Data, Ticker și Crossrate la Data, Text și, respectiv, Decimal. După ce ați terminat, faceți clic pe Închideți și aplicați.

Acum veți primi un tabel combinat de cursuri valutare fără date și cursuri lipsă.

Crearea de relații în modelul de date

În modelul dvs. de date, puteți vedea tabelul FactCurrencyRates . Acum, creați o relație, Ticker to Ticker și Date to Date, cu acel tabel și tabelul DimCurrencyRates și DimDate.

În matricea dvs., trageți Currency Rate No Missing pentru a vedea că acum are ratele pentru al 27-lea și al 28-lea, toate făcute în Power Query.

Coloana Ultimul curs valutar raportat arată, de asemenea, cursurile zilelor lipsă. Afișează aceleași rate și valori. Dar diferența dintre ele este că această coloană este creată în DAX.

Aceasta este măsura și sintaxa acelei coloane. Nu este la fel de simplu ca utilizarea doar a funcției în Power Query.

Privind tabelul, veți vedea coloana Vânzări convertite fără lipsă. Afișează vânzările care sunt convertite utilizând coloana Currency Rate No Missing. Cealaltă coloană cu vânzări convertite utilizează coloana care a fost realizată în DAX.

Funcția utilizată pentru obținerea vânzărilor convertite este . Dacă te uiți la măsura pentru acea coloană, poți vedea SUMX trecând peste tabelul DimDate.

Compararea metodelor DAX și Power Query

De fapt, puteți compara ambele coloane de vânzări convertite care au folosit DAX și Power Query în LuckyTemplates.

Pentru a face asta, scăpați de toate coloanele, cu excepția coloanelor de vânzări convertite. Apoi, porniți analizorul de performanță și faceți clic pe Start Recording.

Apoi, deschideți DAX Studio. Este o aplicație care vă arată cum și de ce funcționează lucrurile în modelul dvs. După aceea, copiați interogarea matricei și lipiți-o în studio.

Pentru comparație, transformați mai întâi unul dintre ele într-un comentariu, astfel încât să nu interfereze cu cealaltă coloană care este testată.

Și apoi, încărcați Orarul serverului și Planul de interogare. Deoarece comparați performanțe, ștergeți mai întâi memoria cache înainte de a rula comparația.

După rularea testului, puteți vedea că coloana care a folosit DAX a primit o mulțime de scanări și are un timp total de 71 de milisecunde.

Acum, rulați cealaltă coloană care a folosit interogare de putere. Dar mai întâi, transformați cealaltă coloană din sintaxă într-un comentariu.

Puteți vedea că coloana a rulat doar 25 de milisecunde și are doar 7 scanări.

Puteți vedea clar care funcționează mai rapid și mai bine între metodele DAX și Power Query.

Concluzie

Un avantaj al utilizării metodei Power Query în LuckyTemplates este că datele sunt deja stocate. Deci, măsura poate găsi rata și apoi o poate scoate folosind funcții simple.

Un alt lucru despre această metodă este că nu încetinește dacă calculul devine complex.

Cu toate acestea, acest lucru funcționează numai dacă datele pe care le solicitați nu trebuie să fie convertite din mers. Dacă o face, trebuie să utilizați DAX.

Toate cele bune,