databasepowerquerylinked-tables

Power Query New column with Unique ID

I have an excel power query that transforms new files when added to a folder. The resultant table is then linked to a database. I would like to create a new column in power query that creates a unique ID that will never change (Always assigned to the specific record) even when table gets appended by a query refresh. The ID will serve as primary key in the linked database table.

The solutions that I'm trying either involve a concat of index and/or timenow functions, but these change every time the query is refreshed or resorted.

Solution

  • If you have some non-changing columns, you could try to hash them into a unique ID such as

     = Binary.ToText(Text.ToBinary(Text.Combine({[Column1],[Column2],[Column3]})))

    or with

    =  Text.Combine(List.Transform(Text.ToList(Text.Combine({[Column1],[Column2],[Column3]})), each Text.From(Character.ToNumber(_))))

    full sample code

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Hash1 = Table.AddColumn(Source, "Hash", each Text.Combine(List.Transform(Text.ToList(Text.Combine({[Column1],[Column2],[Column3]})), each Text.From(Character.ToNumber(_))))),
Hash2 = Table.AddColumn(Hash1, "Hash2", each Binary.ToText(Text.ToBinary(Text.Combine({[Column1],[Column2],[Column3]}))))
in Hash2

    enter image description here