Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

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
Anonymous
Not applicable

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
Community Champion
Community Champion

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

Anonymous
Not applicable

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 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.