Query breaks because of changing amount of columns in other files

[Chrisjr]

Hello Communty. 

I have made some transformation to an excel file. 

I would like to apply these transformations to any other files with about the same strucure. 

The issue is that the amount of columns is not always the same. Therefore, in my query, when I move a custom column to the bigining, the query breaks because on the new file, it does not find for example the column 5 (The column 'Column5' of the table wasn't found.)

Let's say I have the below dataset on whicj I applied the transformations :

Column1	Column2	Column3	Column4
brand	01/01	01/02	01/03
Xyz	500	200	200
DUM	100	200	100

after I create a custom column that seats next to Column4, I move it to the begining.

= Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Column1", "Column2", "Column3", "Column4"})

Now if I apply the same query step on the below dataset, my query breaks:

Column1	Column2	Column3	Column4	Column5
brand	01/01	01/02	01/03	01/03
FUM	100	200	200	400
RIM	200	200	100	500

How could I solve this issue? 

Thanks,

Chris 

[Chrisjr]

If someone would find this post and having the same kind of question, I found this video that is very helpfull: 

https://www.youtube.com/watch?v=4qqXho7ndgU

[Chrisjr]

thanks for the help @AlienSx 

[Chrisjr]

Hi @AlienSx , thanks for your reply. Do you replace the "source" term by your actual source? whether I replace the term source or I leave it as it is, I get an error. 

If I use the query as it is above, I get Expression.Error: The column 'Name' of the table wasn't found.Details:
Name

thanks

[AlienSx]

the one that was modified by 

#"Added Conditional Column"

[Chrisjr]

@AlienSx 

so this is my step : = Table.ReorderColumns(#"Duplicated Column",{"Column1 - Copy"} & Table.ColumnNames("C:\Users\CC\Drive\ABC\ABC.xlsx")). 

But somehow I get an error

[AlienSx · Accepted solution]

We started with Table.AddColumn, now it's Table.DuplicateColumn. Okay. Try this 

= Table.ReorderColumns(#"Duplicated Column",{"Column1 - Copy"} & List.Difference(Table.ColumnNames(#"Duplicated Column"), {"Column1 - Copy"}))

[AlienSx]

Hi, @Chrisjr 

= Table.ReorderColumns(#"Added Conditional Column",{"Custom"} & Table.ColumnNames(Source))

where Source is your original table.