cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Birinder
Helper II
Helper II

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
Helper II
Helper II

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors