Pivoting error

[rdnguyen]

I hope you could help me understand this error better as I know exactly it came from Pivoting table; the scenario is I am having 79K record describing part, with various characteristics, maximum 06, but some part only have 02 characteristics per say. Then I wanted to pivot this characteristics column to 06 columns, thus the part which less than 06 characteristics would be pivotted with some null value which worked fine in power query design. Yet this error failed the refresh after all and I hardly find a way to get over it.

Error: Expression.Error: There weren't enough elements in the enumeration to complete the operation. {0.0002094 5.98E-05}. RootActivityId = ea8346e5-c552-4feb-93ef-6636046f6c00.Param1 = Expression.Error: There weren't enough elements in the enumeration to complete the operation. {0.0002094 5.98E-05} Request ID: 3816da8e-ffdb-4e45-99ec-95fd2c87ba51.

[rdnguyen]

Hi @BA_Pete, 

Your mention of adding index column reminded me to check for duplicated values and I did find a dozen of those orphan records causing trouble.

After removing those orphan record, that worked well, no index needed. Thanks alot Pete!

[BA_Pete · Accepted solution]

Hi @rdnguyen ,

This occurs when there's no unique key in the table for PQ to accurately allocate the values to the correct rows.

Try adding an index column starting from 1 to your table before the pivot step.

Pete