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

v-echaithra's avatar
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's avatar
damit230183
Helper II
1 year ago

No, I have not got expected answer yet.

 

Thanks

  • 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