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

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!

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.