Referencing a dynamic name in PowerQuery results in an error

Question

I have created a new name (variable) in excel using the Name Manager which is called fPath. The value for the name is =CELL("filename").

I would like to reference this name as a parameter to the source within a function query. But, when I try to invoke the function within the Power Query Editor, I get an error that it wasn't able to find the name.

When the name refers to a cell within the sheet, this works perfectly but when a name is created without a reference to a cell, it is unable to find it.

Am I correct in assuming that what I'm trying to do is not possible?

EDIT:

Here's the code for for the Function Query where I have defined a parameter which is then used to dynamically get the source of the file.

[ query details ]

Mario Di Noia · Accepted Answer · 2019-08-30 22:06:45Z

1

Please see this from Ken Puls

https://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

answered Aug 30, 2019 at 22:06

Mario Di Noia

442 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Navik Goswami Over a year ago

I’m aware of this but sheets / tables can be deleted. I was looking for a solution that uses Names without having to add references in the sheets.

Mario Di Noia Over a year ago

I would put the table on a Sheet then mark it very hidden, that way it cannot be deleted. Best work around , I have been using this for about 6 months

Aleksei Zhigulin · Accepted Answer · 2019-08-15 11:01:35Z

1

Try this:

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

Or:

= Excel.Workbook(File.Contents("C:\YourFile.xlsx"), null, true){[Item="fPath",Kind="DefinedName"]}[Data]

answered Aug 15, 2019 at 11:01

Aleksei Zhigulin

1,6241 gold badge10 silver badges11 bronze badges

1 Comment

Navik Goswami Over a year ago

I've tried the first one already and it doesn't work - it results in the same error. I cannot use the 2nd one as it defeats the purpose. I'm trying to dynamically get the file path.

Collectives™ on Stack Overflow

Referencing a dynamic name in PowerQuery results in an error

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related