cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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

 

 

 

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

7 REPLIES 7
Community Support
Community Support

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,

 

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

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

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

Super User III
Super User III

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.

 

2020-01-23 07_53_40-Data source settings.png



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors