Inconsistent translation of SQL Server UniqueIdentifier data type.

[Mixednuts]

I have noticed in PowerQuery there is a difference in how the GUID is returned between DirectQuery and Import. This would cause problems for linking I would have thought.

Both methods convert the UniqueIdentifier datatype in SQLServer to Text in Power BI

However when the same table is queried via Direct Query the GUID is returned surrounded by braces {}, eg. {8B8B9F7B-21A8-4C87-02DA-08D74D01C96B}.

When Import is used the GUID is returned without braces, eg 8B8B9F7B-21A8-4C87-02DA-08D74D01C96B

I also noticed that while in the original source the GUID alpha characters are in upper case (which is consistent when retrieved via Direct Query), when Imported, they are all reduced to lower case, eg. b830bed8-3e01-4525-02ce-08d74d01c96b

Any idea why....?

[pedrocrmventura]

Hi @Mixednuts , v-frfei-msft

I had the exact same issue and I was able to solve it by adding a Step to transform that column data ("RequestId") to a GUID type:

= Table.TransformColumnTypes(#"Changed Type with Locale",{{"RequestId", Guid.Type}})

After adding this step, in Edit Relationship you can see that the columns are now of the same type, check image below :

You can check for more info on this on: https://learn.microsoft.com/en-us/powerquery-m/guid-from

[v-frfei-msft]

Hi @Mixednuts ,

Kindly disable the option to check again.

[Mixednuts]

Hi Frank,

Thanks for responding.

After unsetting the type detection, PowerQuery showed the values in both the Import and Direct Query tables with consistent formats in the data preview, ie. no braces and all lower case.

However, after a full refresh back in the model, Direct Query tables are still showing GUIDs with braces and in capitals while Import tables are showing the same GUIDs without braces and in lower case.

Both tables (Import and DirectQuery) are sourced from the same Azure SQL Server table using basically the same SQL script with different date filtering.

regards,

John