Hi, I would like to query to multiple API endpoints in a single table. Currently I have a column called [Site URL] which has the URLs for a few sites in the form
https://site1.atlassian.net
https://site2.atlassian.net
https://site3.atlassian.net
I have a custom column as follows to build a list containing the users for each site as the API only returns 50 results at a time
= Table.AddColumn(Source, "Custom", each let
path = "/rest/api/3/users?maxResults=50",
fxGetOnePage = (RelPath, offSet) =>
let
Source = Json.Document(Web.Contents([Site URL], [RelativePath = RelPath, Query = [startAt=Number.ToText(offSet)], Headers=[Authorization="Basic " & Credentials]]))
in
Source,
GeneratedList = List.Generate( () => [offset = 50, WebCall = fxGetOnePage(path, 0)],
each not List.IsEmpty( [WebCall]),
each [offset = [offset] + 50, WebCall = fxGetOnePage(path, [offset]) ],
each [WebCall]
),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in #"Converted to Table")
This table is then expanded and which gives an column called [accountId]. There is another custom column which uses this field as below
Json.Document(Web.Contents([Site URL], [RelativePath="/rest/api/3/user?accountId=" & [accountId] & "&expand=applicationRoles", Headers=[Authorization="Basic " & Credentials]]))
This is all working fine in the Power BI Desktop, however in the Power BI service I get the message.
"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"
Is there a problem with my approach? I understand that the Relative Path can be used when there is a static base URL. But in this case I want to query the same endpoint for multiple sites in a single table. I basically want to be able to create a column in Power Query with all the site URLs and then have a few API endpoints queried for all sites. Alternative ways of doing this are welcome.
Any help will be highly appreciated. Thank you in advance!
