Forum Discussion
Duplicate Record and change one value
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
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.
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.
2 Replies
- danextian
Super User
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.
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.