Infinite data refresh after powerquery edition

Hi there, thanks for posting your query. I can totally understand how frustrating this can be. However, have you tried to use the "Table.Buffer"function to avoid repeated evaluations.

The issue of infinite refresh in Power BI, especially after complex transformations or modifications in Power Query, is something many of us have come across at some point. 

Please follow the following steps that may help you:

1. Review and Optimize Query Steps
Every transformation step in Power Query adds to the processing load. Here are a few tips:

Consolidate Steps: Look for opportunities to merge similar transformation steps to reduce redundancy.
Remove Unnecessary Steps: Ensure there are no redundant operations—sometimes Power Query retains steps that may no longer be needed, especially after edits.

2. Use Buffering to Optimize Data Handling
In Power Query, repetitive calls to the data source can occur, which may lead to long refresh times or, in your case, an infinite refresh loop. One effective solution is to use Table.Buffer to force Power Query to cache a table in memory, thus preventing multiple queries to the source.

Example Usage:

let
BufferedTable = Table.Buffer(PreviousStep)
in
// Perform transformations on BufferedTable here

Using Table.Buffer helps particularly when you’re referencing previous rows or merging queries, as it prevents Power Query from going back and forth to the source.

3. Optimizing Custom Functions
When using custom functions that reference previous rows, performance can degrade significantly. In your case, where you’re working with previous values using try ... otherwise, here’s an improvement:

Buffer Lists Before Accessing Elements: This minimizes the cost of repeatedly accessing a list.

let
BufferedList = List.Buffer(#"Index ajouté"[Column6]),
currentRow = [Index],
prevRowValue = if currentRow > 0 then BufferedList{currentRow - 1} else null,
currentVal = [Column6]
in
if (prevRowValue = null and currentVal = null) then "Biberonage" else null

By buffering the list, you avoid recalculating it every time you need to reference a previous value, which can drastically improve refresh times.

4. Disable Background Data Preview
Power Query, by default, downloads previews of your data in the background, which can add overhead, especially for complex queries:

Go to File > Options and settings > Options.
Under Global > Data Load, uncheck Allow data preview to download in the background.
This reduces the workload during development, especially when you are editing queries frequently.

5. Break Down the Load Incrementally
Instead of loading all 180 files at once, I recommend testing your transformations on a smaller subset of data—perhaps a single file or just one month of data. This makes it easier to debug and fine-tune your transformations without overwhelming Power BI.

Once you’re confident that everything works correctly, you can scale it up to include the full dataset.

6. Monitor Your Resource Usage
Lastly, make sure your machine has sufficient memory and CPU power. Handling 180 files with complex transformations can be very resource-intensive, and if your system is underpowered, it can lead to performance issues like what you're experiencing. Increasing RAM or optimizing how much data is being processed at once can help alleviate this.

I hope this will help you. Good luck!!