Use a named range in Excel for Power Query Data Source

[MatthewPerren]

My client needs txt files from a supplier converted to excel.

I've created a PQ to do that but I'd like to make life easy for them so I've added some VBA which allows them to look up the file to be converted and returns the file path to a named range in the workbook.

The path is then passed into PQ as a single value list

Try as I might I cannot get the main query to regcognise the path query as its data source

Is it possible or am I wasting my time?

[MatthewPerren]

THAT'S IT!!!!

Thank you! it's been driving me mad!

[ronrsnfld]

To get the contents of a file as Source, you use:

File.Contents(full_pathname)

Of course, you also have to properly parse the file and depending on the file type, you might use Csv.Document, Json.Document, Xml.Document or some custom connector.

[MatthewPerren]

So you can't use a pathway stored in another query? It HAS to be the full pathway?

[MatthewPerren]

So...after a few hours of trail and error, it would seem that the answer to my question is, "No", you cannot.
The error message shown in the previous screenshot: Formula.Firewall: Query 'BLAE3685 DD' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination. Is saying that, if the query bringing the named range into power query has any steps, it may not be quoting a full path and there doesn't seem to be a way of directly accessing the named range in the workbook from the Power Query editor.

So if you directly enter the file path into a parameter you can use it just fine. But if the parameter is picking the file path up from a query then it won't accept it for the reason above.

Very frustrating. 

If anyone knows how I can get arpund this, I'd be really interested.

Thanks

[mromain · Accepted solution]

Hi everyone

A dirty solution would be to ignore the privacy levels.
The procedure is described here:
https://exceloffthegrid.com/formula-firewall-in-power-query/#AlwaysIgnore