Forum Discussion
Exchange rates Data not refresh
Hi Team,
I have below code in my Exchange rate table in advance editor. Everythinng is working fine what i want but when i publish in BI service i am geeting below Error message due to not geeting data refresh.
"Query contains unsupported function. Function name: Web.Contents"
can anyone help me on this issue to get my data refresh, please ?
=======================
Below Code i am using in my table.
=======================
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
You have to have a gateway installed. You cannot refresh web results directly. I cannot find the article on this but this was a change in late 2018 I believe.
EDIT: Here is the ultimate list of data sources for Power BI. Scroll down to Web and you'll see that a gateway is required.
7 Replies
I pasted in your code and it works fine here. Two things to check:
- Make sure the permissions are not blocking. It is a website, so I'd set permissions to NONE unless you are combining internally with sensitive data. See image below. You get there via the Data Source icon in the ribbon.
- Go to Options for Power BI, then Preview Features, and make sure New Web Table inference is checked. There are some new bits in this connector that may overcome errors from previous web queries. I'm also on the Dec 2019 desktop, the latest.
This is a very nice function. Mind if I use it internally? We do CNY conversions all of the time.
Hi PiyushH1 ,
Could you please change the query of Fn_GetHistoricExchangeRates function to the following query?
Fn_GetHistoricExchangeRates let Source = (Date as text) => let Source = Json.Document(Web.Contents("https://api.exchangeratesapi.io" ,[RelativePath = "/" & 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,Hi Dong Li,
Thank you.
i have changed code as you suggested but still i am geeting error on data connection (see below).
Message[Unable to combine data] Section1/Daily Rates-EUR/AutoRemovedColumns1 is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination. Table: Daily Rates-EUR.
Can you please help ?
Kind Regards,
Piyush
For grins PiyushH1 can you go into the Options and Settings menu, select Privacy, and set this to "Always Ignore Privacy Level settings?" Then try to rerun it.
If it works, then there is a privacy setting for one of the queries that is more stringent than this web query and it isn't allowing data to be passed to it.
