Duplicate Record and change one value

[Florex]

Hello kind community!

I want to duplicate any record in my dataset and want change one value (due to multiple relationship is not possible)

How is currently looks:

Product	ID	old_ID
Cola	123	ABC
Fanta	888	DEF
Sprite	777	GHF

What it should look like:

Product	ID	old_ID
Cola	123	ABC
Cola	ABC	ABC
Fanta	888	DEF
Fanta	DEF	DEF
Sprite	777	GHF
Sprite	GHF	GHF

Background: Want to use ID column for a relationship to other table and i need old and new identifier to find all processes 

Thank you

[danextian · Accepted solution]

Hi @Florex 

You  can easily duplicate all rows of a table in the query editor by using Table.Repeat or by refererring to another t query for the list of IDs to be duplicated for selective duplication. But doing either will most likely result to a many-to-many relationship, the current table being on the many side.  Please see attached pbix for reference.

[v-junyant-msft · Accepted solution]

Hi @Florex ,

@danextian Good Answer! And you can also achieve this by Unpivoted in Power Query:
Duplicate the original table first:

And in the new table Table(2), select column ID and old_ID and choose "Unpivot Only Selected Columns":

Then choose "Merge Queries as New" in Table(2):

The output is as below:

Follow the diagram below to set up and delete the column Attribute:

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @Florex 

You  can easily duplicate all rows of a table in the query editor by using Table.Repeat or by refererring to another t query for the list of IDs to be duplicated for selective duplication. But doing either will most likely result to a many-to-many relationship, the current table being on the many side.  Please see attached pbix for reference.