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

[tking]

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'd like it to. 

When I group them, I get two tables with displayValue but I need to transpose both of them to be ready to append to my table. My goal is to have it in a format like this. 

Any ideas ?

[ImkeF]

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]

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]

Hi @tking 

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

[tking]

Hey @ImkeF 

Something like this based on the initial data

[ImkeF]

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. 

[sturlaws]

hi @tking,

first group by rowNumber, make sure you change Operation to 'All rows'. You could also change the new column name to something more meaningfull than Count, e.g. newDisplayValue.

Then add a custom with this code: Table.Column([newDisplayValues],"displayValue")

Now you will see that the new column has an expand button in the title bar. Click on this and select Extract values, and separate the values with a delimter, e.g. ||. 

Next split the new column by the delimter you choose.

Finish off by removing the newDisplayValue column

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[tking]

hey @sturlaws Grouping by rowNumber gives me a new table and I get a conversion error from table to list when I try your custom code.