Forum Discussion

tking's avatar
tking
Regular Visitor
6 years ago

Transforming one large column into multiple distinct columns based on a third column

Hey I've ran into a situation where I need to pivot a grouping column and the relevant data should be grouped based on that. I'm trying to group display value by row number but it's not working as I...
Need Help
ImkeF's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
6 years ago

Hi tking 

please paste this code into the advanced editor and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PMQzKLzdU0lEyVIrVAQsYoQsYowuAtBgBBYyQtaAIGCMEYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column2"}, {{"Row", each _[Column1]}}),
    Custom1 = Table.FromRows( #"Grouped Rows"[Row] )
in
    Custom1

 

tking's avatar
tking
Regular Visitor
6 years ago

hey ImkeF 

 

I tried your approach and it worked fine but the problem is that I have null values for some of my cells. Is there a way to keep them without writing more custom M code? I was hoping for an easier non code solution to the problem because it seems so trivial when you think about it

  • ImkeF's avatar
    ImkeF
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi tking 

    could you please paste an example of the exact result you want to see from the sample data you've provided?

     

    • tking's avatar
      tking
      Regular Visitor
      6 years ago

      Hey ImkeF 

      Something like this based on the initial data

       

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi tking 

        my solution keeps the nulls as you've requested.

        Much of it can be done through the UI:

        - Check column "Row Number"

        - Group and select "All" at "Operations"

        - Tweak the resulting code by adding the ColumnName in square brackets

         

        I'm afraid I cannot think of a simpler solution here.