Ontbrekende valutakoersen oplossen met Power Query in LuckyTemplates

In deze zelfstudie leert u hoe u Power Query kunt gebruiken voor valutaconversies in LuckyTemplates. Je kunt de volledige video van deze tutorial onderaan deze blog bekijken.

De methode die in deze blog wordt besproken, helpt u bij het oplossen van problemen in uw valutakoerstabel met behulp van de laatst beschikbare koers.

U zult begrijpen hoe u dingen moet oplossen als de valutakoersentabel niet elke dag een valutakoers heeft.

Inhoudsopgave

• Ontbrekende gegevens identificeren
• Verschillende valuta's scheiden
• Ontbrekende tarieven invullen
• Alle valuta's samenvoegen
• Relaties creëren in het datamodel
• DAX- en Power Query-methoden vergelijken
• Conclusie

Ontbrekende gegevens identificeren

Dit probleem kan optreden als de tabel Datum geen gegevens bevat voor feestdagen of weekenden.

Als u op geen enkele dag een tarief heeft en er geen rekening mee houdt, worden statistieken zoals Total Sales niet geconverteerd.

Als u deze tabel met valutakoersen bekijkt, ziet u de koersen in euro's voor januari 2016.

Maar merk op dat de 27e en 28e dag ontbreken en geen tarief hebben. Dus als je naar de matrix gaat, zie je dat er geen tarieven zijn voor 27 en 28.

De valutakoers met ontbrekende maat is gewoon een som van de tabel Feitelijke valutakoersen.

Het totaal van 26,6983 in de tabel is zinloos. Het is gewoon de som van alle valutakoersen erboven die u niet hoeft op te tellen omdat ze zijn wat ze zijn onder de huidige dag.

Dit kan nu worden opgelost in Power Query , omdat dit meer een probleem met gegevensmodellering is waarbij query's en aggregatie betrokken zijn. Dat kan ook met DAX, maar het gaat makkelijker en sneller met Power Query in LuckyTemplates.

Klik dus op Gegevens transformeren en open vervolgens het Power Query-venster.

Dit is de tabel met ontbrekende gegevens. U kunt in deze valutakoersentabel ook zien dat deze Euro's, Ponden en Dollars bevat.

En dan is dit de finaletafel met waarden op 27 en 28 januari. Dit is de tabel of uitvoer die u zou moeten hebben.

Importeer uw gegevens uit de bron waar u ze vandaan haalt en wijzig gewoon het gegevenstype.

Verschillende valuta's scheiden

Als u vervolgens meer dan één valuta heeft, is het belangrijk om ze te scheiden en te groeperen op basis van hun ticker.

Wanneer u ze groepeert, zorg er dan voor dat u ze in Alle rijen groepeert, zodat als u bijvoorbeeld op Euro's klikt, alleen de valutakoersen in Euro's worden weergegeven.

Je kunt ook zien dat er hier dagen ontbreken, evenals de pond-valuta.

Er is zoiets als de Invoke Custom Function in LuckyTemplates. Dit is een functie met FillMissingRates.

Als u op Euro klikt, ziet u een volledige tabel met daarin de ontbrekende gegevens die u in de uitgevouwen tabel zag. Het is gesorteerd van de oudste naar de nieuwste tarieven.

Zoek uit wat de aangepaste functies doen. Als u op het tandwielpictogram in de buurt van de Invoke Custom Function klikt, ziet u de kolomnaam en de bron die in de kolom All wordt getrokken.

Om dat te doen, begint u met een basistabel. Voer de transformaties uit, krijg het gewenste resultaat en gebruik dat vervolgens om de functie te bouwen. Het is logischer dan te proberen het te doen in de tabel waar je alle andere subtabellen hebt.

Als u zich bijvoorbeeld op de euro wilt concentreren, filtert u deze naar de ticker.

Om erachter te komen welke dagen ontbreken, voegt u deze samen met uw datumtabel.

Hier is een standaard gefilterde valutatabel en DimDate-tabel:

Voor de Join Kind kunt u Full Outer gebruiken om alle rijen van beide tabellen in te voeren. Zo zie je wat er ontbreekt en welke tarieven daarbij passen.

Wanneer u het samenvoegt, krijgt u een volledige tabel als een subtabel.

Sorteer daarna de rijen en vouw vervolgens de DimDate uit om alleen de kolom Datum op te nemen.

Nu kun je zien dat alles op elkaar is afgestemd. En wanneer u de rijen sorteert van oudste naar nieuwste, ziet u ook de twee ontbrekende dagen met null-waarden.

Ontbrekende tarieven invullen

Het is belangrijk dat ze in deze volgorde staan, omdat u alle nullen in alle kolommen gaat invullen behalve Datum.1.

Nadat u het heeft ingevuld, ziet u dat de nulwaarden zijn gewijzigd in Euro en het laatst beschikbare tarief.

Verwijder vervolgens de overbodige kolommen zoals de kolom Datum uit de tabel.

Datum.1 kolom bevat al de datums, dus de kolom Datum is niet nodig. Stel vervolgens de Crossrate in op Decimaal getal.

