Skip to main content
BackedHelp Centre

Reporting feed for analytics tools

Pull applications, funding rounds, applicants, contracts and milestones into Power BI or another analytics tool through the API, with a Power Query sample and a scheduled refresh.

Last updated

Power BI, Excel and other analytics tools can read your organisation's records through the Backed API and refresh them on their own schedule. There is no separate connector to install: the tool asks for a token with an API credential, then reads the records page by page. This article gives a Power Query (M) sample that does that for Power BI, and explains what the feed holds.

Backed offers no Power BI connector and sends nothing to Power BI. The API is pull-only: Power BI asks, and Backed answers with the data as at each request. A dashboard is only as current as its last refresh.

For a connector-based pull of ten record types, with filters and change tracking, see OData feed for BI tools and data warehouses. The developer site has guides for connecting Power BI and Tableau: see Developer documentation.

Before you start

  • Create a credential under Settings > Profile and terms, on the API Access tab, as API access explains. Read is enough; the feed never writes.
  • Keep the Client ID and Client Secret to hand. The secret is shown once.
  • Know your organisation's address: the one your team signs in at, such as https://your-subdomain.backed.giving.

What the feed holds

Table Read from What each row is
funding_applications GET /api/v2/funding_applications an Application, with its Funding Round's id, category and applicant
funding_rounds GET /api/v2/funding_rounds a Funding Round, including total_funding
registration_entities GET /api/v2/registration_entities an applicant organisation or person
contracts GET /api/v2/contracts a contract
milestones GET /api/v2/milestones a milestone, with its deliverables answers

Each also has a single-record path, such as GET /api/v2/funding_applications/:id. An Application's single record adds entry.inputs: every answer on its form, each with the field's internal_identifier.

The feed only ever holds your own organisation's records. Assessments, assessors, payments, funds and decisions are not in it: report on those in Backed with a data table.

Paging

A list returns at most 50 records per call, oldest first by id. Ask for the next page with gt set to the last id you received, and stop when a page holds fewer than 50 records. A limit above 50 is read as 50. For example, 120 submitted Applications come back in three calls:

GET /api/v2/funding_applications?submitted=1&limit=50              → 50 records
GET /api/v2/funding_applications?submitted=1&limit=50&gt=<last id> → 50 records
GET /api/v2/funding_applications?submitted=1&limit=50&gt=<last id> → 20 records

funding_applications also takes submitted=1, with_contract=1 and not_initial=1 to narrow the list, and extra_methods[] (requested_amount, recommended_amount, bank_account_number, registration_entity_id) to add those values to each row.

Answers keyed by Field Tag

An answer's internal_identifier is the name of its field's Field Tag, such as amount_requested. That is the same name the field's column has under Tagged Form Fields in a Data Table on Applications, so a column in Power BI and a column in a Backed report match by name. A field with no Field Tag is keyed field_ followed by its number. Tag the fields you report on before you build a report on them, and the column names stay the same across Funding Rounds.

A table or repeatable answer comes back as one input per cell, grouped by input_group_id; the sample puts each column's cells in one column, one per line.

Value formats

Amounts are text such as "12500.00", so change their column type to a decimal number in Power Query. Dates and times are in UTC (2026-11-08T03:15:00.000Z) and dates are YYYY-MM-DD.

Tokens and limits

A token lasts five days (expires_in: 432000). A call with an expired token answers 401 Unauthorized, and the fix is to ask for a new token. The sample asks for one at every refresh, so it never holds an old one. A credential may make 120 calls a minute; a call past that answers 429 with a Retry-After header, and the sample waits a minute and tries again.

Load the feed into Power BI Desktop

  1. In Power BI Desktop, select Get data, then Blank query.
  2. In the Power Query editor, select Advanced Editor, paste the sample below, and replace the three settings at the top with your organisation's address, Client ID and Client Secret.
  3. Select Done, and name the query Backed. It returns the five tables as one record.
  4. For each table you want, right-click Backed, select Reference, and in the formula bar make the step = Backed[funding_applications] (or funding_rounds, registration_entities, contracts, milestones). Name the query after the table.
  5. Set each amount column's type to a decimal number and each date column's to a date, then select Close & Apply.

If Power BI asks how to connect to your organisation's address, choose Anonymous: the sample sends its own token.

The sample reads every Application's single record to collect its answers, one call each, so a first load of a few thousand Applications takes a few minutes while it keeps under the limit.

