Forum Discussion

damit230183's avatar
damit230183
Helper II
1 year ago
Solved

Error while doing PIVOT in power query

Hi,   I have table like below generated from PDF file, when i tried to pivot based on Field name and Value with Don't Aggregate, there is an error "There were too many elements in the enumer...
Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    Hi damit230183 ,

     

    Try the following steps:

    • Group by Field Name

     

    • Add a custom Column wiht index

     

    • Expand the new column

     

    • Delete the previous group by column

     

    • Pivot by Field Name and don't aggregate the Value

    • Delete index column

     

    See the full code below:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQrKz0kFUsGlSVmpySVKsToQ4eCSxBKQeEhqRWIxWNQIu2IjJMV+qeUKkflF2TjUKxhi6HDLyS/KTElUio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"File Name" = _t, #"Field Name" = _t, Value = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Field Name"}, {{"Count", each _, type table [File Name=nullable text, Field Name=nullable text, Value=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn ([Count], "Index", 1)),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"File Name", "Value", "Index"}, {"File Name", "Value", "Index"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Count"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Field Name"]), "Field Name", "Value")
in
    #"Pivoted Column"

     

     Believe this is what you need please let me know if there is the need for any further assistance

v-echaithra's avatar
v-echaithra
Community Support
1 year ago

Hi damit230183 ,

Thank you for reaching out to Microsoft Community.

Add an Index Column
Go to Add Column > Index Column > From 0
Create a Unique Column Key for Pivot
Combine Field Name and Index to create a unique column for pivoting.

Go to Add Column > Custom Column > Rename(PivotKey)
[Field Name] & "_" & Text.From([Index])

Field Name Value File Name index PivotKey
Role Subject 1 0 Role_0
Role Subject 2 1 Role_1
Role Subject 1 2 2 Role_2


Now go to Transform > Pivot Column
Pivot the PivotKey column
Use Value as the values column
Use Don’t Aggregate
Remove the unecessary columns if required

you will get a table like this:

File Name Role_0 Role_1 Role_2
1 Subject null null
2 null Subject null
2 null null Subject 1



Select the Role_0, Role_1, and Role_2 columns
Go to the Transform tab > click Unpivot Columns
Now your table looks like this:

File Name Attribute Value
1 Role_0 Subject
2 Role_1 Subject
2 Role_2 Subject 1



Remove the attribute column if it's not needed
Right-click > Remove

You can also refer to the PBIX file attatched below.
Hope this will help.

Best Regards,
Chaithra E.

  • damit230183's avatar
    damit230183
    Helper II
    1 year ago

    Hi Thanks for your response, but it's not working as expected. 

     

    As in my case, I want ROLE as Column name (Only One) and values should Subject, Subject and Subject1 for 3 different records as I have pasted above in questions (Result Screen Shot).

     

    Thanks

    • v-echaithra's avatar
      v-echaithra
      Community Support
      1 year ago

      Hi RemyO ,

      Unfortunately, Power Query cannot pivot a column when the pivot key (Field Name) has duplicate entries for the same key like File Name, unless you aggregate.
      This happens because Power Query expects one unique value per pivot column (Field Name) per key (File Name). When it sees two or more (e.g., multiple "Role" entries for the same file), it doesn’t know how to fill the cell without aggregating and since you chose "Don’t Aggregate", it fails.