cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mra1984
Helper II
Helper II

how to copy a query where dates are involved

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"

1 ACCEPTED SOLUTION
PattemManohar
Community Champion
Community Champion

@mra1984 If you are looking for text find & replace in "Advanced Editor", the answer is NO. You need to copy the code into an editor or Notepad and then find/replace the text as you like





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

1 REPLY 1
PattemManohar
Community Champion
Community Champion

@mra1984 If you are looking for text find & replace in "Advanced Editor", the answer is NO. You need to copy the code into an editor or Notepad and then find/replace the text as you like





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors