Error while doing PIVOT in power query

[damit230183]

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!

[v-echaithra]

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]

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]

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]

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]

No, I have not got expected answer yet.

Thanks

[MFelix]

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.

[damit230183]

Hey, Sure

So for an example, I have like this

I would like to have end result like this

Apolgoies but I cant share orig data but data layout is like what I have pasted here. This is just an example. But, in reality there are more than 50-60 different field name and their value exists which needs to be convert into Column name (Different Fieldname) and Value as an data under each column.

THanks

[MFelix · Accepted solution]

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

[RemyO]

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]

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]

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]

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]

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]

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]

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

[MFelix]

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: