Power Query and Named ranges with Data validation

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
it seems you defined Name for a single cell so try Table.DemoteHeaders(table as table)
 

dotsent

Board Regular
Joined
Feb 28, 2016
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
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

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
text vs number
PQ recognise text and create text header from this text
PQ recognise number and adding text header, eg. Column1
 

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,318
Members
416,912
Latest member
danluk12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top