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

[e175429]

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!

[Jai-Rathinavel · Accepted solution]

@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

[e175429]

Thank you! This worked perfectly. 

[Praful_Potphode]

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

[AlexisOlson]

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.

[e175429]

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