Type error on refresh in service but not Desktop

[jervt]

Running into an issue where the service is throwing a type error when trying to refresh a dataset. This error doesn't show up when refreshing on PBI desktop - which otherwise refreshes without issue. The error specifies a table, but no columns. The query that feeds into that table has several appends which I have checked for type mismatches and can't find anything. Error message below.

"The following system error occurred: Type mismatch. Table: Billing TX all services."

[SYM1986]

HI @jervt ,

i also had this issue, but i fixed it now, after check it is because i have 2 step of add custom column in query, and its type is time.

After delete this two steps, i use new column, and everything works well.

[Dataviz_Global]

Change all columns to undefined data type before all append steps after the final step, apply desired data type to the columns.

[SaiTejaTalasila]

Hi @jervt ,

Open transform data and on your left hand side check error queries and helper queries you might get more details.

As per my understanding you need to back track all your applied steps in transform data.Base on the details you mentioned The following system error occurred: Type mismatch. Table: Billing TX all services." First you need to check Billing TX all services carefully if you can't able to find errors then you need to check it's source tables which are getting append.

Thanks,

Sai Teja 

[SaiTejaTalasila]

Hi @jervt ,

This is how I trouble shoot issues-

I will open transform data and I will check error message on your left it will show error queries,then I will click on any of the error rows in that query it will give an idea what is causing the issue.From my experience on issue based ticket handling most of the time the errors will come from date/time columns.If I was unable to find the error in that case I will copy the power query and I will take new blank query and I will try to do some adjustments or rebuild all transformation steps.If everything is fine then I will replace the orginal query with this one.

Thanks,

Sai Teja 

[JMDESMON]

I've exactly the same issue. Did you finally find a fix?

[jervt]

Nope. I ended up opening a ticket with microsoft support who I worked with for several months before they finally got me to close the ticket by (apparently) lying to me about how it had been escalated to the dev team who was allegedy working on rolling out a fix within the following month. This did not happen and when I asked the support tech to reopen the ticket as they said I could, I got no response.

[khaledHabib90]

This can be a problem with a date colum. You have in your data. It might be ok on your desktop due to local date settings but when published to the service the date settings change. Like in MM/DD/YYYY and DD/MM/YYYY

check your date columns and maybe this article helps

https://www.myonlinetraininghub.com/change-type-using-locale-with-power-query

If my response helped you solve this problem, please mark it as the solution

[jervt]

This doesn't seem to be what's going on. The date field in both tables is exactly the same and stored in exactly the same format in the database.

[SaiTejaTalasila]

Hi @jervt ,

In transform data tum on column quality and check whether your date columns have any null values.If yes-then you have to replace the null with some date.

Thanks,

Sai Teja 

[jervt]

No nulls.