Returning PQ parameter values from a range name

[Charley]

I have this query, named cMyPath, which returns the file path entered in a named cell:


---------------------
let
Source = Excel.CurrentWorkbook(){[Name="cFilePath"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Column1 = #"Changed Type"{0}[Column1]
in
Column1
------------------


And I have a query that begins...


-------------------
let
Source = Folder.Files("E:\MyPath"),
-------------------


But when I replace the path text with the first query's name, like this...


-------------------
let
Source = Folder.Files(cMyPath),
--------------------


...I get this error message: Formula.Firewall: Query 'MyText' (step 'Changed Type') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.


How can I set this up so that Source can use whatever path has been entered in the cMyPath cell?


Thanks.

 

[Ken Puls]

Hi Charley,

Try this:

[COLOR=#333333]-------------------[/COLOR]
[COLOR=#333333]let
[/COLOR]FilePath = Text.From(cMyPath),
[COLOR=#333333]Source = Folder.Files(FilePath),[/COLOR]
[COLOR=#333333]--------------------

Let me know if that works. I find that loading the parameters into a variable then passing that variable down works in most cases. Calling another query in the middle of your code (if it comes from a different data source) doesn't.[/COLOR]

 

[Charley]

Ken,

It failed.

I boiled the problem down to this:

Here's my parameter query for cFilePath:

let
Source = Excel.CurrentWorkbook(){[Name="cFilePath"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
Column1 = #"Changed Type"{0}[Column1]
in
Column1

Here's my query that works...

let
Source = Folder.Files("E:\Test")
in
Source

...which I've just changed to...

let
FilePath = Text.From(cFilePath),
Source = Folder.Files(FilePath)
in
Source

...which fails.

Is there a privacy setting that might affect this? Or am I making an obvious error?

Thanks!

Charley

 

[Charley]

Ken,

Thanks to help from Miguel, I figured out the privacy settings, which corrected the problem. That is, I edited each of the queries and then chose:

File, Options and Settings, Query Options, Global section, Privacy, Always ignore Privacy Level settings.

Also, in the Query Options, Current Workbook section: Privacy, Ignore the Privacy Levels and potentially improve performance.
And I also discovered something else that was interesting. The query...
let
Source = Folder.Files(cFilePath)
in
Source
...returned a Table to Excel. But the query...
let
FilePath = Text.From(cFilePath),
Source = Folder.Files(FilePath)
in
Source
...returned the same data, but NOT formatted as a Table.
That's something for me to keep in mind!
Thanks, Ken!
Charley

 

[MushroomFace]

Charley,

I came across the firewall issue and did the same but when I got the team to test the documents out, it obviously failed first time. The above is a simple fix but I prefer shared documents to be fool proof in case I'm not there.

let
Source = Excel.CurrentWorkBook(){[Name="FilePath"]}[Content],
cFilePath = Source{0}[Column1],
GetSource = Folder.Files(cFilePath)
in
GetSource

"FilePath" is the named range

This code is essentially, selects the defined name in excel and loads the file details from said filepath, in one query rather than two. However a pop up occurs on initial refresh, asking for privacy levels through a drop down, which is easy enough for them to select organisational and ok. Disabling privacy works and once it's set you don't have to think about it again, just personal preference (=

Amy