When I change the setting of the driver (in the ODBC Data Source Administrator) to "Always BIGINT",
the data appears in Power BI as "Binary" and I cannot convert it to "Whole Number". I can actually see single values (as text) when drilling down on the Binary and thereby invoking Lines.FromBinary - but this would not give me the complete column(s).
I believe I had already checked out the referenced site - now I just followed all the steps again to verify, you can see the result in the following screenshot: Right-hand side SQLite Browser (sorry in German...), showing large numbers for the fields id and cid, left-hand side the same table during the Get Data / Import process - clearly id / cid are truncated to Int32.Max, while other values are imported correctly.
The number "214..." is a string of your id number in SQLite 3 or not?
how many digits in your number in SQLite 3?
In Powre BI, It allows Whole Number data type for 19 digits; positive or negative whole numbers between -9,223,372,036,854,775,808 (-2^63) and 9,223,372,036,854,775,807 (2^63-1).
If the digits is more that the max limitation of the Power BI, when imported, it may be cut down.
It seems the number are not used for calculation, if so, you could change it to text in SQLite 3.
The Text type support more characters.
Text - A Unicode character data string. Can be strings, numbers, or dates represented in a text format. Maximum string length is 268,435,456 Unicode characters (256 mega characters) or 536,870,912 bytes.
Best Regards Maggie
Community Support Team _ Maggie Li If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You are correct to mention that the numbers in the SQLite3 database are used as ids, not for calculation - but unfortunately I cannot modify the SQLite3 database, because it is created by a third-party application (Anki with a defined database format). The numbers / ids are actually Unix epoch timestamps in milliseconds (factor 1000 !), so nowadays we are at roughly 1.5e12 (therefore 13 digits). Therefore the number "214..." only appears in Power BI and is not an id number in SQLite3.
Now I have tried to create a minimal SQLite3 database to reproduce the issue: Link
The table 'cards' contains 2 lines with the 13 digits ids:
In Power BI, after Get Data / ODBC / SQLite3 / database=(...)BIGINT-Test.anki2 / Default Credentials / Connect, I get
it seems that in the thread Sqlite - Wrong integer imports in the Power BI community last year you had a similar issue like the one I am facing now: Large integer values are not imported correctly from SQLite3 to Power BI. As your thread does not contain an Accepted Solution: Have you been able to solve your problem otherwise or could you give me any hints on my issue? Any support would be appreciated...