Infinite data refresh after powerquery edition

[medmbchr1989]

Hi

I have done some transformation on data in a power query table from a folder with 180 Excel files (1 file per day for 6 months data). In my first editions of the powerquery edition, the refresh was done smoothly, however now I think the power query edition went a bit too complex and I have my data that is stuck in refresh, below is a screenshot, my old advanced query code which worked and the new advanced query code that keeps my data refreshing indefinitely, also sharing a function in which I have doubts that it might be memory consuming.

PS: pbix files cannot be shared due to privacy.

Function in which i have doubts about memory consumption:

let
    currentRow = [Index],
    prevRowValue = try #"Index ajouté"[Column6]{currentRow - 1} otherwise null, // issue here, always returns null
    currentVal = [Column6]
in
    if (prevRowValue = null and currentVal = null) then "Biberonage" else null

Screenshot (refresh stuck here):

Old code (data refreshes normally):

let
    Source = Folder.Files("C:\Users\moham\Desktop\dispatch 2024"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Column2] <> "ETAT DES TOURNEES" and [Column2] <> "TELEPHONE")),
    #"Type modifié1" = Table.TransformColumnTypes(#"Lignes filtrées",{{"Column7", type text}}),
    #"Lignes filtrées1" = Table.SelectRows(#"Type modifié1", each not Text.Contains([Column7], "Code") or not Text.Contains([Column7], "Version") or not Text.Contains([Column7], "Nature")),
    #"Lignes filtrées2" = Table.SelectRows(#"Lignes filtrées1", each ([Column6] <> null)),
    #"Colonnes renommées" = Table.RenameColumns(#"Lignes filtrées2",{{"Column1", "Chauffeur"}, {"Column2", "Tél"}, {"Column3", "Camion"}, {"Column4", "CU"}, {"Column5", "Ville"}, {"Column6", "Client"}, {"Column7", "Poids total"}}),
    #"Lignes filtrées3" = Table.SelectRows(#"Colonnes renommées", each ([Source.Name] <> "709DA600")),
    #"Rempli vers le bas" = Table.FillDown(#"Lignes filtrées3",{"Chauffeur", "Tél", "Camion", "Ville"}),
    #"Colonnes renommées2" = Table.RenameColumns(#"Rempli vers le bas",{{"Source.Name", "Dispatch"}}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Colonnes renommées2",{{"CU", type text}, {"Poids total", type number}}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Type modifié2",{"CU"}),
    #"Lignes vides supprimées" = Table.SelectRows(#"Colonnes supprimées", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Erreurs supprimées" = Table.RemoveRowsWithErrors(#"Lignes vides supprimées", {"Dispatch"}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Erreurs supprimées", "Date", each Date.FromText(Text.Select([Dispatch], {"0".."9"} ), [Format="ddMMyyyy", Culture="fr-FR"])),
    #"Lignes filtrées5" = Table.SelectRows(#"Personnalisée ajoutée", each [Client] <> null and [Client] <> ""),
    #"Valeur remplacée" = Table.ReplaceValue(#"Lignes filtrées5","nnul","XXX",Replacer.ReplaceText,{"Client"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","NNUL","XXX",Replacer.ReplaceText,{"Client"}),
    #"Lignes filtrées4" = Table.SelectRows(#"Valeur remplacée1", each not Text.Contains([Client], "XXX")),
    #"Espaces supprimés" = Table.TransformColumns(#"Lignes filtrées4",{{"Client", Text.Trim, type text}}),
    #"Majuscule à chaque mot" = Table.TransformColumns(#"Espaces supprimés",{{"Ville", Text.Proper, type text}}),
    #"Espaces supprimés1" = Table.TransformColumns(#"Majuscule à chaque mot",{{"Ville", Text.Trim, type text}}),
    #"Colonnes renommées3" = Table.RenameColumns(#"Espaces supprimés1",{{"Client", "Point de vente"}}),
    #"Lignes filtrées6" = Table.SelectRows(#"Colonnes renommées3", each [Poids total] >= 1),
    #"Lignes filtrées7" = Table.SelectRows(#"Lignes filtrées6", each [Date] >= #date(2024, 1, 1) and [Date] <= #date(2024, 6, 30)),
    #"Espaces supprimés2" = Table.TransformColumns(#"Lignes filtrées7",{{"Camion", Text.Trim, type text}}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Espaces supprimés2"," ","-",Replacer.ReplaceText,{"Camion"}),
    #"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","--","-",Replacer.ReplaceText,{"Camion"})
in
    #"Valeur remplacée3"

New code (keeps data refresh infinite):

