Anyone?

Jacek

You have already a topic here: https://www.myonlinetraininghub.com/excel-forum/power-query/trying-to-create-table-from-named-range-on-the-fly

The following expression works with both tables and named ranges:

Source= Excel.CurrentWorkbook(){[Name="NamedRange"]}[Content]

Source= Excel.CurrentWorkbook(){[Name="TableName"]}[Content]

A new topic will not give you a different solution 🙂 , that's just the way things work in PQ.

Sorry, 

this is not helpful. 

i am merging multiple workbooks together and want to get exactly what i wrote.
What power query will be for this? 

Named ranges what you are referring working for specific workbook (the same where power query is).

Here i want to use GetFolder method to merge all data but from external workbooks (merge tables plus named ranges all together in one query).

Please help anybody,
Jacek

I am attaching workbooks

You cannot connect to named ranges from external books.
All you can do is to convert the named range into a table with same name, Then query the folder where you have all workbooks.

Thanks.

It is not possible to query named ranges? 

And combining tables is only for whole worksheet, not exact range where table is? 

How can i do this approach with 2 tables? Can you provide example ?

Best,
Jacek

You cannot connect to named ranges from external books.

Why don't you add a new column in the source table you have, with the formula=NamedRangeName

Hmm this is idea! 

But few questions still: 

1) i see that power query sees named ranges and not seeing tables:

So still retriving tables equails to retrive whole worksheet?

I think it is possible to retrive somehow named ranges if power query sees them

Best,
Jacek

Ok, seems like it reads named ranges, you can right click the first table and add as a new query, same for second table, drill down until the result of these new queries are text, not lists, then you'll be able to add them as new column formulas in the third table.

Ok Guys,

maybe will be useful for you, i solved the issue (with help from outside)

AllFiles - first query:

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

retriving named range:

let
Source = AllFiles,
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"Column1"}, {"Data.Column1"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Name] = "R_Surname")),
#"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Data.Column1", "Nazwisko"}})
in
#"Renamed Columns"

retiving table:

let
Source = AllFiles,
#"Filtered Rows2" = Table.SelectRows(Source, each ([Name] = "Sheet1")),
#"Expanded Data" = Table.ExpandTableColumn(#"Filtered Rows2", "Data", {"Column1", "Column2", "Column3"}, {"Data.Column1", "Data.Column2", "Data.Column3"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Data", each ([Data.Column1] <> null and [Data.Column1] <> "Month" and [Data.Column1] <> "Surname"))
in
#"Filtered Rows"

and last step is just to merge queries 🙂 

Best wishes for all Power Query developers!,
Jacek