search

microsoft / bc2adls

Exporting data from Dynamics 365 Business Central to Azure data lake storage
MIT License
92 stars 53 forks source link

CDM data format set to CSV shows unexpected results in Power BI #135

Closed Arthurvdv closed 1 year ago

Arthurvdv commented 1 year ago

We've started with the Parquet CDM data format and wanted to review the performance with Power BI if we switched over to CSV. The use case here is to import the files directly into Power BI (CDM Folder view) and without the use of shared metadata tables .

When using the Parquet files everything works perfectly. In the switch I've hit reset on all the tables in BC and deleted all the data of the container in Azure Data Lake.

The export in Business Central and Consolidation pipelines in Synapse works without any issue. Retrieving the data in Power BI shows unexpected results and errors (only when using CSV, the Parquet format doesn't have these issues).

image

What we try'ed was removing all the special characters from the data. There where some like a single quote, ampersand, minus sign and Parenthesis. Still after removing all these the output in Power BI still shows the unexpected values and errors.

Does anybody have a clue or could point me in the right direction?

Arthurvdv commented 1 year ago

The more I'm getting familiar with Azure Synapse and Microsoft Fabric I realize it doesn't make sense to read the files directly into Power BI with the CDM Folder view, so I'm going to close this issue.