Power BI

You can import Shout form responses into Microsoft Power BI using Power Query’s Web connector. The connection is pull-based: Power BI calls the Shout API when the dataset is refreshed.

This guide covers form responses. The same approach can be adapted for the other read endpoints in the API Reference.

Before you start

You need:

• A Shout account on a plan with API access.
• The form ID for the form whose responses you want to report on.
• Power BI Desktop, and permission to publish and refresh datasets in your Power BI workspace.

Create a read-only Shout key

Create a separate key for Power BI under Developers → API keys. Give it only these scopes:

• forms:read — if Power BI will discover or read form metadata.
• responses:read — to read submissions.

Copy the key when it is shown. Shout displays the full value only once. Treat it as a password: do not put it in a URL, commit it to source control, or share a PBIX file containing the real key.

Create the Power BI query

In Power BI Desktop:

1. Select Get data → Blank query.
2. Open Advanced Editor.
3. Replace the query with the example below.
4. Replace YOUR_FORM_ID with the form ID.
5. Replace ShoutApiKey with the API key, or preferably with a Power Query parameter containing the key.
6. Select Done, then load the resulting table into the model.

The query uses a fixed base URL and RelativePath, which keeps the data source stable when the query is published. It also follows Shout’s cursor pagination until all response pages have been retrieved.

let
    BaseUrl = "https://publicapi.shout.com",
    ApiKey = ShoutApiKey,
    FormId = "YOUR_FORM_ID",

    GetPage = (Cursor as nullable text) as record =>
        let
            Query =
                if Cursor = null then
                    [limit = "100"]
                else
                    [limit = "100", cursor = Cursor],
            Response = Json.Document(
                Web.Contents(
                    BaseUrl,
                    [
                        RelativePath = "v1/forms/" & FormId & "/responses",
                        Query = Query,
                        Headers = [Authorization = "Bearer " & ApiKey],
                        Timeout = #duration(0, 0, 2, 0)
                    ]
                )
            )
        in
            Response,

    Pages = List.Generate(
        () => GetPage(null),
        each _ <> null,
        each
            if Record.FieldOrDefault(_, "nextCursor", null) = null then
                null
            else
                GetPage([nextCursor]),
        each [items]
    ),

    Rows = List.Combine(Pages),
    Responses =
        if List.IsEmpty(Rows) then
            #table({}, {})
        else
            Table.FromRecords(Rows),
    ShapedResponses =
        if List.IsEmpty(Rows) then
            Responses
        else
            let
                #"Expanded answers" = Table.ExpandListColumn(Responses, "answers"),
                #"Expanded answers1" = Table.ExpandRecordColumn(
                    #"Expanded answers",
                    "answers",
                    {"questionId", "values"},
                    {"questionId", "values"}
                ),
                #"Expanded values" = Table.ExpandListColumn(#"Expanded answers1", "values"),
                #"Expanded values1" = Table.ExpandRecordColumn(
                    #"Expanded values",
                    "values",
                    {"optionId", "label", "text", "number", "date"},
                    {"optionId", "label", "text", "number", "date"}
                )
            in
                #"Expanded values1"
in
    ShapedResponses

Web.Contents supports request headers, RelativePath, query parameters and a custom timeout. See Microsoft’s Web.Contents documentation.

Store the key as a parameter

For a quick local test, ShoutApiKey can be a text value in the query. For a report that will be shared or published, create a Power Query parameter named ShoutApiKey and keep the real value out of the query text. After publishing, configure the dataset’s data-source credentials in the Power BI service and confirm that the scheduled refresh succeeds.

Do not distribute a PBIX file containing a live production key. If the key is exposed, revoke it in Shout and create a replacement.

Shape the response data

The API returns one row per response. The answers column contains a list of question answers, and each answer can contain several value fields depending on the question type. The example expands both nested lists so that the final table contains one row per answer value. If you prefer one row per response, return Responses instead of ShapedResponses at the end of the query.

The expansion keeps questionId so that answers can be matched to the question definitions from GET /v1/forms/{id}. The Responses guide explains the answer value shapes.

If you need readable question titles alongside answers, import the form definition separately and join on the question ID. The response payload does not repeat the complete question definition for every answer.

Refresh and incremental loads

The example performs a full paginated read for one form. For a small or medium response set, that is usually the simplest starting point.

For larger datasets, adapt the first-page query to use Shout’s since and until filters and use Power BI incremental refresh where appropriate. Keep the cursor for walking a single API response and use since for finding responses added after the previous load; cursors are not date checkpoints.

Power BI scheduled refresh is subject to the credentials and data-source rules of the customer’s Power BI tenant. Test a refresh after publishing rather than assuming that a query which works in Desktop will refresh unchanged in the Power BI service. Microsoft’s Power BI Web connector guide documents the supported connection options.

Limits and design notes

• Shout returns at most 100 items per page; the query follows nextCursor until it is null.
• Each API key is limited to 120 requests per minute. Avoid refreshes that repeatedly reload very large datasets at the same time.
• This is an import integration, not a live DirectQuery connection.
• API keys are tenant-scoped and should be dedicated to the report or reporting process.
• Form response and contact data may contain personal data. Apply the customer’s Power BI workspace, dataset and report permissions accordingly.
• Shout does not currently provide a native Power BI connector. The Web connector plus Power Query is the supported integration pattern.