Business Central FlowFields are not extracted: they are rebuilt

You connect Business Central to Power BI, bring in the Customer table, place the Balance field in a visual, and get zero everywhere. The ERP screen, meanwhile, shows perfectly correct balances.

Nothing is broken. This field simply never existed as stored data.

A FlowField is a formula, not a column

Business Central, and Dynamics NAV before it, distinguishes two kinds of fields. Normal fields are stored: they take up space, they are backed up, they can be read. FlowFields are not stored at all. They are calculation definitions that the server runs at the moment the screen is displayed.

Balance on the Customer table is a FlowField. Its definition says, in essence: “add up the Amount field of the Cust. Ledger Entry table, for every line whose Customer No. matches this card”. The calculation runs every time the customer card is opened.

An SQL extraction or an API read retrieves what is stored. Since nothing is stored, it retrieves the default value for the type: zero for an amount, empty for a text. No error is raised, and that is what makes the trap costly: the report displays, the totals are wrong, and nobody notices until someone reconciles with the ERP.

The FlowFields we meet most often

On our projects, it is almost always the same ones that are missing:

| Table | Field | What it actually aggregates | |---|---|---| | Customer | Balance, Balance (LCY) | Open customer ledger entries | | Vendor | Balance, Balance (LCY) | Vendor ledger entries | | Item | Inventory | Stock movements | | Item | Qty. on Sales Order | Unshipped order lines | | G/L Account | Balance at Date, Net Change | General ledger entries over a period | | Job | Total Cost, Total Price | Project lines |

The common point is obvious: each one is the sum of a movements table. That is exactly what a star schema does natively.

Rebuilding rather than extracting

The good news is that rebuilding produces a result better than the original. A FlowField returns one value, at the present moment, for one card. A measure returns the same value, but at any date, over any grouping, and with the detail behind it.

The principle comes down to three steps.

One, import the movements table, not the card. For customer balances, that is Cust. Ledger Entry or, better, Detailed Cust. Ledg. Entry, which carries the detail of the applications. The Customer table remains useful as a dimension: it carries the name, the country, the posting group. It no longer carries the balance.

Two, write the measure. For a customer balance, the basic calculation is a filtered sum:

Customer balance =
CALCULATE (
    SUM ( 'Detailed Cust. Ledg. Entry'[Amount (LCY)] ),
    'Detailed Cust. Ledg. Entry'[Entry Type] = 1   -- initial entry
)

For the real outstanding amount, what the customer still owes, the applications must be taken off, which means summing all entry types rather than filtering:

Customer outstanding =
SUM ( 'Detailed Cust. Ledg. Entry'[Amount (LCY)] )

It is counter-intuitive, and yet it is the definition Business Central applies: the balance is the algebraic sum of all detailed entries, with payments carrying a negative amount.

Three, add the time dimension. This is the gain the FlowField never offered. With a date table correctly related to Posting Date, the same measure answers “what was the outstanding amount on 31 December?”:

Outstanding at date =
CALCULATE (
    [Customer outstanding],
    FILTER (
        ALL ( 'Date' ),
        'Date'[Date] <= MAX ( 'Date'[Date] )
    )
)

What the rebuild makes possible

Once the measure is written, the balance stops being a figure frozen on a card: it becomes a value you can group, compare and read over time.

The same figures, read in a second

On the left, every row has to be read to find the month that dips. On the right, it is seen without reading. That is the whole work of a well-designed report.

How to check that it is right

A rebuild is validated by reconciliation, never by reading the code. This is always how we proceed:

1. Open the customer list in Business Central, with the Balance (LCY) column displayed, and export it to Excel.
2. Produce the same list from the model, with the measure, at today's date.
3. Compare customer by customer, not just the total. Two errors of opposite sign cancel each other out perfectly in a total, and show up immediately in the detail.

The remaining differences almost always come from three causes: entries in an unconverted currency, a company filter forgotten in a multi-company environment, or adjustment entries of a type that the measure excludes.

With several instances, it gets more involved

If you consolidate several Business Central instances, each instance has its own customer numbers. Customer CLI-0042 in the French company and C00042 in the Belgian company may designate the same business, or two different ones.

The measure will work in both cases, but the grouping will be wrong until a master data alignment step has settled the question. It is the most underestimated topic when pricing a multi-instance project, and we have devoted a whole page to it.

At this volume, we export with bc2adls rather than through the API: the export is incremental, it writes Delta directly into the lake, and it does not consume your ERP's call quotas.

What to remember

A zero balance in Power BI is not a connection bug: it is a field that was never stored. Rebuilding it as measures takes half a day on a simple scope, it is validated by line-by-line reconciliation, and it gives a model more capable than the original screen, because it answers at any date and on any axis.