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

damit230183's avatar
damit230183
Helper II
1 year ago

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's avatar
MFelix
Super User
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