cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ronaldbalza2023
Super User
Super User

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

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_0-1653872958391.png

 

1 ACCEPTED SOLUTION
KT_Bsmart2gethe
Super User
Super User

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

View solution in original post

5 REPLIES 5
ronaldbalza2023
Super User
Super User

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 🙂

ronaldbalza2023_0-1653870680423.png

 

@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
Super User
Super User

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

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

wdx223_Daniel
Super User
Super User

try to merge those codes into one query.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors