How to preserve or restore a unique primary key after pivoting

[leo124]

Hi all,

I loaded an Excel file into Power BI that originally had a unique primary key, and I wanted to create a 1-to-many relationship with two other tables. However, after unpivoting, the primary key started appearing multiple times in the column. I had to perform the unpivoting, but now the primary key is no longer unique. How can I find a way to keep the primary key usable despite this?

If you need more information, just let me know.

[v-mdharahman]

Hi @leo124,

Thanks for reaching out to the Microsoft fabric community forum. This is expected behaviour after an unpivot, the unpivot operation creates multiple rows per original record, so the original primary key will appear multiple times in that table. To keep a true unique primary key you should separate the unique attributes from the repeated (pivoted) rows. As @SamWiseOwl responded to your query, kindly go through the response and chekc if your issue can be resolved.

I would also take a moment to thank @SamWiseOwl, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.

[v-mdharahman]

Hi @leo124,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround so that other users can benefit as well.  And if you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[SamWiseOwl]

Hi @leo124 

You can split the data into 3 tables.

Unique table - the primary key and any other unique information

Join table - unique id and the pivotid

Pivot table - pivotid for each row and the pivoted data

I've attached a sample file with the Query Editor steps.