Some time ago I wrote a pair of popular posts about using the Query and RelativePath options of the Web.Contents function in Power Query and why they are important for dataset refresh. I have recently learned something extra about this subject which merits a new post, though: how to handle multiple URL query parameters with the same name.
In the examples for this post I’ll be using a free, fake web service available at http://jsonplaceholder.typicode.com/ which requires no authentication so you will be able to run the code I show yourself. Let’s start by considering the following call to the comments resource of this API:
http://jsonplaceholder.typicode.com/comments?postId=1
In M, you can use the Web.Contents function and the Query option to call the API like so:
//Generates the URL http://jsonplaceholder.typicode.com/comments?postId=1
Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId="1"]])
Now this API – unlike many others – allows you to pass multiple URL query parameters of the same name. For example this is a valid call:
http://jsonplaceholder.typicode.com/comments?postId=1&postId=2
The problem is, if you try to do the following in M:
Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId="1",postId="2"]])
…you’ll get an error, because the Query option take a record and a record can’t have two fields with the same name.
Here’s where it gets interesting. In the working example above, the postId field in the Query record contains a text value. The field can contain a list of text values instead, though, and this is how you solve the problem:
//Generates the URL http://jsonplaceholder.typicode.com/comments?postId=1&postId=2
Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId={"1","2"}]])
This results in a URL with two query parameters that are both called postId but have the values 1 and 2 respectively.
There’s something else worth mentioning about this too. Sometimes when you’re generating a URL you may not want to add a query parameter to it if a value is null. One way of handling this is to start with an empty record and then add fields to it using the Record.AddField function, but using an empty list provides another approach. For example:
//Generates the URL http://jsonplaceholder.typicode.com/comments
//and *not* http://jsonplaceholder.typicode.com/comments?postId=
Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId={}]])
…generates a URL without the postId query parameter in. This means you can write a simple function with an optional parameter for postId like this:
(optional myPostId as text) =>
Json.Document(
Web.Contents("http://jsonplaceholder.typicode.com/comments",[Query=[postId=myPostId ?? {}]])
)
If a text value is passed to myPostId then the postId query parameter is added to the URL; if no value is passed, myPostId is null and the ?? null coalescing operator (see Ben Gribaudo’s post here for more information on this operator) can be used to replace it with an empty list.
One last thing to mention: if you need to see the web service calls generated by Web.Contents when you’re testing in the Power Query Editor, you can use the query diagnostics functionality as I show here.
[I’m extremely grateful to Dave Maiden for letting me know about all this – it answers a question I have been asked several times in the past]
That’s neat. I solved it by generating `options[RelativePath]` using [Uri.EscapeDataString](https://docs.microsoft.com/en-us/powerquery-m/uri-escapedatastring)
https://ninmonkeys.com/blog/2021/01/20/web-contents-using-dynamic-and-duplicate-key-names-in-a-query/
Have you seen docs or information on how `Query` behaves for this function or other data sources, like parameterized SQL queries? The [pingback](https://curatedsql.com/2021/01/11/handling-duplicate-query-string-values-with-power-query/) says he found different results depending on the environment.
Hi Chris, thank you for this information! I am trying to replicate the RelativePath soultion to my dynamic data source which uses the following REST API:
Json.Document(Web.Contents(“https://api-au.myconnectwise.net/v4_6_release/apis/3.0/sales/opportunities?pagesize=1000&conditions=locationId in (32)”, [Headers=[Authorization=”Basic Sxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==”, Clientid=”dxxxxxxxxxxxxxxxxxxxxxxxxxxxx0″]]))
This gives me a list which I further transform into tables. Not sure how to get the condition part in Query [].
I have come to following reformatting so far:
let
Source = Json.Document(
Web.Contents(
“https://api-au.myconnectwise.net”,
[
RelativePath = “v4_6_release/apis/3.0/sales/opportunities”,
Query = [pagesize = 1000
& locationId = {“32”}]
],
[
Headers = [
Authorization = “Basic Sxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx==”,
Clientid = “dxxxxxxxxxxxxxxxxxxxxxxxxxxxx0”
]
]
)
)
in
Source
Error returned:
“Expression.Error: The name ‘locationId’ wasn’t recognized. Make sure it’s spelled correctly.”
Could really use your help as I am just stepping in the M query universe!
Thank you!
Thank you so much for the info! I have a question. How can you solve a in the relative path, a query parameter that incluye a M function:
This is the normal url –> Source = Json.Document(Web.Contents(“https://mycompany.pipedrive.com/v1/notes?limit=500&pinned_to_person_flag=0&api_token=12345abcde&start=”&Number.ToText(Page))),
This is the relative path source that is not working (because of the NumberToText) –> Source = Json.Document(Web.Contents(“https://mycompany.pipedrive.com/v1/notes”, [Query=[api_token=”03a31747879be86fe4350e0aa439b7c231396109″, limit=”500″, pinned_to_person_flag=”0″, start=”Number.ToText(Page)]])),
It looks like you need to remove the double quote immediately before Number.ToText
Thank you so much Chris, it works!
Hi I am trying to populate azure application Insights log data to power bi using API key.
But I am not able to schedule refresh when I publish the report to the portal.
Error says : You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh:
The Query I am using looks as follows :
let AnalyticsQuery =
let Source = Json.Document(Web.Contents(“https://api.applicationinsights.io/v1/apps//query”,
[Query=[#”query”=”requests
| where timestamp > ago(12h)
| project timestamp, operation_Id
| join (dependencies
| where timestamp > ago(1d)
| summarize sum(duration) , count(itemCount) by operation_Id, type)
on operation_Id
“,#”x-ms-app”=”AAPBI”,#”prefer”=”ai.response-thinning=true”],Timeout=#duration(0,0,4,0),
Headers=[#”x-api-key”=””, #”Prefer”=”response-v1=true”]])),
TypeMap = #table(
{ “AnalyticsTypes”, “Type” },
{
{ “string”, Text.Type },
{ “int”, Int32.Type },
{ “long”, Int64.Type },
{ “real”, Double.Type },
{ “timespan”, Duration.Type },
{ “datetime”, DateTimeZone.Type },
{ “bool”, Logical.Type },
{ “guid”, Text.Type },
{ “dynamic”, Text.Type }
}),
DataTable = Source[tables]{0},
Columns = Table.FromRecords(DataTable[columns]),
ColumnsWithType = Table.Join(Columns, {“type”}, TypeMap , {“AnalyticsTypes”}),
Rows = Table.FromRows(DataTable[rows], Columns[name]),
Table = Table.TransformColumnTypes(Rows, Table.ToList(ColumnsWithType, (c) => { c{0}, c{3}}))
in
Table,
#”Changed Type” = Table.TransformColumnTypes(AnalyticsQuery,{{“timestamp”, type datetime}}),
#”Added Custom” = Table.AddColumn(#”Changed Type”, “Rounded_timestamp”, each Number.Round(Number.From([timestamp]) * (60 * 24 /60),0) / (60 * 24 /60)),
#”Changed Type1″ = Table.TransformColumnTypes(#”Added Custom”,{{“Rounded_timestamp”, type datetime}})
in #”Changed Type1″
Appreciate your help in resolving this issue
Thanks Saket
Hi, 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 the 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.
Hello Chris, I really need your help.
The code below is before authorization with token, but now our company passed to authorization with token:
= Table.AddColumn(#”Renamed Columns”, “Data”, each try Json.Document(Web.Contents(“https://issuetracking.com/rest/api/2/issue/”,
[RelativePath= [key] & “?expand=changelog&maxResults=999”])) otherwise null)
Here is how we normally access with token:
= Json.Document(Web.Contents(“https://issuetracking.sdd.com/rest/api/2/search?jql=&maxResults=2000,[Headers=[Authorization=”Bearer NzQ4NzcwMjk2NTM5OsK58HIugv+tY3Y”]]))
But now I dont know how to access by token with also relative path. I tried this (double authorization) but data column is empty:
= Table.AddColumn(#”Renamed Columns”, “Data”, each try Json.Document(Web.Contents(“https://issuetracking.com/rest/api/2/issue/”,
[Headers=[Authorization=”Bearer NzQ4NzcwMjk2NTM5OsK58HIugv+tY3YCw”]],
[RelativePath= [key] & “?expand=changelog&maxResults=999″,
Headers=[Authorization=”Bearer NzQ4NzcwMjk2NTM5OsK58HIugv+tY3YCw”]])) otherwise null)
Could you dynamically refresh a dataset where the url is the dynamic part not the relative path and query? Ex. I am getting a items from a list called “Gifts” from multiple Sharepoint sites. The site url changes but the list name doesn’t.
No you can’t do that, sorry
I was able to be somewhat successful by having the base of my Sharepoint site as the baseURL and then the location of the site as apart of the relative bath.
Ex. https://microsoft.sharepoint.com/ is the base url and the RelativePath becomes urlsubSite & “_api” call.
Hi Chris,
I’ve followed your advice and changed a bunch of Wec.Content() calls to use RelativePath & Query params to overcome the Dynamic Data Source issue. Most scripts work fine with the exception of the one reported below.
I’ve tried a number of different combinations but this last one will refresh fine in PowerBI desktop but it won’t refresh once published. (Connection is set to anonymous and public + I skip the test on server).
The API will return holiday data including if present a next page link
Example: “nextPageLink”: “https://app.timetastic.co.uk/api/holidays?start=2023-01-01&end=2024-04-10&pagenumber=2″
M Query follows:
let
FetchData = (url as text, optional pgNum as text) =>
let
_startDate=”2024-01-01″,
_endDate=DateTime.ToText(DateTime.LocalNow(),”yyyy-MM-dd”),
_httpHeaders = [#”Authorization”=”Bearer xxxxx-xxxxxx-xxxxxxx-xxxxxxx-xxxxx”, #”Content-Type”=”application/json”],
_pageNum = if pgNum null then pgNum else “”,
Source = Json.Document(
Web.Contents(
url,
[
RelativePath=”holidays”,
Headers=_httpHeaders,
Query=[
Start=_startDate,
End=_endDate,
pagenumber=_pageNum
]
]
)
),
Data = Source[holidays],
nextLink = try Source[nextPageLink] as text otherwise “”,
_pgNum = Text.From(if List.Last(Text.Split(nextLink, “=”)) null then List.Last(Text.Split(nextLink, “=”)) else “”),
Output = if nextLink “” then Data & @FetchData(“https://app.timetastic.co.uk/api”, _pgNum) else Data
in
Output,
FullSet = FetchData(“https://app.timetastic.co.uk/api”)