MongoDB Custom Power BI DirectQuery Connector Issue - Failed to Convert Bigint to Integer

Question

I am using a connection to MangoDB to Power Bi and I am getting the following error

"ErrorMessage":"Failed to convert type bigint to integer, expression `LinkID` to 1506780."

"ErrorMessage":"This ODBC driver doesn't support SQL_FN_CVT_CONVERT or SQL_FN_CVT_CAST. You can override this by using SqlGetInfo for SQL_CONVERT_FUNCTIONS."

and Here is in the SqlGetInfo

SQLGetInfo = [
        // place custom overrides here
        SQL_SQL92_PREDICATES = ODBC[SQL_SP][All],
        SQL_AGGREGATE_FUNCTIONS = ODBC[SQL_AF][All],
        SQL_CONVERT_FUNCTIONS = ODBC[SQL_CVT][BIGINT]
    ],

//this is from OdbcConstants file that is called in Power Query file
SQL_CVT = [
BIGINT = 0x00004000 

] 

How can I resolve this? 

Answer 1

Use the following formula

SQLColumns = (catalogName, schemaName, tableName, columnName, source) =>
                let
                    OdbcSqlType.BIG_INT = -5,
                    OdbcSqlType.INTEGER = 4,

                    FixDataType = (dataType) =>
                        if dataType = OdbcSqlType.BIG_INT then
                            OdbcSqlType.INTEGER
                        else
                            dataType,
                    Transform = Table.TransformColumns(source, { { "DATA_TYPE", FixDataType } })
                in
                    // the if statement conditions will force the values to evaluated/written to diagnostics
                    if (Diagnostics.LogValue("SQLColumns.TableName", tableName) <> "***" and Diagnostics.LogValue("SQLColumns.ColumnName", columnName) <> "***") then
                        let
                            // Outputting the entire table might be too large, and result in the value being truncated.
                            // We can output a row at a time instead with Table.TransformRows()
                            rows = Table.TransformRows(Transform, each Diagnostics.LogValue("SQLColumns", _)),
                            toTable = Table.FromRecords(rows)
                        in
                            Value.ReplaceType(toTable, Value.Type(Transform))
                    else
                        Transform,

and use the following SQL datatypes

SQL_TYPE =
    [
        // Base data types (sql.h)
        UNKNOWN             = 0,
        NULL                = 0,
        CHAR                = 1,
        NUMERIC             = 2,
        DECIMAL             = 3,
        INTEGER             = 4,
        SMALLINT            = 5,
        FLOAT               = 6,
        REAL                = 7,
        DOUBLE              = 8,
        DATETIME            = 9,      // V3 Only
        VARCHAR             = 12,

        // Unicode types (sqlucode.h)
        WCHAR               = -8,
        WVARCHAR            = -9,
        WLONGVARCHAR        = -10,

        // Extended data types (sqlext.h)
        INTERVAL            = 10,    // V3 Only
        TIME                = 10,
        TIMESTAMP           = 11,
        LONGVARCHAR         = -1,
        BINARY              = -2,
        VARBINARY           = -3,
        LONGVARBINARY       = -4,
        BIGINT              = -5,
        TINYINT             = -6,
        BIT                 = -7,
        GUID                = -11,   // V3 Only

        // One-parameter shortcuts for date/time data types.
        TYPE_DATE           = 91,
        TYPE_TIME           = 92,
        TYPE_TIMESTAMP      = 93,

        // SQL Server Types -150 to -159 (sqlncli.h)
        SS_VARIANT          = -150,
        SS_UDT              = -151,
        SS_XML              = -152,
        SS_TABLE            = -153,
        SS_TIME2            = -154,
        SS_TIMESTAMPOFFSET  = -155 

    ], 

Want to learn more about Power Bi, Refer & learn Power bi   

Also, check out our YouTube video to know the data visualization basics and best practices with Power BI

Learn Power BI Course from this video : 

Intellipaat gives its learners an industrial training on Power BI Course. Enroll now to get a professional certificate!