Forum Discussion

damit230183's avatar
damit230183
Icon for Helper II rankHelper 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
Icon for Community Support rankCommunity 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.