cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

[data.format error] not specified error

Hello everyone, i would like some help with this error on the screenshot.

 

its in brazillian portuguese, but the error more or less transtales into:

 

applying query changes

error ole db or odbc: [dataformat.error] not specified error.

DATA.FORMAT ERROR.PNG

 

Some of my works are starting to show this kind of error after i apply or release filters in the query editor.

 

Example: Some times i need to apply filters on cities in the query editor to 4 specified cities (instead of leaving the file with 12 cities) when adding newer month data, to have a pbi file with less information for cooworkers with old pcs to use.

 

I really dont know where the problem is, in my mind theres some problem between the 2 pc`s where i usually apply query changes. i have my work pc, which have 16Gb ram with windows 7 where i always start and develop my power bi desktop reports and in this pc i dont remember getting this kind of error applying query changes. My personal pc, i usually apply query changes while studing or watching tv. My home pc is on windows 10, also 16gb of memory and is far newer than my work pc (thats another motive to apply query changes home, because its faster). 

 

just another observation, everything seens to be set in the right type of data in the query editor, i cant really find where is the problem, since the error is not pointing me where is the problem.

 

 

 

 

 

 

 

 

 

 

 

8 REPLIES 8
Helper V
Helper V

Hummmm... setting everything to text in query editor does make the query changes to load fine....is there anything i could to find the inconsistent fields?? its a 60 million rows table. Any hints would be great. thks in advance.

 

Another question: is there any problem converting every data type on the data tab?? everything seens to work fine and the results are correct. But when converting the columns from the data tab, it shows a message telling me that i could loose data or precision in the process!!!

@bolabuga,

I would recommend  you go to Query Editor of Power BI Desktop, check if there are any issues for each step under "APPLIED STEPS" . It is possible that there are some invalid values for the numerical type columns or date type columns,

In addition, do you convert all the columns to text type? If so, the columns will be aggregated using different functions in visuals comparing to numerical type columns.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

I did convert all the columns to text type to bypass the problem, because im not finding the inconsistent information on the columns.

 

Taking account of what you said, my problem probably is 1 or more of the numerical type columns, which i have, if i remember correctly, 5. For reasons of testing, i converted everything on the query editor to "text", applied the changes, i didnt had any errors.

 

After that my reports on the view where all with errors. Then i went to the data tab and converted everything that i needed into integer type column and decimal type column. Comparing the results before and after applying all the changes, everything is the same.

 

What concerns me is the fact that, when converting from text to integer or decimal type, using the data tab, power bi warns me telling that i could loose data or precision in the process.

 

Just another observation, i did convert today the same powerbi report on my work pc, with no errors at all, several times. In my personal pc i can only apply the query changes if i convert everything to text format in query editor.

@v-yuezhe-msft

 

I found 1 of the columns that have the error, while trying to see the data in the filter. its a yearmonth column defined as integer type. Im sure that i have other errors, because when setting this specific column to text, i still have data.format error applying query changes.

 

The question is: Is there any way to find the rows with those errors??? any tutorial that anyone could point me?? thks in advance.

 

YEARMONTH ERRROR.PNG

@bolabuga,

Is there ant possibility that you share the source file to me? I will test it in my enviornment.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

I really would like to share it, but it is against the bussiness rules in my work place to share this database.  Any way really thks for your replies.

@bolabuga,

You can share dummy data to me.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yuezhe-msft

 

Sorry for my lack of knowledge, but, whats "dummy data"?? will this dummy data not contain all the years of info anyway??? my concern is that im forbidden to share any actual data of my service.

 

Again thks for your reply.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors