Memory consumption issue while refreshing the data source

[Scott_walter]

Hi community,

I have a Power BI report that was created using the OData source. I have five tables, the highest contains 30,000 rows currently. I have a requirement to add an index column in the two data tables. Before adding the index column, the two data tables are sorted by three column lets say by ItemId(String), PackingDate(Date) and Status(String) in the Power Query Editor before loading it into Power BI. I am able to perform sorting on these columns and after that I added the index column. So far so good. But when the sources are loaded into the Power BI and data is being refreshed then its throws memory issue although records are very less.

What can I do to mitigate this issue?
Thanks
Scott

[v-prasare]

Hi @Scott_walter 

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[v-prasare]

Hi @Scott_walter 

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

@Kedar_Pande, @d_m_LNK , @cengizhanarslan & @FBergamaschi  Thanks for your prompt response

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[Kedar_Pande]

@Scott_walter 

1. Power Query → Delete Steps: "Sorted Rows" (keep Index only)

2. Index Column → ensure "From 1" (not Table.Buffer)

If this answer helped, please click 👍 or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[d_m_LNK]

Can you post the code you used to create the index column?

[cengizhanarslan · Accepted solution]

1) Check query folding at the step right before Sort/Index

In Power Query, right-click the step → View Native Query.

• If it’s disabled, folding is already broken → sorting/indexing is happening locally.

• Goal: keep folding up to the point where you filter/reduce rows, then do sort/index.

2) Reduce data before sorting/indexing

Apply these as early as possible (while folding still works):

• Filter rows (date range, status, etc.)

• Remove unused columns

• Avoid unnecessary type conversions early

3) If you need stable “row number”, do it at the source (best practice)

If you can change the upstream system:

• Generate row numbers / ordering in the source (SQL/view/service)

• Expose it via OData as a column
  This avoids Power Query doing expensive sorts.

[FBergamaschi]

Hi @Scott_walter,

the problem can't be the data size.

Do you by chance have calculated columns using those columns with index? Those are evaluated during refresh

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI