Power Query Merging Multiple Excel Named Ranges

[NCSUAaron]

I'm trying to use the "From Folder" option of connecting to Excel Files to pull in multiple named ranges from multiple Workbooks. I'm hoping that I can find a way to make it so that new Workbooks and/or new Tabs that contain this named range will automatically get included.

My thought was to use the same Named Range in Excel, with a "Worksheet" scope (so that users can duplicate the Sheet name, which will automatically create a new version of the Named Range with the scope of that new Worksheet.

The named range would be rng_Data, so then I might have 1 workbook with 2 Sheets that have this Named Range, and another with 4 Sheets, etc.

workbook1.xlsx!Sheet1!rng_Data
workbook1.xlsx!Sheet2!rng_Data
workbook2.xlsx!Sheet1!rng_Data
workbook2.xlsx!Sheet2!rng_Data
workbook2.xlsx!Sheet3!rng_Data
workbook2.xlsx!Sheet3!rng_Data
etc.

I've used the "From Folder" option, chosen Combine and Transform, and then in the Combine Files pop-up I've chosen one of the rng_Data Tables. I'm getting the following error:

(BBB is one of the Sheet names).

If I go back to an earlier step, and click through the Binary on one of the Workbooks, and click through the Table associated with this range on this worksheet, I am able to see the data from the range, so Power Query is recognizing the Range and the data in it.

So, I'm not understanding why the "key isn't matching".

Is what I'm trying to do possible?

 

[GraH]

Please try to select the folder (which in essence is the file) and forget about selecting one of the sheets with a named range.
That way each file in the folder is read, and all sheets will appear.
Later in the same transformation query you can filter on sheets with that named range.

New files? -> from folder will catch it
New sheets? -> not selecting any sheet, will ensure all sheets are seen
New data range? -> the filter selecting will include/exclude accordingly.

 

[NCSUAaron]

Please try to select the folder (which in essence is the file) and forget about selecting one of the sheets with a named range.
That way each file in the folder is read, and all sheets will appear.
Later in the same transformation query you can filter on sheets with that named range.

New files? -> from folder will catch it
New sheets? -> not selecting any sheet, will ensure all sheets are seen
New data range? -> the filter selecting will include/exclude accordingly.

Thanks for the reply. What you're describing is exactly what I would like to happen, but I'm getting the indicated "Transform" error, and I can't figure out why.

These are the steps I am following.

From Excel, Data > Get Data > From File > From Folder, and then selecting the appropriate folder.

The list of all files comes up, and I select "Combine and Transform".

The 'Combine Files" popup comes up. I change the "Sample File" to one of the ones that contains the tabs I am looking for, and I select one of the tabs with the data. (Note that these tabs contain summary data above the data tables I want to extract, so my intention is to remove those rows within PowerQuery once I get to that step. There are also tabs in these files that do NOT contain the data that should be combined, so my intention was to ignore those through filters once I got the connection working.)

From there, PowerQuery loads, but immediately gives me the "Transform File" error.

I've read that this error generally means something has been deleted from the file, but this is me trying to set up the Query initially, so I don't understand the error.

Furthermore, if I manually navigate to this same worksheet, the data is visible without errors, so I can't figure out where the Transform error is coming from.

 

[GraH]

Try with selecting the 'folder', the yellow thing on top. That's your file. Do not select a sheet like you do here.

 

[NCSUAaron]

View attachment 104033
Try with selecting the 'folder', the yellow thing on top. That's your file. Do not select a sheet like you do here.

Thank you. This solved my issue. I really appreciate the feedback.

 

[GraH]

Glad to help. Nice to read you got it working.
Very good you provided the details of your process with those screenshots.
Btw, the trick of selecting the folder was actually provided in #2, my initial reply.