How to Join Business Central Tables in Excel Without the Plumbing

You can join Business Central tables directly in Excel without a data warehouse, a Power BI model, or a column of VLOOKUPs. In Exsion Reporting you open the query builder on a download, add a second table, accept the join key it proposes (for example Cust. Ledger Entry "Customer No." to Customer "No."), pick the fields you want from the second table, and refresh. The join is written into the workbook in plain text, it works across extension tables, and it comes in four flavours: inner, outer, inner top 1, and not exists. The rest of this article shows how that works in practice, with an aged receivables report as the worked example, and is fair about the cases where a join belongs inside Business Central rather than in your spreadsheet.

The one reason people give for staying on Jet

In a demo this September with a US Business Central partner, a finance director with 20 years on Jet Reports and 22 on NAV and Business Central put it as plainly as anyone could: "The reason I'm a big Jet user is because I can create tables that join tables. Other reporting doesn't really allow that."

That is the most common reason we hear for staying put, and it is a fair one. If your reporting tool cannot join Cust. Ledger Entry (table 21) to Customer (table 18), you are not reporting. You are exporting, and then doing the reporting by hand.

So this post takes that reason seriously. It is not an argument that joins do not matter. It is an argument that they should not require plumbing.

Why every useful Business Central report is a join

Business Central is a normalised database, and that is a good thing for posting and a nuisance for reporting. A customer ledger entry stores the Customer No. and almost nothing else about the customer. The name, city, salesperson code, payment terms, and customer posting group all live on the Customer card. A sales line stores an Item No.; the item category, the vendor, and the unit cost live on the Item card (table 27). A G/L entry stores a G/L Account No.; the account name and the income/balance classification live on G/L Account (table 15).

So a useful report joins two tables. A good one joins four or five. An aged receivables report that a credit controller can actually act on needs the open entries, the customer name and phone, the salesperson who owns the account, and the payment terms that explain why it is late. That is three tables before you have calculated a single bucket.

The three ways finance teams have joined tables until now

The first way is VLOOKUP or XLOOKUP against a second export. It works on a few thousand rows and breaks at volume, both in performance and in trust, because the two exports were taken at different moments and nobody remembers which one is stale.

The second way is a BI layer: a data warehouse or a Power BI semantic model where the joins are defined once by someone who knows the tables. That is a legitimate architecture. It also puts a data engineer between you and the numbers, and every new field is a ticket.

The third way is a tool like Jet Reports, where you build the join in Excel through the Table Builder. This is why the finance director above stayed on Jet for two decades, and it is the closest of the three to what a controller actually wants. The price is that each Jet function is its own request to Business Central, and the design version of the workbook is what carries the logic; the run output most people receive does not (we cover the consequences in why a Business Central Excel report should explain itself).

How a join works in Exsion

Start with any download, for instance every open entry in Cust. Ledger Entry. Open the query builder and add a table. Exsion looks at the two tables and proposes the join key, in this case Customer No. on the ledger side to No. on the Customer card. Accept it, or change it if you are joining on something less obvious. Then double-click the fields you want from the second table: City, Phone No., E-Mail, Salesperson Code, Payment Terms Code. Click OK and refresh.

The result lands in Excel with the joined fields as ordinary columns, and the grid that describes the query shows the join in blue. That grid is written into the workbook as plain text: the base table, the joined table, the key, the fields, and the filters on each. Anyone who opens the file can read exactly how the customer name got next to the ledger entry, and anyone with a licence can change it in place.

Extension tables are included. If your industry add-on stores a project code or a contract number on its own table, you can join to it the same way as to a standard Microsoft table. That matters more than it sounds, because the field you need for the board pack is usually the one the extension added.

Refresh pulls the whole set in one request rather than a request per cell, which is why a joined report of a few hundred thousand rows comes back in seconds rather than minutes. We covered the speed side in why Jet Reports feels slow and Excel freezes, so we will not repeat it here.

The four join types and what they mean in Business Central

