1

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.

Philip M
  • 13
  • 2

1 Answers1

1

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

horseyride
  • 17,007
  • 2
  • 11
  • 22
  • Unfortunately, the dataset is large and there are rows with duplicate data. These duplicates are not errors, so I need to generate the number without reference to column contents. I will have rows with identical data, but each row needs a unique ID. – Philip M Mar 10 '23 at 19:29
  • If the data just gets more and more rows but "sticks" in the same spot, you could use an index. If there were no duplicates you could use a hash. You can not assign a unique ID to a row in powerquery if the source data provides duplicate rows, and the contents of the rows and/or the sort order thereof changes each time you refresh. You would need to get a unique ID added at the source before powerquery gets it. You could look into powerbi incremental refresh at https://learn.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview – horseyride Mar 10 '23 at 21:15