copy and insert a row with one change

[JohnSalt]

Hi 

I have a large table (83 Cols, 2M+ Rows)  is that large these days?, I need to find the rows where the field 'productcode' is between 8000 and 9000, generate a duplicate row for each but with the 'createdDate' field set to one month earlier, and then again prior to that etc all the way back to the start of the uk fiscal year i.e. 1st April.

I sort of have parts of the picture using [createdDate = Date.AddMonths([createdDate],-1)] to reduce the month by one and using Table.insertrows for an additional row however with 83 cols i dont fancy specifying each and every single one! Is there a way in power query to just duplicate the entire row and change the createdDate to the month before?

[SpartaBI · Accepted solution]

Hi @JohnSalt,

You can try to manipulate the rows as records, but I think that a simpler approach will be to:
1. Filter the rows you need to duplicate.
2. Change the values.
3. Create a union of the original table with the changed rows.

look at this code

let
    Source = #table( type table [value1=text,value2=text,date=date], {{"a","aa",#date(2000,1,1)},{"b","bb",#date(2000,2,1)},{"c","cc",#date(2000,3,1)}}),
    #"Filtered Rows" = Table.SelectRows(Source, each ([date] >= #date(2000, 2, 1))),
    #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",each _[date],each Date.AddMonths(_[date],-1),Replacer.ReplaceValue,{"date"}),
    RestoreType = Value.ReplaceType(#"Replaced Value",Value.Type(#"Filtered Rows")),
    #"Appended Query" = Table.Combine({Source, RestoreType})
in
    #"Appended Query"

 Start:

End:

You will need to check the performance of this operation for your case.

Showcase Report – Contoso By SpartaBI