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

[adelmonte]

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

[parry2k]

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.

[adelmonte · Accepted solution]

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

[pk651]

Hi There,

Its not working for me. still getting same error.

[Anth79]

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

[parry2k]

@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

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.

[adelmonte]

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

[parry2k]

@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.

[adelmonte]

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