topic Re: Expression.Error: There were too many elements in the enumeration to complete the operation. in DAX Commands and Tips

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

e175429 — Fri, 14 Nov 2025 20:58:53 GMT

Hello all,

This is my data, raw:

I need it to be in this format:

(These I manually created, but my data is over 20,000 rows and I can't manually do them all.)

In power query, I've tried piveting the question and response row

But when I do this, some of the records come of the empty and I get a error

I googled that error and was advised to add a index column, which I did and repeated the steps.

But when I do that, what should be one record based on one day per email becomes 3 records based on the 3 different questions.

So all the repsonses from email: acooke on 1/31 should be on 1 line (it's on 3)

All the responses from fwstowel on 1/26 should be on 1 line (it's on 3)

All the responses from mdew on 1/24 should be on 1 line (it's on 3)

All the responses from mdew on 1/26 should be on 1 line (it's on 3)

So on and so forth.

It's creating a whole bunch of extra records.

It should all be on one line per email, per date:

Any help would be greatly appreciated!

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

AlexisOlson — Fri, 14 Nov 2025 22:08:34 GMT

This error would happen if you have any cases where the same person took the survey multiple times on the same day.

If the pivot works when you switch Don't Aggegate to take a max or a min instead, then I bet that's the problem. If that's the problem, you'll need to decide how you want to aggregate the records.

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

e175429 — Fri, 14 Nov 2025 22:24:25 GMT

It doesn't matter what aggregate function i choose, it does the same thing of triple counting the records.

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

Praful_Potphode — Sat, 15 Nov 2025 04:29:58 GMT

Hi e175429 ,

Please try approach suggested by AlexisOlson .If it doesnt work ,share more input/output information in the form of PBIX or sample data.

When you pivot the "Question" column with "Response" as values, Power Query expects each "Question" to appear only once per grouping key. If the same "Question" appears multiple times for the same "Email Address" or "Date", Power Query tries to aggregate them — but if no aggregation function is specified, it throws this error. Below is the power query code i tried and it worked beacuse there were no duplicates.can you also confirm the same?

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdRNT4QwEAbgvzLhTISy3ydNdK960JgYsodmnQ1NWorTIvDvbXWjZutisvTCoZB5Om+YKcvkTsOgWzggSrAVt6BbAotcAa9Nh4Sv/j2BqN9aQQM0pFVj5XCdpMkLGvdsDRK7wZ6rRuLVXit3xrJikRV5MU92aZk8vCNxKVOodAeGW2EOwtX11T/xTtjqSyE0ja4N+urP6Ljvr49QMR3yzm1rrFZIsO0bJIH1HuHJNe3dbW8JFcoQn4X48gePk+R81LgsyUfXaudvdNrQIsRWkdO81xac7OoE+nJUjxPnKjTWU+M8/4Osp2v/5fnnVGxCdxM7SZaPIhFnnIXbZJZHjvHsSLBww/zGI2V5ukmKjB2N3Qc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Question = _t, Response = _t, #"Email Address" = _t, Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Question", type text}, {"Response", type text}, {"Email Address", type text}, {"Date", type text}}), #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Question]), "Question", "Response") in #"Pivoted Column"

to answer your second question, you will see blanks in certain columns as pivot in power query creates all possible combinations with respect to other columns present in data.it is designed that way.

Thanks and Regards,

Praful

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

Jai-Rathinavel — Sun, 16 Nov 2025 08:01:29 GMT

e175429 I believe buffering the table would remove the enumeration error. I have tried to replicate your requirement using a mockup data and have achieved the output you are expecting. The PBIX file is attached for your reference.

Input Data:

Output:

You can remove the last before step if you want to display nulls instead of "No Response" for the users who haven't answered the questions.

Hope this helps !

Thanks,
Jai

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

e175429 — Mon, 17 Nov 2025 15:55:17 GMT

Thank you! This worked perfectly.