Speeding up power query (Table.Buffer?)

[jhd]

Hi,

I have an excel file (called GOLDEN_ID) which contains information about the products that we purchase e.g. it contains a taxonomy.

The file itself is about 13MB.

The file is loaded into the power query, and merged with many other queries.

When refreshing the dataset, 1GB is loaded from GOLDEN_ID.

This takes forever.

Is there any way that I can speed this up? I have read https://www.thebiccountant.com/speedperformance-aspects/

which suggests that Table.Buffer might be able to help, but I can't find much documentation on it and I don't know where to appy it.

Thanks.

[v-juanli-msft]

Hi @jhd 

When you connect data to Power BI, It would generate a M query connection, you could find M query by opening Advanced editor in Edit Queries where you could use Table.Buffer in the M code.

Reference:

Improving Power Query Calculation Performance With List.Buffer()

Table.Buffer for cashing intermediate query results or how workaround Unnecessary Queries Issue

Performance tip for List.Generate (1): Buffer your tables in Power BI and Power Query

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.