Forum

Copying and reusing...
 
Notifications
Clear all

Copying and reusing Combine Files Query

4 Posts
2 Users
0 Reactions
103 Views
(@culsh)
Posts: 43
Trusted Member
Topic starter
 

Hello.  I have been using PowerQuery, PowerPivot and now PowerBI for a few years.  I keep running into this issue.  I have a few queries that pull in a large number of excel files from a folder.  Sometimes I will want to pull the files from a different folder.  When I started using PowerBI (and PowerPivot or just reusing in a new PowerQuery file), I wanted to use some of those queries in my reports, but I run into a problem when I copy or import the main query.  I don't get the "Sample File" nor the "Transform Sample File" and then my query wont run.  I have watched the video in the parameter section of PowerQuery class for using the cell function, but that doesn't fix my problem.  What is the best way to copy or reuse a "from folder" query to get all of the "Invoke custom function" steps, including sample files to come along for the ride?

 

Combine-folder-files.JPG

 
Posted : 17/03/2020 4:36 pm
(@mynda)
Posts: 4761
Member Admin
 

Hi Cheryl,

Each query in the image you shared is a separate query, so you can't just copy one as they are dependent on each other, as you have found.

Session 7.03 Dynamic Folder Paths should work where you simply hard code the folder path that you want the query to use, assuming the problem you're having is caused by the automatic folder path formula.

Alternatively, you can simply make a copy of the Excel file containing the query you want to re-use and then edit the folder paths in the queries via the Advanced Editor window.

I hope that points you in the right direction.

Mynda

P.S. I've moved this thread to the Power Query course members forum, as opposed to the general public Power Query forum. Please use the course forums so that I give your questions priority.

 
Posted : 17/03/2020 7:37 pm
(@culsh)
Posts: 43
Trusted Member
Topic starter
 

The supporting/dependent queries are automatically created by the "from folder" query.  The only query that I create is the EnrollHistory query, and the dependent queries are then created automatically because it is from folder.  I have lots of queries that are from folder in other files, would each of the dependent queries have to be manually updated in order for the Dynamic Folder Paths process to work?  The folder path name is duplicated many times in the dependent queries (transform, sample, transform from sample, etc.).  I am looking for a way to update those existing query files that I have.  It sounds like that won't be possible, unless it is initially written according to the instructions for the dynamic path instructions.

 
Posted : 20/03/2020 10:40 am
(@mynda)
Posts: 4761
Member Admin
 

Hi Cheryl,

Of the 5 queries generated when you import files from a folder, only two have the file path; Sample File and the final query under the 'other queries' folder.

You would need to update these two queries with the new folder path. Or try the technique shown in session 7.03.

Mynda

 
Posted : 21/03/2020 1:47 am
Share: