Changing Power Query Transforms, without destroying previous previous

%3CLINGO-SUB%20id%3D%22lingo-sub-1414118%22%20slang%3D%22en-US%22%3EChanging%20Power%20Query%20Transforms%2C%20without%20destroying%20previous%20previous%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414118%22%20slang%3D%22en-US%22%3E%3CP%3ESetup%20a%20Folder%20Power%20Query%20with%20transforms%20for%20a%20daily%20downloaded%20.csv%20report.%20It%20works%20well%2C%20but%20now%20we%20need%20to%20add%20a%20new%20column%20to%20the%20downloaded%20report.%20I%20can%20easily%20define%20new%20transforms%20for%20the%20extra%20column%2C%20but%20won't%20this%20be%20a%20problem%20for%20previously%20imported%20.csv%20data%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20update%20the%20transform%20for%20the%20new%20column%20of%20data%20without%20messing%20up%20the%20previous%20data%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1414118%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1414412%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20Power%20Query%20Transforms%2C%20without%20destroying%20previous%20previous%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414412%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20theory%20if%20it's%20just%20an%20additional%20column%20it%20may%20not%20be%20a%20problem%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20need%20to%20change%20the%20Sample%20file%20to%20the%20new%20file%20number%20temporarily%20(or%20easier%20option%20is%20to%20name%20the%20new%20file%20something%20that%20forces%20it%20to%20appear%201st%20alphabetically%20just%20while%20you're%20fixing%20up%20the%20Transform%20Sample%20file%20step)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22tinyMceEditorWyn%20Hopkins_0%22%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20731px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194123iF843A656E87014F3%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAlso%2C%20you%20may%20need%20to%20change%20the%20column%20number%20in%20the%20source%20step%20of%20the%20Transform%20sample%20file%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F194124i5463245EC05D7AEA%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EHope%20that%20helps%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EWyn%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EMVP%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3EAustralia%20UTC%2B%208%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20style%3D%22font-style%3A%20italic%3B%22%3EIf%20this%20answer%20was%20the%20best%20response%20please%20click%20the%20button%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20style%3D%22margin%3A%200in%3B%20font-family%3A%20Calibri%3B%20font-size%3A%2011.0pt%3B%22%3E%3CSPAN%20style%3D%22font-style%3A%20italic%3B%22%3EI%20also%20happily%20accept%20likes%20%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1414757%22%20slang%3D%22en-US%22%3ERe%3A%20Changing%20Power%20Query%20Transforms%2C%20without%20destroying%20previous%20previous%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1414757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F677903%22%20target%3D%22_blank%22%3E%40spectrl%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20in%20addition%20to%20Wyn's%20suggestion%2C%20in%20Sample%20File%20not%20to%20select%20concrete%20file%2C%20but%20on%20previous%20step%20before%20selecting%20first%20in%20the%20list%2C%20sort%20table%20in%20descending%20order%20on%20date%20of%20modification%2C%20thus%20the%20latest%20file%20will%20be%20taken%20as%20the%20sample.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%2C%20from%20my%20point%20of%20view%20most%20reliable%20is%20to%20keep%20in%20the%20folder%20the%20file%20as%200000.xlsx%20with%20only%20one%20row%20of%20column%20names%2C%20and%20modify%20it%20more%20columns%20are%20added.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20that%3C%2FP%3E%0A%3CP%3E1)%20In%20Transform%20Sample%20File%20query%20with%20formula%20on%20Source%20step%20change%20Columns%3D%3CSOME%20number%3D%22%22%20you%3D%22%22%20have%3D%22%22%3E%20on%20Columns%3Dnull%20and%20add%20one%20more%20step%20to%20Promote%20First%20Row%20as%20Header.%20Remove%20automatically%20generated%20after%20it%20step%20with%20type%20change.%3C%2FSOME%3E%3C%2FP%3E%0A%3CP%3E2)%20In%20main%20query%20remove%20last%20Changed%20Type%20step%20(most%20probably%20it'll%20be%20an%20error%20on%20it)%20and%20add%20step%20to%20filter%2000000.xlsx%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Setup a Folder Power Query with transforms for a daily downloaded .csv report. It works well, but now we need to add a new column to the downloaded report. I can easily define new transforms for the extra column, but won't this be a problem for previously imported .csv data?

 

How can I update the transform for the new column of data without messing up the previous data?

2 Replies
Highlighted

In theory if it's just an additional column it may not be a problem

 

You may need to change the Sample file to the new file number temporarily (or easier option is to name the new file something that forces it to appear 1st alphabetically just while you're fixing up the Transform Sample file step)

 

 

image.png

 

 

 

Also, you may need to change the column number in the source step of the Transform sample file

image.png

 

Hope that helps

 

Wyn

MVP

Australia UTC+ 8

 

If this answer was the best response please click the button

I also happily accept likes

 

Highlighted

@spectrl 

As variant in addition to Wyn's suggestion, in Sample File not to select concrete file, but on previous step before selecting first in the list, sort table in descending order on date of modification, thus the latest file will be taken as the sample.

 

However, from my point of view most reliable is to keep in the folder the file as 0000.xlsx with only one row of column names, and modify it more columns are added.

 

With that

1) In Transform Sample File query with formula on Source step change Columns=<some number you have> on Columns=null and add one more step to Promote First Row as Header. Remove automatically generated after it step with type change.

2) In main query remove last Changed Type step (most probably it'll be an error on it) and add step to filter 00000.xlsx file.