Append tables from the same workbook but make modifications before expending the tables.

[Nandor]

Dear Community,

I am trying to append several tables from the same excel file and I need to do the same modification to each of them to have the tables normalised, with the same header (several modifications are needed).

I would need to do the transformation before expanding the tables. I would not like to do the modifications to each tabel manualy by importing them to Power query, exporting it back to excel sheets ant then collect them again for appending.

I have several tables, that need the same modification and I would like to make the modifications right before expending the tables.

I used Excel.CurrentWorkbook to obtain the following table:

Now I would need to expend the tables using a function that will modify the tables before the system appends the data.

(I tried with the Sample file aproach but I could't get to a final result, I have no column with Binary).

I am attaching a sample file: Dropbox link to the file.

Thank you,

N

[Anonymous · Accepted solution]

Actually the Excel files in the folder are the binary files. I think that if you look at the sample file itself, you've expanded the binary file into a table. Instead, do not expand the sample file (remove the step that makes the sample file a table), and then go back to your Transform Query. Now the steps that you add to the Transform file will be applied to all the files, and you'll see a column of binaries before you invoke the transform function. 

--Nate