Skip to main content

Developers · Guide

Connect Power BI

Load the OData reporting feed into Power BI Desktop, with Basic authentication or a token query, and refresh it on a schedule.

Power BI reads your organisation’s records through the OData reporting feed, the same records your Backed reports read, and only your own organisation’s.

Before you start

  • A credential with the read:reporting_feed scope (`Read reporting feed` on the `API Access` tab). Only an Administrator who can generate reports can give it. Without it the feed answers `403 insufficient_scope`.
  • Your organisation’s address plus /odata/v4/, such as https://your-org.backed.giving/odata/v4/.

The feed is OData v4. /odata/v4/ lists its entity sets and /odata/v4/$metadata describes every column. The entity sets are Applications, Applicants, RegistrationEntities, FundingRounds, Assessments, Decisions, Contracts, Milestones, Payments, Funds. The OpenAPI document points to the feed (x-odata-feed) but does not describe it: $metadata does.

Signing in

A tool that can only ask for a user name and password signs in to the feed with the credential itself: the Client ID as the user name and the Client Secret as the password. The pair is checked on every request, so there is no token to renew. Only the feed takes a user name and password; the rest of the API takes a token alone. A tool that can send headers may instead send Authorization: Bearer <token>, with a token from POST /oauth/token.

With the OData feed connector

  1. In Power BI Desktop, select Get data, then OData feed.
  2. Under URL, enter https://your-org.backed.giving/odata/v4/ with your organisation’s address, and select OK.
  3. When Power BI asks how to connect, choose Basic. Enter the Client ID as the User name and the Client Secret as the Password, and select Connect.
  4. In the Navigator, tick the entity sets you want, then select Load or Transform Data.

With a token query

If you would rather the feed see a token, start from Get data, Blank query, open Advanced Editor, paste this query, and replace the three settings at the top. If Power BI asks how to connect to your organisation’s address, choose Anonymous: the query sends its own token.

Power Query (M)
let
    // Your organisation's address and your credential's pair.
    BaseUrl = "https://your-org.backed.giving",
    ClientId = "your-client-id",
    ClientSecret = "your-client-secret",
    // A token, asked for at every refresh (each lasts 5 days).
    Token = Json.Document(
        Web.Contents(BaseUrl, [
            RelativePath = "oauth/token",
            Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],
            Content = Text.ToBinary(Uri.BuildQueryString([
                grant_type = "client_credentials",
                client_id = ClientId,
                client_secret = ClientSecret
            ]))
        ])
    )[access_token],
    // The feed, with the token on every request it makes.
    Feed = OData.Feed(
        BaseUrl & "/odata/v4/",
        null,
        [Headers = [Authorization = "Bearer " & Token], Implementation = "2.0"]
    )
in
    Feed

Refresh on a schedule

  1. In Power BI Desktop, select Publish and choose a workspace.
  2. In the Power BI service, open the semantic model’s Settings.
  3. Under Data source credentials, select Edit credentials: Basic with the pair for the connector, Anonymous for the token query.
  4. Under Refresh, turn on Configure a refresh schedule, choose the times, and select Apply.

With Basic, every refresh signs in with the pair; the token query asks for a new token at every refresh. Either way the 5-day token limit never stops a refresh. A refresh that fails with 401 means the credential was deleted or its secret is wrong; with 403, it has lost read:reporting_feed.

Paging and query options

  • A page holds up to 1000 rows and ends with @odata.nextLink while more remain. Power BI follows it for you.
  • Filters and column choices you make in Power Query are sent as $filter and $select, so only what you keep is read. $orderby, $top and $count are supported too.
  • A step the feed cannot answer, such as Remove Top Rows (sent as $skip) or Begins With (sent as startswith), fails with 501 not_implemented. Put it after a Table.Buffer step, so Power BI applies it to rows it has already read.

Every column, every query option and the change-tracking read for warehouse loaders are in OData feed for BI tools and data warehouses. Tableau is in Connect Tableau.