Forum Discussion
Extract particular cell values from several workbooks into row of one column in excel
Hi,
I would like to extract particular cell values from several workbooks. I did the first step in order to list all the files I want:
Then for each files, I wan to get the information contained in the B5 and put it in a columns B. In the column A, I want to put the name of file.
Is it possible to do it? I am very new in power query...
My problem is perhaps related to: https://community.powerbi.com/t5/Power-Query/How-to-extract-particular-cell-values-from-several-workbooks/m-p/1795655
But I didn't success to use the given solution.
Thank in advance for your help
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
2 Replies
- BA_Pete
Super User
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
Thank you for your help,
I success to do what I want to do.
Best,
