Lösa saknade valutakurser med Power Query i LuckyTemplates

I den här handledningen får du lära dig hur du använder Power Query för valutaomvandlingar i LuckyTemplates. Du kan se hela videon av denna handledning längst ner på den här bloggen.

Metoden som diskuteras i den här bloggen hjälper dig att lösa problem i din valutakurstabell med den senast tillgängliga kursen.

Du kommer att förstå hur du ska lösa saker när valutakurstabellen inte har någon valutakurs varje dag.

Innehållsförteckning

• Identifiera saknade data
• Separera olika valutor
• Fyller i saknade priser
• Slå samman alla valutor
• Skapa relationer i datamodellen
• Jämför DAX och Power Query-metoder
• Slutsats

Identifiera saknade data

Det här problemet kan uppstå om tabellen Datum inte har data för helgdagar eller helger.

Om du inte har en kurs på någon dag och inte tar hänsyn till det, kommer mätvärden som Total försäljning inte att konverteras.

Om du tittar på den här valutakurstabellen har den kurser i euro för januari 2016.

Men lägg märke till att den 27:e och 28:e dagen saknas och inte har någon kurs. Så om du går till matrisen kan du se att det inte finns några priser för 27 och 28.

Valutakursen med mått saknas är helt enkelt en summa av tabellen Faktavalutakurser.

Totalt 26,6983 i tabellen är meningslöst. Det är bara summan av alla valutakurser ovanför som du inte behöver lägga till eftersom de är vad de är under den aktuella dagen.

Nu kan detta fixaseftersom detta mer är en datamodelleringsfråga som involverar frågor och aggregering. Detta kan också göras med DAX, men det är enklare och snabbare med Power Query i LuckyTemplates.

Så klicka på Transformera data och öppna sedan Power Query-fönstret.

Detta är tabellen med saknade data. Du kan också se i denna valutakurstabell att den innehåller euro, pund och dollar.

Och då är det här finalbordet som har värden den 27:e och 28:e januari. Detta är tabellen eller utgången som du bör ha.

Importera din data från vilken källa du än hämtar den från och ändra bara datatypen.

Separera olika valutor

Därefter, om du har mer än en valuta, är det viktigt att separera och gruppera dem efter deras ticker.

När du grupperar dem, se till att gruppera dem i Alla rader så att om du klickar på Euro till exempel, kommer det bara att visa valutakurser i Euro.

Du kan också se att det saknas dagar här liksom pundvalutan.

Det finns en sak som heter Invoke Custom Function i LuckyTemplates. Detta är en funktion med FillMissingRates.

Om du klickar på Euro kommer du att se en komplett tabell som innehåller de saknade data som du såg i den utökade tabellen. Det är sorterat från de äldsta till de senaste priserna.

Ta reda på vad de anpassade funktionerna gör. Om du klickar på kugghjulsikonen nära Anropa anpassad funktion kan du se kolumnnamnet och källan som drar i kolumnen Alla.

För att göra det, börja med en grundläggande tabell. Gör omvandlingarna, få det resultat du vill ha och använd det sedan för att bygga funktionen. Det är mer meningsfullt än att försöka göra det i tabellen där du har alla andra undertabeller.

Om du till exempel vill fokusera på euron, filtrera ner den till tickern.

För att ta reda på vilka dagar som saknas, slå samman den med din datumtabell.

Här är en grundläggande nedfiltrerad valutatabell och DimDate-tabell:

För Join Kind kan du använda Full Outer för att få in alla rader från båda borden. Detta visar dig vad som saknas och de priser som matchar.

När du slår ihop det kommer det att ge dig ett fullt bord som en undertabell.

Efter det, sortera raderna och expandera sedan DimDate till att endast inkludera kolumnen Datum.

Nu kan du se att allt är matchat. Och när du sorterar raderna efter äldsta till nyaste kan du också se de två saknade dagarna med nollvärden.

Fyller i saknade priser

Det är viktigt att de är i den här ordningen eftersom du kommer att fylla alla nollvärden i alla kolumner utom Date.1.

När du har fyllt i den kan du se att nollvärdena har ändrats till Euro och den senaste tillgängliga kursen.

Ta sedan bort onödiga kolumner som datumkolumnen från tabellen.

Kolumnen Datum.1 har redan datumen, så kolumnen Datum behövs inte. Ställ sedan in korshastigheten till decimaltal.

Ändra ordning och namn på kolumnerna och se till att dina datatyper är inställda.

