cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Birinder
Frequent Visitor

Can I use the same query steps for every excel file ?

Hi there,

I have some analysis to perform on some files. Though only file name changes, but data types and column names remains the same for every file. I have created 5 queries to load data into 5 different tabs for each file. Is there any way where I can use those steps again for every file I import in excel with same attributes. 

I guess I am making sense.

 

Regards -

Birinder Singh

 

1 ACCEPTED SOLUTION

OK, so in Power Query you will have the query on the left-hand side that contains the transformations you want to do. Let's say the file you did this on is called XL1.xlsx, and the query is called XL1.

Right click on the name of the XL1 query on the left of the page.

Select 'Duplicate' from the dropdown list. This will create a new query called 'XL1 (2)'. Change the name of this query to XL2 from the right-click menu.

Select XL2 from your query list and, from the Home tab ribbon, select Advanced Editor:

BA_Pete_0-1634026387587.png

 

This will open up the M code for the XL2 query.

At the top of this code, you will see the filename (still your/path/folder/XL1.xlxs) as per my previous post.

Replace the XL1 filename with the XL2 filename then hit OK.

Rinse and repeat for XL3, XL4 etc.

 

If you are using Power Query within Excel, and not within Power BI, then you can open the advanced editor as above and just copy the whole M code, then paste it into a blank query in your new Excel workbook. Chances are that the source line will include something like 'This.Workbook' or similar, so it will just apply the code steps to the currnet workbook you have pasted it into.

 

Pete

View solution in original post

3 REPLIES 3
Birinder
Frequent Visitor

So whenever I will open new excel file, How can I access these queries. Copying queries is possible for same workbook. But what about the case when we have different workbook but we want to apply the same query steps. Sorry, I am not that good in Power Query, So a nice explanation would help me for sure. 

I appreciate your time for solving my problem.

Thanks and regards.

OK, so in Power Query you will have the query on the left-hand side that contains the transformations you want to do. Let's say the file you did this on is called XL1.xlsx, and the query is called XL1.

Right click on the name of the XL1 query on the left of the page.

Select 'Duplicate' from the dropdown list. This will create a new query called 'XL1 (2)'. Change the name of this query to XL2 from the right-click menu.

Select XL2 from your query list and, from the Home tab ribbon, select Advanced Editor:

BA_Pete_0-1634026387587.png

 

This will open up the M code for the XL2 query.

At the top of this code, you will see the filename (still your/path/folder/XL1.xlxs) as per my previous post.

Replace the XL1 filename with the XL2 filename then hit OK.

Rinse and repeat for XL3, XL4 etc.

 

If you are using Power Query within Excel, and not within Power BI, then you can open the advanced editor as above and just copy the whole M code, then paste it into a blank query in your new Excel workbook. Chances are that the source line will include something like 'This.Workbook' or similar, so it will just apply the code steps to the currnet workbook you have pasted it into.

 

Pete

View solution in original post

BA_Pete
Super User
Super User

Hi @Birinder ,

 

You can right-click on your original query and select duplicate if you want separate queries for each file, or you can just do this in your original file to overwrite it, but all you need to do is change the source file in Advanced Editor for the query:

BA_Pete_0-1634025772243.png

As long as all other variables are the same e.g. sheet name, column names etc. then this will just perform all of your transformation steps on the new file that you change the source to.

 

If we're talking about 20, 30, 100 files where it wouldn't be feasible to do it manually, you should put all of your Excel files into the same folder then use the Folder source in Get Data. This will allow you to create an example file containing the transformations you want to do, and PBI will perform these transformations on every file in that folder on import, even when new ones are added.

 

Pete

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors