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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors