I have a question please. I was trying to replicate the formular thought in this video
( https://www.youtube.com/watch?v=tpK_xklbDf0)
with respect to appending worksheets with different column names using step 1 option of adding custom column. My question is; how do I use the custom column to automatically replicate my transformation techniques across all tables when my column headers have different names and I do not plan to replace the names. I could do it one by one for each table before appending, but that is a longer option especially if I have numerous worksheets.
Hi,
You could modify the Transform Sample File query (it's one of the Helper Queries created automatically in PQ) so that it removes the first row of the source files. This should remove the column headers, allowing you to append the files. You can then name the columns as you wish.
That said, if you don't plan on replacing the column names, which column names will you use?
If you need further help, please supply some sample data showing the column names in different files.
Regards
Phil
Hi Philip, thank you so much for your response. I have attached the respective file. However, I want to retain all column headers, but my major challenge is that I do not want to replicate the transformation technique applied in one table/sheet manually in others. I want to be able to copy the transformation technique applied in one table, create custom column, paste the formulars and then it automatically transforms all the other tables. I will appreciate your response to this. Thank you so much.
Hi,
You need to explain and show through examples, what transforms you wish to perform. The file you supplied has no queries so I am none the wiser as to what it is you are trying to achieve.
You say that you do not want to manually replicate the transformation applied in one table in other tables. Why would you be manually replicating the transformations? If all files are processed by the same sample queries then they all have the same transformations applied to them.
If you intend to keep all column headers from the different files, you're going to end up with something like this
I don't understand why you would want that, it's not a good layout for creating reports later e.g. with a pivot table. You have multiple redundant rows where the column headers from each file are.
You would be better off rearranging the data to look like this, which is a tabular layout and ideal for producing reports. See attached file.
Regards
Phil
