Improve reference query on txt or csv ? table buffer vs load to table and load from table

[Watever]

My question is for power query in Excel but could probably be related to power bi etc...

I work a lot with report in text format with header and footer, meaning formated for printing but not for data manipulation.

I will often run a query to transform the report in a tabular data.

Then I will want to run different output from this data.

If I do a reference, it's way slower compare if I load the query to a table in a sheet and then run my next query based on that table instead of reference.

I believe it's related to rerun the query because it's a txt or csv file, there is no unloading transformating occuring.

But that's still the case if I use a table.buffer on the source.

Why is it so ? Anything I can do to otherwise or is loading the data to a table the best way in term of speed ?

For exemple, the query below will take a 2 seconds or less to run when using excel.currentworkbook, but if I use table.buffer(query) it will take a few minutes.

let
    //Source = Table.Buffer(rf_load),
    Source = Excel.CurrentWorkbook(){[Name="rf_load"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Usager] = null)),

    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",
        {
            {"Route", Int64.Type}, 
            {"Livraison", type date}, 
            {"Porte", type text}, 
            {"Arret", Int64.Type}, 
            {"No Client", Int64.Type}, 
            {"Nom Client", type text}, 
            {"Stage", Int64.Type}, 
            {"Palette", type text}, 
            {"Assignation", Int64.Type}, 
            {"Type", type text}, 
            {"Caisses", Int64.Type}, 
            {"Nom", type text}, 
            {"Prenom", type text}, 
            {"Usager", Int64.Type}, 
            {"Facture", Int64.Type}, 
            {"Complete Time", type datetime}
        }),

    #"Grouped Rows" = Table.Group(#"Changed Type", 
        {"Route"}, 
        {
            {"Livraison", each List.Average([Livraison]), type nullable date}, 
            {"Nbr Palettes", each Table.RowCount(Table.Distinct(_)), Int64.Type}
        }),

    #"Sorted Rows" = Table.Sort(#"Grouped Rows",
        {
            {"Livraison", Order.Descending},
            {"Route", Order.Ascending}
        })
in
    #"Sorted Rows"

Is there another way to do it that I am missing ?

[v-karpurapud · Accepted solution]

Hello @Watever 

Thank you @lbendlin for prompt response.

Thank you for reaching out to the Microsoft Fabric community. We understand you are experiencing performance issues when referencing queries during data transformation. Here is the clarification: 

• When a query references another query that loads data from a CSV/TXT file, Power Query re-reads and re-processes the file each time, resulting in slow performance due to repeated execution of all transformations.

• Although Table.Buffer() caches data within a single query execution, it does not persist across multiple queries. When referenced from another query, the file is still reloaded and processed again, leading to the same performance issue.

• The best solution is to use an Excel Table as a cache to improve performance. Store the transformed data in an Excel Table (Excel.CurrentWorkbook()). Power Query will then read directly from the Excel file’s memory, which is much faster than reloading a CSV/TXT file.

• Excel Tables act as a cache, preventing repeated file processing and significantly improves query performance.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you!

[v-karpurapud]

Hello @Watever 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @Watever 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[ZhangKun · Accepted solution]

First, CSV (or TXT) is usually faster than XLSX, although CSV may have problems with type conversion. But because XLSX uses XML to reference data, it will be slower.

Second, when you reference other queries, the referenced query is recalculated instead of directly referencing its results.

Third, Table.Buffer is not always useful. Its function is to turn streaming calculations into lazy calculations, which means that it caches more results, in other words, it requires more memory. However, Power Query seems to have a memory limit (it cannot use all the memory of the computer), so when the table is large, it may slow down the entire program.

Finally, sorting is time-consuming.

[v-karpurapud · Accepted solution]

Hello @Watever 

Thank you @lbendlin for prompt response.

Thank you for reaching out to the Microsoft Fabric community. We understand you are experiencing performance issues when referencing queries during data transformation. Here is the clarification: 

• When a query references another query that loads data from a CSV/TXT file, Power Query re-reads and re-processes the file each time, resulting in slow performance due to repeated execution of all transformations.

• Although Table.Buffer() caches data within a single query execution, it does not persist across multiple queries. When referenced from another query, the file is still reloaded and processed again, leading to the same performance issue.

• The best solution is to use an Excel Table as a cache to improve performance. Store the transformed data in an Excel Table (Excel.CurrentWorkbook()). Power Query will then read directly from the Excel file’s memory, which is much faster than reloading a CSV/TXT file.

• Excel Tables act as a cache, preventing repeated file processing and significantly improves query performance.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you!

[v-karpurapud]

Hello @Watever 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[lbendlin]

what is rf_load?  A reference? Why would you buffer a reference rather than the original data?