Forum Discussion
Power Query x Excel - Reading source 7 ~ 8 times
Hey guys, I'm making a new report and I stumbled into a situation that never happened before. It looks like PQ is loading my excel file about 8 times, taking about 6 minutes:
only for these basic transformations:
let
Source = Excel.Workbook(File.Contents("C:\Users\user\Downloads\Gestão Custos 2024_Dashboard.xlsx"), null, true),
Database_Sheet = Source{[Item="Database",Kind="Sheet"]}[Data],
#"Removed Top Rows" = Table.Skip(Database_Sheet,2),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
#"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"VP-2", "VP-3", "VP-4", "Corredor", "Cost Center", "Tipo", "Nome Tipo"}),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"VP-2", type text}, {"VP-3", type text}, {"VP-4", type text}, {"Corredor", type text}, {"Cost Center", type text}, {"Tipo", type text}, {"Nome Tipo", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type")
in
#"Removed Duplicates"
The sheet I'm pulling data from have 10k rows and 231 columns. I now that the more columns the slower it gets, but why is it reading the file so many times? And what can I do to speed things up?
4 Replies
Hi,
Use Table.Buffer: The Table.Buffer function can help by buffering the table in memory, reducing the number of times the data is read from the source
Table.Buffer - PowerQuery M | Microsoft Learn
Solved: Power Query taking forever to load or refresh - Microsoft Fabric Community
I don't think Table.Buffer helps in this situation... just to make sure I tried on every step and still got the same results.
There's one more setting that you can try to disable background analysis.
Why does my query run multiple times - Power Query | Microsoft Learn
Hi, BlackBird
The reason for reading a data source multiple times, some connectors call the data source multiple times to get metadata, cache results, pagination, and so on. This is normal behavior. If multiple queries pull data from the same data source, each query and its dependencies are evaluated independently. Power Query evaluates each query to make sure they can run together safely, which can result in multiple requests. The collapse layer in Power Query may generate multiple requests based on downstream operations. You can check the following link:
Why does my query run multiple times - Power Query | Microsoft Learn
For optimization information for Power Query, you can check out the links below:
Power Query Optimization | Svitla Systems
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
