HelpID:LocalArchive

Overview

In some use cases, it is not possible to use OdataLink's inbuilt archive to improve performance and reduce refresh time.

This may be due to organisational policies, subject to approval, or operational decisions.

A Local Archive keeps previously downloaded data in your report (Power BI or Excel) and fetches only the most recent period live from your Data Files on each refresh.

Older records are read from the archive instead of being downloaded again.

Why create a Local Archive

1. Lower Data Usage and Egress

By default, every refresh re-downloads your full history. Older transactions rarely change, so re-fetching them each time spends your monthly usage allowance for no benefit.

A local archive fetches only the recent window live and keeps the rest locally. See What is Usage.

2. Stay within Xero's API limits

Xero throttles data file refreshes (for example 5,000 calls per day and 60 per minute), and some endpoints are paged (Journals return 100 records per page regardless of your model's page size).

Pulling only recent data on each refresh keeps you comfortably under these limits and avoids rate-limit errors (9004 to 9008).

3. Faster, more reliable refreshes

Less data per refresh means quicker refreshes and fewer timeouts, especially for larger datasets.

4. Preserve history

Your data stays in the model even if it ages beyond your OdataLink model's Default Start Date, exceeds a report limit, or the live connection is temporarily unavailable.

5. If historical data does not change, do not re-download it

Posted historical transactions are stable, and Xero journals are immutable (a correction is posted as a new journal, never an edit).

Downloading the same historical rows on every refresh is redundant.

How to build a Local Archive

1. Create a parameter in your Power Query, MonthsToRefresh which indicates how many recent months' data is fetched live.
2. A cut-off date ArchiveFrom can be calculated by reducing the MonthsToRefresh from today's date.
3. Rows recorded on or after ArchiveFrom will be considered Live (re-fetched on each refresh).
4. Rows before ArchiveFrom date are history (retained from the archive).
5. The two datasets are then combined into the final table.

Power Query

STEP 1: Create ArchiveFrom parameter

MonthsToRefresh = 3

STEP 2: Calculate Cut-off date

let
     Today = Date.From(DateTimeZone.FixedLocalNow()),
     ArchiveFrom = Date.AddMonths(Today, -MonthsToRefresh)
in
    ArchiveFrom

STEP3: Split the dataset and combine

let
    // Live data: the most recent MonthsToRefresh months
    KeepLive = Table.SelectRows(Source, each Date.From([Date]) >= ArchiveFrom),

    // History: older than the cut-off
    KeepHistory = Table.SelectRows(Source, each Date.From([Date]) < ArchiveFrom),

    // Combine history + live
    Combined = Table.Combine({KeepHistory, KeepLive})
in
    Combined

Note: Apply this filter right after Navigation step so that the filter flows though OdataLink and filters data at the source.

See Model Date Filters and Report Date Filters

Make it a True Archive

In a single file, KeepHistory and KeepLive read from the same Source, so they recombine the same rows. To actually persist history and fetch only recent data live, point each branch at a different source:

• KeepHistory: a stored copy that is not refreshed (a Power BI dataflow, a saved CSV/Parquet, or a separate archive query).
• KeepLive: the live OdataLink feed, filtered to >= ArchiveFrom.

In Power BI, the productionised equivalent is Incremental Refresh, which uses RangeStart/RangeEnd parameters to retain old partitions and refresh only recent ones.

Related topics

• Model Date Filters and Report Date Filters
• Usage