Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Birinder
Helper III
Helper III

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




View solution in original post

3 REPLIES 3
Birinder
Helper III
Helper III

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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




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



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors