Dataset Refresh Error : The data types varchar and uniqueidentifier are incompatible

[leJuan5150]

Hi There,

As of 2017/09/14 we began to experience the following dataset refresh errors in the Power BI Service:

Neither the database schema or Power BI report were changed and the refresh had been working properly for months.  

I opened the Power BI Report in the latest version of Power BI Desktop (September 2017) and received the same error.

Finally, I opened the Power BI report in a previous version of the Power BI Desktop (July 2017) and the refresh worked fine.

So, I am guessing something was released in both the service and desktop in September that caused the issue.

After some troubleshooting I believe something has changed in M when dealing with "uniqueidentifier" fields from SQL Server.  In my report, I load a table containing a uniqueidentifier field (GUID) into Power BI, convert it to a "Text" value, then create a new calculated column where I concatenate it as part of a string.  This works in the July version of Power BI Desktop, but errors out in September's.

[Anonymous]

I notice the error talks about the "add operator".  I also notice you are talking about concatination.  What does your concatination statement look like?  Is it using the + operator?  Could you convert it to the & operator?

[leJuan5150]

Thanks for the responce @Anonymous.

I am adding a custom column where I concatinate the uniqueidentifier field to a string.  The step before creating the new field I made sure the uniqueidentifier field was of type "Text" and when doing the concatination I used the "&" operator.

This is something that has being working for ages, but is broken in the Septemeber 2017 release.

Any thoughts on a work around?

Microsoft ... Can you confirm this is a bug or is it working as intended?

[njrandell · Accepted solution]

I'm seeing exactly the same - stopped working on the 13th September. 

I've got it working by calling the Text.From function (https://msdn.microsoft.com/en-us/library/mt186370.aspx)

= Table.AddColumn(#"Filtered Rows", "Content Key", each [VenueId] & "#" & [ContentId])

to 

= Table.AddColumn(#"Filtered Rows", "Content Key", each Text.From([VenueId]) & "#" & [ContentId])

[leJuan5150]

I spoke to Microsoft support and they confirmed this issue is due to a design change to improve direct query performance against SQL Server.  No ETA for a fix as of yet.

Text.From is a good work around.