He is the leader of the New Zealand Business Intelligence users group. He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. His articles on different aspects of technologies, especially on MS BI, can be found on his blog. Reza is also co-founder and co-organizer of Difinity conference in New Zealand. Reza is an active blogger and co-founder of RADACAD. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. He has a BSc in Computer engineering he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant.
#Currency converter by date free#
#Currency converter by date code#
You also need to have a table of all the currencies that you want to change (FROM and TO), like below:Īnd then call the function above with the code below Table.AddColumn(Source, "Rate", each GetExchangeRate(,)) Go to an organizational workspace, and create a dataflow Īdd New Entities, under Define Entities, and then choose Blank QueryĬopy and paste the script from above in there Īnd remember to set the paste your API Key in the script. Here is how you can do it: Create The Function in Dataflow Using that approach, you just have one API call per combination of rates from and rates to parameters. Then use the exchange table in merge with other tables. The method that I suggest, and it works for billions of records, is to use dataflows to create an exchange table. If you call this function in a table with 10,000 rows, it means it will be 10,000 API calls, which you need to get a paid version of the API for that. Why? This is a free API, and has the limitation of 100 calls. You can create the function above in Power Query in Power BI Desktop, and then use it in other tables, However, I strongly recommend to refrain from doing so. Remember that you have to paste your APIKey instead of the paste-your-api-key-here in the script above. #"Converted to Table" = Record.ToTable(Source), Source = Json.Document(Web.Contents("",)), Source = (#"From Currency" as text,#"To Currency" as text) as number=> The function mainly just expand to the result value. So I ended up using this URL and building a Power Query function that does the conversion using the the API. Then it works with a URL such as below to give you the Exchange rate:
![currency converter by date currency converter by date](http://www.mytucsonblog.com/wp-content/uploads/2010/11/exchange-rates-canadian-vs-us-dollar-2008_thumb.jpg)
You need to get a free API Key first Īfter entering your email address, the API key will be emailed to you. Yes, absolutely free, and the good news is that it works perfectly fine. Currenc圜 is one of the websites that offers you a FREE currency converter API. There are many websites and services for currency exchange rate tables. This post is about a function that gives you the latest currency rate using Power BI and Power Query.
![currency converter by date currency converter by date](https://www.asiaone.com/sites/default/files/styles/a1_og_image/public/original_images/Nov2019/131119_sleepinginbus_fb.jpg)
You either need to keep an up-to-date table of all new currency rates and use that in your Power BI solution, or use a function that gets the rate from one of the live services. If you ever need to convert amounts from one currency to another, you face the challenge that currency rates are not constant.