none
What does this mean? "Expression.Error: There were too many elements in the enumeration to complete the operation." RRS feed

  • Question

  • I have a Date Duration calculation

    if  [Status Date]= null then null else Duration.Days(DateTime.Date(DateTime.LocalNow()) -  [Status Date])

    that works for most records but fails for some with error:

    Expression.Error: There were too many elements in the enumeration to complete the operation.
    Details:
        List

    What does htis error mean?  It seems like it might be a 'timeout' type of issue?

    Thanks!

    Tuesday, March 3, 2015 1:33 AM

Answers

  • Sorry I lied.  There was a pivot in upstream query. 

    Two rows that were pivoted had same value, resulting in dupe column names. That was what was throwing error.

    In this context the error makes sense eg there were too many elements to complete operation, and both Gil and Curt asked questions that pointed to issue.

    Thanks!

    Expression.Error: There were too many elements in the enumeration to complete the operation.
    Details:
        List



    Wednesday, March 4, 2015 7:25 PM

All replies

  • Do you have a list in one of the cells of Status Date column?
    Tuesday, March 3, 2015 7:52 AM
  • And if not, are you doing a pivot?
    Tuesday, March 3, 2015 1:34 PM
  • Answers to questions above:

    No list in cell.

    And no privot.

    The table is 7 columns x 84 rows.

    Its just text and dates.

    Power Query is doing Duration.Day calc on all of the dates.

    It works on most but then for some rows it just craps out with Error message above.

    Can anybody say what situations that error message presents? Might give some clues as to reasons. Thanks!

    Tuesday, March 3, 2015 11:47 PM
  • Could you please share the entire M expression you use?

    I cannot reproduce this error when I try your formula above on a single column.

    Wednesday, March 4, 2015 10:47 AM
  • Sorry I lied.  There was a pivot in upstream query. 

    Two rows that were pivoted had same value, resulting in dupe column names. That was what was throwing error.

    In this context the error makes sense eg there were too many elements to complete operation, and both Gil and Curt asked questions that pointed to issue.

    Thanks!

    Expression.Error: There were too many elements in the enumeration to complete the operation.
    Details:
        List



    Wednesday, March 4, 2015 7:25 PM
  • I had the same problem on several occasions when I establish a dynamic user selectable date filter.  The table enables users to select an upper and lower date range referenced by downstream queries.  The issue I have is when I do a RefreshALL it knocks out the user selects as it refreshes the DateSelect table, meaning the cascaded queries have empty record sets.  The user has to go back and re-select. Haven't been able to get around this issue, So if you any ideas?
    Friday, March 13, 2015 11:20 AM
  • You may consider to repost this issue on a new thread. It looks like a different issue. Could you elaborate what is the user experience? Do you require user interaction which includes selection of a value from a query table? Can you share the M expression?

    Sunday, March 15, 2015 6:53 PM
  • Interesting PQUK.

    My power query setup did include a similar arrangement. 

    I allowed user to input criteria by selecting value in a one row/one header range. 

    This range was used to create 'mini' Power Query, that was merged into another query, so that the user criteria can be used in Power Query filters and calculations.

    My user criteria range always had a value so was never blank.

    However, I never used RefreshAll (the Excel menu icon open). I either ran Power Query directly from Query Pane or with a specifically targeted Query Refresh VBA on that worksheet's query.

    Also, I am 100% certain my user criteria were not being reset or otherwise not included as expected in Power Queries.

    So this does appear to be different issue worthy of its own thread.

    Sunday, March 15, 2015 9:27 PM
  • If I understood the issue - You can create a single query from the two queries (in which one query depends on the other). This way in refresh all you will not get an empty set like you get now, as the second query is refreshed before for the first query.


    Sunday, March 15, 2015 9:38 PM
  • In my case, data wasn't refreshed and one of the columns was renamed in the database and on that column, I applied Replace Values under Transform Edit Query. This was causing the error.

    IN
    Thanks, Kashif









    Sunday, January 7, 2018 10:11 AM