Løse manglende valutakurser med Power Query i LuckyTemplates

I denne opplæringen lærer du hvordan du bruker Power Query for valutakonverteringer i LuckyTemplates. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Metoden som diskuteres i denne bloggen vil hjelpe deg med å løse problemer i valutakurstabellen din ved å bruke den siste tilgjengelige kursen.

Du vil forstå hvordan du skal løse ting når valutakurstabellen ikke har en valutakurs hver eneste dag.

Innholdsfortegnelse

• Identifisering av manglende data
• Skille forskjellige valutaer
• Fylling av manglende priser
• Slå sammen alle valutaer
• Opprette relasjoner i datamodellen
• Sammenligning av DAX- og Power Query-metoder
• Konklusjon

Identifisering av manglende data

Dette problemet kan oppstå hvis datotabellen ikke har data for helligdager eller helger.

Hvis du ikke har en kurs på noen dag og ikke tar hensyn til den, vil ikke beregninger som Totalt salg bli konvertert.

Hvis du tar en titt på denne valutakurstabellen, har den kurser i euro for januar 2016.

Men legg merke til at den 27. og 28. dagen mangler og ikke har takst. Så hvis du går til matrisen, kan du se at det ikke er noen priser for 27 og 28.

Valutakursen med manglende mål er ganske enkelt en sum av tabellen med faktiske valutakurser.

Totalen på 26.6983 i tabellen er meningsløs. Det er bare summen av alle valutakursene over den som du ikke trenger å legge sammen fordi de er det de er under gjeldende dag.

Nå kan dette fiksesfordi dette er mer et datamodelleringsproblem som involverer spørringer og aggregering. Dette kan også gjøres med DAX, men det er enklere og raskere med Power Query i LuckyTemplates.

Så klikk Transform Data , og åpne deretter Power Query-vinduet.

Dette er tabellen med manglende data. Du kan også se i denne valutakurstabellen at den inneholder euro, pund og dollar.

Og så er dette finalebordet som har verdier den 27. og 28. januar. Dette er tabellen eller utgangen du bør ha.

Importer dataene dine fra hvilken som helst kilde du henter dem fra, og endre datatypen.

Skille forskjellige valutaer

Deretter, hvis du har mer enn én valuta, er det viktig å skille og gruppere dem etter ticker.

Når du grupperer dem, sørg for å gruppere dem i alle rader, slik at hvis du klikker på for eksempel euro, vil den kun vise valutakurser i euro.

Du kan også se at det mangler dager her så vel som pundvalutaen.

Det er denne tingen som heter Invoke Custom Function i LuckyTemplates. Dette er en funksjon med FillMissingRates.

Hvis du klikker Euro, vil du se en fullstendig tabell som inneholder de manglende dataene som du så i den utvidede tabellen. Det er sortert fra de eldste til de nyeste prisene.

Finn ut hva de egendefinerte funksjonene gjør. Hvis du klikker på tannhjulikonet nær Invoke Custom Function, kan du se kolonnenavnet og kilden som trekker i Alle-kolonnen.

For å gjøre det, start med en grunnleggende tabell. Gjør transformasjonene, få resultatet du ønsker, og bruk det til å bygge funksjonen. Det er mer fornuftig enn å prøve å gjøre det i tabellen der du har alle de andre undertabellene.

For eksempel, hvis du ønsker å fokusere på euroen, filtrer den ned til tickeren.

For å finne ut hvilke dager som mangler, slå den sammen med datotabellen din.

Her er en grunnleggende nedfiltrert valutatabell og DimDate-tabell:

For Join Kind kan du bruke Full Outer for å hente inn alle radene fra begge bordene. Dette viser deg hva som mangler og prisene som samsvarer.

Når du slår det sammen, vil det gi deg et fullt bord som et underbord.

Deretter sorterer du radene og utvider DimDate til bare å inkludere Dato-kolonnen.

Nå kan du se at alt stemmer. Og når du sorterer radene etter eldste til nyeste, kan du også se de to manglende dagene med nullverdier.

Fylling av manglende priser

Det er viktig at de er i denne rekkefølgen fordi du skal fylle alle nullverdiene i alle kolonnene unntatt Dato.1.

Etter at du har fylt det ut, kan du se nullverdiene har endret seg til Euro og den siste tilgjengelige kursen.

Deretter fjerner du unødvendige kolonner som Dato-kolonnen fra tabellen.

Dato.1-kolonnen har allerede datoene, så Dato-kolonnen er ikke nødvendig. Sett deretter krysshastigheten til desimaltall.

Til slutt, omorganiser og gi nytt navn til kolonnene og sørg for at datatypene dine er angitt.

