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

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

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