none
Power Query Folder Path Parameter RRS feed

  • Question

  • Dear Team,

    In Excel I am trying to use a folder path parameter in a From Folder query. I would like to enter the folder path in a cell in Excel and then use that path as the folder path for a From Folder query. Then later I want to change the folder path in that Excel cell and have the query update. This is what I tried:

    1) I created an Excel Table with a single row and column that contains the parameter folder path.

    2) I imported it and drilled down using this code:

    FolderPathVariable

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

    3) I used this code to try and import files from a folder using the FolderPathVariable using the code here:

    let
        Source = Folder.Files(FolderPathVariable)
    in
        Source

    4) I am getting the Formula.Firewall error.

    5) I have tried two other variations that try to land the imported data first using the parameter folder path, and then use the data, but I always get the Formula.Firewall error. I thought I understood the concept of the Formula.Firewall error and how we needed to separate the landing of the data and the use of the data, but I guess I do not.

    Any ideas how I can create a parameter or variable folder path that we can use in a From Folder Query without having to change my privacy settings?

    Sincerely, Mike Girvin


    Sincerely, Mike Girvin

    Thursday, January 17, 2019 7:02 PM

Answers

  • Okay Team,

    I guess I did this and it seemed to work:

    let
        Source = Excel.CurrentWorkbook(){[Name="FolderPathTable"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderPath", type text}}),
        FolderPath = #"Changed Type"{0}[FolderPath],
        LandData = Folder.Files(FolderPath)
    in
        LandData

    Adding the extra last line to the FodlerPathVariable Query.

    Sincerely, Mike Girvin


    Sincerely, Mike Girvin

    Thursday, January 17, 2019 7:13 PM

All replies

  • Okay Team,

    I guess I did this and it seemed to work:

    let
        Source = Excel.CurrentWorkbook(){[Name="FolderPathTable"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"FolderPath", type text}}),
        FolderPath = #"Changed Type"{0}[FolderPath],
        LandData = Folder.Files(FolderPath)
    in
        LandData

    Adding the extra last line to the FodlerPathVariable Query.

    Sincerely, Mike Girvin


    Sincerely, Mike Girvin

    Thursday, January 17, 2019 7:13 PM
  • Hi Mike

    I created an Excel Table with a single row and column that contains the parameter folder path. Purely for the record:

    let
        Source = Table.FirstValue(Excel.CurrentWorkbook(){[Name="FolderPathTable"]}[Content]),
        FolderFiles = Folder.Files(Source)
    in
        FolderFiles

    Power Query is fun ;-) Not yet for me though...

    Saturday, January 19, 2019 4:07 PM