[data.format error] not specified error

[bolabuga]

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.

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.

[bolabuga]

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!!!

[v-yuezhe-msft]

@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

[bolabuga]

@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.

[bolabuga]

@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.

[v-yuezhe-msft]

@bolabuga,

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

Regards,
Lydia

[bolabuga]

@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.

[v-yuezhe-msft]

@bolabuga,

You can share dummy data to me.

Regards,
Lydia

[bolabuga]

@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.