Forum Discussion
Expression Error when Pivot Column (no value aggregation)
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
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"
4 Replies
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.
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"Thank you, I had solved it by adding index and now know the reason of doing this
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
