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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ronaldbalza2023
Continued Contributor
Continued Contributor

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
Impactful Individual
Impactful Individual

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
Continued Contributor
Continued Contributor

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
Impactful Individual
Impactful Individual

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors