Expression Error when Pivot Column (no value aggregation)

[WLou]

Hi there 

I'm sure this question has been ask many many times

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

This happened to me when I was trying to pivot two columns, and no aggregation of value here they all text

The wired thing is, there were 5 files in the folder, only the fifth one started to have this problem, I fixed it by redownload the file; but when it comes down to 6th file, I just could not fix it 

I fixed it in the end by adding an index column

However can anyone kindly expalin (again) of the reason behind the error and the indexing? 

Below is what I have as an example, (data are randomly created)

Regards,

Wendy

[camargos88]

Hi @WLou ,

You get this error because you are trying to expand 2 values in 1 row (combination of columns).

Tha's why it works when you add an index, you have a different combination for the values.

[WLou]

Hi @camargos88 

Thank you ! My data set is quite large which is why it didn't error in the first few files as they are distinctive, but the very last file has duplicated rows in there 

I now understand 

Regards,

Wendy

[Anonymous · Accepted solution]

try this code to see if overcomes your problem (I hypothesized that the two tables you published are the starting one and the expected one)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSipT0gERsTq42cmGQHaqIYRtBGQXGCGJG2GI4zUnH4s5xOgFqzFGshfIjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1),
    #"Duplicated Column" = Table.DuplicateColumn(#"Added Index", "Column1", "Column1 - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Column1]), "Column1", "Column1 - Copy"),
    #"Sorted Rows" = Table.Sort(#"Pivoted Column",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

if you don't need index, use this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSipT0gERsTq42cmGQHaqIYRtBGQXGCGJG2GI4zUnH4s5xOgFqzFGshfIjgUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Column1", "Column1 - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Column1]), "Column1", "Column1 - Copy",each _),
    #"Expanded bv" = Table.ExpandListColumn(#"Pivoted Column", "bv"),
    #"Expanded c1" = Table.ExpandListColumn(#"Expanded bv", "c1"),
    #"Expanded c2" = Table.ExpandListColumn(#"Expanded c1", "c2"),
    #"Sorted Rows" = Table.Sort(#"Expanded c2",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"

[WLou]

Thank you, I had solved it by adding index and now know the reason of doing this