Power Query and Named ranges with Data validation

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
60
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.

named_range.jpg


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.
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
it seems you defined Name for a single cell so try Table.DemoteHeaders(table as table)
 

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
60
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))?

brand_pre.jpg


Code generated by Power Query:

Code:
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.

brand_post.jpg


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

Code:
    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

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,829
text vs number
PQ recognise text and create text header from this text
PQ recognise number and adding text header, eg. Column1
 

Forum statistics

Threads
1,082,348
Messages
5,364,862
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top