pinarello
Member
- Joined
- Jun 21, 2019
- Messages
- 214
- Reaction score
- 4
- Points
- 18
- Location
- Germany
- Excel Version(s)
- Office 365
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.
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.