cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mieltn
New Member

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.

 

powerquery_binary.pngpowerquery_binary_convert.pngpowerquery_binary_converted.png

 

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

1 ACCEPTED SOLUTION
KNP
Super User
Super User

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

3 REPLIES 3
rufengli
Microsoft
Microsoft

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. 

KNP
Super User
Super User

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

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).
chrome-9xf-Zagzel-B

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr
Proud to be a Super User!PBI-Super-User-Rank-30x30-1x

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors