cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Scheduled Refresh with Web Source Not Working

Hi all,

 

I have built a PBI report on PBI Desktop using SQL database and Web as data sources.

The Web source is used to call http://useragentstring.com and the report passes user agent values in a table to extract user agent-related data (via Invoked Function)

 

Source = Json.Document(Web.Contents("http://useragentstring.com/?uas="&userAgent_URLEncoded&"&getJSON=all")),

 

All work fine in PBI Desktop - refreshing the report works there. However, it does not work when I publish the report to on-premise PBI Report Server and run scheduled referesh.

The error is:

 

SessionID: f327bfa4-fb1c-4662-ab33-a1340c81e00f
[0] -1055784932: Credentials are required to connect to the Web source. (Source at http://useragentstring.com/.). The exception was raised by the IDbCommand interface.
[1] -1055129594: The current operation was cancelled because another operation in the transaction failed.
[2] -1055784932: The command has been canceled.. The exception was raised by the IDbCommand interface.


If I go to Data Sources on the left hand side, it only shows the SQL data source there and I have authenticated it using a service account successfully.

The Web Source does not appear in this section.

 

When I go to the PBI Desktop >  Edit Queries > Data Source Settings, under "Data sources in current file", it only shows the SQL source there, though there is a warning above the buttons saying "Some data sources may not be listed because of hand-authored queries". Not sure what this warning is for and if it is relevant to this issue.

 

If I go to PBI Desktop >  Edit Queries > Data Source Settings, under "Global permissions", I can see http://useragentstring.com/ there and if I click on Edit Permissions, the Credentials type is Anonymous with Privacy Level = None.

 

Not sure why http://useragentstring.com/ is on "Global permissions" not in "Data sources in current file".

Can this be why the scheduled refresh does not work because the permission to that URL is not explictely configured in the file?

If so, how can I add this data source to "Data sources in current file" section?

 

Thanks

 

P.S. If there is a better (and free) way to extract useful data (e.g. OS type, OS version, browser info, etc.) from given user agent values, please let me know also.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

I managed to resolve the issue with the scheduled refresh by the method on this blog.

 

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

View solution in original post

12 REPLIES 12
roman_gorbunov
Frequent Visitor

Hi, in my similar case the solution was the following: added web sources (through API) in the gateway connection section (in the PBI service) and in the settings set skip connection check and the privacy level is none.

amkhullar
Advocate I
Advocate I

I also have a similar issue and to solve it i did below as per the post by @Anonymous 

 

I have a API URL which works like this :
https://jira.company.com/rest/api/2/issue/RC-2345/worklog


to get the data from JIRA , the param is "RC-2345" in this url , rest is static , how can we create a similar funtion to pass the JIRAID as param , https://jira.company.com/rest/api/2/issue/JIRAID/worklog

 

i tried the below sample function but it doesnt pass it correctly.

let WorkLog = (JIRAID as text) =>        
    let Source   = Json.Document(Web.Contents("https://jira.company.com/rest/api/2/issue", [Query=[JIRAID]], [RelativePath="worklog/"]))
    in  Source
in WorkLog

but i get the error as 

 

An error occurred in the ‘CalcWorkLog’ query. Expression.Error: 3 arguments were passed to function which expects between 1 and 2.
Details:
Pattern=
Arguments=[List]

Was able to fix the error by the approach below but the sync issue still exists for data refresh:

 

let WorkLog = (JIRAID as text) =>        
    let Source   = Json.Document(Web.Contents("https://jira.company.com/rest/api/2/issue/" & ""&JIRAID&"", [RelativePath="worklog/"]))
    in  Source
in WorkLog

Created a param as shown on this url http://prntscr.com/pm3wod with a default value.

 

Error on Data Set sync is as below

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
Data source for "JIRA Sub Task WorkLog"
Data source for "JIRA Main Task WorkLog"
Discover Data Sources
Query contains unsupported function. Function name: Web.Contents

Same issue... similar to mine... bidax solution is not valid for me

Hello,

My schedule refresh will not work and gives me the following error(see below). I am using the On-prem Report Server. The datasource is Smartsheet & SharePoint. I can manually go into the Power BI Desktop and refresh the report, however the schedule refresh will not work in the Report Server? Is schedule refresh not supported in Report Server?

 

Thanks,

Patrick

 
 

Error message details:

Several error occured during data refresh. Please try again later or contact your administrator.

[0] -1055784932: The import Smartsheet.Tables matches no exports. Did you miss a module reference.

Smallegrue
Frequent Visitor

@Anonymous  your solution worked perfectly! Thanks!

Matija
Frequent Visitor

I have the same issue. I get a message on the dataset in Power BI Service: Query contains unsupported function. Function name: Web.Contents. In Data Sorce settings in Power BI desktop, I see: “Some data sources may not be listed because of hand-authored queries”, even though I changed all data sources to Public as suggested in this blog.

 

Apparently, there is a Skip test connection function added recently which might solve this issue but I cannot find it in Power BI Service (or desktop for that matter).

 

Any thoughts Power people?

 

 

Matija
Frequent Visitor

Does anybody have an idea?

 

Maybe there is a better way to fetch data from API, something I can use instead of Web.contents function?

Hi all, I am having same problems with Scheduled refresh

In content i am getting the contents of a excel file in sharepoint

 

 

CUSTOM_FUNCTION

(content) =>
let
Origen = Excel.Workbook(Web.Contents(content), null, true),
#"ATR Contract Report_Sheet" = Origen{[Item="ATR Contract Report",Kind="Sheet"]}[Data],
#"XXX" = Table.PromoteHeaders(#"ATR Contract Report_Sheet", [PromoteAllScalars=true]),
#"XX"

 

 

let
Origen = SharePoint.Files("https://sharepoint.com/teams/XXXXX/", [ApiVersion = 15]),
#"Filas filtradas" = Table.SelectRows(Origen, each Text.Contains([Folder Path], "Data/")),
#"Otras columnas quitadas" = Table.SelectColumns(#"Filas filtradas2",{"Content", "Name", "Folder Path"}),
#"Columna duplicada" = Table.DuplicateColumn(#"Otras columnas quitadas", "Name", "Name - Copia"),
#"Valor reemplazado" = Table.ReplaceValue(#"Columna duplicada","ATRContract_Report_","",Replacer.ReplaceText,{"Name - Copia"}),
#"Columnas con nombre cambiado" = Table.RenameColumns(#"Valor reemplazado",{{"Name - Copia", "Fecha"}}),
#"Primeros caracteres extraídos" = Table.TransformColumns(#"Columnas con nombre cambiado", {{"Fecha", each Text.Start(_, 8), type text}}),
#"Tipo cambiado" = Table.TransformColumnTypes(#"Primeros caracteres extraídos",{{"Fecha", type date}}),
#"Columnas reordenadas" = Table.ReorderColumns(#"Tipo cambiado",{"Fecha", "Name","Folder Path", "Content"}),
#"Personalizada agregada" = Table.AddColumn(#"Columnas reordenadas", "Personalizado", each CUSTOM_FUNCTION([Folder Path]&[Name])),
#"Columnas quitadas" = Table.RemoveColumns(#"Personalizada agregada",{"Folder Path", "Content"}),

 

 

 

Thanks in advance

Grumelo
Advocate II
Advocate II

I have the same issue.

I'm also using a lot of generated web queries.

 

For some of them passing the credential in the http header combined with an anonymous authentication work.

 

Unfortunately most of my generated web content call are getting the data from SharePoint on premise.

 

The Data Source authentication on Power BI report server is really not good.

 

We should be able to define it the same way as the global permissions in Power BI Desktop.

The Data Source concept is inheritate from Report Server that is not capable of dealing with dynamic queries.

 

An improvement from Microsoft is really needed, this issue is currently a Killer factor for the ramp up of Power BI Report Server in my company.

Anonymous
Not applicable

I managed to resolve the issue with the scheduled refresh by the method on this blog.

 

https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...

I followed this, it went flawlessly :). Thanks!

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.