Data Refresh Issue in Power BI Service Due to Type Mismatch

[Dhruv_Ranpura]

Hello All,

I’m encountering a data refresh issue in Power BI Service. I’ve created a Power BI report that publishes successfully, but when I schedule an automatic refresh or try to refresh manually on the service side, I receive an error indicating a type mismatch in one of my tables.

The table in question is coming from SQL Server, and here’s the M-query I’m using in Power BI:
And this is my M-query for that particular tbale in PowerBI .
 let
 Source = Sql.Database("***********", "********"),
 dbo_BSEG_FI_New = Source{[Schema="dbo",Item="BSEG_FI_New"]}[Data],
 #"Sorted Rows" = Table.Sort(dbo_BSEG_FI_New,{{"H_BUDAT", Order.Ascending}}),
 #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"H_BUDAT", type date}})
 in
 #"Changed Type"

I’m trying to identify the root cause of the type mismatch error so that I can successfully schedule an auto-refresh for this report without any issues. Could you please help me figure out the potential reasons for this error and how to resolve it?

Any guidance would be greatly appreciated!

[Dhruv_Ranpura · Accepted solution]

I think the issue was with PowerBI file. Then I again recreated the same powerBi and I am able to shedule a refresh in PowerBI Service.

[pallavi_r]

Hi @Dhruv_Ranpura ,

Please ensure the data source column types should be same data model column type. For debugging and identifying the root cause, I suggest pull it from view rather than table. In the view, type cast the error prone columns specifically datetime related fields.

Thanks,

Pallavi

[Dhruv_Ranpura]

Hi @pallavi_r ,
Thank you for your response.
I tried to create view for that table where I typecasted the date field from nvarchar to date datatype and used that view instead of original table. And then I publised that report to service it got published successfully. But, after publishing report when I am trying to refresh from service side I again got the same error.
"Data source errorThe following system error occurred: Type mismatch. Table: BSEG_FI_New."

I try one different way too I created one blank report where I connected that newly created view  and created one sample visual using some fields from that view and published that report. And, also tested to refresh that sample report from PowerBi service side it gets refreshed successfully. 
So, what to do? 

[pallavi_r]

Hi @Dhruv_Ranpura 

Good that you tried with this approach. If you have already verified the type conversion in data source, power query transformation and model, then the probable cause could be in any of the following.

a) verify that any relationship with this table should take place using matching data types.

b)verify all dax and calculated formulas associated with table is taking place with matching data type

c)if datasource has null values, then make sure model handles it correctly with default value or as per the requirement.

Can you please check these 3 points and let me know.

Thanks,

Pallavi