Exchange rates Data not refresh

[PiyushH1]

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

[v-lid-msft]

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,

[PiyushH1]

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 

[edhans]

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.

[PiyushH1]

Hi edhans ,

I did above chanages in privacy setting but i am still getting same error  in Bi service.

Is their any other way to do it ?

Thank you.

Piyush

[edhans · Accepted solution]

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.

[acrmorris]

Has anybody got this to work on a scheduled refresh with the amended function using RelativePath below?

The error I get in Power BI service is "Information is needed in order to combine data". Refresh in the desktop application is working perfectly and I have applied all of the recommended privacy settings below. The service is working through a gateway so I am at a bit of a loss.

Thanks

[edhans]

I pasted in your code and it works fine here. Two things to check:

1. 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.
2. 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.