Add CAST to select in Odbc.Datasource?

[joakimE]

Hi!

I have developed a custom connector for ODBC. It all works as intended but I need to make it add a CAST around the select of fields of a certain data type.

Today, the preview of Native Query looks like:

select "Id",
    "ModifiedBy",
    "Timestamp",
    "Comment"
from "dbo"."Table1"

What I want:

select "Id",
    "ModifiedBy",
    CAST("Timestamp" as datetime) as "TimestampDatetime",
    "Comment"
from "dbo"."Table1"

Using the below as the AstVisitor option in Odbc.Datasource, I get no actual change in the generated SQL query.

AstVisitor = [
            Constant =
                let
                    Cast = (value, typeName) => [
                        Text = Text.Format("CAST(#{0} as #{1})", { value, typeName })
                    ],
                    Visitor = [
                        12 = each Cast(_, "datetime")
                    ]
                in
                    (typeInfo, ast) => Record.FieldOrDefault(Visitor, typeInfo[DATA_TYPE], each null)(ast[Value])
        ],

I have not found much documentation around the AstVisitor, can anyone confirm it should be possible to modify the select statements as I need?

Adding a Change type step in Power query breaks the query folding, so that is no option.

Any help is very appriciated!

[v-yiruan-msft]

Hi @joakimE ,

Providing an override for this value has been deprecated, please find more details in this official documentation.

In addition, please review the following thread which has the similar problem as yours. Hope it can help you resolve the problem.

Power BI custom data connector - how to override cast behavior for int64/uint data type

---

I didn't find a workaround for modifying the cast statement. But I fixed this issue by modifying the column info in SQLColumns handler. Just change the columns which are int64/uint64 types to DOUBLE type, by updating DATA_TYPE/SQL_DATA_TYPE/TYPE_NAME columns in the metadata table.  This way power BI even doesn't generate the cast statement. Here is a sample for SQLColumns - DataConnectors/SnowflakeODBC.pq at master · microsoft/DataConnectors (github.com)

---

Best Regards

[joakimE]

Hello @v-yiruan-msft,

Thanks for your valuable answer, I had totally missed out the part of the Constant override being deprecated.

The other link you provided, I have seen it before and managed to test some different mappings but never succeeded with selecting a DATA_TYPE that Power BI automatically interpreted as the Date/time/zone column type. Any suggestion?

But even though I got that working, I am fearing that the casting will still be required in the query's select statement, which I now realize will not be possible. 😕 Are there any other ways to query fold the column type casting?

[v-yiruan-msft]

Hi @joakimE ,

Please review the following links, hope they can help you.

How (not) to break Power BI Query folding by changing data types!

Enable QUERY FOLDING for native queries in Power Query / Power BI

Changing data types that do not break query-folding in Power Query / Power BI

Best Regards