Forum Discussion
Expression.Error at pivot column step
Hi all
I am getting this error message after a pivot column query:
Expression.Error: There were too many elements in the enumeration to complete the operation
Details:
List
which according to previous forum posts can be due to choosing 'Don't Aggregate' as the Aggregate Value Function. However, I have tried the other options and still get the same message.
Is there anything that I can do to work out why this is happening?
4 Replies
Hi tgjones43 ,
By my research, the above error may also occur when you pivot columns with same name, please check column names in your data source. And there is a similar thread for your reference.
Best Regards,
Cherry
Hi v-piga-msft
Thanks for your advice, but I don't think this applies to my case. A few more details - the column that is being pivoted has 3 unique values, none of which match the names of columns that already exist in the table, so I think that rules out your explanation?
If I filter out 2 of those values, so there is just 1 unique name in the pivoted column, the error disappears and the pivot works. If I filter out just 1 of the names, leaving 2 in the column, the error is still present.
Does that provide any clues?
Should anyone else have this problem, it turns out I had some duplicated rows in my dataset. Removing these before the pivot step solved the issue.
I had the same problem and figured out, that it is really important to read the instructions carefully. You can find in the bottom the explanation for "Error with the Don't Aggregate option".
You need at least one column, which consists the same value for all the rows, that should be combined to a new row in the new table. Power BI needs this to even know, what belongs to each other. To understand further, you can imagine, that the ordering of the table is not fixed. So any Project Name could belong to any Responsible if you mix up the table.
Pivoting the follwing table will fail:
Column 1 Column 2 Project Name Project 1 Responsible Person 1 Project Name Project 2 Responsible Person 2 To fix it add a column, that you can pivot the table as follows:
Identifier Column 1 Column 2 ID 1 Project Name Project 1 ID 1 Responsible Person 1 ID 2 Project Name Project 2 ID 2 Responsible Person 2 It will result in:
Identifier Proejct Name Responsible ID 1 Project 1 Person 1 ID 2 Project 2 Person 2