[[TABLE_START]]

Join type | What it returns | A Business Central use case

Inner | Only rows where a match exists in both tables | Posted sales invoice lines joined to Item (27), so lines with a G/L account instead of an item are left out of the product margin report

Outer | Every row from the base table, matched fields blank where there is no match | Every customer from Customer (18) with their open entries from Cust. Ledger Entry (21), so customers with a zero balance still appear on the credit review list

Inner top 1 | The first matching row only, one per base row | Each item with its most recent purchase price from the Purchase Price or Price List Line table, without multiplying the rows

Not exists | Only base rows with no match at all | Customers with no posted invoice in the last twelve months, for a dormant account clean-up

[[TABLE_END]]

That last one deserves a sentence. "Customers with no posted invoice in the last 12 months" is a question that usually gets answered with two exports and a COUNTIF. As a not exists join, with a posting date filter on the ledger side, it is a two-minute report, and it refreshes.

Worked example: aged receivables with salesperson and custom buckets

Everybody changes the standard aging report on day one. The buckets are wrong for the business, the salesperson is missing, or credit control wants the phone number in the same row so they do not have to open the card. Here is how the report is assembled in Exsion, field by field.

The one piece that is not a plain field or a plain join is the bucket itself. An aging bucket is the open amount per customer for entries whose due date falls in a range, which is a calculation over Detailed Cust. Ledg. Entry (table 379), not a field you can read off a row. In Exsion you define a custom function inside the query: sum Amount from table 379 with a filter on the due date range, and use the result as a column. Changing from 30-day buckets to 7-day or 90-day buckets means editing that date filter, not rebuilding the report.

[[TABLE_START]]

Report column | Source table | How it is obtained

Customer No. | Cust. Ledger Entry (21) | Field on the base table

Customer Name | Customer (18) | Join on Customer No. = No.

City | Customer (18) | Join

Phone No. | Customer (18) | Join

Salesperson Code | Customer (18) | Join

Salesperson Name | Salesperson/Purchaser (13) | Join on Salesperson Code = Code

Payment Terms Code | Customer (18) | Join

Document No. | Cust. Ledger Entry (21) | Field

Posting Date | Cust. Ledger Entry (21) | Field

Due Date | Cust. Ledger Entry (21) | Field

Remaining Amount | Cust. Ledger Entry (21) | Flowfield, calculation shown in the grid

Not yet due | Detailed Cust. Ledg. Entry (379) | Custom function: sum Amount, due date filter greater than today

1 to 30 days | Detailed Cust. Ledg. Entry (379) | Custom function: sum Amount, due date filter today minus 30 to yesterday

31 to 60 days | Detailed Cust. Ledg. Entry (379) | Custom function, due date range shifted

61 to 90 days | Detailed Cust. Ledg. Entry (379) | Custom function, due date range shifted

Over 90 days | Detailed Cust. Ledg. Entry (379) | Custom function, due date filter earlier than today minus 90

[[TABLE_END]]

One note on the table. The date filters use Business Central's own filter syntax, with two dots for a range, so a bucket filter reads the same way it would in a Business Central list page; Microsoft documents the syntax on Learn.

Want the state or region as well? Join Customer to Country/Region or read County from the card. Want it by salesperson for the sales meeting? That is the pivot in the next section.

Pivots that keep up with the join

A joined download is only half the job if the pivot on top of it goes stale. In Exsion, if you name the pivot after the download, the pivot refreshes automatically when the download does. One refresh, and the aged receivables by salesperson pivot, the aged receivables by payment terms pivot, and the pivot chart on the summary tab all update together.

That is also the recipe for a dashboard. Four downloads (say open receivables, open payables, revenue by item category, and cash by bank account), four pivots named after them, four pivot charts on a summary tab. One refresh button. No Power BI licence, no gateway, no scheduled load, and no data model that only one person understands. If the downloads are large, send them direct to the PivotTable data model instead of a worksheet, which removes the 1,048,576-row worksheet limit and keeps the file small; we go through that in the Excel row limit and Business Central reporting.

