String Fields and Date Hierarchy Not Working in DirectQuery Mode Using Simba-based Custom BI Connect

[sahdevISW]

I would like to provide details regarding a scenario where I am working on using a Simba-based Custom BI Connector for Presto with Power BI in DirectQuery mode and encountered some field recognition issues when using DirectQuery mode in Power BI.

Setup Details

• ODBC Driver: Simba Presto ODBC driver

• Custom BI Connector: compiled as a .mez file (Simba_presto.mez)

• Data Source: Presto 

 

Once we give details and clicking OK

What We See

• The table preview loads successfully – we can see data in a tabular format and loading tables like tpch.tiny.orders

• After clicking Load we can see the Data but when we select Text Data type it will failed to load but Integer Data type is loading successfully.

• After clicking on See details we can see the actual error 

• And even in Orderdate I am unable to see hierarchy of (year, quarter, month or day)

But When connecting to our data source in Import mode and loading tables like tpch.tiny.orders:

• Both string and integer columns load correctly

• The orderdate column is automatically recognized as a Date type

• A date hierarchy is created (Year > Quarter > Month > Day)

What I’m Trying to Understand:

• Is this a known limitation of DirectQuery mode with Custom BI Connector ?

• Is Power BI skipping metadata inference in DirectQuery mode, leading to unrecognized types?

• Is there a way to force type recognition or hierarchy creation for date columns when using DirectQuery?

Would appreciate any input or suggestions.

[grazitti_sapna · Accepted solution]

Hi @sahdevISW ,

This is actually expected behavior due to how Power BI handles DirectQuery vs Import Mode, especially with Custom Connectors.

• In Import Mode, Power BI downloads a sample of data and infers data types (string, number, date, etc.) automatically.

• In DirectQuery Mode, Power BI depends completely on the connector’s metadata.
  → It does not sample the data.
  → It only trusts what the connector/driver says about data types.

• Simba Presto Driver / your Custom Connector might not be exposing correct metadata, especially for:

  • Text columns

  • Date columns (no strong typing, or type is exposed as "string")

Your connector probably exposes text and date fields as "VARCHAR" instead of rich types.

Try below fixes:-

Update your .mez (DataSource.Kind extension) to specify strong field types.

After loading, go to Power Query Editor → change column types manually.

Use Import Mode If performance is acceptable.

Modify Presto SQL or connector to explicitly cast fields

Here is a sample query

[DataSource.Kind="SimbaPresto", Publish="SimbaPresto.Publish"]
shared SimbaPresto.Contents = (server as text, database as text, optional options as record) =>
let
// Connect to your Presto database
Source = Odbc.DataSource("dsn=YourPrestoDSN;UID=user;PWD=pass", [HierarchicalNavigation=true]),

// Navigate to table
YourTable = Source{[Name="tpch.tiny.orders", Kind="Table"]}[Data],

// Apply explicit column types
TypedTable = Table.TransformColumnTypes(
YourTable,
{
{"orderkey", Int64.Type},
{"custkey", Int64.Type},
{"orderstatus", Text.Type},
{"totalprice", type number},
{"orderdate", type date},
{"orderpriority", Text.Type},
{"clerk", Text.Type},
{"shippriority", Int64.Type},
{"comment", Text.Type}
}
)
in
TypedTable;

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!

[v-sgandrathi · Accepted solution]

Hi @sahdevISW,

Thank you for the detailed explanation! The solution provided by the super user is on point. The issue you're experiencing is related to how Power BI handles metadata in DirectQuery mode with custom connectors, like the Simba Presto ODBC driver.

As @grazitti_sapna mentioned, DirectQuery relies on the metadata provided by the connector, and in cases like this, the data types (e.g., dates and text) may not be correctly inferred, which can lead to issues with field recognition and missing hierarchies.

Here are a couple of key points to consider:

Power BI doesn't pull sample data in DirectQuery mode, so it solely depends on the metadata exposed by the connector. If the Simba Presto connector is not providing the right type information (e.g., treating dates as text), this can cause issues.
As suggested, updating the .mez file for your custom connector to specify explicit data types could help. Alternatively, you can adjust the column types manually in Power Query Editor after loading.

Another option is to modify your SQL queries to explicitly cast fields to the correct types (such as converting a string to a date).

If performance allows, switching to Import Mode could help bypass some of these limitations, as Import Mode loads data into Power BI and provides full metadata recognition.

If this solution worked for you, kindly mark it as Accept as Solution and feel free to give a Kudos, it would be much appreciated!

Thank you.

[v-sgandrathi]

Hi @sahdevISW,

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If my answer resolved your query, please mark it as "Accept Answer" and give Kudos if it was helpful.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

Hi @sahdevISW,

Since we haven't heard back from you yet, I'd like to confirm if you've successfully resolved this issue or if you need further help?
If you've already resolved the issue, you can mark the helpful reply as a "solution" so others know that the question has been answered and help other people in the community. Thank you again for your cooperation!
If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

[v-sgandrathi]

Hi @sahdevISW,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

If our response addressed by the community member for  your query, please mark it as Accept Answer and give us Kudos. Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

Thank you.

[grazitti_sapna · Accepted solution]

Hi @sahdevISW ,

This is actually expected behavior due to how Power BI handles DirectQuery vs Import Mode, especially with Custom Connectors.

• In Import Mode, Power BI downloads a sample of data and infers data types (string, number, date, etc.) automatically.

• In DirectQuery Mode, Power BI depends completely on the connector’s metadata.
  → It does not sample the data.
  → It only trusts what the connector/driver says about data types.

• Simba Presto Driver / your Custom Connector might not be exposing correct metadata, especially for:

  • Text columns

  • Date columns (no strong typing, or type is exposed as "string")

Your connector probably exposes text and date fields as "VARCHAR" instead of rich types.

Try below fixes:-

Update your .mez (DataSource.Kind extension) to specify strong field types.

After loading, go to Power Query Editor → change column types manually.

Use Import Mode If performance is acceptable.

Modify Presto SQL or connector to explicitly cast fields

Here is a sample query

[DataSource.Kind="SimbaPresto", Publish="SimbaPresto.Publish"]
shared SimbaPresto.Contents = (server as text, database as text, optional options as record) =>
let
// Connect to your Presto database
Source = Odbc.DataSource("dsn=YourPrestoDSN;UID=user;PWD=pass", [HierarchicalNavigation=true]),

// Navigate to table
YourTable = Source{[Name="tpch.tiny.orders", Kind="Table"]}[Data],

// Apply explicit column types
TypedTable = Table.TransformColumnTypes(
YourTable,
{
{"orderkey", Int64.Type},
{"custkey", Int64.Type},
{"orderstatus", Text.Type},
{"totalprice", type number},
{"orderdate", type date},
{"orderpriority", Text.Type},
{"clerk", Text.Type},
{"shippriority", Int64.Type},
{"comment", Text.Type}
}
)
in
TypedTable;

🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!