The Web.Contents() function in M is the key to getting data from web pages and web services, and has a number of useful – but badly documented – options that make it easier to construct urls for your web service calls.
Consider the following url:
https://data.gov.uk/api/3/action/package_search?q=cows
It is a call to the metadata api (documentation here) for https://data.gov.uk/, the UK government’s open data portal, and returns a JSON document listing all the datasets found for a search on the keyword “cows”. You can make this call using Web.Contents() quite easily like so:
Web.Contents(
"https://data.gov.uk/api/3/action/package_search?q=cows"
)However, instead of having one long string for your url (which will probably need to be constructed in a separate step) you can use the RelativePath and Query options with Web.Contents(). They are given in the second parameter of the function and passed through as fields in a record. RelativePath adds some extra text to the base url given in the first parameter for the function, while Query allows you to add query parameters to the url, and is itself a record.
So, taking the example above, if the base url for the api is https://data.gov.uk/api we can use these options like so:
Web.Contents(
"https://data.gov.uk/api",
[
RelativePath="3/action/package_search",
Query=[q="cows"]
]
)RelativePath is just the string “3/action/package_search” and is added to the base url. There is just one query parameter “q”, the search query, and the search term is “cows”, so Query takes a record with one field: [q=”cows”]. If you want to specify multiple query parameters you just need to add more fields to the Query record; for example:
Web.Contents(
"https://data.gov.uk/api",
[
RelativePath="3/action/package_search",
Query=
[
q="cows",
rows="20"
]
]
)Generates a call that returns 20 results, rather than the default 10:
https://data.gov.uk/api/3/action/package_search?q=cows&rows=20
Obviously these options make it easier to construct urls and the code is much clearer, but there are also other benefits to using these options which I’ll cover in another blog post soon.
Note: at the time of writing there is a bug that causes the value given in RelativePath to be appended twice when the Web.Page() function is also used. Hopefully this will be fixed soon.
UPDATE January 2021: This blog post has more details on using the Query option:
https://blog.crossjoin.co.uk/2021/01/10/handling-multiple-url-query-parameters-with-the-same-name-using-web-contents-in-power-query-power-bi/
2018-05-21 Still not fixed. Thanks for your solution, it saved my day.
Hi Chris, quick question which keeps puzzling us. After finding this brilliant solution of yours we proceeded but ran into a new problem. The API we are calling uses the same query option 4 times.
The URL would be something along the lines of:
https://domain/path?&properties=name&properties=description&properties=price&hapikey=xxxxxxxxxxxxxxxxxxxxxx
In your example this would lead to:
Query =
[
properties = “value1”,
properties = “value2”,
properties = “value3”,
properties = “value4”,
hapikey = “xxxxxxxxxxxxxxxxxxxxxx”,
]
I feared this would pose a problem, knowing how standard arrays work, the last value assigned to properties would over write all previous ones. And indeed Power BI M actually warns for this problem.
Any idea how to circumvent this?
sincerely, Wouter
Hmm, interesting question. Can you try something like [ properties = “value1&properties=””value2″”&properties=””value3″””] ?
Thank you very much for your reply. Unfortunately power bi encodes all data between “”, and although it skips the quotes, the ampersand and the equal sign all get encoded rendering the string unusable. I even installed Netmon to observe this from happening. (thankfully the api was reachable using http instead of https) I – again – am at a loss. Any new ideas?
Hmm, no – I would need to do some tests to see if there’s a workaround in M
If you would do that, that would be great, but that sounds like something I cannot ask of you 🙂
I’ve tried a few things but I can’t get anything to work. Sorry…
Neverteless, thank you very much for getting back to me. Much appreciated.
Just a little feedback. I pushed everything in the RelativePath variable. Problem solved. Custom Functions are also not allowed, so those I have integrated into alle Table-code. 🙂
Interesting, thanks!
Hi Chris,
Thanks for this article.
I encounter an issue where the data from the website does not return correctly it seems if I use multiple query string.
For example,
this works fine – the data returns correctly (not using Query option)
Source = Json.Document(Web.Contents(“http://useragentstring.com/?uas=”&userAgent_URLEncoded&”&getJSON=all”)),
but this will cause failure in Scheduled Data Refresh in Power BI, so I have to use the Query option, like below.
Source = Json.Document(Web.Contents(“http://useragentstring.com/”,[Query=[uas=userAgent_URLEncoded,getJSON=”all”]])),
However, it seems that the data is not parsed in correctly, and I’ve got unknown values for most of the records.
Try using
userAgent_URLEncoded = Mozilla%2F5.0%20%28Windows%20NT%2010.0%3B%20Win64%3B%20×64%29%20AppleWebKit%2F537.36%20%28KHTML%20%20like%20Gecko%29%20Chrome%2F69.0.3497.100%20Safari%2F537.36
Do you know why?
Thanks
I don’t know for sure, but I’ve heard of similar problems. I need to do some research to find out what’s going on here…
We have tried using your method, and it works for PBIX and updates in the workbool, but when we upload to PBIS, and try to run the refresh there, the only APIs that will connect are those that do not use your method of breaking the relative path out from the main URL string. All of our data sources (APIs) that are called with the relative path in the main URL string work, and those where we use your method to break them out into their own relative path are unable to connect to the API for data refresh via PBIS. The same queries that work on the desktop do not connect or work in PBIS.
Hmm, that suggests something else is going on here. Have you turned off data privacy checks in Power BI Desktop? If so, does refresh fail when you turn on the data privacy checks?
I know this is old, but I’m seeing the same thing. Did y’all figure this out
Pingback:
Refresh (any) OAuth2 API from PowerBI Online / Dataflows, using Azure Functions
https://www.linkedin.com/pulse/refresh-any-oauth2-api-from-powerbi-online-dataflows-using-jansman/
What about if the query parameter name contains a pipe?
Like this – query_start_date|lt
(not me, but the API i am trying to hit) – is there any way to wrap it so PowerBI accepts it? currently throws an Invalid Identifier error
Something like this should work:
#”query_start_date|It”
This worked perfectly for me but then I found that it requires a gateway. I’m doing straight web calls, any idea how to skip the gateway?
I can’t make it work for any way in order to schedule refresh data. I have even check 404 errors. In Power Bi desktop works perfectly. It is azure devops api.
I’m getting: “We reached the end of the buffer.. The exception was raised by the IDataReader interface. Table: Query1”
let
Capacity = (ProjectSK as text, IterationSK as text, AssignedToUserSK as text) =>
let
Url = Web.Contents(“https://dev.azure.com”, [RelativePath=”/[Companyname]/” & ProjectSK & “/” & “_apis/work/teamsettings/iterations/” & IterationSK & “/capacities/” & AssignedToUserSK , ManualStatusHandling={404}, Query=[#”api-version”=”5.0″]] ),
Origen = Table.FromColumns({Lines.FromBinary(Url, null, null, 65001)}),
#”JSON analizado” = Table.TransformColumns(Origen,{},Json.Document),
#”Se expandió Column1″ = Table.ExpandRecordColumn(#”JSON analizado”, “Column1”, {“teamMember”, “activities”, “daysOff”, “url”, “_links”}, {“Column1.teamMember”, “Column1.activities”, “Column1.daysOff”, “Column1.url”, “Column1._links”}),
#”Se expandió Column1.activities” = Table.ExpandListColumn(#”Se expandió Column1″, “Column1.activities”),
#”Se expandió Column1.activities1″ = Table.ExpandRecordColumn(#”Se expandió Column1.activities”, “Column1.activities”, {“capacityPerDay”, “name”}, {“Column1.activities.capacityPerDay”, “Column1.activities.name”}),
#”Columnas quitadas” = Table.RemoveColumns(#”Se expandió Column1.activities1″,{“Column1.teamMember”, “Column1.activities.name”, “Column1.daysOff”, “Column1.url”, “Column1._links”}),
#”Columnas con nombre cambiado” = Table.RenameColumns(#”Columnas quitadas”,{{“Column1.activities.capacityPerDay”, “capacityPerDay”}}),
GetMetadata = Value.Metadata(Url),
GetResponseStatus = GetMetadata[Response.Status],
Output = if GetResponseStatus=404 then Table.FromRecords({[capacityPerDay = 0]}) else #”Columnas con nombre cambiado”
in
Output
in
Capacity
It’s hard to say what’s going on here, but it doesn’t look like a problem with your code.
It is curious how Power Bi Service is different to Desktop. I found my solution taking [CompanyName] out of relative path to join de URL parameter in Web.Content. It doesn’t have so much sense, but it is actually working.
Since this albeit very useful post is written in 2016 it ends with “Note: at the time of writing there is a bug that causes the value given in RelativePath to be appended twice when the Web.Page() function is also used. Hopefully this will be fixed soon.”
This bug seems to be ongoing still today.
I can’t get my function to work with the Query parameter when I use Web.Page() around the Web.Contents(“url”, [Query=[term=value]])
However it works when I use Web.Page(Web.Contents(“url/?term=value)) (No
I really wanted to let my custom connection run through a gateway but stumbled upon the unsupported source error because of Web.Contents() function – hence me reading this post.
Now I seem to have two problems instead of one. 😐
Can you try using Web.BrowserContents instead of Web.Page?
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 rest is static , how can we create a similar funtion to pass the JIRAID as param , i tried the below sample function but it doesnt pass it correctly.
= (JIRAID as text) =>
let Source = Json.Document(Web.Contents(“https://jira.company.com/rest/api/2/issue”, [Query=[JIRAID]], [RelativePath=”worklog/”]))
in Source
Why are you using the Query option here? Both JIRAID and worklog should be passed in through RelativePath here I think
Have you ever seen an issue where it adds a “/” into the url?
The API I’m trying to hit is something like this:
“www.website.com/api/measures/history?metrics=metric1,metric2,metric3&project=ProjectName
RelativePath =”?metrics=metric1,metric2,metric3&project=”
Query=[project=ProjectName]
The problem is that it shoots out
http://www.website.com/api/measures/history/?metrics=metric1,metric2,metric3&project=ProjectName
because it adds a “/” automatically before the relativepath.
Hi Chris,
I have been exploring using USERNAME() or USEPRINCIPALNAME() to dynamically assign the Paranmeter value in the Web.Contents query. The idea is to extract relevant data based on Power BI login user. Using RLS is possible however this alternative (if possible) will greatly reduce the number of records extracted for respective users.
Do you think this is possible ?
No, you can’t pass a value from DAX back to a Power Query function, you will have to use RLS
Chris, first of all, thanks for this incredibly helpful post! I’ve tried to follow what you have done and the code works fine in the desktop, but I’m still having problems with not being able to refresh the data when I have published to the powerbi service.
I’m actually connecting to an ODATA service, and had to figure out how to use params with ‘$’ signs in the name, so the example below might be helpful to others doing the same thing.
I did try and import the data using an ODATA data source, but was having problems, hence ended up going this route:
BaseUri = “https://myurl/v1/”,
QueryRecord =
[ RelativePath=”tickets/”,
Query=
[
token = “xxxxxxxxxx”,
#”$select” = “id,type,origin,baseStatus,createdDate,slaSolutionDate,slaSolutionTime,resolvedIn,urgency,serviceFull,subject”,
#”$filter” = “createdDate gt ” & FormattedDate & “-05:00 and baseStatus ne ‘Canceled'”,
#”$top” = “1000”,
#”$skip” = “1”
]
],
Source = Json.Document(
Web.Contents( BaseUri, QueryRecord )
Anything obvious I am doing wrong?
Thanks
Dan
Hi,
I would also like to know the correct way to express “select” parameter from Query.
Thanks,
Razvan
I am also extremely interested in a solution for this problem. We are trying to use Microsoft Graph api “skiptoken” element which is preceded by a dollarsign too.
I have been facing this Issue for a week and tried various approaches to fix it with no positive results 🙁
I have set a Parameter that provides the ability to make a Dynamic URL with the Following Code which would later be merged with a list-to-table Query:
let
Document=(Numero as number)as table =>
let
Source = Web.Contents(“http://www.sanaa.hn/colonias/dbo_fechas_agua_list.php?masterkey1=”&Number.ToText(Numero)&”&mastertable=dbo.barriocolonia”,[RelativePath=”Numero”]),
#”Extracted Table From Html” = Html.Table(Source, {{“Column1”, “TABLE[id=’form_grid_8′] > TR > :nth-child(1), TABLE[id=’form_grid_8′] > * > TR > :nth-child(1)”}, {“Column2”, “TABLE[id=’form_grid_8′] > TR > :nth-child(2), TABLE[id=’form_grid_8′] > * > TR > :nth-child(2)”}, {“Column3”, “TABLE[id=’form_grid_8′] > TR > :nth-child(3), TABLE[id=’form_grid_8′] > * > TR > :nth-child(3)”}, {“Column4”, “TABLE[id=’form_grid_8′] > TR > :nth-child(4), TABLE[id=’form_grid_8′] > * > TR > :nth-child(4)”}}, [RowSelector=”TABLE[id=’form_grid_8′] > TR, TABLE[id=’form_grid_8′] > * > TR”]),
#”Promoted Headers” = Table.PromoteHeaders(#”Extracted Table From Html”, [PromoteAllScalars=true]),
#”Changed Type” = Table.TransformColumnTypes(#”Promoted Headers”,{{“Id Colonia”, Int64.Type}, {“Barrio o Colonia”, type text}, {“Salida de Tanque”, type text}, {“Horario”, type text}})
in
#”Changed Type”
in Document
Static example of one of the URLS would be :”http://www.sanaa.hn/colonias/dbo_fechas_agua_list.php?masterkey1=1&mastertable=dbo.barriocolonia”
How would the Relative Path and Query solution be applied in this context to solve the Refresh Solution in the PBI Service?
Just Fixed it with help of a friend, turns out it was simpler than I thought:
Source = Web.Contents(“http://www.sanaa.hn/colonias/dbo_fechas_agua_list.php?masterkey1=1&mastertable=dbo.barriocolonia”,[Query=[masterkey1=Number.ToText(Numero),mastertable=”dbo.barriocolonia”]]),
I have a link which content some special character such as “%24” in the Query,
Ex: http://api.*****.com/topics/36261/mentions-trendline?%24date_from=2019-01-01T17%3A00%3A00Z&%24date_to=2019-12-31T17%3A00%3A00Z
How can I change it to Relative Path in Power Query
I got a similiar problem with the upload refresh and i am unable to implement a RelativePath. Either I have a too big dimension or my syntax is not correct. How would you do this? The line without the Relative Path is
WebCall = Json.Document(Web.Contents(URL & “/webapp/api/v1/salesOrder/?pageSize=1000&sort=-id&page=”&Text.From([Page])&””, [Headers=[AuthenticationToken=”” & token & “”, #”Content-Type”=”application/json”, Accept=”application/json”]]))
Best regards
Raphael Schmidt
Chris Webb you beaut! Saved the day with this blog post.
Hi Chris,
I am having a very similar issue with trying to connect to a SharePoint.Files() source.
I need to be able to access a set of files that over time will move from a Development SharePoint folder to an Operations folder.
My original plan was to connect to an excel file that would contain the url pathways (root, path, file), and then load my data using parameters against that excel file.
It works a treat in desktop, but then I get the “Dynamic data source” error once published to a workspace.
Is it possible to use a similar “relative path” solution using SharePoint.Files() as a source?
Hi Chris,
Dynamic URL with Web.Contents and RelativePath worked well on Power BI Report Server (May 2020).
But since we upgraded to Power BI Report Server (October 2020) the refresh failed with this beautiful message :
[0] -1056505856: COM error: mscorlib, An item with the same key has already been added..
[1] -1056505856: COM error: System.Core, Sequence contains no elements.
[2] -1055784828: The database operation was cancelled because of an earlier failure.
That looks like a bug – can you open a support case
We’re getting the same error an in our case, it looks like it’s being caused by a Table.Combine statement – it worked fine before the October release and now fails to refresh on PBI Server. Not sure if that’s your issue, but if so, just here to tell you that I’m not aware of a work-around….
Need help with power Query to schedule dynamic refresh of data and keep old data
Hello,
I need to keep the data for the past 24 hours that I refresh in power BI desctop (thats how I model my report and dataset)
I have premium and pro account
I have set RangeStart and RangeEnd parameters
schedule data refresh and update the data in first point every 15 minutes in power BI workspace ( here I get that my data source is not supported for refresh error – ” This dataset includes a dynamic data source. “)
I have read this blog post about “Faking Out” Web.Contents (http://blog.datainspirations.com/2018/02/17/dynamic-web-contents-and-power-bi-refresh-errors/), but I cannot seem to get it working.
Below is my query, any help would be much appreciated:
let
EntitiesPerPage = 500,
Limit=”&limit=” & Text.From(EntitiesPerPage),
Url = “https://*******.********.com/api/1/rest/public/runtime/dev_org2?org_wide=true&last_hours=24” & Limit,
GetJson = (Url) =>
let
RawData = Web.Contents(Url),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = Url & “&offset=0″,
Json = GetJson(Url),
Count = Json[#”response_map”],
Count1 = Count[#”total”]
in
Count1,
GetPage = (Index) =>
let
//(option A)offset equal to previous row count
offset = “&offset=” & Text.From(Index * EntitiesPerPage),
//(option B)offset equal to page numer
//offset = “&offset=” & Text.From(Index),
Url = Url & offset,
Json = GetJson(Url),
Value = Json[#”response_map”],
Value1 = Value[#”entries”]
in
Value1,
EntityCount = GetEntityCount(),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = { 0 .. PageCount – 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
Entities = List.Union(Pages),
Table = Table.FromList(Entities, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(Table, “Column1”, {“documents”, “state_timestamp”, “error_documents”, “label”, “path_id”, “state”, “create_time”, “duration”, “cc_label”, “runtime_label”}, {“Column1.documents”, “Column1.state_timestamp”, “Column1.error_documents”, “Column1.label”, “Column1.path_id”, “Column1.state”, “Column1.create_time”, “Column1.duration”, “Column1.cc_label”, “Column1.runtime_label”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded Column1″,{{“Column1.cc_label”, “cc_label”}, {“Column1.create_time”, “create_time”}, {“Column1.documents”, “documents”}, {“Column1.duration”, “duration”}, {“Column1.error_documents”, “error_documents”}, {“Column1.label”, “label”}, {“Column1.path_id”, “path_id”}, {“Column1.state”, “state”}, {“Column1.runtime_label”, “runtime_label”}, {“Column1.state_timestamp”, “state_timestamp”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“state_timestamp”, type datetime}, {“create_time”, type datetime}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each [state_timestamp] >= RangeStart and [state_timestamp] < RangeEnd)
in
#"Filtered Rows"
Regards,
Aleksandar
Hi chris,
Iam having the same data source refresh issue. I followed your blog but still finding issue.
Could anyone help me in creating the relative path and query for the below code
let
Source = Json.Document(Web.Contents(“https://nam.api.newvoicemedia.com/stats/agent-activities/interactions?end=”&Date.ToText(EndDate, “yyyy-MM-dd”)& “T23:00:00.000Z”&”&start=”&Date.ToText(StartDate, “yyyy-MM-dd”)& “T00:00:00.000Z”&””, [Headers=[Accept=”application/vnd.newvoicemedia.v3+json”, Authorization=”bearer “&GetAccessToken()]])),
items = Source[items],
#”Converted to Table” = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“agentId”, “start”, “status”, “duration”, “state”, “reason”, “interaction”, “channel”}, {“agentId”, “start”, “status”, “duration”, “state”, “reason”, “interaction”, “channel”}),
#”Expanded interaction” = Table.ExpandRecordColumn(#”Expanded Column1″, “interaction”, {“guid”, “medium”, “mediumManager”, “direction”}, {“guid”, “medium”, “mediumManager”, “direction”}),
#”Expanded channel” = Table.ExpandRecordColumn(#”Expanded interaction”, “channel”, {“guid”}, {“guid.1″}),
#”Changed Type” = Table.TransformColumnTypes(#”Expanded channel”,{{“guid”, type text}})
in
#”Changed Type”
thanks
I am also facing same issue, please let me know if you have any solution
Hi Chris,
I have a small dataset of about 6 workbooks that are stored across various places in a large SharePoint site.
I can’t use a gateway and the Folder connector, because the file paths are too many characters, and I can’t move the files.
Using the SharePoint.Files connector works, but the report takes between 1-2hrs to refresh about 1,000 rows of data, and often fails due to api/contextinfo error.
Using SharePoint.Contents doesn’t help in this case, as the files are spread throughout too many different folder paths.
This got me thinking about your Web.Contents RelativePath solution. I had read it, but never attempted to implement it.
While I can build a Dynamic Data Source that refreshes the SharePoint files in Desktop in about 1 minute using the Web.Contents approach, I can’t seem to take advantage of the RelativePath option because I can’t authenticate into the subsite.
my code:
Desktop Solution works a charm:
=Excel.Workbook(Web.Contents(Root&Path&File), null, true)
Root, Path, File are obviously Parameters, and then I use a function to call the correct details.
So, I attempted to modify this approach for the service:
=Excel.Workbook(Web.Contents(“MyCompany.SharePoint.com/sites/SubSite”, [RelativePath=Path&File]), null, true)
In theory I thought this should work, because if i type “MyCompany.SharePoint.com/sites/SubSite” into my browser, I can sign in and all is good.
BUT, in Power Query, I have to add a suffix to the URL before I can successfully authenticate:
“MyCompany.SharePoint.com/sites/SubSite/SitePages/Home(1).aspx”
This Suffix obviously messes with my ability to use RelativePath.
Do you know why I can’t authenticate into “MyCompany.SharePoint.com/sites/SubSite/” by itself?
Any ideas how I could overcome this?
Hi Chris,
below is my code
let
BaseURL = “https://portal.k3imagine.com/gw-bi/api/v1.0/customeraccounttransactions”,
Token = AccessToken,
Options = [Headers=[Authorization=”Bearer ” & Token]],
URL = BaseURL,
//Define a function that would take step/page as parameter and return results
Source = Json.Document(Web.Contents(URL, Options)),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“transactionDate”, “customerId”, “shopId”, “posId”, “transactionReceiptId”, “updatedByClerkId”, “spend”, “balanceDifference”, “creditLimit”, “loyaltyPointsEarned”, “loyaltyPointsManuallyRewarded”, “loyaltyPointsSpent”, “type”, “currencyCode”}, {“transactionDate”, “customerId”, “shopId”, “posId”, “transactionReceiptId”, “updatedByClerkId”, “spend”, “balanceDifference”, “creditLimit”, “loyaltyPointsEarned”, “loyaltyPointsManuallyRewarded”, “loyaltyPointsSpent”, “type”, “currencyCode”}),
#”Changed Type” = Table.TransformColumnTypes(#”Expanded Column1″,{{“transactionDate”, type datetime}, {“customerId”, Int64.Type}, {“shopId”, Int64.Type}, {“posId”, Int64.Type}, {“transactionReceiptId”, Int64.Type}, {“updatedByClerkId”, Int64.Type}, {“spend”, Int64.Type}, {“balanceDifference”, Int64.Type}, {“creditLimit”, Int64.Type}, {“loyaltyPointsEarned”, Int64.Type}, {“loyaltyPointsManuallyRewarded”, type logical}, {“loyaltyPointsSpent”, Int64.Type}, {“type”, Int64.Type}, {“currencyCode”, type text}}),
#”Filtered Rows” = Table.SelectRows(#”Changed Type”, each ([transactionDate] #datetime(1, 1, 1, 0, 0, 0))),
//Find the current date and time when this query runs
CurrentDateTime = DateTimeZone.FixedUtcNow(),
//Find yesterday’s date
PreviousDay = Date.AddDays(DateTime.Date(CurrentDateTime),-1),
//Put the current date and time in a new column in the table
#”Added Custom” = Table.AddColumn(#”Filtered Rows”, “UTC Data Load Date”, each CurrentDateTime),
#”Changed Type3″ = Table.TransformColumnTypes(#”Added Custom”,{{“UTC Data Load Date”, type datetimezone}}),
//Add the filter required for incremental refresh
//Only return rows in this table if:
//a) The RangeStart parameter equals yesterday’s date, and
//b) RangeEnd is not null (which should never be true)
#”Filtered Rows (1)” = Table.SelectRows(#”Changed Type3″, each DateTime.Date(RangeStart)=PreviousDay and RangeEndnull)
in
#”Filtered Rows (1)”
This code is working fine in Power BI Desktop but
I am getting below error while refreshing from Power BI service.
This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Please help to resolve the issue
I am also facing same issue, please help me if you find any solution.
I’m also facing same issue any one got the solution?
The below code resolved my issue.
let
Source = (ID as number) => let
authkey=”xxxxxxxxxxxxxxxxxxx”,
Source = Json.Document(Web.Contents(“xxxxxxxxxxxxxxxxx”,
[RelativePath =”xxxxxxxxxxxxxxxxx?”,Query=[per_page=”500″,page=Number.ToText(ID)], Headers=[#”Authorization”=authkey]])),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“enrollment_id”, “content_type”, “module_name”, “user”, “campaign_name”, “enrollment_date”, “start_date”, “completion_date”, “status”, “time_spent”, “policy_acknowledged”}, {“enrollment_id”, “content_type”, “module_name”, “user”, “campaign_name”, “enrollment_date”, “start_date”, “completion_date”, “status”, “time_spent”, “policy_acknowledged”}),
#”Expanded user” = Table.ExpandRecordColumn(#”Expanded Column1″, “user”, {“id”, “first_name”, “last_name”, “email”}, {“id”, “first_name”, “last_name”, “email”})
in
#”Expanded user”
in
Source
Hi, I too have followed the blog, but am facing the same issue as the others – the dynamic source can’t be refreshed. I wonder where I have gone wrong?
let
Source = (Team_ID as text) => let
Path = “https://dev.azure.com/”,
Repath = “vfuk-digital/_apis/projects/digital/teams/”&Team_ID&”/members?”,
Source = Json.Document(Web.Contents(Text.From(Path), [RelativePath=Repath, ManualStatusHandling={404}, Query=[#”api-version”=”6.0″]])),
value = Source[value],
#”Converted to Table” = Table.FromList(value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column2″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“identity”}, {“identity”}),
#”Expanded identity” = Table.ExpandRecordColumn(#”Expanded Column2″, “identity”, {“displayName”, “id”, “uniqueName”}, {“displayName”, “id”, “uniqueName”}),
#”Changed Type” = Table.TransformColumnTypes(#”Expanded identity”,{{“displayName”, type text}, {“id”, type text}, {“uniqueName”, type text}})
in
#”Changed Type”
in
Source
Does the path https://dev.azure.com/ return an error when you use it on its own (ie without the rest of the URL added to it)? If so, that will be the problem.
Hi Mr. Chris, those were really helpful blogs that you have written, unfortunately for me the scenario is little different, we are fetching data from cloud service provider but we are getting 1000 records per request along with nextPaginated code, however i have managed to write for each loop and able to get data, but sadly after all the hard work I am getting below error, hoping you can help anyway possible. Huge thanks for Your help to the community
Something went wrong
This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
My code is below for your ref
“`
let
iterations = 100000, // Number of iterations
url =
“https://euce.net/web/api/v2.1/installed-applications?limit=1000&ApiToken=af8O9E1ce”,
nxturl = “https://eucenet/web/api/v2.1/installed-applications?limit=1000&ApiToken=af8O9E1ce&cursor=”,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[pagination][nextCursor] otherwise null,
res = [Data=data, Next=nxturl&next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandListColumn(#”Converted to Table”, “Column1″)
in
#”Expanded Column1”
Hi Chris, Thank you very much for interesting blog post. I have a similar scenario where I am facing refresh issue in Power BI service.
I am connecting to the Power BI rest API’s, For example I would like to get the dataflows users.
Below is the power query code and it works fine in desktop version but the refresh is not working in Power BI service as it is a dynamic datasource.
I followed your recommendation and used Relative path function but still I am facing the issue.
In the below example, I am passing dataflowId as parameter to the url.
let
Source = Dataflows,
#”Removed Other Columns” = Table.SelectColumns(Source,{“dataflowId”, “dataflowName”}),
#”Added Custom” = Table.AddColumn(#”Removed Other Columns”, “Custom”, each Json.Document(Web.Contents(“https://api.powerbi.com/v1.0/myorg/admin/”,
[
RelativePath = “dataflows/”& [dataflowId]&”/users”,
Headers=[Authorization=”Bearer ” & GETAccessToken()]]))),
#”Expanded Custom” = Table.ExpandRecordColumn(#”Added Custom”, “Custom”, {“value”}, {“value”}),
#”Expanded value” = Table.ExpandListColumn(#”Expanded Custom”, “value”),
#”Expanded value1″ = Table.ExpandRecordColumn(#”Expanded value”, “value”, {“dataflowUserAccessRight”, “emailAddress”, “displayName”, “identifier”, “graphId”, “principalType”, “userType”}, {“dataflowUserAccessRight”, “emailAddress”, “displayName”, “identifier”, “graphId”, “principalType”, “userType”})
in
#”Expanded value1″
You won’t be able to connect to the Power BI REST API unless you build a custom connector (which Miguel Escobar has already done). See https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-query-power-bi/ for why.
Hi Chris,
I had this set up successfully for an Azure blob until early 2022.
The issue seems to be the first section of my path. If i remove the RelativePath then the URL works fine however it then doesn’t refresh. Is this a PowerBI issue or an issue with the first part of the link?
If i connect to “https://emidatasets.blob.core.windows.net/publicdata/” on its own then i get an error stating the path does not exist but adding the second part on to the end works so the domain is correct.
Doesnt work
Source = Xml.Tables(Web.Contents(“https://emidatasets.blob.core.windows.net/publicdata/”, [RelativePath=”?restype=container&comp=list&prefix=Datasets/Wholesale/FinalPricing/ClearedOffers/2021″])),
Does Work (but doesn’t refresh in service)
Source = Xml.Tables(Web.Contents(“https://emidatasets.blob.core.windows.net/publicdata?restype=container&comp=list&prefix=Datasets/Wholesale/FinalPricing/ClearedOffers/2021))
Cheers
Hello Chris
I can’t seem to get this to work. For some reason, Power Query underlines the “api” line of the Query array, and produces an “Invalid Identifier” error. I can change this parameter name to something else like “x” which makes the error move down the the “subscription” parameter (and produces the invalid identifier issue at that point). I can change the subscription-key param to something else as well (like “y”), which doesn’t produce any Power Query validation issues, but has the side effect of not being a valid API call. Do you have any ideas for a work-around?
Source8 = Json.Document(
Web.Contents(
“https://atlas.microsoft.com/weather/historical/actuals/daily/”,
[
RelativePath=”json”,
Query=
[
api-version = “1.1”,
query = 49.246292,-123.116226,
startDate= StartDate,
endDate= EndDate,
subscription-key = “qCJ7My_8PCnBlOsPF6fBSX-8da-749NiDdq-1V9VtgA”
]
]
)
),
The problem is the dash, so you should try
Query=
[
#”api-version” = “1.1”
ie, wrap any parameter names that contain problematic characters in double quotes and put a # sign before them. The same goes for #”subscription-key”.
Hi Chris,
Thanks for the post!
What about having special character in query?
Is there any way to avoid encode?
I am using Jira API and sending jql filter as query but contains characters like these +()
If I use web.contents without relative path and query works fine but not sending it using your method to be able to refresh it
Thanks
Ramon
Hello, Fisrt to all I would like to thank you for a blog like this.
i’ve been tryng to solve an issue to connect to an api, the api has a token that change after 10 minutes.
Combining this Cris’s powerfull post i was able to ask a token in a function and put it in the web content, the problem is now with de certificates and credentials.
Once I publish to PowerBIService, It ask me fot credentials that basically dont exist.
I cant use anonimous and also i dont have other credencials than the token,
How can i schedule a refresh of this appi??.
What i;ve done sor far.
1) ask the token in a function called fxtoken
() =>
let
Origen = Table.FromColumns({Lines.FromBinary(
Web.Contents(“https://api.teamplace.example.com/api/”,
[
RelativePath=”oauth/token”,
Query=[
grant_type=”client_credentials”,
client_id=”blabla”,
client_secret=”blablabla”]]
), null, null, 65001)}),
Column1 = Origen[Column1],
Token=Text.Combine(Column1)
in
Token
2)make the query and concatenate with de token,
let
Origen = Json.Document(Web.Contents(“https://api.example.finneg.com/api”,
[RelativePath=”reports/ANAFACTURACION”,
Query=[
PARAMWEBREPORT_FechaDesde=”2021-01-01″,
PARAMWEBREPORT_FechaHasta=”2031-01-01″,
ACCESS_TOKEN=fxtoken()]])),
#”Convertida en tabla” = ***lot of stuff***”CORREDOR”, ***lot of stuff*, “PROVINCIAORIGEN”})
in
#”Columnas quitadas6″
….
I am not a programmer, and i have been am boxing with this a couple of weeks. How can I manage to refresh a api in the service of poerbi??
Thanks, you all.
You need to create a custom connector for this: https://blog.crossjoin.co.uk/2021/08/29/connecting-to-rest-apis-with-oauth2-authentication-in-power-query-power-bi/
2 hs reply lead time, OMG, i will check, thanks!
Hi Chris,
Really insightful blog.
I’m having some issues with the below, are you please able to advise ?
Source = Json.Document(
Web.Contents(
JIRA_URL,
[RelativePath = “/rest/api/2/search?jql=” & QUERY]
)
),
I have two parameters
JIRA_URL which is internal jira url for the company
QUERY which is in JQL
I’m still not able to refresh on Power BI service and getting below error
This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
Would really appreciate your help.
Thank you,
Skandar Feki
Hi
I’m trying to use this method to circumvent this limitation on Power BI service, but it gives me the same famous error (Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed). I am using OneDrive as data source and this is my query:
Excel.Workbook(Web.Contents(“https://onedrive.live.com”,[RelativePath = “/download”, Query = [resid=”someresourceid”,authkey=”someauthcode”,em=”2″,app=”Excel”]]), null, true)
Is there anything wrong with this query? I will be very thankful as I’m really stuck with this report.
If relative path is working fine in desktop then I would presume your code is correct. However service can’t confirm if a dynamic path is correct so you need to turn on “skip conection” for this datasource. I had a similar issue.I have another comment below with my full code that now works online.
Thanks Chris this blog really helped me solve a dynamic refresh error using an API call to sharepoint. For anyone else who is having issues hear is my code that took a bit of trial and error.Hope this helps anyone.
I had trouping using relative path aswell however a lot of trial and error led me to the below code that works! Remember to turn off “Test Connection” in powerbi service aswell for this data source. Main Difference is to remove the square bracket at Headers Otherwise you will get a “3 arguments were passed to a function which expects between 1 and 2″ Error. Hope this helps
let
//sitename =”examplesitename”,–Variable may work but not tested online
//listname = “examplelistname”,
//baseurl = “https://example.sharepoint.com”,
// first id and last id to get differance was used as my ids in sharepoint did not go in order and jumped 1000s of ids last id was 21560 but actuals row count was 14000
lastId =
Json.Document(Web.Contents(“https://example.sharepoint.com/”,
[RelativePath = “sites/examplesitename/_api/web/lists/GetByTitle(‘examplelistname’)/items?$top=1&$orderby=ID desc”,Headers=[Accept=”application/json”]]))[value]{0}[ID],
First_Id =
Json.Document(Web.Contents(“https://flowpne.sharepoint.com/”,
[RelativePath = “sites/examplesitename/_api/web/lists/GetByTitle(‘examplelistname’)/items?$top=1&$orderby=ID asc”,Headers=[Accept=”application/json”]]))[value]{0}[ID]-1,
diff =lastId – First_Id,
skiplist = List.Numbers(First_Id, Number.RoundUp(diff/5000), 5000),
#”Convert to Table” = Table.FromList(skiplist,Splitter.SplitByNothing(),null,null,ExtraValues.Error),
#”Renamed Columns” = Table.RenameColumns(#”Convert to Table”,{{“Column1”, “skip”}}),
#”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“skip”, type text}}),
fieldselect =”&$top=5000″,
#”Added Custom” =Table.AddColumn(#”Changed Type”, “Items”, each
Json.Document(Web.Contents(“https://flowpne.sharepoint.com/”,
[RelativePath = “sites/examplesitename/_api/web/lists/GetByTitle(‘examplelistname’)/items?$skipToken=Paged=TRUE%26p_ID=” & [skip] & fieldselect, Headers=[Accept=”application/json”]]))),
#”Expanded Items1″ = Table.ExpandRecordColumn(#”Added Custom”, “Items”, {“value”}, {“value”}),
#”Expanded value” = Table.ExpandListColumn(#”Expanded Items1″, “value”),
#”Expanded value1″ = Table.ExpandRecordColumn(#”Expanded value”, “value”, {“odata.type”, “odata.id”, “odata.etag”, “odata.editLink”, “FileSystemObjectType”, “Id”,”GUID”}),
#”Removed Duplicates” = Table.Distinct(#”Expanded value1″, {“ID”}),
#”Removed Columns” = Table.RemoveColumns(#”Removed Duplicates”,{“skip”, “odata.type”, “odata.id”, “odata.etag”, “odata.editLink”}),
in
#”Removed Columns”
Hi Chris,
I have tried the above and web.contents it is another error cannot convert byte into text.
My actual error is
This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed.
Below is my power query
let
baseURL = “https://myorg.sharepoint.com/sites/PWA/_api/ProjectData/Projects()?$Filter=ProjectType ne 7″,
Source = OData.Feed(baseURL, null, [Implementation=”2.0″]),
#”Removed Other Columns1” = Table.SelectColumns(Source,{“ProjectId”, “ProjectName”, “ProjectWorkspaceInternalUrl”}),
#”Added Custom” = Table.AddColumn(#”Removed Other Columns1″, “GetWeeklyUpdatesDataColumn”, each LoadWeeklyUpdateData([ProjectWorkspaceInternalUrl])),
#”Expanded GetWeeklyUpdatesDataColumn” = Table.ExpandTableColumn(#”Added Custom”, “GetWeeklyUpdatesDataColumn”, {“Id”, “Title”, “KeyAchievements”, “NextSteps”, “Escalations”, “ID”}, {“GetWeeklyUpdatesDataColumn.Id.1”, “GetWeeklyUpdatesDataColumn.Title”, “GetWeeklyUpdatesDataColumn.KeyAchievements”, “GetWeeklyUpdatesDataColumn.NextSteps”, “GetWeeklyUpdatesDataColumn.Escalations”, “GetWeeklyUpdatesDataColumn.ID”}),
#”Filtered Rows” = Table.SelectRows(#”Expanded GetWeeklyUpdatesDataColumn”, each ([GetWeeklyUpdatesDataColumn.ID] null)),
#”Sorted Rows” = Table.Sort(#”Filtered Rows”,{{“GetWeeklyUpdatesDataColumn.ID”, Order.Descending}}),
#”–StartFindMaxId” = #”Sorted Rows”,
GetMaxId = Table.Group(#”–StartFindMaxId”, {“ProjectId”}, {{“MaxID”, each List.Max([GetWeeklyUpdatesDataColumn.ID]), type number}}),
#”–EndFindMaxId” = #”–StartFindMaxId”,
#”Merged Queries” = Table.NestedJoin(#”–EndFindMaxId”, {“ProjectId”}, #”GetMaxId”, {“ProjectId”}, “–EndFindMaxId”, JoinKind.LeftOuter),
#”Expanded –EndFindMaxId” = Table.ExpandTableColumn(#”Merged Queries”, “–EndFindMaxId”, {“MaxID”}, {“MaxID”}),
#”Added Custom1″ = Table.AddColumn(#”Expanded –EndFindMaxId”, “MatchMaxID”, each [MaxID] = [GetWeeklyUpdatesDataColumn.ID]),
#”Filtered Rows1″ = Table.SelectRows(#”Added Custom1″, each ([MatchMaxID] = true))
in
#”Filtered Rows1″
Hi Chris, I am having this issue, I am using shared drive to pick up my excel file, everything works fine but when I publish to the service I get this error:
This dataset includes a dynamic data source. Since dynamic data sources aren’t refreshed in the Power BI service, this dataset won’t be refreshed. Learn more: https://aka.ms/dynamic-data-sources.
not sur what to do here, first time facing this issue.
any help will be appriciated.
Hi Mo,
Without you configuring a gateway for power bi, it has no means to refresh the data from that shared drive which normally only exists within your own network (I am assuming).
So cofigure a gateway, for which you need to install software on an internal server and gave to make sure the account from the gateway has access to your excell sheet.
https://powerbi.microsoft.com/en-us/gateway/
Best regards, Wouter
Hi Chris,
in trying to solve the scheduled refresh issues with dynamic sources, I’m faced with a dilemma. The URL I need to break up using either the Relative Path or Query options while leaving a functioning base URL in place to satisfy the PBI service.
https://api4.successfactors.com:443/odata/v2/FODepartment(externalCode='000010‘,startDate=datetime’2022-07-01T00:00:00′)/cust_toDivision
the base URL is “https://api4.successfactors.com:443/odata/v2/FODepartment” and the part that is variable starts with the “(“. How do I attach that portion reliably using the Web.Contents framework? (externalCode=’000010′,startDate=datetime’2022-07-01T00:00:00’)/cust_toDivision)
When try using Relative Path, the function adds a “/” after the FODepartment so that doesn’t work. However when I use the Query option, the function pre-fixes “&” to the variable component. Neither option results in the business keys getting passed correctly and the resulting query is erroneously interpreted as just the base URL, returning a full table of Departments.
Can I modify the URL somehow before it’s sent out without making the PBI service consider it dynamic?
Regards
Henrik
Hi Chris,
Thank you very much for this article. I followed it and use json.doc(web.content +URL).
And online refreshing worked without issue.
However, I was advised to use Odata connector instead Web, and when I tried to refresh data online with OAuth2 but it doesn’t work.
What will be your advice please? What can cause this issue. Is it possible at all to refresh data online using Odata?
Best regards,
Jasmina
Hi Chris –
I am having a Power Query where I want to dynamically run AnalysisServices.Database M Query function dynamically and it is working fine in Power BI Desktop but when I published the report in Power BI Service it is failing with the same error “You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
Data source for Query1”
When I checked the Data Source Settings it is showing “Some data sources may not be listed because of hand-authored queries.”
I am trying to call this way,
Source = AnalysisServices.Database(WorkspaceName, DatasetName, [Query=”Select [ID], [TableID], [Name], [Description], [DataSourceID], [QueryDefinition], [Type], [Mode], ModifiedTime from $SYSTEM.TMSCHEMA_PARTITIONS”])
where WorkspaceName = “powerbi://api.powerbi.com/v1.0/myorg/”
and DatasetName = “PBI DatasetName”
I am trying to run this on all my Workspaces and all my Datasets.
Any help would be much appreciated.
Hi Chris,
Not sure if the thread is still active.
I am trying to convert my long query the way you did. but it is not taking all the filters I have added.
Can you please help me.
below is my query
= Json.Document(Web.Contents(“https://test.azure.elastic-cloud.com/snow-prd-req-item-202*/_search?q=(sci_sys_created_on:>=2022-01-01)%20AND%20(sci_sys_updated_on:>=”& Text.From(DateTime.ToText(RangeStart,”yyyy-MM-dd”))&”)%20AND%20(sci_sys_updated_on:<"& Text.From(DateTime.ToText(RangeEnd,"yyyy-MM-dd"))&")&size=1000000&pretty=true"))
let
startMonthDate = Date.StartOfMonth( Date.From( DateTime.LocalNow() ) ) ,
endDate = Date.From( DateTime.LocalNow() ) ,
startDate = Date.AddDays( endDate , -30 ),
endDateText = Date.ToText( endDate, “yyyy-MM-ddT00:00:00” ),
startDateText = Date.ToText( startDate, “yyyy-MM-ddT00:00:00” ),
Source = Json.Document(Web.Contents(“https://management.azure.com/subscriptions/e3e92688-56b2-4069-8388-299fa9622028/resourceGroups/ot-shared-apimgmt-we-prod-rg/providers/Microsoft.ApiManagement/service/ot-shared-apimgmt-we-prod/reports/byApi?$filter=timestamp ge datetime'”& startDateText &”‘ and timestamp le datetime'”&endDateText&”‘&api-version=2022-08-01″)),
#”Converted to Table” = Table.FromRecords({Source}),
#”Expanded value” = Table.ExpandListColumn(#”Converted to Table”, “value”),
#”Expanded value1″ = Table.ExpandRecordColumn(#”Expanded value”, “value”, {“name”, “apiId”, “callCountSuccess”, “callCountBlocked”, “callCountFailed”, “callCountOther”, “callCountTotal”, “bandwidth”, “cacheHitCount”, “cacheMissCount”, “valueCacheHitCount”, “valueCacheMissCount”, “apiTimeAvg”, “apiTimeMin”, “apiTimeMax”, “serviceTimeAvg”, “serviceTimeMin”, “serviceTimeMax”}, {“value.name”, “value.apiId”, “value.callCountSuccess”, “value.callCountBlocked”, “value.callCountFailed”, “value.callCountOther”, “value.callCountTotal”, “value.bandwidth”, “value.cacheHitCount”, “value.cacheMissCount”, “value.valueCacheHitCount”, “value.valueCacheMissCount”, “value.apiTimeAvg”, “value.apiTimeMin”, “value.apiTimeMax”, “value.serviceTimeAvg”, “value.serviceTimeMin”, “value.serviceTimeMax”}),
#”Changed Type” = Table.TransformColumnTypes(#”Expanded value1″,{{“value.name”, type text}, {“value.apiId”, type text}, {“value.callCountSuccess”, Int64.Type}, {“value.callCountBlocked”, Int64.Type}, {“value.callCountFailed”, Int64.Type}, {“value.callCountOther”, Int64.Type}, {“value.callCountTotal”, Int64.Type}, {“value.bandwidth”, Int64.Type}, {“value.cacheHitCount”, Int64.Type}, {“value.cacheMissCount”, Int64.Type}, {“value.valueCacheHitCount”, Int64.Type}, {“value.valueCacheMissCount”, Int64.Type}, {“value.apiTimeAvg”, Int64.Type}, {“value.apiTimeMin”, Int64.Type}, {“value.apiTimeMax”, Int64.Type}, {“value.serviceTimeAvg”, Int64.Type}, {“value.serviceTimeMin”, Int64.Type}, {“value.serviceTimeMax”, Int64.Type}, {“count”, Int64.Type}, {“nextLink”, type any}}),
#”Removed Columns” = Table.RemoveColumns(#”Changed Type”,{“value.bandwidth”, “value.cacheHitCount”, “value.cacheMissCount”, “value.valueCacheHitCount”, “value.valueCacheMissCount”, “value.apiTimeAvg”, “value.apiTimeMin”, “value.apiTimeMax”, “value.serviceTimeAvg”, “value.serviceTimeMin”, “value.serviceTimeMax”, “count”, “nextLink”}),
#”Filtered Rows” = Table.SelectRows(#”Removed Columns”, each ([value.name] = “Sites Entity API”)),
#”Renamed Columns” = Table.RenameColumns(#”Filtered Rows”,{{“value.name”, “API Name”}, {“value.apiId”, “Version”}, {“value.callCountSuccess”, “Success Call”}, {“value.callCountBlocked”, “Blocked Call”}, {“value.callCountFailed”, “Failed Callk”}, {“value.callCountOther”, “Other Call”}, {“value.callCountTotal”, “Total Call”}}),
#”Sorted Rows” = Table.Sort(#”Renamed Columns”,{{“API Name”, Order.Descending}})
in
#”Sorted Rows”
Hello, I have a little problem with my code, I can’t structure correctly my url which must be of this form with TimeStamp variables in Unix :
aaaaaaaaaaaaaa ?from&to&bbbbbbbbbbbbb
From = Text.From(Duration.TotalSeconds(DateTime.LocalNow() –
datetime(1970,1,1,0,0,0))*1000
To = “1683687600000”
Hoping to fix this problem,
Thank you,
My issue started when I used a dynamic URL, which works fine in PowerBI desktop but would not allow a refresh in the Service. So then I changed to using a Relative Path, which still works fine in the desktop but fails in the Service due to a data source credential errors for OAuth. I believe the issue is because the static part of the URL does not work on its own. I am unable to use Skip Test Connection on OAuth. How can I get this to work?
Here is the error when trying to set the data source credentials. This is the URL shown:
https://api.loganalytics.io/v1/workspaces/
The OAuth authentication method isn’t supported for this data source. Contact your admin.Hide details
Activity ID: 4618bdd5-4654-4ff0-9183-f3e5b8bc95fb
Request ID: 3c0f1b21-3b1d-144e-7509-bba42bac255b
Status code: 400
Time: Mon Aug 21 2023 07:32:17 GMT-0500 (Central Daylight Time)
Service version: 13.0.21358.58
Client version: 2308.2.15305-train
Cluster URI: https://wabi-us-east2-c-primary-redirect.analysis.windows.net/
Here is what the function looks like:
(VarURL as text)=>
let AnalyticsQuery =
//let Source = Json.Document(Web.Contents(“https://api.loganalytics.io/v1/subscriptions/1261d4da-bac0-4c7a-84d9-27cf14960ee7/resourcegroups/TWProduction/query”, //Original-Times out
//let Source = Json.Document(Web.Contents(“https://api.loganalytics.io/v1/workspaces/271f2b2a-e9b6-45c2-b0b0-67e0869f7f51/query”, //Hard coded; Works but not maintainable
//let Source = Json.Document(Web.Contents(VarURL, //WORKS in Desktop only; Dynamic Data source-can’t refresh in Service
let Source = Json.Document(Web.Contents(“https://api.loganalytics.io/v1/workspaces/”, [RelativePath = VarURL & “/query”, //Can’t set OAuth2 permissions in Service
Query=[#”query”=”
let over95th = materialize(AppPageViews
| where TimeGenerated > ago(1d)
| where Name in (“”Chart””)
| where DurationMs > -1 and DurationMs ago(1d)
| where Name in (“”Chart””)
| where DurationMs > -1 and DurationMs over95DurationMs
| summarize over95thCountTotal = count() by Client, Name);
let over95thCountByUserAndSite = materialize(AppPageViews
| where TimeGenerated > ago(1d)
| where Name in (“”Chart””)
| where DurationMs > -1 and DurationMs over95DurationMs
| summarize over95thCount = count() by Client, UserAuthenticatedId, TWSiteID, Name
| where isnull(TWSiteID) == false);
AppPageViews
| where TimeGenerated > ago(1d)
| where Name in (“”Chart””)
| where DurationMs > -1 and DurationMs { c{0}, c{3}}))
in
Table
Just wondering what to do when the base URL does not resolve?
Eg.
My base url is something like https://api.fakeapi.com/v1/website
Then i use query parameters to fill it out the be a valid endpoint – ie. https://api.fakeapi.com/v1/website/google.com/monthy_stats?api_key=123456
Works in desktop, however when i publish to the service and need to set its credentials it fails as https://api.fakeapi.com/v1/website returns a 404. What do we do in this case?
Hi Chris and thanks for your superuseful blog! How could we solve the situation if the query part is in the middle of the webpage address, not at the end? Thanks
If you can’t use RelativePath for everything from the middle of the address to the end then you have a problem
I have a PBI dashboard connecting to a web API. In Desktop it refreshes just fine but when I move it to the online service/publish it to the workspace I get an error that “The
Current setup that works in Desktop:
= Xml.Tables(Web.Contents(“https://services.marinetraffic.com/api/exportvessels/apikey?v=8×pan=30&msgtype=extended&protocol=xml”, [Headers=[api_key=”apikey”, v=”8″, timespan=”30″, msgtype=”extended”, protocol=”xml”]]))
I have a PBI dashboard connecting to a web API. In Desktop it refreshes just fine but when I move it to the online service/publish it to the workspace I get an error that “The credentials provided for the Web source are invalid”.
Current setup in PBI Desktop:
= Xml.Tables(Web.Contents(“https://services.marinetraffic.com/api/exportvessels/apikey?v=8×pan=30&msgtype=extended&protocol=xml”, [Headers=[api_key=”apikey”, v=”8″, timespan=”30″, msgtype=”extended”, protocol=”xml”]]))
I have a PBI dashboard connecting to a web API. In Desktop it refreshes just fine but when I move it to the online service/publish it to the workspace I get an error that “The credentials provided for the Web source are invalid”.
I “think” the issue is that I need to use a relative path instead of putting the api key directly into my url but #1- not sure if that is the real issue and #2 – even if it is, I am not sure how to fix it. Can someone assist?
Current setup:
= Xml.Tables(Web.Contents(“https://services.marinetraffic.com/api/exportvessels/apikey?v=8×pan=30&msgtype=extended&protocol=xml”, [Headers=[api_key=”apikey”, v=”8″, timespan=”30″, msgtype=”extended”, protocol=”xml”]]))
Hi dear Chris,
I hope you’re doing well.
Actually I’m getting my data from Udemy but I got the same error. My M Code in Power Query is as below. Could you please help me how I should modify it.
Error : “Some data sources may not be listed because of hand-authored query”
—-
let
BaseUrl = “https://yourdomain.udemy.com/api-2.0/organizations/224484/analytics/user-course-activity/”,
EntitiesPerPage = 100,
GetJson = (Url) =>
let Options = [Headers=[ #”Authorization” = “Token”]],
RawData = Web.Contents(Url, Options),
Json = Json.Document(RawData)
in Json,
GetEntityCount = () =>
let Url = BaseUrl,
Json = GetJson(Url),
Count = Json[#”count”]
in Count,
GetPage = (Index) =>
let Page = “?&page=” & Text.From(Index),
Page_Size = “&page_size=100″,
Url = BaseUrl & Page & Page_Size,
Json = GetJson(Url),
Value = Json[#”results”]
in Value,
EntityCount = List.Max({ EntitiesPerPage, GetEntityCount() }),
PageCount = Number.RoundUp(EntityCount / EntitiesPerPage),
PageIndices = {1 .. PageCount},
Pages = List.Transform(PageIndices, each GetPage(_)),
Hi Chris,
I know this is an old post but unfortuantely this is still a problem.
I have a query which scrapes data from a website incluing a link. I then use a custom function to scrape data from the link (code below). Despite using relativepath and Query, Power Bi Service still seems to treat the function url as dynamic.
Do you have any ideas how I can resolve this?
let
BaseUrl = “https://www.tenders.act.gov.au”,
RelativePath = “/tender/search?keywords=&tenderCode=&tenderTitle=&tenderState=ADVANCE_NOTICE&tenderType=&prequalificationCategory=&procurementCategory=&categories%5B0%5D.id=104789&categories%5B0%5D.percentage=100&issuingBusinessId=&awardedSupplier.id=&awardedSupplier.name=&openThisWeek=false&openingDateFrom=&openingDateTo=&ageRestriction=&closeThisWeek=false&closingDateFrom=&closingDateTo=&awardedThisWeek=false&awardedDateFrom=&awardedDateTo=&orderBy=&groupBy=NONE&page=&searchTitle=”,
Source = Web.Contents(BaseUrl, [RelativePath=RelativePath]),
#”Extracted Table From Html” = Html.Table(Source, {{“Tender ID”, “B:nth-child(1)”}, {“Title”, “.strong”}, {“Agency”, “DIV:nth-child(2) .line-item-detail”}, {“Link”, “.strong”, each [Attributes][href]?}, {“Published Date”, “.opening_date”}, {“Estimated Approach”, “.closing_date”}}, [RowSelector=”TBODY TR”]),
#”Changed Type” = Table.TransformColumnTypes(#”Extracted Table From Html”,{{“Tender ID”, type text}, {“Title”, type text}, {“Agency”, type text}, {“Link”, type text}, {“Published Date”, type datetime}}),
#”Added Custom1″ = Table.AddColumn(#”Changed Type”, “Source”, each “ACT Tenders”),
#”Added Custom2″ = Table.AddColumn(#”Added Custom1″, “State”, each “Australian Capital Territory”),
#”Extracted Text After Delimiter” = Table.TransformColumns(#”Added Custom2″, {{“Agency”, each Text.AfterDelimiter(_, “Issued by: “), type text}}),
#”Added Custom3″ = Table.AddColumn(#”Extracted Text After Delimiter”, “Description”, each “”),
#”Removed Columns2″ = Table.RemoveColumns(#”Added Custom3″,{“State”}),
#”Added Custom4″ = Table.AddColumn(#”Removed Columns2″, “guid”, each “”),
#”Added Custom5″ = Table.AddColumn(#”Added Custom4″, “Relevant Content”, each “”),
#”Added Custom6″ = Table.AddColumn(#”Added Custom5″, “Type”, each “”),
#”Changed Type1″ = Table.TransformColumnTypes(#”Added Custom6″,{{“Published Date”, type date}}),
#”Extracted Text After Delimiter2″ = Table.TransformColumns(#”Changed Type1″, {{“Link”, each Text.AfterDelimiter(_, “=”), type text}}),
// Define a custom function to fetch data for each link using Power Query 2
GetLinkData = (link as text) => let
Source2 = Web.Contents(“https://www.tenders.act.gov.au”, [RelativePath = “/tender/view?”, Query =[id = link]]),
#”Extracted Data” = Table.SelectColumns(Html.Table(Source2, {{“Column4”, “[id*=””tenderDescription””] > :nth-child(2)”}}), {“Column4”})
Hi Chris,
Thank you for the blog ! I need to add a list of all the fields I want in the api query and I cannot figure out how to write the code properly :
Would you mind looking at it and help me figure it out :
Json.Document(Web.Contents(“https://ce-XX.ilucca.net/api/v3/leaves”, [
RelativePath = “”,
Query = [
leavePeriod.ownerId = “1”,
date = “since%2C2022-01-01”,
fields = “id, date, isAm, leaveAccountId, leaveAccount[Name], leavePeriod[ownerId], leavePeriod[isConfirmed], isActive,comment”],
Headers=[Authorization=”lucca application=XX”]
]))
Thank you in advance for the help