let
    Source = Folder.Files("C:\Users\moham\Desktop\dispatch 2024"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier", each #"Transformer le fichier"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier", Table.ColumnNames(#"Transformer le fichier"(#"Exemple de fichier"))),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type any}}),
    #"Lignes filtrées" = Table.SelectRows(#"Type modifié", each ([Column2] <> "ETAT DES TOURNEES" and [Column2] <> "TELEPHONE")),
    #"Type modifié1" = Table.TransformColumnTypes(#"Lignes filtrées",{{"Column7", type text}}),
    #"Lignes filtrées1" = Table.SelectRows(#"Type modifié1", each not Text.Contains([Column7], "Code") or not Text.Contains([Column7], "Version") or not Text.Contains([Column7], "Nature")),
    #"Index ajouté" = Table.AddIndexColumn(#"Lignes filtrées1", "Index", 0, 1, Int64.Type),
    #"Personnalisée ajoutée1" = Table.AddColumn(#"Index ajouté", "Personnalisé", each let
    currentRow = [Index],
    prevRowValue = try #"Index ajouté"[Column6]{currentRow - 1} otherwise null, // issue here, always returns null
    currentVal = [Column6]
in
    if (prevRowValue = null and currentVal = null) then "Biberonage" else null),
    #"Dernières lignes supprimées" = Table.RemoveLastN(#"Personnalisée ajoutée1",1),
    #"Colonnes renommées4" = Table.RenameColumns(#"Dernières lignes supprimées",{{"Personnalisé", "Biberonnage"}}),
    #"Colonnes permutées" = Table.ReorderColumns(#"Colonnes renommées4",{"Source.Name", "Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Biberonnage", "Column7", "Index"}),
    #"Colonnes fusionnées" = Table.CombineColumns(#"Colonnes permutées",{"Biberonnage", "Column6"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Column6.1"),
    #"Colonnes supprimées1" = Table.RemoveColumns(#"Colonnes fusionnées",{"Index"}),
    #"Lignes filtrées2" = Table.SelectRows(#"Colonnes supprimées1", each ([Column6.1] <> null)),
    #"Colonnes renommées" = Table.RenameColumns(#"Lignes filtrées2",{{"Column1", "Chauffeur"}, {"Column2", "Tél"}, {"Column3", "Camion"}, {"Column4", "CU"}, {"Column5", "Ville"}, {"Column6.1", "Client"}, {"Column7", "Poids total"}}),
    #"Lignes filtrées3" = Table.SelectRows(#"Colonnes renommées", each ([Source.Name] <> "709DA600")),
    #"Rempli vers le bas" = Table.FillDown(#"Lignes filtrées3",{"Chauffeur", "Tél", "Camion", "Ville"}),
    #"Colonnes renommées2" = Table.RenameColumns(#"Rempli vers le bas",{{"Source.Name", "Dispatch"}}),
    #"Type modifié2" = Table.TransformColumnTypes(#"Colonnes renommées2",{{"CU", type text}, {"Poids total", type number}}),
    #"Erreurs supprimées1" = Table.RemoveRowsWithErrors(#"Type modifié2", {"Poids total"}),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Erreurs supprimées1",{"CU"}),
    #"Lignes vides supprimées" = Table.SelectRows(#"Colonnes supprimées", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Erreurs supprimées" = Table.RemoveRowsWithErrors(#"Lignes vides supprimées", {"Dispatch"}),
    #"Personnalisée ajoutée" = Table.AddColumn(#"Erreurs supprimées", "Date", each Date.FromText(Text.Select([Dispatch], {"0".."9"} ), [Format="ddMMyyyy", Culture="fr-FR"])),
    #"Lignes filtrées5" = Table.SelectRows(#"Personnalisée ajoutée", each [Client] <> null and [Client] <> ""),
    #"Valeur remplacée" = Table.ReplaceValue(#"Lignes filtrées5","nnul","XXX",Replacer.ReplaceText,{"Client"}),
    #"Valeur remplacée1" = Table.ReplaceValue(#"Valeur remplacée","NNUL","XXX",Replacer.ReplaceText,{"Client"}),
    #"Lignes filtrées4" = Table.SelectRows(#"Valeur remplacée1", each not Text.Contains([Client], "XXX")),
    #"Espaces supprimés" = Table.TransformColumns(#"Lignes filtrées4",{{"Client", Text.Trim, type text}}),
    #"Majuscule à chaque mot" = Table.TransformColumns(#"Espaces supprimés",{{"Ville", Text.Proper, type text}}),
    #"Espaces supprimés1" = Table.TransformColumns(#"Majuscule à chaque mot",{{"Ville", Text.Trim, type text}}),
    #"Colonnes renommées3" = Table.RenameColumns(#"Espaces supprimés1",{{"Client", "Point de vente"}}),
    #"Lignes filtrées6" = Table.SelectRows(#"Colonnes renommées3", each [Poids total] >= 1),
    #"Lignes filtrées7" = Table.SelectRows(#"Lignes filtrées6", each [Date] >= #date(2024, 1, 1) and [Date] <= #date(2024, 6, 30)),
    #"Espaces supprimés2" = Table.TransformColumns(#"Lignes filtrées7",{{"Camion", Text.Trim, type text}}),
    #"Valeur remplacée2" = Table.ReplaceValue(#"Espaces supprimés2"," ","-",Replacer.ReplaceText,{"Camion"}),
    #"Valeur remplacée3" = Table.ReplaceValue(#"Valeur remplacée2","--","-",Replacer.ReplaceText,{"Camion"})
in
    #"Valeur remplacée3"

Any help please? The difference between my codes starts from inserting an index for a new function and adding some other transformations.

Thanks!

[danextian]

Hi @medmbchr1989 

We're talking about 180 flat files and Power Query is essentially opening each file and applying your transformations. If they were just one or two flat files but with all the rows, your query would have been fine. And M is also not optimized for transformations that involve scanning a table. What I would do is I use DAX's EARLIER function to access the previous row. For example

Previous Row =
CALCULATE (
    MAX ( 'table'[column] ),
    FILTER ( ALL ( 'table' ), 'table'[index] = EARLIER ( 'table'[index] ) - 1 )
)

Now, if that is still slow as you have  large table, I would

• reference the original query
• keep the index column and all other columns that need to be accessed
• subract 1 from the  index column using the gui for math functions
• load both query
• create a relationship using the index column
• use RELATED function to create a calcualted column/s in the original table to return the column/s from the other table or simply use the columns from the other table.

[connect]

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!!