Forum Discussion
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'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 ?
7 Replies
- ImkeF
Community Champion
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 - sturlaws
Resident Rockstar
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.
