Validity of using "Transform File from Query" folder in two or more queries

[Serrano]

Hello, I am trying to do this: I have one CSV Excel file in my SharePoint online site. I pull the data in the file into my PowerBI report. I use the Power Query editor for this. Since there are some files in the folder of the SharePoint site, I filter the file I want and use the Combine Files option despite selecting just 1 Excel file. It automatically creates a "Transform File from Query" folder with some sample queries, parameters, etc. This folder is a bit annoying, but I still can live with it. Then I use this query as my table in the PowerBI report. No problem so far.
My question is: with the Advance Editor I want to copy-paste the following top lines of the table to a new table and avoid pulling the same Excel file twice (detailed data and some lines are ommited):

Source = ..
#"Filtered Rows" = ...

#"Filtered Hidden Files1" = ..

#"Invoke Custom Function1" = ...
#"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File (5)"}),
#"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File (5)", Table.ColumnNames(#"Transform File (5)"(#"Sample File (5)"))),

Is this Copy-Paste action valid? Two or more queries using the same "Transform File from Query" folder?

[Poojara_D12 · Accepted solution]

Hi @Serrano 

Yes, your approach of copy-pasting the top portion of the query from the Advanced Editor to create a new table, while still referencing the same “Transform File from…” folder structure, is valid and commonly used in Power BI. When you use the Combine Files feature—even for a single file—Power Query generates helper queries (like the sample file, parameter, and transform file function) to handle the file structure uniformly. These helper queries can be reused by multiple queries without reloading or duplicating the file. So, if you copy the initial steps like Source, Filtered Rows, Invoke Custom Function1, and subsequent transformation steps into a new query, and both queries point to the same underlying SharePoint file and reuse the same sample file and transformation function, it’s an efficient way to avoid pulling the same file again. This avoids redundancy and improves performance, especially when working with large files or in refresh scenarios. Just ensure that you don’t accidentally duplicate or modify the shared helper queries in a way that could affect the original query. As long as the structure remains stable and you're simply referencing shared steps, it’s a clean and efficient Power Query practice.

[Nasif_Azam · Accepted solution]

Hey @Serrano ,

Yes, your copy-paste action is valid it is completely acceptable for multiple queries to reuse the same "Transform File from ..." function and its associated folder in Power Query.

What the "Transform File from ..." Folder Does? 

When you click "Combine Files" in Power Query, Power BI generates:

• A sample query that defines the transformation steps to apply to each file.

• A function (usually called something like "Transform File (5)") that’s invoked for each file.

• A helper folder query ("Transform File from ...") that organizes this logic and applies the function to all files (or in your case, just the one selected).

These assets are bundled together to support dynamic and scalable ingestion, especially for multiple files in a folder.

Reusing the Transformation Logic: Best Practice

If you’re only using one file and want to split its content into multiple queries (tables) for different report pages or uses (e.g., one for summary, one for detail):

• You can absolutely reuse the Source, Filtered Rows, and "Transform File from ..." steps in multiple queries.

• You avoid reloading the same file multiple times all queries reference the same base data in memory (Power BI’s engine is optimized for that).

How to Do It Safely

1. Duplicate the original query.

2. In the duplicate, modify steps after:

   #"Expanded Table Column1" = Table.ExpandTableColumn(...)

   to apply new filters, transformations, or summarizations.

   You're simply applying different logic on the same base data.

Things to Remember

• Renaming or deleting the "Transform File" function or its associated sample queries if you do, any queries depending on them will break.

• If you truly only have one file, you might not need the combine logic but it's still safe to use.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam

[Poojara_D12 · Accepted solution]

Hi @Serrano 

Yes, your approach of copy-pasting the top portion of the query from the Advanced Editor to create a new table, while still referencing the same “Transform File from…” folder structure, is valid and commonly used in Power BI. When you use the Combine Files feature—even for a single file—Power Query generates helper queries (like the sample file, parameter, and transform file function) to handle the file structure uniformly. These helper queries can be reused by multiple queries without reloading or duplicating the file. So, if you copy the initial steps like Source, Filtered Rows, Invoke Custom Function1, and subsequent transformation steps into a new query, and both queries point to the same underlying SharePoint file and reuse the same sample file and transformation function, it’s an efficient way to avoid pulling the same file again. This avoids redundancy and improves performance, especially when working with large files or in refresh scenarios. Just ensure that you don’t accidentally duplicate or modify the shared helper queries in a way that could affect the original query. As long as the structure remains stable and you're simply referencing shared steps, it’s a clean and efficient Power Query practice.

[Serrano]

Do you know if it is possible to bypass the creation of all those folders and add just the code in query using the Advanced Editor? Because I am pulling many files and these created too many folders.

[Poojara_D12]

Hi @Serrano 

Yes, you can bypass the folder/query explosion and use only the core code in the Advanced Editor — without duplicating the file load. You can manually extract the actual transformation logic and apply it directly in a simpler query without using the “Transform Sample File” setup.

let
    Source = SharePoint.Files("https://yourdomain.sharepoint.com/sites/yoursite", [ApiVersion = 15]),
    FilteredFiles = Table.SelectRows(Source, each Text.EndsWith([Name], ".xlsx") and Text.Contains([Folder Path], "Documents/YourFolder/") and [Name] = "YourTargetFile.xlsx"),
    FileBinary = FilteredFiles{0}[Content],
    ExcelFile = Excel.Workbook(FileBinary, null, true),
    YourSheet = ExcelFile{[Item="Sheet1", Kind="Sheet"]}[Data],
    PromotedHeaders = Table.PromoteHeaders(YourSheet, [IgnoreNulls=true])
in
    PromotedHeaders

[Serrano]

Hi, @Poojara_D12 

I am running this query approach with 1 CSV file in SharePoint site feeding at most 4 tables in a PowerBI report published online in a workspace under Premium capacity license.
Simultaneously, I have 8 more CSV files also in the same SharePoint site, each file feeding other tables (at most 4) in the same PowerBI report.

However, when closing the Power Query screen and returning to the PowerBI report page, the data refresh process is very slow and blocking many queries due to the same error of "query may not directly access a data source. Please rebuld this data combination." But I didn't get this error in Power Query screen. 

Is there a limit in the number of simultaneous queries from CSV files stored in SharePoint?