Results 1 to 5 of 5

Thread: Returning PQ parameter values from a range name

  1. #1
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    56
    Articles
    0
    Excel Version
    365

    Returning PQ parameter values from a range name



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,319
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Charley,

    Try this:

    Code:
    -------------------
    let
    FilePath = Text.From(cMyPath),
    Source = Folder.Files(FilePath),
    --------------------


    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.
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    56
    Articles
    0
    Excel Version
    365
    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

  4. #4
    Acolyte Charley's Avatar
    Join Date
    Jul 2017
    Posts
    56
    Articles
    0
    Excel Version
    365
    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

  5. #5
    Seeker MushroomFace's Avatar
    Join Date
    Jun 2018
    Location
    UK
    Posts
    6
    Articles
    0
    Excel Version
    2013
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •