Load Named Range from Multiple Files in a Folder

Power Query
1

I want to be able to extract a named range from all Excel files in a folder. The named range may / may not be present in one or more of the files. The source files are files BB and CC and the destination file is Book2.

I could do this using the Combine & Transform UI but I want to avoid getting the Function Sample files etc.

How do I need to change my current Code in Book2.

Thanks
Book2.xlsx (13.2 KB)

Allister
BB.xlsx (407.3 KB)
CC.xlsx (406.5 KB)

2

Hi @AllisterB,

Give something like this a go, using key match lookup to get the Record.
{ [Kind= “DefinedName”, Hidden= false] }? [Data]?
.

let
    Source = Folder.Files(FolderPath),
    AddCustom = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content]){[Kind= "DefinedName", Hidden= false]}?[Data]? ),
    RemoveOthers = Table.SelectColumns(AddCustom,{"Name", "Custom"}),
    FilterRows = Table.SelectRows(RemoveOthers, each ([Custom] <> null) and (not Text.Contains([Name], "$")))
in
    FilterRows

.
You might want to read this, as well.

I hope this is helpful

3

Thank You Melissa

I get an error on the First Line. Where should I look for the mis match in types

Thank You

Allister

Expression.Error: We cannot convert a value of type Table to type Text.
Details:
Value=[Table]
Type=[Type]

the Code is

let

Source = Folder.Files(#"Folder Parameter"),
AddCustom = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content]){[Kind= "r_xInvestmentproperties", Hidden= false]}?[Data]? ),
RemoveOthers = Table.SelectColumns(AddCustom,{"Name", "Custom"}),
FilterRows = Table.SelectRows(RemoveOthers, each ([Custom] <> null) and (not Text.Contains([Name], "$")))

in
FilterRows

4

Hi @AllisterB,

Key match lookup is applied to the table depicted below, therefore Kind= “r_xInvestmentproperties” can only return null after which a conditional branch should start else subsequent steps will return an error please refer to the LinkedIn post for available options

Kind= “r_xInvestmentproperties” can be replaced by Name= “r_xInvestmentproperties” if that DefinedName is present in all files.

image
image930Ă—314 23.5 KB

5

Hi Melissa

Thank you for a very helpful explanation about the working of Kind on the Table.

I have read the Linkedin post and have tried to use Method 3. My problem is that I am unsure of what, in my case, would replace the Query1 Query2 etc.

My best attempt is below - it results in a 1 row , 1 column table with the feild Custom and no contents.

Could you provide the code i need.
Also do you have a link to a tutorial that might help me understand how to use code such as that in your linkedin post and adapt it for one’s own use.

Thank you
Allister

let

Source = Table.Combine(
Table.ToColumns(
Table.RemoveRowsWithErrors(
Table.FromColumns(
{{Folder.Files(#“Folder Parameter”)}}
)
)
){0}
),

AddCustom = Table.AddColumn(Source, "Custom", each Excel.Workbook([Content]){[Kind= "r_xInvestmentproperties", Hidden= false]}?[Data]? )

in
AddCustom

6

Hi
Can someone assist me to use what Melissa has suggested.

Thank You

7

Hi @AllisterB,

Apologies, couldn’t get back to you sooner.

What I’ve done is the following: Placed your 2 sample data files in a folder and added an empty file called DD.xlsx (so this does not include your NamedRange).

let
  Source = Folder.Files(FolderLocation), 
  // this adds a column with the NamedRange table and returns null if not found
  AddCustom = Table.AddColumn( Source, "Custom", each 
    Excel.Workbook( [Content] ){ [Name = "r_xInvestmentproperties", Hidden = false] }? [Data]?
  ),
  // clean up other columns (can be replaced with Projection)
  RemoveOthers = Table.SelectColumns(AddCustom, {"Name", "Custom"}), 
  // the first filter argument filters out the nulls (when the NamedRange isn't found), the second filter argument was already present in your file.
  FilterRows = Table.SelectRows(RemoveOthers, each (
    [Custom] <> null) and (not Text.Contains([Name], "$"))
  )
in
  FilterRows

With this result, after which you can expand the Custom column.

image

Here is your sample file.
Book2.xlsx (14.7 KB)

I hope this is helpful

1 Like