Herschik ten slotte de kolommen en hernoem ze en zorg ervoor dat uw gegevenstypen zijn ingesteld.

Je hebt nu een complete valutakoersentabel.

Dit werkt echter alleen voor deze tabel. Je moet een manier vinden om dezelfde logica toe te passen op een tabel waarin valuta's zijn gegroepeerd.

Alle valuta's samenvoegen

Als u verschillende valuta's heeft, is het moeilijk om verschillende koerstabellen bij te houden. U moet ze dus samenvoegen om één valutatabel te maken.

Om te beginnen, neemt u uw bestaande tabel en maakt u een functie door op Tabel te klikken en Functie maken te selecteren . Voer vervolgens een functienaam in.

Ga daarna naar Geavanceerde editor . Verwijder de berekeningen die u niet nodig heeft. Verwijder in dit geval de bron-, filter- en wijzigingssyntaxis.

Geef vervolgens uw parameter Source een naam en voer vervolgens Let in. Aangezien de gefilterde rijen zijn verwijderd, wijzigt u de waarde in de syntaxis van samengevoegde query's in bron. Verwijder daarna de bron aan het einde van de syntaxis en klik vervolgens op Gereed.

Ga nu naar Kolom toevoegen, klik op Aangepaste functie aanroepen. Voer een kolomnaam in zoals Alle gegevens. Selecteer voor de functiequery de functie die eerder is gemaakt. Kies Alles als Bron en klik vervolgens op OK.

Het volgende dat u moet doen, is alle kolommen verwijderen behalve Alle gegevens. Vouw vervolgens die kolom uit en schakel de instelling "Oorspronkelijke kolomnaam als voorvoegsel gebruiken" uit. Klik daarna op OK en wijzig de kolomtypen.

Stel het kolomtype Datum, Ticker en Crossrate in op respectievelijk Datum, Tekst en Decimaal. Als u klaar bent, klikt u op Sluiten en toepassen.

U krijgt nu een gecombineerde valutakoerstabel zonder ontbrekende datums en koersen.

Relaties creëren in het datamodel

In uw gegevensmodel ziet u de tabel FactCurrencyRates . Maak nu een relatie, Ticker to Ticker en Date to Date, met die tabel en de DimCurrencyRates- en DimDate-tabel.

Trek in uw matrix de valutakoers niet ontbrekend in om te zien dat de tarieven voor de 27e en 28e nu allemaal zijn gedaan in Power Query.

De kolom Laatst gerapporteerde valutakoers toont ook de koersen van de ontbrekende dagen. Het toont dezelfde tarieven en waarden. Maar het verschil tussen beide is dat deze kolom is gemaakt in DAX.

Dit is de maat en syntaxis van die kolom. Het is niet zo eenvoudig als het gebruik van alleen de functie in Power Query.

Als u naar de tabel kijkt, ziet u de kolom Geconverteerde verkopen geen ontbrekende. Het toont de verkopen die zijn geconverteerd met behulp van de kolom Valutakoers niet ontbrekend. De andere kolom met geconverteerde verkopen gebruikt de kolom die in DAX is gemaakt.

De functie die wordt gebruikt om de geconverteerde verkopen te verkrijgen, is . Als je naar de maat voor die kolom kijkt, zie je SUMX over de DimDate-tabel gaan.

DAX- en Power Query-methoden vergelijken

U kunt zowel de geconverteerde verkoopkolommen die DAX als Power Query in LuckyTemplates gebruikten, vergelijken.

Om dat te doen, verwijdert u alle kolommen behalve de geconverteerde verkoopkolommen. Start vervolgens de prestatie-analyzer en klik op Opname starten.

Open vervolgens DAX Studio. Het is een applicatie die u laat zien hoe en waarom dingen in uw model werken. Kopieer daarna de query van je matrix en plak deze in de studio.

Maak voor de vergelijking eerst een opmerking, zodat deze de andere kolom die wordt getest niet verstoort.

En laad vervolgens de Server Timings en Query Plan. Aangezien u prestaties vergelijkt, moet u eerst de cache wissen voordat u de vergelijking uitvoert.

Na het uitvoeren van de test kunt u zien dat de kolom die DAX gebruikte, veel scans heeft gekregen en een totale tijd heeft van 71 milliseconden.

Voer nu de andere kolom uit die Power-query gebruikte. Maar verander eerst de andere kolom in de syntaxis in een opmerking.

U kunt zien dat de kolom slechts 25 milliseconden heeft gedraaid en slechts 7 scans heeft.

U kunt duidelijk zien welke sneller en beter presteert tussen de DAX- en Power Query-methoden.

Conclusie

Een voordeel van het gebruik van de Power Query-methode in LuckyTemplates is dat de gegevens al zijn opgeslagen. De meting kan dus de snelheid vinden en deze vervolgens weergeven met behulp van eenvoudige functies.

Een ander ding over deze methode is dat het niet vertraagt ​​als de berekening complex wordt.

Dit werkt echter alleen als de gegevens waar u om vraagt ​​niet direct hoeven te worden geconverteerd. Als dit het geval is, moet u DAX gebruiken.

Al het beste,