Extract particular cell values from several workbooks into row of one column in excel

Hi gwuill ,

Put all of your workbooks into one folder.

Use the Folder or SharePoint Folder source in Get Data/New Source.

Select your folder and, when prompted, choose 'Combine & Transform Data'.

Select the file you want to use an example for transformations to be replicated from. Usually better to leave as 'First File' if there are only the workbooks you want to import in the folder as then the example selection will be dynamic and not hard-coded to a file name.

This will set you up with a function that will take whatever transformations you apply to the Example File you chose and apply them to every file in the folder, lastly appending all the results together.

The process will automatically add a [Source.Name] column to your output query containing the source file name.

To change the transformations you want done to all files before they are appended, adjust the steps in this query which will have appeared in your query list:

Pete