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 enumeration to complete the operation". Reason is in file two there two entries for Role.

But I want final result should looks like this;

Any suggestion or help would be appreciated!

Thanks in Advance!

Need Help

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

16 Replies

MFelix
Super User
1 year ago
Hi damit230183 ,

Apologies for the question but if the final result is just to keep the Value and File name you don't need to Pivot the table you just need to delete the Field Name column and rename the value one, or am I understanding you end result incorrectly?

Concerning the error you are getting you can check the video link below that can assist on how to correct this:
RemyO
Resolver I
1 year ago
Option 1) Dont import fieldname
Option 2) Delete fieldname from resultset after importing
OPtion 3) dont pivot over Fieldname but over eithe 1 of the other columns
- damit230183
  Helper II
  1 year ago
  Field Name custome created field in power query after pulling 2 pdf files from Folder.
  
  Field name is must becuase value under field name is COLUMN NAME we are going to use.
  
  Thanks
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.
Pivot_Table.pbix18 KB
- 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
    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.
RemyO
Resolver I
1 year ago
Why do you need a Pivot ? The first table with 3 columns IS THE EXACT SAME TABLE as the third table.

You could just rename the value column to "Role"
- damit230183
  Helper II
  1 year ago
  Hi,
  Thanks for your response.
  
  Well it's needed because in screenshot only one value as shawn, however in real data there are lot of different values with different field name set up. For example,
  
  This is the reason why I need to pivot.
  
  Thanks
v-echaithra
Community Support
1 year ago
Hi damit230183 ,

We would like to confirm if you've successfully resolved this issue or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are more than happy to continue to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.
- damit230183
  Helper II
  1 year ago
  No, I have not got expected answer yet.
  
  Thanks
  - MFelix
    Super User
    1 year ago
    Hi damit230183 ,
    
    Can you please explain the overall result you want and how the data is ingested.
    
    In your images it seems that you just need to delete a column and rename the other One but you say that is not the final outcome.
    
    Can you please give more context.
v-echaithra
Community Support
1 year ago
Hi damit230183 ,

We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Chaithra E.
v-echaithra
Community Support
1 year ago
Hi damit230183 ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.
v-echaithra
Community Support
1 year ago
Hi damit230183 ,

We wanted to follow up to see if the issue you reported has been fully resolved. If you still have any concerns or need additional support, please don’t hesitate to let us know, we’re here to help.

We truly appreciate your patience and look forward to assisting you further if needed.

Warm regards,
Chaithra E.

Forum Discussion

Error while doing PIVOT in power query

16 Replies

Related content

error message

network error

RDL Error

Underlying Error: OnPremiseServiceException

Data Refresh Errors

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

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