Forum Discussion
Extracting named cells from multiple sources
Hi,
I have a number of project management templates that contain info such as project number, start and end date, RAG status etc. All files are in .xls format, sit in the same network folder, and each info item has a cell name (e.g. 'ProjectNo').
I'd like to use Power Query to extract selected named cells from all files in the folder into a single table sorted on ProjectNo, so that I have a summary report of RAG status etc for all projects in the folder. This will then be used for input into a PowerBI based report.
Can this be done?
Darrin
10 Replies
This is a bit involved...
1. Create a new Parameter, called FileName, type = Text, currentValue = <File name, excluding folder, of any one of the Excel files (e.g. example.xlsx)>
2. Import the Excel file into a query.
3. Click the gear next to the source step. Clicked advanced. In the location field, delete the file name so only the folder remains. Then add a part, click the ABC button to the left and change it to parameter. Set the parameter as FileName. Click ok
4. Right click your query and choose "Create function". Give the function a name like "Import from Excel".
5. In your origional query, transform until you get the data you want.
6. Add a new query using the "Folder" data source.
7. Select the folder that has the excel fiels.
8. Click the Name column, and under Add column, choose "Invoke custom function".
9. Choose the function that was created.
10. If you see no errors in the new column, you should be able to click the combine tables button in the column header.
11. Right click and choose "Drill down" on the single cell in that column.
Hi - many thanks for the solution. I managed to go all the way through without error, but only getting a table with a list of the files, creation date etc...not the slected cells.
Can I clarify Step 2? When I start to import the file into a query, I get this screen:
Do I select my chosen data items at this point and do I use load or transform.
What I am not getting automatically is seeing the gear next to the source step.
Darrin
Make sure you are loading the data from a file, not the current workbook. Just transform the data.
You seem to be missing step 8 where you invoke the function to get to the data.
Hi Anonymous
What are the positions of the selected named cells? Are they always in a sheet with a specified sheet name and are at the same positions in all files?
In step 2, you will see all sheets and tables in a file. You need to select the sheet where you want to extract data from and click transform data to go on transforming it.
Best Regards,
Community Support Team _ JingThanks. I'm still having problems and I think it might be step 2 that is doing it.
As per my previous attached pic, If I select just the sheet 'one pager' I get presented with a table with multiple unnamed colums, but I can see the data is visible in the tabke, but not against the cell reference names I want. If I choose a small number of cell names in step 2, then I dont get visible data at all.
