I am building a data model querying azure devops APIs. The current focus is some git queries on repos, projects, branches, commits etc - quite large queries and due to formula firewall and the inability to fold on rest API/JSON queries (at least as far as I have been able to gather?).
For example I am querying the same upstream items like repos again times to use them as function variables to then call commits APIs. Because we have over 12 million commits in scope, and the API only returns 100 results by default, I need to use a $top querystring, so to get all commits by branch I would need to use something crazy with a nice ceiling like $top=1000000. managed to get that to load once in PowerBI desktop but it was pretty unsustainable.
Now I have parameterised $top with a view to get it nice and low in desktop, perhaps as low as $top=10 and then change it once published to the service.
I find that the parameter is being ignored on the service though:
If I publish through my GIT enabled workspace, where the format is a PBIP and the dataset is zeroed out when commiting to or syncing from GIT, the API returns the default 100 regardless of what I had set in desktop.
If I publish a PBIX to a non GIT enabled workspace, it retains the $top amount I had specified in desktop, but cannot be changed.
I have been doing some reading on parameter issues and have checked the following potential issues:
Ensure the datatype is not 'any' - check - it's text.
Ensure it is not mandatory - I wasn't sure if this one applied to my situation but check - it's non mandatory and specified.
Below is my function and query code, and some screenshots of the parameter UIs in desktop and service.
let Source = (repoId, branchId, top) =>
Source = VSTS.Contents("https://dev.azure.com/org/project/_apis/git/repositories/"& repoId &"/commits?searchCriteria.itemversion.version="& branchId &"&searchCriteria.$top"& top &"&api-version=7.0"),
#"Imported JSON" = Json.Document(Source,65001),
value = #"Imported JSON"[value]
Query (...skipping intermediate steps for brevity...[repoId] and [branchName] are queried from APIs and functions and used directly as columns in invoking the fucntion below)
I'd really appreciate any ideas anyone might have here. I think I am starting to see I need to build dataflows for this anyway, but I'd like to at least know how to get this working for simple tasks in the future. I am starting to wonder if maybe parameters just aren't supported to use in this way?