Merging Business Central Document and Archive Tables for Reporting

In this article, we’ll see how to merge Business Central current & archived documents in Power BI to enable reporting on this data.

Introduction

Business Central archives certain documents such as purchase orders and sales invoices when they are edited or completed. This means that:

Before applying edits to a document, Business Central saves the original document as an archived version. For example, the Sales Header table has all current versions of documents, while Sales Header Archive has all previous versions of those (if any).
An archive table has the same columns as its corresponding document table, plus some additional columns for versioning metadata. These columns enable Business Central to store multiple versions of a given document:

The Sales Header Archive table showing multiple versions of a same document
Importantly, an archive table can have more than just previous document versions. If your workflow includes archiving documents after a transaction is done, archive tables will also have the latest version of your (completed) documents!

Therefore, reporting on your business performance may require querying for documents across these two types of tables.

Let’s see how we can intelligently merge these tables to always keep only the latest version of a document, whether it lives in the main table or its archived version.

Walkthrough

The following technique identifies the highest version number for each document, then performs a join to keep only highest version rows from both tables.

Using Purchase Header and Purchase Header Archive as an example:

Load both tables into Power BI as queries
Add a Version_No column set to 999 to the Purchase Header table. The number should be high enough to be higher than any version number.
```
= Table.AddColumn(PurchaseHeader_table, "Version_No", each 999)
```
With both tables sharing the same set of columns, append Purchase Header Archive into Purchase Header
```
= Table.Combine({#"Add Version_No column with 999", #"Purchase Header Archive"})
```
The magic sauce starts here. Create a new Purchase Header Max Version query (right-click in the Queries area -> New Query -> Blank Query) with a step grouping the previous table by document number (No_) and identifying the highest version number in each group:
```
= Table.Group(#"Purchase Header", {"No_"}, {{"MaxVersion", each List.Max([Version_No]), type nullable Int64.Type}})
```
- Any document only present in Purchase Header or in both tables will have its MaxVersion set to 999. This means the Purchase Header version will have been identified as the latest.
- Any document only present in Purchase Header Archive will have its MaxVersion set to whatever is the latest version available in that table (a number below 999).
Create another new query (ex.: Purchase Header Aggregated), with the following step.
```
= Table.NestedJoin(#"Purchase Header", {"No_", "Version_No"}, #"Purchase Header Max Version", {"No_", "MaxVersion"}, "Purchase Header Max Version", JoinKind.Inner)
```
It joins Purchase Header from step 3 with Purchase Header Max Version from step 4 using an inner join. It’ll only keep purchase header rows of the highest version, and discard archived rows of previous versions.
Finally, delete the Version No_ column from Purchase Header Aggregated and disable all previous queries from loading into the report (right-click on the query -> uncheck “Enable load”). The table we created in step 5 is all our report needs!

Conclusion

By identifying the highest version number for each document and joining that on the data set, we are left with a table containing only the latest version of documents in Business Central, whether they are archived or not.

There is also the benefit of condensing two fact tables into a single one:

Purchase Header & Purchase Header Archive fact tables condensed into one — A single fact table enables a star schema and makes reporting easier

Credit

SQL select only rows with max value on a column (stackoverflow.com)