Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHas 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
I pasted in your code and it works fine here. Two things to check:
This is a very nice function. Mind if I use it internally? We do CNY conversions all of the time.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.