A scheduled refresh loads the query result again and replaces the table. To keep history and download only questionnaires closed in the last days, use incremental refresh. The service stores older rows in partitions and re-queries the recent window on each refresh.
The service passes that window through two parameters, <obj>RangeStart</obj> and <obj>RangeEnd</obj>. <obj>RangeEnd</obj> is exclusive. The feed accepts an inclusive range on the property <obj>ClosedAt</obj>:
<obj>ClosedAt ge 2026-01-01T00:00:00.000Z and ClosedAt le 2026-01-01T23:59:59.999Z </obj>
The same timestamp is the column <obj>closed_at</obj>. Put the filter on the request. A filter step on <obj>closed_at</obj> is sent as <obj>closed_at ge</obj> / <obj>closed_at lt</obj>, and the service responds with HTTP 400.
In Power Query, create two parameters of type Date/Time. The names are case-sensitive:
| Parameter | Preview value in Desktop |
|---|---|
| <obj>RangeStart</obj> | Start of a short window, for example yesterday at 00:00:00 |
| <obj>RangeEnd</obj> | End of that window, for example today at 00:00:00 |
Use a short preview window so Desktop does not download the whole survey while you author the model. After publishing, the service replaces both values for each partition.
Keep the request URL static, including the project id and the <obj>Responses</obj> entity set. Pass the window as the query options <obj>closed_from</obj> and <obj>closed_before</obj>. closed_from is inclusive and takes <obj>RangeStart</obj>. <obj>closed_before</obj> is exclusive and takes <obj>RangeEnd</obj>, so a row on the boundary belongs to the next partition.
<code-block>
let
// Replace <project id> with the encoded id (GET /external/api/v1/projects → id).
FormatInstant = (value as datetime) as text =>
DateTime.ToText(value, [Format="yyyy-MM-dd'T'HH:mm:ss.fff'Z'", Culture="en-US"]),
Source = OData.Feed(
"https://dc.2ask.com/external/api/v1/projects/<project id>/responses/odata/",
null,
[
Implementation = "2.0",
Query = [
closed_from = FormatInstant(RangeStart),
closed_before = FormatInstant(RangeEnd)
]
]
),Responses_table = Source{[Name="Responses",Signature="table"]}[Data]
in
Responses_table
</code-block>
Load <obj>ValueLabels</obj> with a separate query and without these parameters. It is the label table and stays a full load.
Optionally check the request in Power Query diagnostics. The URL contains closed_from=…Z and closed_before=…Z. Leave Detect data changes off, and do not add a real-time DirectQuery partition.
In Model view, open Incremental refresh on <obj>Responses</obj>:
The first refresh in the service loads every partition in the archive. Each partition is its own filtered request, and each page inside it counts as another request (default limit 600 per day). A long daily archive can stop on HTTP 429 before the model is filled. Later refreshes request only the incremental window.
<obj>RangeStart</obj> and <obj>RangeEnd</obj> follow the time zone under Semantic model settings → Refresh. The default is UTC, which matches <obj>ClosedAt</obj>. Keep UTC. The query marks the parameter values with <obj>Z</obj>.
Publish the report. Under Semantic model settings, set the data source credentials to Basic. The user name is the API key. Leave the password empty. If the service refuses to save an empty password, enter any placeholder. The API ignores it.
Schedule the refresh once a day. The feed is public HTTPS, so no on-premises gateway is required.
Publishing the same <obj>.pbix</obj> again deletes the partitions already stored in the service and runs another full archive load. After the first successful refresh, change the model through a metadata deployment, or accept a new full load.