split column

Hi Carlla_n83, with this example you can do this:

Result

Hi,

Thanks for the solution dufoq3  and HotChilli  provided, and i want to offer some more information for user to refer to.

hello Carlla_n83 , you can create a blank query and put the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY5LDsIwDESvMuoSIcRPhROwZo+6CKlpoxY7io3o8UnVwIKlR2/e+Hardri8lPAMqoG7qlnPEe4yQR7wPfkBY1BDR2YZwNOlgdrNNQlYNqf6vK+P51ILrJTsz5U11hO8MJM3SQgKE4wiedZxO98sueX8MA8UMAgXwYQ4viLewfrSUksuxt/EdgXtXYrLtb9+H4DRZEt4gIaWssgx6aLS3AB1pAtRF0kBMzdWTfMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}}),
    Custom1 = Table.TransformColumns(#"Changed Type1",{{"Column1.1",each Text.Remove(_,{"x","X","P","p","*"})},{"Column1.2",each  Text.Combine(List.RemoveItems(Text.Split(_," "),{"x","X","P","p","","*"})," "),type text}}),
    #"Changed Type2" = Table.TransformColumnTypes(Custom1,{{"Column1.1", Int64.Type}})
in
    #"Changed Type2"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.