'General error: Year is out of valid range: 1400..9999' when refreshing dataset

[Rigensis]

Hi,

I have a dataset which has been running on a scheduled refresh multiple time per day in PBI service environment. The dataset is getting data from Google BigQuery.

This morning it failed to refresh mutiple times, every time giving me the following error message:

DataSource.Error: ODBC: ERROR [HY000] [Microsoft][ODBC] (10001) General error: Year is out of valid range: 1400..9999. DataSourceKind = GoogleBigQuery. DataSourcePath = GoogleBigQuery. OdbcErrors = error "Microsoft.Mashup.Engine1.Runtime.ValueException: [Expression.Error] Value was not specified.

At first glance the error message looks self explanatory, a date field has a messed up date which needs to be adjusted.
However:

1) The previous day the same dataset was able to refresh just fine. No development in the source for the tables involved has been done in this time
2) Checking out the data that has been added to or updated in the underlying data, no entries with weird dates can be found
3) If I download the pbix file from Service, open it in PBI desktop and refrehs it there, all runs perfectly fine
4) Taking a look at the whole data within the supposedly problematic date fields, I can find cases with date 01-01-0001, but these entries are more than a year old, so why should they start causing problems in PBI service now
5) I changed the found weird dates to more realistic ones (for example, from 01-01-0001 to 1-1-1900) but after publishing the adjustments to PBI service, the issue still persists

If you have any knowledge of what this error is actually pointing towards or more ideas how it could be fixed, I would greatly appreciate that

Best regards

[Rigensis · Accepted solution]

This approach works, and I think it is not so much a workaround, rather close to or an actual solution, as making the adjustment is not too cumbersome. 

However, I would like to add a couple of comments.

If you are creating a new connection to BigQuery, then the setting which you have to set as false can be found under 'Advanced options' that will show in the pop-up window:

However, if you already have an established connection, then you should go to 'Transform Data' > Open Advanced editor > in the source row of the query add the following code

Important: Something I experienced with my data model is that if I added this piece of code only to the table that was showing up in my initial error, then after the next refresh retry another table with the same error popped up, so I would suggest to add this condition in the Source line of advanced editor for all tables within your data model (could try doing it for only those that have date fields, but haven't tried if that works)

[Rigensis]

I'm currently in contact with microsoft support regarding this issue. We have tried various ways of refreshing the data model, clearing caches, but no luck. We also have taken a look at the supposedly problematic date fields, and confirmed that there is no visibly wrong data or setup in them.

I will update this ticket if we manage to find a solution or a viable workaround for the issue.

[Walter_Andrade]

Ontem, tambem fiquei com o suporte da microsoft e foi dito que ja existiam varios outros com a mesma tematica. 
O que fizemos aqui como paleativo para esse problema foi alterar a o UseStorageAPI = false. 

apos alterar de true para false, o painel passou a atualizar. 
parece que não é algo na MS e sim no ODBC do Google com a Microsoft, o nosso chamado ainda esta em avaliação para saber se vão ou não enviar para equipe de produtos para solução geral. 

[Walter_Andrade]

Estamos tambem como o mesmo problema, olhando no banco de dados os dados em formatos estranhos existem desde de março de 2024, mas o relatorio apenas travou dia 10/09. 
Vocês sabem se teve alguma atualização da Microsoft em seus servidores? 

[lili_santos]

I also have this error, do you know if this was some update in PowerBI?

[ThibautSTIEN]

I have exactly the same problem, 

Since yesterday, this error pops-up and doesn't allow my dashboard to be refreshed, which is really problematic.

I have no wierd data also, only 'null'  but that shouldn't be a problem

[v-jtian-msft]

Hello,GilbertQ ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@Rigensis .I am glad to help you.

You mention that it is a problem that has only occurred today, but not before. I think this is very strange, have you tried clearing the cache first and then re-performing the checking operation you have described. Sometimes data caching in the browser (on the service) and in the Desktop can affect the presentation of the data and refreshing on the service.

Check the data source cache: Make sure that the Google BigQuery data source is not caching old data. You can try running a query in BigQuery to make sure the data has been updated.
Republish the dataset: If the problem persists after changing the data, try republishing the entire dataset to the Power BI service to make sure that all the changes have been applied.
I think this is very strange indeed, do you provide more information which could help to find out what the problem is.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi @Rigensis 

What I would suggest doing is to ensure that you replace the dates with the wrong format to another date format which is valid. This will ensure that it actually will not fail on a particular date?

[Rigensis]

@GilbertQ Thanks for the suggestion, however the date format for all of the inputs in the table is the same and its a regular month-day-year. What was off with some of the dates was that the year in them was outside of the range the error mentions (1400-9999).

Did change the year in these instances to '1900' so that it is within range, but the error still persists