Forum Discussion
split column
Hello all,
I need help splitting a column into text and quantity. I've tried using "Split by" and "Text.Remove," but I'm not getting satisfactory results. Is there a quick way to accomplish this? Any suggestions?
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.
5 Replies
It looks like you can split by space character, once, as far left as possible. Then use
Text.Select([ColumnName], {"0".."9"})to get the number.
I think you have some additional conditions but that will be a good start
Please define/show what you want.
There are lots of ways to separate numbers and text but only you know what you need from the picture shown.
Thank you for your response. so I need to split a column into two separate columns: one for quantity (QTY) and another for reject reasons. The data comes from the production line, and operators use different notations to describe the quantity, such as 15x, 15 x, 15*, 15 *, 15p, 15 p, or 15 sharp edges. Wherever a number is not provided, it should be treated as 1. Any suggestions on how to achieve this? example from excel doc below. thank you 🙂
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.
