Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to 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"
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.
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
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
26 | |
12 | |
11 | |
11 | |
8 |