Du har nå en komplett valutakurstabell.

Dette fungerer imidlertid bare for dette bordet. Du må finne en måte å bruke den samme logikken på en tabell der valutaer er gruppert.

Slå sammen alle valutaer

Hvis du har forskjellige valutaer, er det vanskelig å opprettholde forskjellige kurstabeller. Så du må slå dem sammen for å lage en valutatabell.

For å starte, ta den eksisterende tabellen og lag en funksjon ved å klikke Tabell og velge Opprett funksjon . Skriv deretter inn et funksjonsnavn.

Etter det, gå til Advanced Editor . Fjern beregningene du ikke trenger. I dette tilfellet må du kvitte deg med kilden, filtrere og endre syntaks.

Navngi deretter parameteren Kilde og skriv deretter inn Let. Siden de filtrerte radene ble fjernet, endre verdien inne i syntaksen for sammenslåtte spørringer til Kilde. Etter det sletter du kilden på slutten av syntaksen, og klikker deretter på Ferdig.

Gå nå til Legg til kolonne, klikk Påkall egendefinert funksjon. Skriv inn et kolonnenavn som Alle data. For funksjonsspørringen velger du funksjonen som ble opprettet tidligere. Velg Alle som kilde, og klikk deretter OK.

Den neste tingen å gjøre er å fjerne alle kolonnene unntatt Alle data. Utvid deretter kolonnen og fjern merket for "Bruk originalt kolonnenavn som prefiks". Klikk deretter OK og endre kolonnetypene.

Sett dato-, ticker- og kryssrate-kolonnetypene til henholdsvis Dato, Tekst og Desimal. Når du er ferdig, klikker du på Lukk og bruk.

Du vil nå få en kombinert valutakurstabell uten manglende datoer og kurser.

Opprette relasjoner i datamodellen

I datamodellen din kan du se FactCurrencyRates- tabellen. Opprett nå en relasjon, Ticker til Ticker og Dato til dato, med den tabellen og tabellen DimCurrencyRates og DimDate.

I matrisen din, trekk inn Valutakursen Ingen mangler for å se at den nå har kursene for den 27. og 28. gjort i Power Query.

Kolonnen Siste rapporterte valutakurs viser også kursene for de manglende dagene. Den viser de samme prisene og verdiene. Men forskjellen mellom dem er at denne kolonnen er opprettet i DAX.

Dette er mål og syntaks for den kolonnen. Det er ikke så enkelt som å bruke bare funksjonen i Power Query.

Når du ser på tabellen, vil du se kolonnen Konvertert salg ingen mangler. Den viser salgene som er konvertert ved å bruke kolonnen Valutakurs ingen mangler. Den andre kolonnen med konverterte salg bruker kolonnen som ble laget i DAX.

Funksjonen som brukes for å få det konverterte salget er. Hvis du ser på målet for den kolonnen, kan du se SUMX gå over DimDate-tabellen.

Sammenligning av DAX- og Power Query-metoder

Du kan faktisk sammenligne begge de konverterte salgskolonnene som brukte DAX og Power Query i LuckyTemplates.

For å gjøre det må du kvitte deg med alle kolonnene bortsett fra de konverterte salgskolonnene. Deretter starter du ytelsesanalysatoren og klikker på Start opptak.

Deretter åpner du DAX Studio. Det er en applikasjon som viser deg hvordan og hvorfor ting fungerer i modellen din. Etter det kopierer du spørringen til matrisen din og limer den inn i studioet.

For sammenligningen, gjør en av dem til en kommentar først, slik at den ikke forstyrrer den andre kolonnen som blir testet.

Og last deretter inn Server Timings og Query Plan. Siden du sammenligner ytelser, må du først tømme bufferen før du kjører sammenligningen.

Etter å ha kjørt testen kan du se at kolonnen som brukte DAX fikk mange skanninger og har en total tid på 71 millisekunder.

Kjør nå den andre kolonnen som brukte Power-spørring. Men først gjør du den andre kolonnen i syntaksen til en kommentar.

Du kan se at kolonnen bare kjørte i 25 millisekunder og har bare 7 skanninger.

Du kan tydelig se hvilke som yter raskere og bedre mellom DAX- og Power Query-metodene.

Konklusjon

En fordel med å bruke Power Query-metoden i LuckyTemplates er at dataene allerede er lagret. Så tiltaket kan finne satsen og deretter få den frem ved hjelp av enkle funksjoner.

En annen ting med denne metoden er at den ikke går langsommere hvis regnestykket blir komplekst.

Dette fungerer imidlertid bare hvis dataene du ber om ikke må konverteres umiddelbart. Hvis den gjør det, må du bruke DAX.

Beste ønsker,