Cannot change data type in Power Query

[Akos90]

Changing data type throws error. Column is calculated and only contains 0 and 1.

Is there a way to disable query folding?

[ilyagithub1]

@Akos90 Thank you for reporting the issue. It's been fixed.

Tip: to resolve the issue before the next release of MariaDB Power BI Connector and without rebuilding the Connector manually:

Replace Int64 with Int32 in a statement generated by Power Query Editor:
Table.TransformColumnTypes(myTable,{{"Some Column", Int64.Type}})` yields driver error 1064 "check for the right syntax".
Table.TransformColumnTypes(myTable,{{"Some Column", Int32.Type}})` works without the error.

Insight into the driver error 1064 "check for the right syntax"

When data type is changed to Whole Number, Power Query Editor uses M data type Int64 and generates a line of code similar to this:
= Table.TransformColumnTypes(myTable,{{"Some Column", Int64.Type}})

Power BI Mashup Engine translates M data type Int64 to SQL data type BIGINT by default.
Data type transformation in SQL is done with CAST or CONVERT.

In your screenshot the error message says check... for the right syntax to use near 'BIGINT) as C2...
SQL snippet that the Mashup Engine generated and that resulted in the driver error (code 1064) should look like this:
CAST (`your_column` AS BIGINT) as `C2` where use of BIGINT with CAST produces the error 1064.

MariaDB implementation of CAST and CONVERT functions supports a limited set of data types and BIGINT is not supported.

This is a long-standing issue with CAST and CONVERT in MariaDB:
MDEV-17686 BIGINT type doesn't work within CAST

The fix in MariaDB Power BI Connector excludes BIGINT from the list of of the supported SQL data types. Effectively, Power BI Mashup Engine assumes BIGINT is not supported by the database driver and does not use the data type when transforming M statements to SQL statements. This workaround does not affect consuming BIGINT data from MariaDB, because in a typical SELECT statement data type is not explicitly specified. The fix has been tested with MariaDB 10.5.8 in strict mode by fetching data from BIGINT and BIGINT UNSIGNED columns. The was no loss of data observed, so the fix is safe to use with actual BIGINT data.

[gcarneiro]

I am having same issue here, but i am trying to alter type from int to text.

Tried to update MariadbODBC Driver but it is in the newer version. Any ideas?

[ilyagithub1]

@gcarneiro Thank you for reporting the issue. It is being looked into.

[gcarneiro]

I am using MariaDB 11.1.2 on ubuntu. Tried to update mariadb jdbc connector but no success...