Power BI
You can pull ScopeStack data into Power BI and refresh it on a schedule using the ScopeStack API and Power Query. No data gateway is required.
The old ScopeStack Power BI custom connector is retired. If you find an older guide describing a
Scopestack.mezorScopestackSandbox.mezfile, that connector is no longer supported and the instructions no longer apply. The approach on this page replaces it, and it does not require a gateway or any change to Power BI’s extension security settings.
Before you start
You need a service account with client credentials.
- Go to Settings > Users & Groups > Users and create a new user with the User Type set to Service. Do not change an existing person’s user type — a converted user keeps a working login but loses access to the application. See Service Account Credentials for the full setup.
- Give the service account a read-only role. This report only reads, and a read-only role limits what the credential can do if it is ever exposed.
- Open the account’s Client Credentials tab and create a credential. Copy the Client ID and Client Secret — the secret is shown only once.
Creating credentials requires Manage rights on Settings > Account. If you do not see the option, ask an administrator on your account.
Service accounts do not count against seat-based pricing.
Build the query
- In Power BI Desktop, choose Home > Get Data > Blank Query.
- Choose Home > Advanced Editor.
- Replace the contents with the script below and choose Done.
- Fill in
ClientIdandClientSecretat the top. - When Power BI asks how to connect to
api.scopestack.ioandapp.scopestack.io, choose Anonymous for both.
“Anonymous” does not mean unauthenticated. It means Power BI is not holding a credential for that host. The requests are still fully authenticated: your client credentials are sent in the token request, and every data call carries the access token that comes back. Power Query also only permits POST requests anonymously, so this is a requirement rather than a preference.
let
// ---------- Settings ----------
ClientId = "PASTE_YOUR_CLIENT_ID",
ClientSecret = "PASTE_YOUR_CLIENT_SECRET",
PageSize = 250,
// ---------- 1. Get an access token ----------
TokenResponse = Json.Document(
Web.Contents(
"https://app.scopestack.io",
[
RelativePath = "oauth/token",
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(
"grant_type=client_credentials"
& "&client_id=" & Uri.EscapeDataString(ClientId)
& "&client_secret=" & Uri.EscapeDataString(ClientSecret)
)
]
)
),
AccessToken = TokenResponse[access_token],
AuthHeaders = [#"Authorization" = "Bearer " & AccessToken],
// ---------- 2. Fetch one page of projects ----------
GetPage = (pageNumber as number) as record =>
Json.Document(
Web.Contents(
"https://api.scopestack.io",
[
RelativePath = "v2/projects",
Query = [
#"page[number]" = Number.ToText(pageNumber),
#"page[size]" = Number.ToText(PageSize)
// To pull only recently changed projects, add:
// , #"filter[updated_at.after]" = "2026-01-01"
],
Headers = AuthHeaders
]
)
),
// ---------- 3. Read the page count, then pull every page ----------
FirstPage = GetPage(1),
PageCount = Number.From(FirstPage[meta][#"page-count"]),
AllPages = List.Transform({1..PageCount}, each if _ = 1 then FirstPage else GetPage(_)),
AllRecords = List.Combine(List.Transform(AllPages, each _[data])),
// ---------- 4. Flatten into a table ----------
AsTable = Table.FromList(AllRecords, Splitter.SplitByNothing(), {"row"}),
WithIds = Table.ExpandRecordColumn(AsTable, "row", {"id", "attributes"}, {"project-id", "attributes"}),
FieldList = List.Distinct(
List.Combine(
List.Transform(
AllRecords,
each Record.FieldNames(Record.FieldOrDefault(_, "attributes", []))
)
)
),
Padded = if List.IsEmpty(FieldList) then WithIds
else Table.TransformColumns(
WithIds,
{{"attributes", each Record.SelectFields(_, FieldList, MissingField.UseNull)}}
),
Projects = if List.IsEmpty(FieldList) then Table.RemoveColumns(Padded, {"attributes"})
else Table.ExpandRecordColumn(Padded, "attributes", FieldList, FieldList)
in
ProjectsThe script uses a fixed base URL with RelativePath and Query kept separate rather than building one long URL string. That is deliberate: it keeps the Power BI Service from classifying the query as a dynamic data source, which would block scheduled refresh.
Troubleshooting in Power BI Desktop
These are the four things that actually go wrong on a first setup, in the order they tend to appear. Everything here happens before you publish; the Scheduled refresh section below covers what breaks afterwards.
”This table is empty” and no error
Your first step is fetching a web page rather than requesting a token. Open Query1 > Advanced Editor and look at the very top of the script. If there is a step above the opening let from the guide, such as:
Html.Table(Web.Contents("https://app.scopestack.io"), {})then the query was started through Get Data > Web instead of Blank Query, and Power BI’s wizard-generated first step survived when the script was pasted underneath it. That step fetches our marketing homepage, parses it as an HTML table, and never requests a token, so it returns nothing without raising an error.
Select everything in Advanced Editor, delete it, paste the whole script from this page, and re-enter your Client ID, Client Secret and page size at the top.
A 401, or a token request that never succeeds
Check what is actually in the ClientId and ClientSecret lines. They want a credential generated from Settings > Users & Groups > Users > (the service account) > Client Credentials, not the service account’s own login and password. Both values are 43-character random strings and the secret is shown only once. If what is pasted in looks like an account name or a password you chose, no client credential was ever created.
DataSource.Error on the token step, with settings that look correct
Power Query only allows a POST request on a connection set to Anonymous, and the token step is a POST. Microsoft’s Web.Contents reference states it directly: “POST requests may only be made anonymously.”
This error still fires for people who have already set both hosts to Anonymous, which makes it look like nothing is wrong. Power BI keeps a global credential list alongside the per-file one, and it can attach a credential to a URL below the host level, so an entry created during an earlier attempt can shadow the one you can see. The error message names the path it is unhappy about, usually ending in oauth/token.
Clear it in both lists:
- File > Options and settings > Data source settings.
- Check Data sources in current file, then switch to Global permissions. That is the second radio button and a separate list.
- Clear permissions on every entry beginning with
app.scopestack.io. - Run the query and choose Anonymous when prompted.
Clearing permissions also clears the privacy level, so set both hosts back to Organizational if you are asked. Credentials are cached for the session, so if the error survives all of this, close Power BI Desktop and reopen it.
The column 'access_token' of the table wasn't found
This one is usually caused by trying to fix the previous error with the Edit Settings button that Power BI offers alongside it.
Do not use Edit Settings on this query. That dialog has no field for a POST body, so accepting it rewrites the token step and drops the request. It removes RelativePath = "oauth/token" so the call goes to app.scopestack.io itself, removes the entire Content block carrying your client ID and secret, and replaces Json.Document with Web.Page. The query then loads our sign-in page and parses it as HTML, which is why the step returns a table of Caption, Source, ClassName, Id and Data columns, and why access_token is not among them.
Open Advanced Editor and restore the token step:
TokenResponse = Json.Document(
Web.Contents(
"https://app.scopestack.io",
[
RelativePath = "oauth/token",
Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
Content = Text.ToBinary(
"grant_type=client_credentials"
& "&client_id=" & Uri.EscapeDataString(ClientId)
& "&client_secret=" & Uri.EscapeDataString(ClientSecret)
)
]
)
),Or paste the whole script from this page again and re-enter your credentials. Clear the credential first, then restore the step. Doing it the other way round runs the query against the stale credential, triggers the same error, and puts you back on the same dialog.
When a step on this query errors, fix it in Advanced Editor rather than through Edit Settings.
Scheduled refresh
Three settings decide whether the report refreshes after you publish. The first two are easy to miss, because everything works in Power BI Desktop without them and only fails once the report is in the Service.
1. Privacy levels (required)
The token comes from app.scopestack.io and the data comes from api.scopestack.io. Power Query treats that as data moving between two sources, which its Data Privacy Firewall governs. Both connections must carry the same privacy level, or refresh fails with a Formula.Firewall error.
Set both to Organizational: gear icon > Manage connections and gateways > Connections > select the connection > Settings > General > Privacy level. You will need to re-enter credentials to save.
Two things worth knowing: Power BI Desktop does not publish privacy settings, so setting them on your machine does not carry them up. And Desktop’s “Ignore the Privacy Levels” option has no effect on a published semantic model, so it is not a way around this.
2. Credentials
Set the data source credentials for both hosts to Anonymous in the dataset settings after publishing.
3. If the credential test blocks you
The Service tests the base URL on its own, without the query string, so a base URL that returns an error standalone can prevent you from saving credentials. Some tenants show a Skip test connection checkbox in the data source settings, which is the escape hatch for this. It is not present in every tenant. If you do not see it, contact support with the error you are seeing.
Notes
Token lifetime. Access tokens last 24 hours and no refresh token is issued. The script requests a fresh token as part of each refresh, so there is nothing to maintain. Power Query may evaluate a query more than once per refresh, so expect more than one call to the token endpoint each time.
Where the secret lives. The client secret is stored inside the published semantic model and is readable by anyone with edit or download rights on the workspace. Restrict workspace access accordingly, and rotate the credential when someone with that access no longer needs it. Rotating means updating the query and republishing. Paired with a read-only service account, the worst case stays read-only.
Page size. 250 is a good default. The maximum the API accepts is 1000.
Column types. Columns arrive untyped. Set data types once in Power BI and they persist.
Other data. The same pattern works for any ScopeStack API collection. Change RelativePath from v2/projects to the collection you want and reuse the token step unchanged. See API Documentation for what is available.
Check your field mapping before you report on ownership. Fields such as sales-executive-name and presales-engineer-name return whoever is set in those native project fields. If your team tracks account executives or solution specialists in project variables instead, report from project-variables rather than the native fields.
New to ScopeStack?
ScopeStack automates scoping, pricing, and SOW generation for IT services teams. See how it fits your process.