How to join two tables for which type of UniqueIdentifier is lost after adding a column

[jwathena]

 I have imported a table "TaxonomyElements" from SQL Server. It contains an ID column of the type UniqueIdentifier, shown in Power BI as text value between curly brackets.

Now I created a derived table from this in Power Query, that does some transformation on the table. It appears however that after adding some columns, the type information of the HierarchyNodeId (the foreign key reference) somehow's messed up. Its value is now shown as text GUID value.

Whilst I am able to join column values with the text GUIDs presentation with the  UniqueIdentifier values in Power Query just fine, Power Query just won't handle the relationship properly. None of my records get matched in Power BI. Screenshot 1 demonstrates the problem in different column types (columns do contain in fact the same values)

I initially thought of adding an index column to all tables but doing so unfortunately already makes the ID column lose its type so even then I can't join it on the ID column to find the index column.. . Now as a last resort I can only think of simply converting all my UniqueIdentifier to GUIDS using Guid.Value([ID]) as text for ALL my tables in my workbook, but it seems really inefficient and cumbersome. Screenshot 2 shows me adding the custom column, in screenshot 3 shows the type of the original table is affected by simply adding this custom column. While that does join these two tables, of course the issue now just cascades to any other relationship I have to make in my model.

How can I preserve the type of HierarchyNodeId in Power Query so that Power BI will handle the relationship properly? Or at least cast it back in one way or another?

 

 

[v-heq-msft]

Hi @jwathena ,
Thanks for PwerQueryKees reply.

Based on your description, in Power Query, make sure that the HierarchyNodeId column in both tables is explicitly set to the Text data type. This helps maintain consistency when creating relationships. After ensuring that the data types are consistent, recreate the relationship in Power BI. Go to the Modeling tab, select Manage Relationships, and manually create the relationship between the tables. Ensure that there are no duplicate values in the HierarchyNodeId column, as this can cause relationship issues. You can use the Remove Duplicates feature in Power Query to clean up the data. You can use the following code to transform the data types

let
    Source = ...,
    TaxonomyElements = Source{[Schema="dbo",Item="TaxonomyElements"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(TaxonomyElements,{{"HierarchyNodeId", type text}})
in
    #"Changed Type"

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[PwerQueryKees · Accepted solution]

Not at my laptop, so could not test.

Forget about the custom column to convert a GUID to text. There are several text representation standards for a GUID. But there is only one binary one.

So, convert textual GUID column to binary. After that your join should work

Table.TransformColumns(Source, 
   {"Your TextualGUID column name here",
      each Binary.FromText(Text.Remove(_, {"-","{","}"}), BinaryEncoding.Hex)
})