Power Query internal evaluation loops

[Syndran]

I have a small table loaded from an excel file.  Table is 3 columns and around 30 rows, total size is <50k

This table has existed for a long time, but in the last few days has caused my data refresh to stall/freeze my machine. On running diagnostics, I have noticed that the same steps are being repeated constantly.  Includes is snapshot from the file. 

Any information on what causes this, and how to prevent it would be greatly appreciated.

[Jimmy801]

Hello @Syndran 

this might be caused by a Table.AddColumn or Table.SelectRow because of the multiple access for your single file. Try to put the function Table.Buffer() arround your 2nd step 

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Syndran]

Hi.. Thanks for the response. 
The query is very simple, and the steps for this load finish around line 10 of the trace, You may notice on the screenshot provided that we are looking at lines 900+, where there is NO activity and NO step.

The query is very simple, no calculations are performed. no merges, adds, or other manipulations. just a simple excel file

let
Source = Excel.Workbook(File.Contents("\\XXXXXXXXXXXX"), null, true),
    #"Activity Keys_Sheet" = Source{[Item="Activity Keys",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(#"Activity Keys_Sheet", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Team", type text}, {"Activity", type text}, {"Activity Type", type text}}),
in
    #"Changed Type1"

[Jimmy801]

Hello @Syndran 

you can try this out

let
Source = Excel.Workbook(File.Contents("\\XXXXXXXXXXXX"), null, true),
    #"Activity Keys_Sheet" = Table.Buffer(Source{[Item="Activity Keys",Kind="Sheet"]}[Data]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Activity Keys_Sheet", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Team", type text}, {"Activity", type text}, {"Activity Type", type text}}),
in
    #"Changed Type1"

let us now if it had some impact

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Syndran]

Sadly no impact. 
sometimes the query is fine, runs/completes in <5 seconds, sometimes it sits there thrashing itself to death for 40 odd minutes.
remade excel sheet, remade connection, same thing.

Currently my data model, all 60 meg of it, is taking up 19 gig of memory, trying to reactivate a relationship that I set inactive, seems the issue is in powerbi not the model.