My code is below:
Issue is that I can duplicate the query and change the source but because the transformations involve columns with the name containing '2018' and in the new file these will be '2019'; everything else is the same. How can I do this?
Is there something like find and replace all in Advanced editor?
let
Source = Excel.Workbook(File.Contents("\\10.195.25.40\ww-data\Business\UK\Stourton\Commercial Team\Customer Business Unit\ECOMMERCE and DIGITAL\PERFORMANCE MANAGEMENT ECOMM\Template for Data_2018.xlsx"), null, true),
Asda_Sheet = Source{[Item="Asda",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Asda_Sheet,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type text}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}, {"Column32", type any}, {"Column33", type any}, {"Column34", type any}, {"Column35", type any}, {"Column36", type any}, {"Column37", type any}, {"Column38", type any}, {"Column39", type any}, {"Column40", type any}, {"Column41", type any}, {"Column42", type any}, {"Column43", type any}, {"Column44", type any}, {"Column45", type any}, {"Column46", type any}, {"Column47", type any}, {"Column48", type any}, {"Column49", type any}, {"Column50", type any}, {"Column51", type any}, {"Column52", type any}, {"Column53", type any}, {"Column54", type any}, {"Column55", type any}, {"Column56", type any}, {"Column57", type any}, {"Column58", type any}, {"Column59", type any}, {"Column60", type any}, {"Column61", type any}, {"Column62", type any}, {"Column63", type any}, {"Column64", type any}, {"Column65", type any}, {"Column66", type any}, {"Column67", type any}, {"Column68", type any}, {"Column69", type any}, {"Column70", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column1", "Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column1", type any}, {"Days to capture >", type text}, {"YTD", type any}, {"01/01/2018", type any}, {"01/02/2018", type any}, {"01/03/2018", type any}, {"01/04/2018", type any}, {"01/05/2018", type any}, {"01/06/2018", type any}, {"01/07/2018", type any}, {"01/08/2018", type text}, {"01/09/2018", type text}, {"01/10/2018", type text}, {"01/11/2018", type text}, {"01/12/2018", type text}, {"YTD_1", type any}, {"01/01/2018_2", type any}, {"01/02/2018_3", type any}, {"01/03/2018_4", type any}, {"01/04/2018_5", type any}, {"01/05/2018_6", type any}, {"01/06/2018_7", type any}, {"01/07/2018_8", type any}, {"01/08/2018_9", type text}, {"01/09/2018_10", type text}, {"01/10/2018_11", type text}, {"01/11/2018_12", type text}, {"01/12/2018_13", type text}, {"YTD_14", type any}, {"01/01/2018_15", type any}, {"01/02/2018_16", type any}, {"01/03/2018_17", type any}, {"01/04/2018_18", type any}, {"01/05/2018_19", type any}, {"01/06/2018_20", type any}, {"01/07/2018_21", type any}, {"01/08/2018_22", type text}, {"01/09/2018_23", type text}, {"01/10/2018_24", type text}, {"01/11/2018_25", type text}, {"01/12/2018_26", type text}, {"YTD_27", type any}, {"01/01/2018_28", type any}, {"01/02/2018_29", type any}, {"01/03/2018_30", type any}, {"01/04/2018_31", type any}, {"01/05/2018_32", type any}, {"01/06/2018_33", type any}, {"01/07/2018_34", type any}, {"01/08/2018_35", type text}, {"01/09/2018_36", type text}, {"01/10/2018_37", type text}, {"01/11/2018_38", type text}, {"01/12/2018_39", type text}, {"YTD_40", type any}, {"01/01/2018_41", type any}, {"01/02/2018_42", type any}, {"01/03/2018_43", type any}, {"01/04/2018_44", type text}, {"01/05/2018_45", type text}, {"01/06/2018_46", type text}, {"01/07/2018_47", type text}, {"01/08/2018_48", type text}, {"01/09/2018_49", type text}, {"01/10/2018_50", type text}, {"01/11/2018_51", type text}, {"01/12/2018_52", type text}, {"Column68", type any}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type1",1),
#"Removed Columns1" = Table.RemoveColumns(#"Removed Top Rows",{"Days to capture >"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Column1", "EAN"}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"EAN", Int64.Type}}),
#"Removed Columns2" = Table.RemoveColumns(#"Changed Type2",{"YTD", "01/01/2018", "01/02/2018", "01/03/2018", "01/04/2018", "01/05/2018", "01/06/2018", "01/07/2018", "01/08/2018", "01/09/2018", "01/10/2018", "01/11/2018", "01/12/2018", "YTD_1", "01/01/2018_2", "01/02/2018_3", "01/03/2018_4", "01/04/2018_5", "01/05/2018_6", "01/06/2018_7", "01/07/2018_8", "01/08/2018_9", "01/09/2018_10", "01/10/2018_11", "01/11/2018_12", "01/12/2018_13", "YTD_14", "01/01/2018_15", "01/02/2018_16", "01/03/2018_17", "01/04/2018_18", "01/05/2018_19", "01/06/2018_20", "01/07/2018_21", "01/08/2018_22", "01/09/2018_23", "01/10/2018_24", "01/11/2018_25", "01/12/2018_26", "YTD_27", "YTD_40", "01/01/2018_41", "01/02/2018_42", "01/03/2018_43", "01/04/2018_44", "01/05/2018_45", "01/06/2018_46", "01/07/2018_47", "01/08/2018_48", "01/09/2018_49", "01/10/2018_50", "01/11/2018_51", "01/12/2018_52", "Column68"}),
#"Removed Top Rows1" = Table.Skip(#"Removed Columns2",1),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Top Rows1",{{"01/01/2018_28", "01/01/2018"}, {"01/02/2018_29", "01/02/2018"}, {"01/03/2018_30", "01/03/2018"}, {"01/04/2018_31", "01/04/2018"}, {"01/05/2018_32", "01/05/2018"}, {"01/06/2018_33", "01/06/2018"}, {"01/07/2018_34", "01/07/2018"}, {"01/08/2018_35", "01/08/2018"}, {"01/09/2018_36", "01/09/2018"}, {"01/10/2018_37", "01/10/2018"}, {"01/11/2018_38", "01/11/2018"}, {"01/12/2018_39", "01/12/2018"}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Renamed Columns1", {"01/01/2018", "01/02/2018", "01/03/2018", "01/04/2018", "01/05/2018", "01/06/2018", "01/07/2018", "01/08/2018", "01/09/2018", "01/10/2018", "01/11/2018", "01/12/2018"}, "Attribute", "Value"),
#"Changed Type3" = Table.TransformColumnTypes(#"Unpivoted Only Selected Columns",{{"Attribute", type date}, {"Value", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type3", "Retailer", each "ASDA"),
#"Changed Type4" = Table.TransformColumnTypes(#"Added Custom",{{"Retailer", type text}, {"Value", type number}})
in
#"Changed Type4"
