cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

Opendata import problem

Hello pBI experts,

I have been using this opendata dataset for months : https://opendata.arcgis.com/datasets/814f8e1550db4852b2c0a79f23c66f9c_0.geojson

But for a few days, I have a problem : "OLE DB or ODBC Error: [DataFormat.Error]".

It seems that after navigation-> convert to table->column1 developed the column "date_fin" you cannot change text to date and time format.

I have tried to find where the problem comes from : there is no "strange" row data, no header used to make the format... I still can't change the text format at the time of the date...

Is there any way to make sure that the text matches the date format before formatting? Could you help me solve this formatting problem?

Best regards

Antoine

4 REPLIES 4
Highlighted
Super User IV
Super User IV

Re: Opendata import problem

@AntoineBa , Open Edit Query / Data Transformation mode. You will get a better idea of issues there. Correct Data type or any other stuff there

https://docs.microsoft.com/en-us/power-bi/desktop-shape-and-combine-data



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Resolver I
Resolver I

Re: Opendata import problem

Hello @amitchandak ,

 

I already used it to change the column type from text to date/time and it seems to work on the preview. But when applying queries changes, there is an error.

query preview with text field changed to date fieldquery preview with text field changed to date field

I suspect one of the rows not to be "date" text, but I do not know how to fix it.

 

Here is the PBIX file : https://1drv.ms/u/s!Av9rycz_5ECFjbUcR_P-PZzqLqScyQ?e=S2DVD5 

 
Highlighted
Super User IV
Super User IV

Re: Opendata import problem

@AntoineBa , DD/MM/YYYY format, If all your dates are going to be like this

Then do like

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

 

Or Covert DD/MM/YYYY to MM/DD/YYYY

 

DD/MM/YYYY to MM/DD/YYYY
mid(DD__MM__YY[Version_Id],4,2) &"/"& left(DD__MM__YY[Version_Id],2) & "/" & right(DD__MM__YY[Version_Id],4)

or
date(year( right(DD__MM__YY[Version_Id],4)), month(mid(DD__MM__YY[Version_Id],4,2)) ,day(left(DD__MM__YY[Version_Id],2)))

 

First one you can convert to M, using text funtions



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Resolver I
Resolver I

Re: Opendata import problem

@amitchandak ,

 

I tried to convert the values to date / time in both formatting :  DD/MM/YYYY to MM/DD/YYYY

Also tried regional settings...

 

But still not able to get it. When closing the power query editor and loading all dataset, something is crashing it.

If you want to give it a try, here is the file : https://1drv.ms/u/s!Av9rycz_5ECFjbUcR_P-PZzqLqScyQ?e=LCHZZ4

 

Regards,

 

Antoine

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors