Create Duplicate Entries based on Column Value and change data entries

[DeanP]

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.

[v-juanli-msft]

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
    final

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

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