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

[Birinder]

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

[Birinder]

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.

[BA_Pete · 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:

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

[BA_Pete]

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:

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