Forum Discussion

TiagoKorynek's avatar
TiagoKorynek
Regular Visitor
9 years ago
Solved

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

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 Acce...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
TiagoKorynek
Regular Visitor
9 years ago

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

 

TblTcDelIDDATESTNATAATA4DIGITATA2DIGITATALAST2ATASecondaryDELAY HRSDELAY MINSAOGDelayTotalMinsEvent TypeSeverity Index

115/01/2001MAN52-00 DOORS52-00520 203No123DLY0.55
224/01/2001MAN77-00 ENGINE INDICATING SYSTEM77-00770 239No159DLY0.55
330/09/2001PMI36-00 PNEUMATIC SYSTEM36-00360 102No62DLY0.3
401/01/2006AMS52-00 DOORS52-00520 033No33DLY0.24
501/01/2006GVA21-31 PRESSURIZATION CONTROL SYSTEM21-312131 640No400DIV1.6
602/01/2006PRG34-00 NAVIGATION34-00340 029No29DLY0.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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      TiagoKorynek
      Regular Visitor
      9 years ago

      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's avatar
        TiagoKorynek
        Regular Visitor
        9 years ago

        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