Forum Discussion
Create Duplicate Entries based on Column Value and change data entries
I want to add a duplicate of a row in my existing table based on value in another column in the same table using power query:
I have a column called 'ITM CODE' in my table. I want, for every row where the ITM CODE = SLMANFEE, that entire row to be duplicated and placed at the bottom of the existing table. Then I want the data the the second column of the copied row to be changed to "WMC".
Any help would be appreciated.
Hi DeanP
In Power Query
Assume your table is "Sheet9"
create a new blank query, open Advanced editor, write the code
let Source= Table.SelectRows(Sheet9, each[ITM CODE]="SLMANFEE") in Source
then in this table,
click on the [ITM CODE] column, select "Transform"->Replace values,
Then in Sheet9, select "Home"->Append Queries", Append Queries as new,
Please refer to my pbix.
Best Regards
Maggie
2 Replies
Hi DeanP
In Power Query
Assume your table is "Sheet9"
create a new blank query, open Advanced editor, write the code
let Source= Table.SelectRows(Sheet9, each[ITM CODE]="SLMANFEE") in Source
then in this table,
click on the [ITM CODE] column, select "Transform"->Replace values,
Then in Sheet9, select "Home"->Append Queries", Append Queries as new,
Please refer to my pbix.
Best Regards
Maggie
Hi DeanP
You could also use the following code in your data table to create Duplicate Entries directly.
let Source = Excel.Workbook(File.Contents("C:\Users\maggiel\Desktop\case\11\11.28\11.28.xlsx"), null, true), Sheet9_Sheet = Source{[Item="Sheet9",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Sheet9_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"col1", Int64.Type}, {"ITM CODE", type text}, {"col3", Int64.Type}, {"col4", type text}, {"col5", type text}}), Source2 = Table.SelectRows(#"Changed Type", each[ITM CODE]="SLMANFEE"), #"Replaced Value" = Table.ReplaceValue(Source2,"SLMANFEE","WMC",Replacer.ReplaceText,{"ITM CODE"}), final=Table.Combine({#"Changed Type", #"Replaced Value"}) in finalBest Regards
Maggie