Convert UUID from MongoDb to UNIQUEIDENTIFIER

[Marusyk]

Records in MongoDB have the field _id

_id: UUID('ea265ecd-6d46-4af3-b41d-76fd11154574')

 I'm using Data pipeline in Microsoft Fabric to Copy Mongo collection to Warehouse,  as a result, I got these 2 columns:

how can I get my UUID back in the Warehouse?

Can I use Power Query (Dataflow Gen2 in Fabric) or transform it in Data Pipeline before/after Copy?

I've tried to convert it in PowerShell and it works as expected:

$b64 = "zV4m6kZt80q0HXb9ERVFdA=="
$bin = [System.Convert]::FromBase64String($b64)
$id= [System.Guid]::New($bin)
Write-Host $id
---> ea265ecd-6d46-4af3-b41d-76fd11154574

But how to do it in Fabric?

There is a similar post but the solution is for Spark which we don't use. 

[v-nikhilan-msft]

Hi @Marusyk 
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks 

[v-nikhilan-msft]

Hi @Marusyk 

You can do this in T-SQL in the warehouse.

select cast(base64_decode('zV4m6kZt80q0HXb9ERVFdA==') as uniqueidentifier)

Hope this helps. Please let me know if you have any further questions.

[v-nikhilan-msft]

Hi @Marusyk 
We haven’t heard from you on the last response and was just checking back to see if your query got resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[Marusyk · Accepted solution]

Yes, this query works fine. Thank you for your help. Also, I found one more solution by using Power Query. 

Solution 1. SQL Function

First, create the function. Unfortunately, Fabric doesn't support (yet?) scalar functions, so I used table-valued one

CREATE FUNCTION dbo.Base64ToUUID (@base64String VARCHAR(MAX))
RETURNS TABLE
AS
RETURN(
SELECT cast(base64_decode(@base64String) AS UNIQUEIDENTIFIER) AS UUID
);

 Then the function can be used as Ad-hoc transformation in queries:

select 
 "_id.$binary.base64" AS Original,
  U.UUID AS Converted
from dbo.Data c
CROSS APPLY dbo.Base64ToUUID("_id.$binary.base64") AS u

or it can be used in Dataflows Gen2 to transform the data and store:

add the function under Get Data

let
  Source = Fabric.Warehouse(null){[workspaceId = "bf28769e-c926-427d-b860-2e9999fe2fea"]}[Data]{[warehouseId = "33539h44-9bfc-48dd-93ba-73b0898ad6a9"]}[Data],
  #"Navigation 1" = Source{[Schema = "dbo", Item = "Base64ToUUID"]}[Data]
in
  #"Navigation 1"

then in other query use it:

// transformation
  Result = Table.TransformColumns(Data,
  {
    {"Id", each Base64ToUUID(_)0}[UUID], type nullable text},
     ...
  }),

Solution 2. Power Query

let
  ...
  Result = Table.TransformColumns(Data, {
    {"_id.$binary.base64", each GetGuid(_), type nullable text}
  }),

  GetGuid = (guidBase64 as nullable text) as nullable text =>
    if guidBase64 = null then null
    else
    let
        binaryGuid = Binary.FromText(guidBase64, BinaryEncoding.Base64),
        textGuid = Text.FromBinary(binaryGuid),
        guid = Guid.From(Binary.ToText(binaryGuid, 1))
    in 
      TransformGuid(guid),
  TransformGuid = (guid as text) as text =>
    let
      parts = Text.Split(guid, "-"),
      transformedParts = List.Transform(parts, each SwapLetterPairs(_)),
      finalGuid = Text.Combine({transformedParts{0}, transformedParts{1}, transformedParts{2}, parts{3}, parts{4}}, "-")
    in 
      finalGuid,
  SwapLetterPairs = (word as text) as text =>
    let
      pairs = List.Generate(
        () => [i = 0, result = ""],
        each [i] <= Text.Length(word),
        each [i = [i] + 2, result = Text.Range(word, [i], 2) & [result]],
        each [result]
      )  
    in
      List.Last(pairs)
in
  Result 

here you can see a little bit of magic. I'll try to explain. I don't know why but this Power Query code can return a UUID but the first part of that UUID has swapped symbols pair! Example:

guidBase64 = "zV4m6kZt80q0HXb9ERVFdA==" // which represents ea265ecd-6d46-4af3-b41d-76fd11154574
binaryGuid = Binary.FromText(guidBase64, BinaryEncoding.Base64),
textGuid = Text.FromBinary(binaryGuid),
guid = Guid.From(Binary.ToText(binaryGuid, 1))
// guid is : cd5e26ea-466d-f34a-b41d-76fd11154574
// Original: ea265ecd-6d46-4af3-b41d-76fd11154574
// problem is  here   here here

  So I defined the SwapLetterPairs function.

Is it a bug or is it by design? It would be great to have a simpler solution but ...

I use Solution 1 because it is more native and less error-prone. Hope this will be helpful for those who have spent hours trying to get this to work 😉