PowerQuery Data from Folder named range issue

Question

Trying to get my feet wet with Power Query in Excel 2016. I need to compile a list of files in a folder. Excel Get data > From Folder does this fine. However I also need to add data from an Excel worksheet named range to that query.

Named range is just a cell with some data in it, no headers (not a table). Edit query > Content accommodates this as well, defined tables and named ranges are available there to be added to the query. But this is where I run into a problem. If the named range contents is a number, I don't have any issues. However if it's an alphabetic entry, I get a message "This table is empty" and the actual contents are shown as the header of this range.

Obviously the queries will also provide totally different-looking results:

What is the reason that alphabetic value in named range is automatically converted into header and is there a neat way to avoid that?

Just as a remark - I think I found a workaround - if I'll have the first file with the named range contents as numbers, the query will be built correctly and data from the next files will be imported as expected (even if in alphabetic form), but I can't help to think that this could be avoided in the first place...

Answer 1

Since the named range should only contain one cell, it seems okay to assume that if the table is empty, then the range's value is, for whatever reason, in the headers.

A custom function like this:

maybeDemoteHeaders = (someTable as table) as table => if Table.IsEmpty(someTable) then Table.DemoteHeaders(someTable) else someTable

might be okay.

It would have been good to see your code (to see if a custom function is necessary at all).