Power Query x Excel - Reading source 7 ~ 8 times

[BlackBird]

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?

[Anonymous]

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.

[Kaviraj11]

There's one more setting that you can try to disable background analysis.

 Why does my query run multiple times - Power Query | Microsoft Learn

[Kaviraj11]

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

[BlackBird]

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.