Twinfield in Excel and Power Query via OData

Load Twinfield data into Excel with Power Query

A Twinfield Power Query connector lets you load transactions, debtors, creditors, and general ledger accounts into Excel without rebuilding a CSV process for every reporting period. DigiData synchronizes your selected Twinfield offices and makes the supported tables available through a stable OData service root. Power Query stores your filters, data types, column names, and other transformation steps so the workbook can apply them again during refresh.

This guide focuses on Excel: connect to the feed, select tables, transform the data, choose how to load it, and refresh the workbook. If you are building an interactive dashboard, follow the separate guide to connect Twinfield to Power BI.

Why OData fits recurring Excel reports

Excel includes Power Query and an OData connector, so you do not need a separate database driver. You enter the DigiData service-root URL, authenticate, and select the Twinfield tables shown in the Navigator. DigiData handles the source synchronization while Power Query runs the saved transformations in the workbook.

This separation is useful for recurring checks. The source tables can refresh without replacing a file path or repeating copy-and-paste steps. Read the technical explainer if you first want to understand what an OData feed is.

Prerequisites

Confirm that the Twinfield user can access the intended offices and that those offices are selected in DigiData. You also need the DigiData OData service-root URL, the credentials provided for that feed, and an Excel version with Power Query and the OData Feed connector.

For the first connection, use the service root without filters added to the URL. Select tables in the Navigator and create filters in Power Query. This keeps the source definition clear and makes credential scope easier to diagnose.

Which Twinfield tables can you load?

Relevant finance tables include transaction headers, transaction lines, debtors, creditors, general ledger accounts, cost centers, projects, offices, and VAT codes. Availability depends on Twinfield access, the selected offices, and the synchronized source data.

For a first Excel report, choose only the tables needed for one control or analysis. A general-ledger check may need transaction lines, account data, offices, and dates. A receivables overview also needs debtors. Loading every table makes the workbook harder to understand and refresh.

Step by step: connect Excel to Twinfield OData

Step 1: open the OData Feed connector

Open the workbook and select Data, Get Data, From Other Sources, and From OData Feed. Menu labels can differ slightly between Excel versions, but the connector is listed as OData Feed.

Step 2: enter the service-root URL and authenticate

Paste the DigiData service-root URL and continue. Choose the authentication method specified for your feed and enter the corresponding credentials. Apply the credential scope to the service root so the authorized tables below that root can be opened from the same connection.

Step 3: choose tables in the Navigator

The Navigator shows the tables described by the OData metadata. Select the Twinfield tables required for the workbook and choose Transform Data instead of immediately loading everything into worksheets.

Step 4: save the transformations

In Power Query, remove unused columns, filter periods, confirm data types, and give queries recognizable names. Keep account and office codes as text when leading zeroes or formatting matter. Apply transformations in a consistent order so another maintainer can understand the query steps.

Step 5: choose where the data loads

Use Close & Load To to decide whether a query becomes a worksheet table, feeds a PivotTable, or remains a connection for the Excel Data Model. Large transaction tables do not always belong directly on a worksheet. Load only the detail that users need to inspect there.

Step 6: refresh the workbook

Use Refresh All to retrieve the latest data available from the OData feed and reapply the saved Power Query steps. The result reflects the most recent completed DigiData synchronization, not a live request to Twinfield at the moment a cell is opened.

Excel and Power BI use the same Power Query foundation

The same OData connector and transformation language are available in Excel and Power BI Desktop, but the reporting workflows are different. Excel suits working models, reconciliations, and defined analysis sheets. Power BI adds a semantic model, interactive reports, workspace publishing, and scheduled cloud refresh.

Keep this workbook focused on Excel transformations and refresh. The Twinfield Power BI guide covers model relationships and refresh in Power BI Service.

OData or CSV for Excel?

Use OData when the workbook needs to load the same tables repeatedly and preserve its transformation steps. This fits month-end controls, recurring reconciliations, and management workbooks whose source data changes over time.

Use CSV for a one-off check, a fixed archive, or a limited dataset shared with someone who should not refresh the source. CSV is a snapshot, so every update requires a new export and a reliable process for replacing or combining files.

For a broader decision that also covers a direct API and MCP, use the Twinfield integration comparison.

Fix refresh and merge problems

If Excel reports an authentication error, open Data Source Settings, select the OData source, and review its permissions and credentials. Confirm that the saved source uses the service-root URL rather than a path to one table with manually added query options.

If a query is slow, reduce rows and columns before merging tables. Load related tables separately when expanding navigation columns produces many additional requests. When a merge repeatedly reads a small lookup query, buffering that smaller query can help in some models. Test it before keeping it because buffering uses local memory and can prevent later filters from being passed to the source.

If records appear stale, compare the DigiData synchronization time with the workbook refresh time. Refreshing Excel cannot include Twinfield changes that have not yet been synchronized into the feed.

Validate finance data after refresh

Compare a new workbook with Twinfield before relying on it for decisions. Start with simple totals by period and office, such as revenue, costs, debtors, and creditors. Add dimensions only after those totals match the intended source view.

Be precise about dates. Invoice date, posting date, due date, and accounting period can answer different questions. Document which field controls each worksheet or PivotTable.

Use stable IDs and office codes for relationships and lookups instead of names. Names and descriptions can change, while a documented identifier is less likely to break a recurring join.

When a managed OData feed fits

A managed feed is useful when several workbooks, users, or offices depend on the same synchronized source tables. It keeps Twinfield authentication, pagination, rate limits, and source error handling outside the individual Excel file. Power Query remains responsible for the transformations used by that workbook.

A direct or local connector may still suit a small isolated model. The choice depends on how often the workbook refreshes, how many offices it covers, and who will maintain the source connection.

Common Power Query mistakes

Avoid embedding a direct Twinfield API client in Power Query without a maintenance plan. Token renewal, pagination, and error handling then become hidden dependencies inside a finance workbook.

Do not load only transaction lines when the analysis needs general ledger accounts, projects, offices, or VAT codes to explain the amounts. Do not join on display names when stable keys are available.

Keep transformation steps readable. Remove unused columns, name queries consistently, and document filters that exclude offices or periods. A workbook is not ready for recurring use if only its creator knows why a total is different.

Checklist before sharing the workbook

Confirm that all required Twinfield offices are selected and synchronized. Reconcile totals for a fixed period and office. Verify data types for dates, amounts, and identifiers. Check whether coworkers who need to refresh the workbook have access to the OData feed. Record when DigiData synchronizes and when users should refresh the workbook.

Summary

A Twinfield Power Query connector makes recurring Excel reporting less dependent on manual exports and custom API code. DigiData supplies the selected Twinfield tables through OData, and Power Query reapplies the transformations stored in the workbook. Review the supported data on the Twinfield connector page, follow the separate Power BI setup guide, or compare all Twinfield connection methods.