Integration Details

Power BI

Pull form submissions into Power BI over an API-key-authenticated JSON feed.

Talk to us

Overview

Monival exposes a submission feed per form that returns JSON in an OData-shaped envelope, authenticated with a scoped API key. It supports column selection, sorting, paging and a total row count, which is what you need to build a refreshable Power BI dataset over live submission data.

Connect it with the Web connector

This is the most important practical detail on the page, so it goes first.

Use Power BI’s Web / JSON connector. Do not use the native OData Feed connector.

The feed returns an OData-shaped response — a value array, and @odata.count when requested — but it is not a fully conformant OData service. Two specific gaps, both real:

  1. There is no $metadata document. The OData Feed connector begins by fetching $metadata to discover the entity model. There is nothing at that address, so the connector fails at the first step. This is not a configuration problem you can work around within that connector.
  2. $filter is accepted and silently ignored. The parameter is present on the endpoint and will not raise an error, but no filtering is applied to the result. A query that appears to request approved submissions only will return everything, up to the page limit. Filtering must be done in Power Query after the data lands.

We would rather you read that here than discover it in a report that has been quietly over-counting for a quarter.

What the feed does support

  • $select — choose which question fields to return. Submission timestamp, status and collection metadata are always included alongside your selection.
  • $orderby — sort by submittedAt or by a question field, ascending or descending. Defaults to newest submission first.
  • $top — page size. Defaults to 100 if you do not set it.
  • $skip — offset, for paging through a form with more submissions than one page.
  • $count — when set, adds @odata.count with the total number of submissions, which is what you page against.

Response shape

Rows come back flattened, which is what makes them usable in Power BI without a transformation step:

  • _id, submittedAt, status, respondentName
  • every answer, as a top-level column keyed by question
  • gps_lat, gps_lng, gps_address — where GPS was captured
  • duration — how long the submission took to complete

Setting it up

  1. Create an API key in Monival, scoped to the project and to read access, with an expiry date.
  2. In Power BI Desktop choose Get Data → Web, and provide the feed URL for the form together with the API key header.
  3. Set $top and $skip deliberately. The 100-row default will otherwise quietly truncate your dataset. Build a Power Query paging loop driven by @odata.count for any form with more than a page of submissions.
  4. Do all filtering in Power Query, for the reason given above.
  5. Model, visualise, and configure scheduled refresh.

Practical notes

  • Question keys, not labels, are the column names. Renaming a question’s label in Monival does not change the key, which is good for report stability — but it means your Power BI columns will not automatically follow a label change. Map them once in Power Query.
  • Form versions. Every submission is stamped with the form version it was collected on. If a form changed mid-round, the same column may hold answers to two different questions; segment by version in your model rather than pooling blindly.
  • Key rotation. API keys can carry an expiry. Put the rotation date in a calendar — a dataset refresh failing on an expired key at 6am is an avoidable morning.