Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
amitchandak
Super User
Super User

@Anonymous , 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

Anonymous
Not applicable

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 

 

@Anonymous , 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/599712

 

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

Anonymous
Not applicable

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.