Incorrect values in table imported from Microsoft Access

[David1205]

Hello everyone,

I have the following problem: I have imported a table from Microsoft Access into Power BI Desktop. Among other things, this table contains the column "Value".

Now I have noticed that the imported table in Power BI Desktop displays different values in the "Value" column than in the original table in Microsoft Access.

The "Value" column has the same data type in both tables (both in Power BI Desktop and in Access).

Can you help me? Why do I have a discrepancy between Power BI Desktop and Access?

Thank you very much for your help!

David

[HotChilli · Accepted solution]

Thank you for that.  I don't have an immediate answer for you but I think we can start from an assumption that powerbi has not suddenly started applying random factors when importing from Access.

Here's what I would do.
1. Attempt to completely confirm that we are looking at the same row. Can do this by importing all the data to powerbi and looking at rows in Data View.

2. Focus attention on the particular column in Access.  The nearby column BUCH_MON shows no problems. Is the Value field a calculated field? Is there any manipulation of the column taking place in Access (Date or time-related calculations, maybe?) Can you remove all formatting in Access and make all fields text type then import (either as Access or csv? 

These are the sort of things I would look at

[HotChilli · Accepted solution]

Thank you for that.  I don't have an immediate answer for you but I think we can start from an assumption that powerbi has not suddenly started applying random factors when importing from Access.

Here's what I would do.
1. Attempt to completely confirm that we are looking at the same row. Can do this by importing all the data to powerbi and looking at rows in Data View.

2. Focus attention on the particular column in Access.  The nearby column BUCH_MON shows no problems. Is the Value field a calculated field? Is there any manipulation of the column taking place in Access (Date or time-related calculations, maybe?) Can you remove all formatting in Access and make all fields text type then import (either as Access or csv? 

These are the sort of things I would look at

[David1205 · Accepted solution]

Hi HotChilli,

thank you for your reply. I have found the solution: In the query in Microsoft Access, I filtered a text column using * in the criteria section of the design view: for example [Not like "* DL *].

I noticed that Power BI does not recognize the * and needs % instead. If I replace all * with a % in the criteria area in Microsoft Access, the filter also works in Power BI: For example [Not like "% DL %"]

[HotChilli]

Can you show the evidence please? The same rows in Power Query and Access. And any transforms in Power Query.

[David1205]

And that's the code from the extended editor:

let
Quelle = Access.Database(File.Contents("\\fs011\daten\Reports\Controller's_Book\Reports\Vacir\Power BI_Servicereport\Servicereport_PowerBI.accdb"), [CreateNavigationProperties=true]),
_qDL_Servicereport_Vorlage_Annehmerstatistik_PowerBI_End = Quelle{[Schema="",Item="qDL_Servicereport_Vorlage_Annehmerstatistik_PowerBI_End"]}[Data]
in
_qDL_Servicereport_Vorlage_Annehmerstatistik_PowerBI_End

[David1205]

Table in Power BI Desktop:

Origin table in Microsoft Access: