Using The RelativePath And Query Options With Web.Contents() In Power Query And Power BI M Code

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.

27 responses

  1. Pingback: Dew Drop - August 16, 2016 (#2310) - Morning Dew

  2. Pingback: daily 08/16/2016 | Cshonea's Blog

  3. Pingback: Web.Contents(), M Functions And Dataset Refresh Errors In Power BI – Chris Webb's BI Blog

  4. Pingback: Dynamic Web.Contents() and Power BI Refresh Errors – Data Inspirations

  5. Pingback: Implementing Basic Query Folding On A Web Service In Power Query/M And Power BI « Chris Webb's BI Blog

  6. Pingback: Credentials, Data Privacy Settings And Data Sources In Power Query/Power BI « Chris Webb's BI Blog

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

  8. 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?

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

  10. 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?

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

  12. 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?

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

Leave a Reply to Paul Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: