In Fabric for table it show blank value as '' however in export to data lake its NULL , why is this difference ?

Question

I have other question in Fabric for table ex: select vatnum, * from vendinvoicejour it show blank value as '' however in export to data lake its NULL , why is this difference ?

Answer 1

In Dynamics 365 Finance and Operations (Fabric export to Data Lake scenario), the difference is the result of the application layer and the database layer treating empty strings and NULL differently.

In the D365FO UI or when querying through X++/AOT (for example in a form, table browser, or some SQL views exposed by the application), string fields are often shown as empty strings ('') even when the underlying SQL value is actually NULL. The application framework normalizes string values so that NULL is handled as an empty string in most business logic. This avoids null reference issues in X++ and makes string handling simpler at the application level.

However, when data is exported to Data Lake, the export reflects the physical value stored in SQL Server. In SQL, a string column can store either NULL or ''. These are not the same. NULL means “no value”, while '' means “an empty value”. If the record in SQL contains NULL for vatnum, the Data Lake export will show NULL because it is a direct representation of the database state, without the application-layer normalization.

So the behavior you are seeing means that vatnum is actually NULL in the database, but the D365FO query layer displays it as '' for consistency in the application. Effectively, AFAIK, this difference is expected behavior and comes from the separation between the application framework handling of strings and the raw database export used by Fabric/Data Lake.

---

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin

Answer 2

A better place to ask questions about Fabric is https://community.fabric.microsoft.com/.