Conversion failed when converting from a character string to uniqueidentifier

[Anonymous]

I'm getting the following error on a table from SQL Server:

OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier..

Does anyone know how to get around this? I dont believe there is much I can do to change the table, since I need one of the columns to be the primary key.

[Anonymous]

Hi

I would one of two things in Power Query Editor

A. Change the data type to String but make sure it over-write the original

or

A. If you do not need the column, write a SQL Select and do not select the column in question

I hope this might help

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[mwegener]

Hi @Anonymous ,

i think it's a query folding problem.

Do you merge the query with another query?

Maybe you can use Table.Buffer.

https://docs.microsoft.com/en-us/powerquery-m/table-buffer

Think of Table.Buffer as, "load this table into memory, and stop folding subsequent operations back to the data source".

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

The other options didn't work so I'm trying to use table.buffer but Im getting the syntax wrong. Do you know what I'm doing wrong here?

error: Expression.Error: The import table.buffer matches no exports. Did you miss a module reference

let
    Source = Sql.Database("prod.windows.net", "prod-db"),
    dbo_vps_Sum = Source{[Schema="dbo",Item="vps_Sum"]}[Data],
    dbo_vpsView_SumBuffered = table.buffer(dbo_vps_Sum),
    #"Replaced Value" = Table.ReplaceValue(dbo_vpsView_SumBuffered,"D MHS","D",Replacer.ReplaceText,{"End Customer"}),
    #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"End Customer", Text.Trim, type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Trimmed Text",{{"End Customer", "End Customer(OneView)"}})
in
    #"Renamed Columns"

[mwegener]

Hi @Anonymous ,

it's case sensitive.  Table.Buffer

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Same error, except now I get it in query editor and not when refreshing the table in Power BI:

DataSource.Error: Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier.
Details:
DataSourceKind=SQL
DataSourcePath=prod-sql.database.windows.net;prod-db
Message=Conversion failed when converting from a character string to uniqueidentifier.
Number=8169
Class=16

let
Source = Sql.Databases("prod-sql.database.windows.net"),
#"prod-db" = Source{[Name="prod-db"]}[Data],
dbo_vps_Sum = #"prod-db"{[Schema="dbo",Item="vps_Sum"]}[Data],
vps_Buffer = Table.Buffer(dbo_vps_Sum)
in
vps_Buffer

I tried deleting the relationship between this table and another but that didn't help either.

[v-chuncz-msft]

@Anonymous 

This seems to be more related to Transact-SQL . You may visit https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=&forum=transactsql&filter=&sort=relevancedesc&brandIgnore=true&searchTerm=Conversion+failed+when+converting+from+a+character+string+to+uniqueidentifier.

[mwegener]

Hi @Anonymous ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener]

Hi @Anonymous 

is vps_Sum an SQL table or view?

What happens if you query

SELECT * FROM [dbo].[vps_Sum]

in SQL Management Studio

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

This was the solution for me.  We were trying to join nvachar to a uniqeidentifier, which is fine, but breaks if the field is empty