cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WLou
Helper I
Helper I

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)

Annotation 2020-07-24 000240.jpg

Regards,

Wendy

1 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"

 

 

 

 

View solution in original post

4 REPLIES 4
camargos88
Super User III
Super User III

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.



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



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"

 

 

 

 

View solution in original post

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors