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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Top Kudoed Authors