let
    // Your organisation's address and your credential's pair.
    BaseUrl = "https://your-subdomain.backed.giving",
    ClientId = "your-client-id",
    ClientSecret = "your-client-secret",
    PageSize = 50,

    // 1. A token, asked for at every refresh (each lasts five 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],

    // One call, tried again after a minute when the rate limit answers 429.
    Get = (path as text, query as record) as any =>
        let
            Attempt = (tries as number) =>
                let
                    Response = Web.Contents(BaseUrl, [
                        RelativePath = path,
                        Query = query,
                        Headers = [Authorization = "Bearer " & Token],
                        ManualStatusHandling = {429},
                        IsRetry = tries > 0
                    ]),
                    Status = Value.Metadata(Response)[Response.Status]
                in
                    if Status = 429 and tries < 5
                    then Function.InvokeAfter(() => @Attempt(tries + 1), #duration(0, 0, 1, 0))
                    else Json.Document(Response)
        in
            Attempt(0),

    // 2. Every record of a resource: 50 a page, then gt = the last id, until a page is short.
    GetAll = (resource as text) as list =>
        List.Combine(
            List.Generate(
                () => [Page = Get("api/v2/" & resource, [limit = Text.From(PageSize)]), More = true],
                each [More],
                each
                    if List.Count([Page]) < PageSize
                    then [Page = {}, More = false]
                    else [
                        Page = Get("api/v2/" & resource, [
                            limit = Text.From(PageSize),
                            gt = Text.From(List.Last([Page])[id])
                        ]),
                        More = true
                    ],
                each [Page]
            )
        ),

    ToTable = (records as list) as table => Table.FromRecords(records, null, MissingField.UseNull),

    // 3. One Application's answers: one field per internal_identifier, a table's cells one per line.
    AnswersOf = (id as number) as record =>
        let
            Inputs = Get("api/v2/funding_applications/" & Text.From(id), [])[entry][inputs],
            Pairs = Table.FromRecords(
                List.Transform(Inputs, each [name = [field][internal_identifier], value = [value]]),
                type table [name = text, value = nullable text]
            ),
            Grouped = Table.Group(Pairs, {"name"}, {{"value", each
                let Values = List.RemoveNulls([value])
                in if List.IsEmpty(Values) then null else Text.Combine(Values, "#(lf)"),
                type nullable text}})
        in
            Record.FromList(Grouped[value], Grouped[name]),

    Applications = ToTable(GetAll("funding_applications")),
    WithAnswers = Table.AddColumn(Applications, "answers", each AnswersOf([id])),
    AnswerNames = List.Distinct(List.Combine(List.Transform(WithAnswers[answers], Record.FieldNames))),
    // An answer whose name is already a column (such as "title") is put under "answer.<name>".
    ColumnNames = List.Transform(AnswerNames, each
        if List.Contains(Table.ColumnNames(Applications), _) then "answer." & _ else _),
    FundingApplications = Table.ExpandRecordColumn(WithAnswers, "answers", AnswerNames, ColumnNames)
in
    [
        funding_applications = FundingApplications,
        funding_rounds = ToTable(GetAll("funding_rounds")),
        registration_entities = ToTable(GetAll("registration_entities")),
        contracts = ToTable(GetAll("contracts")),
        milestones = ToTable(GetAll("milestones"))
    ]

Nested values, such as an Application's registration_entity, arrive as record columns: select the expand button in a column's header to choose which of their values to show.

Refresh on a schedule

  1. In Power BI Desktop, select Publish and choose a workspace.
  2. In the Power BI service, open the workspace, find the semantic model, and open its Settings.
  3. Under Data source credentials, select Edit credentials for your organisation's address, choose Anonymous as the authentication method, and sign in.
  4. Under Refresh, turn on Configure a refresh schedule, choose how often and at what times, and select Apply.

Each refresh asks for a new token and reads the whole feed again. If a refresh fails with 401 Unauthorized, the credential has been deleted or its secret is wrong: create a new credential and update the ClientId and ClientSecret in the sample. Keep the .pbix file to people who may see the credential's secret, as it is written in the query.

What the feed is not

  • No connector. There is nothing to install in Power BI, and no Backed connector in its Get data list.
  • No push, webhook or stream. Backed never sends data to Power BI or to any other reporting tool. Power BI has only what its last refresh read.
  • Read only. A credential with only the Read scope can't change anything in Backed.

See also

This page uses Backed's default terms, such as Funding Round and Application Template. An organisation may rename them, so read each as your organisation's own term if you've renamed it.