Forum Discussion
Problems with datetime and time formats
Hello
I have a PBI Dataflow that connects to a BigQuery source. The time and datetime fields show perfectly in the dataflow preview. When I connect to this dataflow with PBI Desktop I get the error "DataFormat.Error: We couldn't parse the input provided as a Time value" for the time fields and "DataFormat.Error: We couldn't parse the input provided as a DateTime value" for datetime fields. I have tried "using locale" and the strange thing is that when i connect to the BigQuery source directly from PBI Desktop all fields import perfectly.
I have noticed that all the columns contain some NULL values. Could this be the reason? If so, how can it be solved.
TIA
Marius
5 Replies
- amitchandak
Super User
mariusla , if the Null/Blank values are text like NULL as a text value. Ideally power BI will show blank for Null. Or some space.
Use the trim and replace null with blank(), if you any such values. Or split the column and append back in a new column and remove unwanted stuff
Hi
I have considered the split and concatenate option but find it very non-effective. It can't by like this. So my question is:
1) When I get data from BigQuery to the PBI Dataflow, the datetime and time columns shows no errors.
2) When I get data from BigQuery directly in PBI Desktop the datetime and time columns shos no errors and Null are handled.
3) When i connect to the dataflow 1) with PBI Desktop the datetime and time columns shows the mentioned errors.
I cant find any reasonable explanation for this.
Marius
HI mariusla,
What type of DateTime values your data source stored? A common date value string or special date types? (e.g. timestamp, date durations...) Please share some sample to help us clarify this.
How to Get Your Question Answered Quickly
In addition, have you tried to use text type to receive these values? You can refer to the following link to use ocbc connector get data with custom queries:
BigQuery Connector for write custom SQL Query to filter Date and join multiple tables
Regards,Xiaoxin Sheng
Hi
The values are stored in datetime, timestamp and time datatypes in BigQuery. All of these three datatypes causes the same problem when first importing to pbi dataflow and then connecting to the dataflow with pbi desktop. The file is huge so therefore difficult to share. How can I provide a working example for you? Screenshots from the dataflow viewer, desktop viewer, etc?
Marius
HI mariusla,
You can copy some of them to an excel sheet and remove sensitive data, then we can try to copy these dummy data to data source and test with power bi connectors.
Regards,Xiaoxin Sheng