The pivot cells still drill through. Double-click a bucket total for one salesperson and Excel shows the underlying rows, joined fields included.

Multi-company and multi-environment joins

Joins do not stop at the company boundary. Select three companies in the grid and the result comes back with a company column, joined fields and all, so the group aged receivables report is the same report with three companies ticked rather than three copies of it. The same works across environments, which is useful for a partner reporting on several customer tenants, or for a group that runs a production environment and a separate consolidation environment.

For the consolidation itself, with eliminations and currency translation, that is the job of Exsion Corporate rather than a join, and we cover the approach in how to consolidate multiple companies in Business Central.

When the join belongs in Business Central instead

Not every join should live in a spreadsheet, and it would be dishonest to pretend otherwise. If the same joined dataset is needed by several systems (a Power BI model, an external auditor's extract, an integration), the right home is a Business Central query object or an API page, built once by a developer, versioned with the rest of your AL code, and exposed through the API. If users need the joined field while they are working in Business Central, on a list page or a card, that is a page extension, not a report. Both of those are Business Central-side work, and both are worth it when the join is stable and shared.

The reporting layer is the right home when the join belongs to the report. The controller who wants payment terms next to the open entries this month, and salesperson region next quarter, should not have to open a ticket for either. A reasonable rule: if the join changes with the question, keep it in the workbook where the grid shows it; if the join is part of the data model everyone relies on, put it in Business Central. Exsion reads both. A query object or API page you have built shows up as a table you can download and join like any other.

Build the report you have been avoiding

Every finance team has one report that was never built because it needed four tables and nobody had the plumbing. That is the one to start with. Install Exsion Reporting from AppSource through Extension Management, download the Excel add-in from the Exsion info page in Business Central, add the connection, sign in; the whole install takes about five minutes and the 30-day trial needs only an email address. Then open the query builder, add the second table, and accept the join.

If you are coming from Jet Reports, we will build that first report together free of charge, and our migration tooling reads the tables and fields your Jet workbook touches to give you a head start. Details on the Exsion Reporting product page, and the wider comparison lives in our guide to Jet Reports alternatives for Business Central in 2026.

Frequently asked questions

[[FAQ_START]]

Can you join tables from Business Central directly in Excel? | Yes. With Exsion Reporting you open the query builder on a download, add a second Business Central table, and accept the join key Exsion proposes. The joined fields arrive as normal Excel columns and the join is written into the workbook as readable text, with no data warehouse or BI model needed.

Which join types does Exsion support for Business Central tables? | Four: inner (matches only), outer (all base rows, blanks where there is no match), inner top 1 (the first matching row per base row), and not exists (base rows with no match). Not exists is the one that answers questions like customers with no invoice in the last 12 months.

How do I build an aged receivables report in Excel from Business Central with my own buckets? | Download open entries from Cust. Ledger Entry (21), join Customer (18) for name, salesperson and payment terms, and define custom functions inside the query that sum Amount from Detailed Cust. Ledg. Entry (379) with a due date filter per bucket. Changing the buckets means editing the date filter rather than rebuilding the report.

Does a joined Business Central report work in a pivot table with live data? | Yes. Name the pivot after the download and it refreshes automatically when the download refreshes. Several downloads with their pivots and pivot charts on one summary tab give you a dashboard with a single refresh, and pivot cells still drill through to the joined rows.

Can I join Business Central extension tables in Excel? | Yes. Tables added by extensions, such as an industry add-on's project or contract table, appear in the query builder alongside the standard Microsoft tables and can be joined the same way.

When should a join be built in Business Central instead of Excel? | When the joined dataset is shared by several systems or needed inside Business Central pages, build it as a query object, API page, or page extension. When the join belongs to a specific report and changes with the question being asked, keep it in the reporting layer where the workbook shows it.

[[FAQ_END]]