Having trouble with key

[danielru]

Hi,

I've created a dataflow where I get and transform an Excel range and push it to the dataverse table.

I'm trying to set a key when mapping the fields before publishing the dataflow.

Firstly, I created a brand new table with a primary column of type text and it populates from Excel to Dataverse fine if I don't set the key but when I run the refresh a second time it adds the same data again resulting in duplicates.

I need to upsert and I understand this requires the key to be set. It seems that it only allows a system key to be selected which expects a GUID argument.

I have a unique column which is of type number in my excel sheet which I want to use for the key but it seems this isn't allowed.

firstly is it possible and secondly how do I implement this?

Thank you so much!

Regards,

Daniel

[jennratten · Accepted solution]

Hello @danielru - you can add a new column to your table in Power Query and use the Text.NewGuid() function like so:

Key = Table.AddColumn ( Source, "Key", each Text.NewGuid(), type text )

https://powerquery.how/text-newguid/ 

[jennratten · Accepted solution]

Hello @danielru - you can add a new column to your table in Power Query and use the Text.NewGuid() function like so:

Key = Table.AddColumn ( Source, "Key", each Text.NewGuid(), type text )

https://powerquery.how/text-newguid/ 

[danielru · Accepted solution]

Hi @jennratten ,

Thanks so much for your help. I found the solution to this problem. I had to set up a key on my table as per this post:

https://powerusers.microsoft.com/t5/Microsoft-Dataverse/How-do-you-prevent-a-dataflow-from-creating-...

Then I was able to select the key and it mapped automatically so working well.

Thank you again for all your help!

Regards,

Daniel