Forum Discussion

ishana's avatar
ishana
Regular Visitor
9 years ago
Solved

Merging 2 rows by splitting one column

Hey! I want to know how can I split my column in the following way  I have data like:               s1  1   2    3                                         s2  1   2    3                           ...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    ishana,

     

    You may refer to the following code snippet.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjZUMFQwUjBWitUBcoyQOcYwTiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Removed Duplicates" = Table.Distinct(#"Split Column by Delimiter"),
    #"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"Column1", Order.Ascending}}),
    #"Transposed Table" = Table.Transpose(#"Sorted Rows")
in
    #"Transposed Table"
TomMartens's avatar
TomMartens
Super User
9 years ago

Hey,

 it is ...

 

I started with this table

 

Than i selected the columns c1, c2, c3 and created a new column: Add column --> From Text --> Merge Columns

 

Than I selected the column "pivot" and pivoted the Transform --> Any Column --> Pivot Column

 

The result

 

Due to the fact that "Pivot column" need a Value column I'm not sure if this will work for you, but maybe you will give it a try

 

Regards

ishana wrote:

Hey! I want to know how can I split my column in the following way 

I have data like:               s1  1   2    3

                                        s2  1   2    3

                                        s3  1   2    3

 

and what I want is   s1   s2   s3  1   2    3, all in one row. Is it possible to do this in power query ?

 

Thank You!