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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors