Extracting named cells from multiple sources

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.