Forum Discussion

New_worker's avatar
New_worker
Icon for Helper II rankHelper II
1 year ago
Solved

Dataflow "mark as a key" VSwithout key column

Hi - 

 

According to official document "Using a key column indicates to the dataflow to upsert records into the destination table, while not selecting a key indicates to the dataflow to create new records in the destination table." I know dataflow follows upsert rule for the output with key column (mark as a key).

But I cannot find instructions for the output without key column. If output has updated value for a specific record which is existing in the destination like lakehouse or warehouse. How does it work when inserting?

Thanks in advance!

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi New_worker ,

    When a dataflow output does not have a key column marked, Power BI does not perform any form of update or upsert; instead, it simply treats each row as a new record and inserts it into the destination table (such as a Lakehouse or Warehouse). This means that even if a record with the same values already exists in the destination, the dataflow will still insert the new row, resulting in duplicate entries over time.

     

    Since there’s no key to match on, Power BI has no way of identifying whether a record should be updated or replaced. Therefore, if your data includes updated values for existing records and you want those to overwrite or update the previous versions, you must mark a key column to enable upsert behavior. Without a key, the dataflow acts purely as an append-only operation.

2 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Hi New_worker ,

    When a dataflow output does not have a key column marked, Power BI does not perform any form of update or upsert; instead, it simply treats each row as a new record and inserts it into the destination table (such as a Lakehouse or Warehouse). This means that even if a record with the same values already exists in the destination, the dataflow will still insert the new row, resulting in duplicate entries over time.

     

    Since there’s no key to match on, Power BI has no way of identifying whether a record should be updated or replaced. Therefore, if your data includes updated values for existing records and you want those to overwrite or update the previous versions, you must mark a key column to enable upsert behavior. Without a key, the dataflow acts purely as an append-only operation.

Related content