Duplicate rows in a table based on details of another column

[JenWilson]

Hi, 

I would like to (hopefully) just add rows to the existing table "Production Data". 

In the table below, the first 3 columns represent existing content while the last 3 columns represent how I would like the outcome of my request to look.

My goal is to duplicate any row where the Product Code column is "AB". The duplicate row would then modify the Department Column data to say "Sanding". In essance, there are 2 departments that worked on the same "product code" item and we need to account for that work across both department. 

So the formula would need to look for the specified Product Code "AB" and if found add a row matching all row content but change the Department column to "Sanding".  If any other Product Code is in the Product Code column, it would just keep the one row. 

Something as simple as this perhaps?

IF [Product Code] = "AB", then duplicate row but change [Department], "Sanding" 

Product Code	Department	quantity		Product Code	Department	quantity
AB	Welding	10		AB	Welding	10
AB	Welding	20		AB	Welding	20
AB	Welding	30		AB	Welding	30
AB	Welding	40		AB	Welding	40
AB	Welding	50		AB	Welding	50
AB	Welding	60		AB	Welding	60
CD	Welding	100		AB	Sanding	10
CD	Welding	100		AB	Sanding	20
CD	Welding	100		AB	Sanding	30
CD	Welding	100		AB	Sanding	40
				AB	Sanding	50
				AB	Sanding	60
				CD	Welding	100
				CD	Welding	100
				CD	Welding	100
				CD	Welding	100

[alekhved]

hi @JenWilson ,

Here you go. ( Power Query Editor)

Step 1 : Create a duplicate table with the same data. Right click on exisiting table, click on duplicate. 

Step 2 : Filter the data in new table based on Prodcut Code = AB

Step 3 : Replace the value from Welding to Sanding

Step 4 : Click on original table, Go to Home -> Append Queries -> Append Queries as New. Select Both the table and in the end you would have one more table with expected rows.

Table 1 :

Table 2

Thanks!

[JenWilson]

@alekhved , will these steps ensure any newly added data will be addressed in the same way? There are multiple entries coming in daily for these product codes. Thanks! 

[SivaMani]

@JenWilson Another way to achieve this is,

• Add a table with Product Code and Department

• Use Merge Queries[Left Outter] - New table <- Existing table

• Expand the columns
  

You can use the New table as a control table and maintain your combinations.

[JenWilson]

This option doesn't seem feasable, as there are hundreds of product codes that I would need to add to the additional table. 

[alekhved]

hi @JenWilson 

That can be done too. In the Advance Editor of Table (2), Change the source defintion as 

Source = #"Table",

(here the  Table  is the original name of the table that you want to replicate)

Thanks!

[JenWilson]

  Unfortunately, I don't think this solution will work as the source is SQL code and it didn't like the # placement.