Issue when importing from an Access Database - Calculated Fields / Columns

[TiagoKorynek]

I have recently started using PowerBI as it looked quite exciting to use to visualise data from my Access Database. I have been using PowerBI desktop and going through the usual Get Data -> From Access Database and going to my file to import the data. However, I seem to be getting quite a few errors and columns which do not get imported correctly and I believe they are to do with being calculated columns. Am I not ticking / selecting certain options or are these columns not supported in PowerBI and do I have to find some sort of work around to get these calculated columns?

I can't seem to be able to view the erroneous entries as all that seems to appear are the loading dots at the top of the screen. Therefore the calculated columns is only a hunch as a lot of these are coming back as 0 or Error even though they are all values >0 in my database.

[v-shex-msft]

Hi @TiagoKorynek,

Based on test, I can import the calculate fields to power bi,these fields display the correct value.

Can you provide detail contents or share some sample file to test?

Regards,

Xiaoxin Sheng

[TiagoKorynek]

How do I upload a database or table?

[v-shex-msft]

Hi @TiagoKorynek,

>>How do I upload a database or table?

You can share the sample data of table and the formula of calculated field.

Regards,

Xiaoxin Sheng

[TiagoKorynek]

Oh that's a shame that we can't do a straight file swap.

TblTcDelIDDATESTNATAATA4DIGITATA2DIGITATALAST2ATASecondaryDELAY HRSDELAY MINSAOGDelayTotalMinsEvent TypeSeverity Index

1	15/01/2001	MAN	52-00 DOORS	52-00	52	0		2	03	No	123	DLY	0.55
2	24/01/2001	MAN	77-00 ENGINE INDICATING SYSTEM	77-00	77	0		2	39	No	159	DLY	0.55
3	30/09/2001	PMI	36-00 PNEUMATIC SYSTEM	36-00	36	0		1	02	No	62	DLY	0.3
4	01/01/2006	AMS	52-00 DOORS	52-00	52	0		0	33	No	33	DLY	0.24
5	01/01/2006	GVA	21-31 PRESSURIZATION CONTROL SYSTEM	21-31	21	31		6	40	No	400	DIV	1.6
6	02/01/2006	PRG	34-00 NAVIGATION	34-00	34	0		0	29	No	29	DLY	0.22

The column I am having an issue with has the following formula 

((IIf([Event Type]="ATB",0.75,(IIf([Event Type]="CANX",0.6,(IIf([Event Type]="DIV",0.8,(IIf([Event Type]="DLY",0.1,(IIf([Event Type]="IFSD",0.9,(IIf([Event Type]="RTOHS",0.5,(IIf([Event Type]="RTOLS",0.4,(IIf([Event Type]="RTS",0.3,0)))))))))))))))))+(IIf([DelayTotalMins]<30,0.12,(IIf([DelayTotalMins]<60,0.14,(IIf([DelayTotalMins]<120,0.2,(IIf([DelayTotalMins]<120,0.3,(IIf([DelayTotalMins]<180,0.45,(IIf([DelayTotalMins]>179.9,0.8,0))))))))))))+(IIf([AOG]="-1",1,0))

Thanks

[v-shex-msft · Accepted solution]

Hi @TiagoKorynek,

Based on test, calculated field works and I haven't faced your issue, below is the test file.

>>>>TblTcDelIDDATESTNATAATA4DIGITATA2DIGITATALAST2ATASecondaryDELAY HRSDELAY MINSAOGDelayTotalMinsEvent TypeSeverity Index

In addition, can you fix your column name? It is hard to understanding above text.

Regard,

Xiaoxin Sheng

[TiagoKorynek]

I tried getting my column headers into the table's format here in the forum reply window, but wasn't able to do so.

The column names are:

ID

DATE

STN

ATA

ATA4DIGIT

ATA2DIGIT

ATALAST2

ATASecondary

DELAY HRS

DELAY MINS

AOG

DelayTotalMins

Event Type

Severity Index

I will have a look are you database example now, how did you attach a zip file? I was struggling to find how to do it. Is it because you have the super contributor title or something?

[TiagoKorynek]

I am trying to figure out what I have done differently with my column of "Severity Index" and what you have done with the column "Calculate".

Are you able to explain to me the steps you took in Access and the steps you took in Power BI to create the "test import access.pbix" file that you created for me please? For example all the properties you assigned to the field in Access as well, as this may have influenced the import into PowerBI.

I have spotted that there is a difference in the Access Databases in our respective calculated columns ("Calculated" in yours and "Severity Index" in mine). You have set yours up to be:

Result Type: Double

Decimal Places: Auto

Where as I have the following:

Result Type: Decimal

Format: General Number

Precision: 18

Scale: 0

Decimal Places: Auto

I will try changing this formatting on a test database and report back my findings whilst I wait to hear your reply. 🙂

(Edit: Reporting back my findings - I changed the properties of the "Severity Index" column to those of your "Calculated" and everything imported correctly. There must have been something PowerBI doesn't like about those properties, but I guess I am now able to start making my pretty charts and visuals so thank you. The error information in PowerBI was a bit vague though and didn't help me much with figuring out the error myself (maybe I'm too much of a novice), maybe it is something to take note of when developing the software to allow newbies like myself to avoid having to spam forums with simple problems? Maybe a simple "The issue you are having is something to do with the properties of severity index coming from your Access database")

(Edit: I am trying to compare my database + the one you sent me to try and isolate what the differences are [so please don't think I am being lazy] I am pretty new to Access and PowerBI so I will probably miss quite a few parameters to compare and these might be causing the problem hence why I asked)

Thanks