Answered by:
PQ Error: Expression.Error: There were too many elements in the enumeration to complete the operation.

Question
-
I know there are other questions about this but I've gone through them and none of the ones I saw helped. I have a very simple Excel file with two columns: one column is a list of names and the other column is a list of roles each user can have in a software product. There can be one person with more than one role; the column with the roles has a row for each person using that role. Here is a shortened list:
Column1 Column2 Administrator Sam Solak Administrator Frank Raspeño Administrator Jo Beck Administrator Mat Carvell Administrator Pablo Moreno Administrator Gary Gilbert Administrator RF Smart Administrator Rick Altenhofen Board Member - View All Kevin Kenney Board Member - View All Billy Holstein CCS - Sales Manager Classic - J. Pop. Maggy Poppenhagen CO A/P Classic Kevin Vega CO A/P Classic William Williams CO A/R & A/P Advanced Classic Gary Gilbert CO A/R & A/P Advanced Classic Jennifer Lopez CO A/R & A/P Advanced Classic Jorge Nigeria CO A/R & A/P Advanced Classic Luma Lumas CO Accountant Classic Jo Beck CO Sales Manager Classic - S. Bar Sabrina Sorriano CO Sales Rep Classic Ariel Franklin CO Sales Rep Classic Cody Colbert All I want to do is put each role as a column header and list the people in those roles under each column header.
1. I select the data I want and click on "Data > From Table/Range in Excel" (I've tried clicking including and not including the Headers)
2. Once the screen comes up with the data, I select column1 and not then click on "Transform > Pivot Column"
3. After clicking on Advanced options at the next screen I select "Don't Aggregate" then click OK
The result lists all the roles in column1 across the top only once each, as I want, but many of the columns have an error in the first row which says "Error: Expression.Error: There were too many elements in the enumeration to complete the operation." rather then listing all the names that have that role.
Wednesday, June 27, 2018 4:23 PM
Answers
-
Hi. I think you can add a grouping with no aggregation (all rows), with adding an simple index column to grouped data, before pivoting your data. Then each repeating value form Names will have a different index, so it could be placed in different rows:
// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Role"}, {{"Count", each Table.AddIndexColumn(_[[Name]],"idx"), type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Name", "idx"}, {"Name", "idx"}), #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Role]), "Role", "Name"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"idx"}) in #"Removed Columns"
Maxim Zelensky Excel Inside
- Proposed as answer by Colin Banfield Wednesday, June 27, 2018 8:08 PM
- Marked as answer by Imke FeldmannMVP Sunday, July 29, 2018 7:50 AM
Wednesday, June 27, 2018 7:37 PM
All replies
-
Hi. I think you can add a grouping with no aggregation (all rows), with adding an simple index column to grouped data, before pivoting your data. Then each repeating value form Names will have a different index, so it could be placed in different rows:
// Table1 let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group(Source, {"Role"}, {{"Count", each Table.AddIndexColumn(_[[Name]],"idx"), type table}}), #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Name", "idx"}, {"Name", "idx"}), #"Pivoted Column" = Table.Pivot(#"Expanded Count", List.Distinct(#"Expanded Count"[Role]), "Role", "Name"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"idx"}) in #"Removed Columns"
Maxim Zelensky Excel Inside
- Proposed as answer by Colin Banfield Wednesday, June 27, 2018 8:08 PM
- Marked as answer by Imke FeldmannMVP Sunday, July 29, 2018 7:50 AM
Wednesday, June 27, 2018 7:37 PM -
Thanks for the suggestion. I got it to work by adding a column then selecting the "Add Index" before doing the pivot. I'm not sure if the is exactly what you meant but it seems to have worked.Friday, June 29, 2018 1:53 PM