cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session Drive Data Culture with Power BI: Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

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.

Top Solution Authors
Top Kudoed Authors