Forum Discussion

mieltn's avatar
mieltn
New Member
4 years ago
Solved

PowerQuery reads SQLite3 table integers as Binary data

Good afternoon everyone,

 

I'm trying to connect to SQLite3 database from PowerBi. When the PowerQuery opens, integer columns appears to be Binary format. I wrote table from Jupyter notebook using sqlalchemy and pandas to_sql(). Then, the file was opened in DB Browser for SQLite. Everything looked fine, so it seems to be sort of a Power BI issue. I can convert to text and then to Whole Number, but since such connections supposed to be created frequently, it's inconvinient to apply 2-steps convertion for 7 columns every time. Why PowerQuery reads integers as Binary?

 

Attaching some photos.

 

 

Any advise will be appriciated, very confused with the issue.

Need Help
  • KNP's avatar
    KNP
    4 years ago

    Hi mieltn,

     

    Not 100% sure of the cause, but one possible issue.

    Did you explicitly set the data types in the Python script?

     

    dtype={'period':  sqlalchemy.types.INTEGER()}

     

     

    Kim

3 Replies

  • KNP's avatar
    KNP
    Super User
    4 years ago

    Hi mieltn,

     

    Not 100% sure of the cause, but one possible issue.

    Did you explicitly set the data types in the Python script?

     

    dtype={'period':  sqlalchemy.types.INTEGER()}

     

     

    Kim

    • mieltn's avatar
      mieltn
      New Member
      4 years ago

      Thank you very much! Explicitly initializing dtypes before writing to SQLite3 was the issue.

  • rufengli's avatar
    rufengli
    Microsoft Employee
    4 years ago

    Hi, mieltn ,

     

    Power BI ODBC connector read the data absed on what driver returns.  It looks like Access dBase driver that you use returned the data as binary, you should notify Access team about the issue if you have verified the data is indeed integer in SQLite.