Cleaner way to import files from folder - Avoid too many helper queries/function

[ronaldbalza2023]

Hi everyone, how can I avoid too many helper queries when transforming files from a folder? Transform file or helper queries are automatically added when transforming a table from folder. Thanks in advance 🙂

[ronaldbalza2023]

Hi everyone, how can I avoid too many helper queries whenever transforming files from a folder? Transform file or helper queries are automatically added when transforming a table from folder. Thanks in advance 🙂

[amitchandak]

@ronaldbalza2023 , That depends on all the operations you have done, also if you are importing each file as a separate table then you will have that many queries

[KT_Bsmart2gethe · Accepted solution]

Hi @ronaldbalza2023 ,

There are many ways to achieve this.

Do you have a sample file?

if not the video below provide a similar concept:

Combine multiple file with custom function 

1. you use one sample file and transform it.

2. turn the sample file into a function.

3. In your case, it is Power BI then you can utilise the parameter to create the input list (i.e. the folder).

4. apply the custom function to your input table; 

OR

you can try the sample code below to import from folder without custom function:

code:

let

//Import from folder (Replace the blue text with your folder path)
Source = Folder.Files("C:\Users\cktan\Documents\PQ Training"),

//Filter for extension xls or xlsx (amend the filter to suit your case)

#"Filtered Rows1" = Table.SelectRows(Source, each Text.Contains([Extension], "xlsx") or Text.Contains([Extension], "xls")),

//Below Excel.Workbook([Content]) turn binary to table (I assumed your source are excel file, it required to change to other code if it is not excel)
#"Added Custom" = Table.AddColumn(#"Filtered Rows1", "GetData", each Excel.Workbook([Content])),

//skip error files
#"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"GetData"}),

//expand to get the worksheet level
#"Expanded GetData" = Table.ExpandTableColumn(#"Removed Errors", "GetData", {"Name", "Data", "Item", "Kind", "Hidden"}, {"GetData.Name", "GetData.Data", "GetData.Item", "GetData.Kind", "GetData.Hidden"}),

//Filter for only "Sheet" (amend the filter to suit your case)
#"Filtered Rows" = Table.SelectRows(#"Expanded GetData", each ([GetData.Kind] = "Sheet") and ([GetData.Item] = "Sales Data")),

//Add the filename and worksheet name to the  nested table under the column - ([Data]) & promote headers to each nested table (this step can be remove if filename and worksheet name not required)
#"Added Custom1" = Table.AddColumn(#"Filtered Rows", "Add Name", each Table.PromoteHeaders(Table.AddColumn([GetData.Data],"Name",(x)=>[Name]&"|"&[GetData.Name]))),

//Combine all tables

#"Add Name" = Table.Combine(#"Added Custom1"[Add Name])
in
#"Add Name"

Regards

KT

[ronaldbalza2023]

thanks @KT_Bsmart2gethe for taking the time on this. Will give a shot and let you know how it goes. 🙂

[wdx223_Daniel]

try to merge those codes into one query.