Du har nu en komplett valutakurstabell.

Detta fungerar dock bara för det här bordet. Du måste hitta ett sätt att tillämpa samma logik på en tabell där valutor är grupperade.

Slå samman alla valutor

Om du har olika valutor är det svårt att hålla olika kurstabeller. Så du måste slå samman dem för att skapa en valutatabell.

Börja med att ta din befintliga tabell och skapa en funktion genom att klicka på Tabell och välja Skapa funktion . Ange sedan ett funktionsnamn.

Efter det, gå till Advanced Editor . Ta bort beräkningarna som du inte behöver. I det här fallet, bli av med källan, filtrera och ändra syntax.

Namnge sedan din parameter Källa och skriv sedan in Let. Eftersom de filtrerade raderna togs bort ändrar du värdet i syntaxen för sammanslagna frågor till Källa. Efter det tar du bort källan i slutet av syntaxen och klickar sedan på Klar.

Gå nu till Lägg till kolumn, klicka på Anropa anpassad funktion. Ange ett kolumnnamn som All Data. För funktionsfrågan, välj den funktion som skapades tidigare. Välj Alla som källa och klicka sedan på OK.

Nästa sak att göra är att ta bort alla kolumner utom All Data. Expandera sedan kolumnen och avmarkera inställningen "Använd ursprungligt kolumnnamn som prefix". Klicka sedan på OK och ändra kolumntyperna.

Ställ in kolumntyperna Datum, Ticker och Crossrate till Datum, Text respektive Decimal. När du är klar klickar du på Stäng och tillämpa.

Du får nu en kombinerad valutakurstabell utan saknade datum och kurser.

Skapa relationer i datamodellen

I din datamodell kan du se tabellen FactCurrencyRates . Skapa nu en relation, Ticker to Ticker och Date to Date, med den tabellen och tabellen DimCurrencyRates och DimDate.

I din matris, dra in Valutakursen Ingen saknas för att se att den nu har kurserna för den 27:e och 28:e gjort i Power Query.

Kolumnen Senast rapporterade valutakurser visar också kurserna för de saknade dagarna. Den visar samma priser och värden. Men skillnaden mellan dem är att den här kolumnen skapas i DAX.

Detta är måttet och syntaxen för den kolumnen. Det är inte så enkelt som att bara använda funktionen i Power Query.

Om du tittar på tabellen ser du kolumnen Konverterad försäljning saknas. Den visar försäljningen som konverteras med hjälp av kolumnen Valutakurs saknas. Den andra kolumnen med konverterad försäljning använder kolumnen som gjordes i DAX.

Funktionen som används för att få den konverterade försäljningen är. Om du tittar på måttet för den kolumnen kan du se SUMX gå över DimDate-tabellen.

Jämför DAX och Power Query-metoder

Du kan faktiskt jämföra båda de konverterade försäljningskolumnerna som använde DAX och Power Query i LuckyTemplates.

För att göra det, ta bort alla kolumner utom de konverterade försäljningskolumnerna. Starta sedan prestandaanalysatorn och klicka på Starta inspelning.

Öppna sedan DAX Studio. Det är en applikation som visar hur och varför saker och ting fungerar i din modell. Efter det, kopiera frågan i din matris och klistra in den i studion.

För jämförelsen, förvandla en av dem till en kommentar först så att den inte stör den andra kolumnen som testas.

Och ladda sedan Server Timings and Query Plan. Eftersom du jämför prestationer, rensa cacheminnet först innan du kör jämförelsen.

Efter att ha kört testet kan du se att kolumnen som använde DAX fick massor av skanningar och har en total tid på 71 millisekunder.

Kör nu den andra kolumnen som använde Power-frågan. Men först, förvandla den andra kolumnen i syntaxen till en kommentar.

Du kan se att kolumnen bara körde i 25 millisekunder och har bara 7 skanningar.

Du kan tydligt se vilka som presterar snabbare och bättre mellan metoderna DAX och Power Query.

Slutsats

En fördel med att använda Power Query-metoden i LuckyTemplates är att data redan är lagrad. Så måttet kan hitta hastigheten och sedan ta fram den med enkla funktioner.

En annan sak med den här metoden är att den inte saktar ner om beräkningen blir komplex.

Detta fungerar dock bara om data du frågar efter inte behöver konverteras direkt. Om det gör det måste du använda DAX.

Med vänliga hälsningar,