Forum Discussion

Syndran's avatar
Syndran
Frequent Visitor
6 years ago

Power Query internal evaluation loops

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.

 
Need Help

4 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    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's avatar
      Syndran
      Frequent Visitor
      6 years ago

      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's avatar
        Jimmy801
        Community Champion
        6 years ago

        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