Split Column with DirectQuery

[TheColdVolcano]

I have a DateTime column in BigQuery. I am importing the table using DirectQuery mode.

When I try to split this DateTime column to Date and Time column, it does not allow me to stating this is not allowed in DirectQuery mode.

I cannot import the data as it won't be the live version.

I also tried to write the DAX function using SEARCH, but that is not allowed either. Is there a workaround for this?

[grazitti_sapna · Accepted solution]

Hi @TheColdVolcano 

Yes, There is limitation in Power BI's DirectQuery mode, especially with BigQuery. In DirectQuery mode, certain transformations like splitting a Datetime column into Date and Time parts or using certain string manipulation DAX functions (like SEARCH) are not supported directly because they can't be translated into native SQL for the underlying source.

Workaround Options

Option1:
Create calculated columns in BigQuery
Since transformations aren't allowed in Power BI with DirectQuery, push the logic to BigQuery by modifying the view or SQL query in BigQuery to return the split DATE and TIME parts separately.

SELECT
  your_datetime_column,
  DATE(your_datetime_column) AS date_part,
  TIME(your_datetime_column) AS time_part
FROM your_dataset.your_table

Then point Power BI to this modified table or view using DirectQuery.

Option2:
If modifying the base table isn't possible, you can create a view in BigQuery that performs the splitting and then connect Power BI to that view.

🌟 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!