Exchange Rates - best website to use for Historical data

[PiyushH1]

Hi Team ,

How are you ?

can anyone help me to suggest best historical rates website which i can use for BI report which shows all currency rates from 2015 to now  & it should be updated automatically every 1st day of the month ?

Regards,

Piyush 

[Jimmy801 · Accepted solution]

Hello @PiyushH1 

you will get a table for every day, depending on your URL that your are sending to the site

and here the code

let
    Quelle = Web.Page(Web.Contents("https://www.xe.com/de/currencytables/?from=EUR&date=2019-12-17")),
    Data = Quelle{0}[Data],
    #"Geänderter Typ" = Table.TransformColumnTypes(Data,{{"Währungscode ▲▼", type text}, {"Währungsname ▲▼", type text}, {"Einheiten pro EUR", type number}, {"EUR pro Einheit", type number}})
in
    #"Geänderter Typ"

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[v-juanli-msft · Accepted solution]

Hi @PiyushH1 

As tested, this thread would be helpful.

https://community.powerbi.com/t5/Service/Historical-exchange-rate-API/td-p/742021

The code in my Advanced editor is:

Query1

let
    Source = {Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-Duration.Days(Date.From(DateTime.LocalNow())-#date(2015,1,1))))..Number.From(DateTime.Date(DateTime.LocalNow()))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "ExchangeRates", each Fn_GetHistoricExchangeRates(Date.ToText([Date],"yyyy-MM-dd"))),
    #"Expanded ExchangeRates" = Table.ExpandTableColumn(#"Invoked Custom Function", "ExchangeRates", {"Currency", "Rate"}, {"Currency", "Rate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ExchangeRates",{{"Rate", type number}, {"Currency", type text}})
in
    #"Changed Type1"

Fn_GetHistoricExchangeRates

let
    Source = (Date as text) => let
        Source = Json.Document(Web.Contents("https://api.exchangeratesapi.io/" & Date & "?base=USD")),
        rates = Source[rates],
        #"Converted to Table" = Record.ToTable(rates),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Currency"}, {"Value", "Rate"}})
    in
        #"Renamed Columns"
in
    Source

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft , I'm using your pbi report and faced an error issue while loading the data. Actually, I'm facing the same kind of issue related to the historical exchange rate. 
Can you please let me know why it is asking for API key? If we need to provide it, can you mention it? Thanks 

Regards,

Ahsan

[v-juanli-msft · Accepted solution]

Hi @PiyushH1 

As tested, this thread would be helpful.

https://community.powerbi.com/t5/Service/Historical-exchange-rate-API/td-p/742021

The code in my Advanced editor is:

Query1

let
    Source = {Number.From(Date.AddDays(DateTime.Date(DateTime.LocalNow()),-Duration.Days(Date.From(DateTime.LocalNow())-#date(2015,1,1))))..Number.From(DateTime.Date(DateTime.LocalNow()))},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Renamed Columns", "ExchangeRates", each Fn_GetHistoricExchangeRates(Date.ToText([Date],"yyyy-MM-dd"))),
    #"Expanded ExchangeRates" = Table.ExpandTableColumn(#"Invoked Custom Function", "ExchangeRates", {"Currency", "Rate"}, {"Currency", "Rate"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded ExchangeRates",{{"Rate", type number}, {"Currency", type text}})
in
    #"Changed Type1"

Fn_GetHistoricExchangeRates

let
    Source = (Date as text) => let
        Source = Json.Document(Web.Contents("https://api.exchangeratesapi.io/" & Date & "?base=USD")),
        rates = Source[rates],
        #"Converted to Table" = Record.ToTable(rates),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Currency"}, {"Value", "Rate"}})
    in
        #"Renamed Columns"
in
    Source

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PiyushH1]

Hi Maggie,

I have a question on exchnage rate. 

i have three base currency , GBP/EUR/USD.

what i am trying to achive is that when in slicer if i select any of above currency (GBP/EUR/USD), than in main table rest currency rates should calculate automatically.

i.e, If i select EUR currency in slicer than dynamicaly CNY,INR,LIRA,PLN,USD these currency should chnage automatically in main table.

Is there any way ,we can do ?

Regards,

Piyush 

[v-juanli-msft · Accepted solution]

Hi @PiyushH1 

Close&&apply, select Edit queries->edit parameter

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft , I'm using your pbi report and faced an error issue while loading the data. Actually, I'm facing the same kind of issue related to the historical exchange rate. 
Can you please let me know why it is asking for API key? If we need to provide it, can you mention it? Thanks 

[PiyushH1]

Hi Maggie,

Thanks it's work now for me.

but , is there any ways we can use parameters in slicer ? under visulatzation.

 2nd question is , while i am pubilisin BI service , i cannot do sehedule referce due below error ? - can you please help me ?

" Query contains unsupported function.Function name : Web.Contents"

Regards,

Piyush

[Jimmy801]

Hello @PiyushH1 

it seems that I forgot to post it...

I've already prepared a nice solution for you, but I asked to open a new message, as this has nothing to do with your original request.

So if you create a new message, I can directly post my solution.

Have a nice time

JImmy

[PiyushH1]

Hi Jimmy,

i sent you message.

[Jimmy801]

Hello @PiyushH1 

I intended to reopen a new post in order to keep things divided per topic. Because the second question has nothing to do with the initial request.

So please reopen a new topic and I will propose a solution that I have already be prepared

Jimmy

[v-juanli-msft]

Hi @PiyushH1 

In Power query, create a parameter, use the parameter in the connection string,

let
    Source = (Date as text) => let
        C=currency,
        Source = Json.Document(Web.Contents("https://api.exchangeratesapi.io/" & Date & "?base="&C&"")),
        rates = Source[rates],
        #"Converted to Table" = Record.ToTable(rates),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Name", "Currency"}, {"Value", "Rate"}})
    in
        #"Renamed Columns"
in
    Source

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PiyushH1]

Hi Jimmy,

thank you for your answer.

but this website is not working as is not coming data in table format while i am loading in bi.

[Jimmy801]

Hello @PiyushH1 

any news on this regard?

Jimmy

[Jimmy801 · Accepted solution]

Hello @PiyushH1 

you will get a table for every day, depending on your URL that your are sending to the site

and here the code

let
    Quelle = Web.Page(Web.Contents("https://www.xe.com/de/currencytables/?from=EUR&date=2019-12-17")),
    Data = Quelle{0}[Data],
    #"Geänderter Typ" = Table.TransformColumnTypes(Data,{{"Währungscode ▲▼", type text}, {"Währungsname ▲▼", type text}, {"Einheiten pro EUR", type number}, {"EUR pro Einheit", type number}})
in
    #"Geänderter Typ"

Copy paste this code to the advanced editor to see how the solution works

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Jimmy801]

Hello

never worked that much with currency

But check out this site. Has livecurrency and exchange rate back as far as 2000.

https://www.xe.com/de/currencytables

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy