Results 1 to 2 of 2

Thread: Power Query - Determine directory with a function and load workbooks using a filter -

  1. #1
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    20
    Articles
    0
    Excel Version
    Office 365

    Power Query - Determine directory with a function and load workbooks using a filter -



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

    Hello,

    for a possible project, where I want Power Query, I try to remove some stumbling blocks.

    It will always be a matter of importing several workbooks from one directory. New workbooks will be added to these directories every month. When loading from these directories, only the workbooks of one month should be considered.

    Since there are also different workgroups, there is a separate directory for each workgroup.

    The point is that I want to specify the directory and the month to import for the import of the data.

    Here for the example I have created a query (Dynamic Filter - read from a sheet) as a list with which I can specify month names for filtering. This works without any problems.

    In the query "Dynamic Filter - read from a folder" I have the input of: Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],

    on: Source = Folder.Files("H:\AAA - PQ import files of folder\Folder_1"),
    changed. This also works perfectly.

    In another folder I had already successfully tested how I can specify the file path dynamically using a function.

    In the query "Dynamic Filter - read from a Folder (2)", I now combined the function for the dynamic selection of the file path and the filter for the dynamic selection of the workbooks.

    Unfortunately I get the error message "Formula.Firewall: Query 'Dynamic Filter - read from a folder (2)' references other queries or steps, so it may not directly access a data source. Please rebuild this data combination" denied.

    Now I can handle Power Query quite well, as I imagine. But I have no idea what I have to do to solve this problem.

    The folder containing the queries is uploaded here. In the table sheet "Input" I have also stored the data of my test environment from column "E", which are needed for the queries "Dynamic Filter - read from a folder" and "Dynamic Filter - read from a folder (2)".

    I would be happy if there is a solution for my problem.

  2. #2
    Acolyte pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    20
    Articles
    0
    Excel Version
    Office 365
    I just saw I uploaded the wrong workbook. Here is the right workbook.
    Last edited by pinarello; 2019-12-04 at 12:06 AM.

Posting Permissions

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