Power Query and Named ranges with Data validation

[dotsent]

Taking literally my first steps in Power Query, so please bear with me. I need to compile a list of XLSM files in a folder with some metadata (name, folder path, date accessed) - this is all fine and functioning through Excel Get Data -> From Folder function.

However, I also need to include some contents from the files to this query. This data is coming from named ranges (instead of tables). When browsing the file Contents in Power Query editor, I can see the list of available tables and named ranges just fine. Though when choosing one range, I get the message "This table is empty".

Please note that "BRAND" is the range name + "REMKO - AMT50" is the contents of the range. Though it's presented here as the table header. What am I doing wrong here? Same problem with several ranges with the exception of one (can't figure out the difference between them). At the same time, all tables show up fine. What could cause a named range act like this? Running Excel 2016 on Office365

Converting named range into a table did solve my problem, but I'd prefer not to go that route.

edit: sorry, never mind the "Data validation" part in the subject. Thought it was relevant, but shouldn't be, can't edit the subject anymore.

 

[sandy666]

it seems you defined Name for a single cell so try Table.DemoteHeaders(table as table)

 

[dotsent]

Yes - that is accurate. I'm trying to import named range where data is in a single cell. Unfortunately I'm still struggling with it, but found a possible hint. If the named range contents is a number (e.g. 12345), Power Query works fine by default. Header is added as "Column1" (some default value, I assume) and the named range value 12345 is recognized as the value I'm trying to fetch.

Meanwhile if the named range contents are alphabetic such as the example REMKO - ATM50 or anything non-numeric, I run into the issue described in OP. Contents are recognized as column header and no actual contents are imported. Is there another way to overcome this?

I also gave it a shot with Power Query edit function "Use headers as first row", but didn't get the expected result (I guess I'd need this to take effect only on specific column(s))?

Code generated by Power Query:

let
    Source = Folder.Files("C:\Active"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Active", each #"Transform File from Active"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Renamed Columns1", "Transform File from Active", Table.ColumnNames(#"Transform File from Active"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"REMKO - AMT50", type any}})
in
    #"Changed Type"

After trying the "Use header as first row" I get the functionality across the columns which is not what I need.

With the M-code saying as below.
Any help is greatly appreciated!

    let
    Source = Folder.Files("C:\Active"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Active", each #"Transform File from Active"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Renamed Columns1", "Transform File from Active", Table.ColumnNames(#"Transform File from Active"(#"Sample File"))),
    #"Demoted Headers" = Table.DemoteHeaders(#"Expanded Table Column1")
in
    #"Demoted Headers"

 

[sandy666]

Picture2
in Power Query Editor:
select column9
Fill down
then Table.PromoteHeaders(table as table)

btw. you can take screenshot from PQ Editor, loading table to the sheet is not necessary

 

[sandy666]

text vs number
PQ recognise text and create text header from this text
PQ recognise number and adding text header, eg. Column1

 

[sandy666]

also you can try