Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
leJuan5150
Helper I
Helper I

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

Hi There,

 

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

 

2017-09-15_12-56-07.png

 

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.

 

2017-09-15_13-27-30.png

 

1 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])

 

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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?

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?

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])

 

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.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.