Hi,
I've read though a dozen articles on this and haven't found this scenario yet. Your assistance, or pointers to prior solutions, will be appreciated. Thanks in advance.
I'm gathering data from a single data source (it happens to be Atlassian Jira Cloud REST API). The API frequently requires the caller to provide data to the API in order to get data. How is this use case supported?
The obvious solution doesn't work. The following code triggers the "references other queries or steps, so it may not directly access a data source." error.
// Fetch the Fix Versions for a list of projects.
// The projects are fetched by the #"Projects" query from the same data source.
let
Next = Table.AddColumn(#"Projects", "Versions", each Json.Document(Web.Contents("...atlassian.net",
[RelativePath = "/rest/api/3/project/"&Number.ToText([project.id])&"/versions",
Headers= [#"Content-Type"="application/json"]]
)))
in
Next
where #"Projects" is a reference to a query which directly access the same "...atlassian.net" data source
Background
- The API doesn't provide for requesting a list of all Fix Versions without providing a project id, so that is not an option.
- I'm preparing a PowerBI dataset for uploading to the Online Service and so the "Ignore the Privacy levels and potentially improve performance" must remain Off. See Understand Power BI Desktop privacy levels - Power BI | Microsoft Docs
- The data source is set to Privacy "Organisational".
- And, if relevant, here is the source of the #"Projects" Query:
let
readPath = "/rest/api/3/project/search",
// This is how you handle paginated rest api's in Jira! Simples.
fetchbatch = (thisStartAt) =>
let
data = Json.Document(Web.Contents("https://...atlassian.net/",
[RelativePath = readPath,
Query = [startAt=Number.ToText(thisStartAt)],
Headers= [
#"Content-Type"="application/json"]]
)),
res = if not Record.Field(data,"isLast") then List.Combine({data[values],@fetchbatch(Record.Field(data,"maxResults")+Record.Field(data,"startAt"))}) else data[values]
in
res,
#"Converted to Table" = Table.FromList(fetchbatch(0), Splitter.SplitByNothing(), null, null, ExtraValues.Error),
// expand project fields
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "key", "name", "projectCategory"}, {"project.id", "project.key", "project.name", "project.projectCategory"}),
// expand category field
#"Expanded projectCategory" = Table.ExpandRecordColumn(#"Expanded Column1", "project.projectCategory", {"name"}, {"project.projectCategory.name"}),
// type project id to integer
#"Changed Type" = Table.TransformColumnTypes(#"Expanded projectCategory",{{"project.id", Int64.Type}})
in
#"Changed Type"
David
