topic Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation. in Desktop

Pivot Column Error - There were too many elements in the enumeration to complete the operation.

adelmonte — Mon, 31 Jan 2022 15:37:56 GMT

Hi all,

I am trying to pivot a column in power query and I get an error:

Expression.Error: There were too many elements in the enumeration to complete the operation.
Details:
[List]

Any idea how to solve the issue?

Thanks,

Alex

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

parry2k — Mon, 31 Jan 2022 15:38:56 GMT

adelmonte before you pivot the data, add an index column.

✨ Follow us on LinkedIn and to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

adelmonte — Mon, 31 Jan 2022 15:53:28 GMT

It doesn't give me the desired outcome.
Thanks anyway!

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

parry2k — Mon, 31 Jan 2022 17:36:26 GMT

adelmonte try this script, start a new query, click advanced editor and paste this code, you can tweak it as you see fit and if works fine, apply it on your data.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksSVXSUTIyMDLUNQAhpVidaKWwxJzSVEOgOBLXCKQMzEXRY4ShxxhVjwmmHmMMPaaoesyUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Group", each if [Column1]= "Date" then [Index] else null), #"Filled Down" = Table.FillDown(#"Added Custom",{"Group"}), #"Removed Columns" = Table.RemoveColumns(#"Filled Down",{"Index"}), #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Column1]), "Column1", "Column2"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Group"}) in #"Removed Columns1"

✨ Follow us on LinkedIn and to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

adelmonte — Tue, 01 Feb 2022 07:40:09 GMT

Hi parry2k,
Your code doesn't make sense for my case but thanks anyway.

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

adelmonte — Tue, 01 Feb 2022 07:44:31 GMT

I added Source name column and used uniqueness of that column to pivot and it worked fabulously 🙂

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

parry2k — Tue, 01 Feb 2022 13:03:01 GMT

My code 100% made sense. It you were unable to understand and that is a different story.

it grouped the rows together. Anyhow your problem is solved that matters the most.

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

Anonymous — Wed, 25 Oct 2023 08:43:20 GMT

Hi There,

Its not working for me. still getting same error.

Re: Pivot Column Error - There were too many elements in the enumeration to complete the operation.

Anth79 — Thu, 08 Aug 2024 08:00:29 GMT

I had the same issue. I added an Index column before my pivot, as suggested by parry2k, and my pivot worked after that.