Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tgjones43
Helper IV
Helper IV

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 4
dofrey
Frequent Visitor

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 1Column 2
Project NameProject 1
ResponsiblePerson 1
Project NameProject 2
ResponsiblePerson 2

 

To fix it add a column, that you can pivot the table as follows:

IdentifierColumn 1Column 2
ID 1Project NameProject 1
ID 1ResponsiblePerson 1
ID 2Project NameProject 2
ID 2ResponsiblePerson 2

 

It will result in:

IdentifierProejct NameResponsible
ID 1Project 1Person 1
ID 2Project 2Person 2
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

https://social.technet.microsoft.com/Forums/en-US/7923e025-a0c4-407b-bd0f-65959e5038bb/what-does-thi...

Best Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.