Effecient way to extract files from folders and perform clean data from all sheets

[var-anaz]

Hi,

So I have a PBI file where the data source is retrieved from a folder contains excel files. Each excel file consists of multiple sheets(A, B, C etc.) which need to be transformed beforehand in the query (Transform sample file), before they are combined into a single Table. 

My process:

Source from folder -> Select sheet (for ex. A) -> combine and transform -> Transform Sample file (cleaning data in order to combine them)

I perform this process for all the required sheets (B,C etc.) and then merge all the resultant tables from their respective sheets into one. 

This process takes a lot of time to refresh and process. 

Is there an m-code through which I can extract all the respective files in the folder, perform the cleaning for the respective sheets in lesser steps?

I am trying to improve the efficiency of my query!

Would really appreciate the help! Thanks.

[artemus]

The secret is to use the extract function.

• Add a parameter query with the file name (not path) of a sample excel file
• Replace the excel file name in your main query with your parameter query. You can use & to concatenate the path with the name
• Right click your main query and choose extract function.
• Create a new folder query pointing to your excel folder
• Under add column tab choose invoke custom function. With the function you created and the file name column as the parameter.
• Finally click the combine button on the new column