none
PQ Error: Expression.Error: There were too many elements in the enumeration to complete the operation. RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

    Wednesday, June 27, 2018 7:37 PM
    |

All replies

  • Question
    Sign in to vote
    1
    Sign in to vote

    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

    Wednesday, June 27, 2018 7:37 PM
    |
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
    |