Forum Discussion
Split column but remove leading space and dash
Hello,
I have the following table named "Student records" and i am trying to split the "Code" column into two columns, with the first column showing the first two letters only and the second column showing the rest but without any leading spaces or dash "-". Is that possible to achieve in Power Query? Any help is much appreciated!
| Date | Student Name | Submissions | Feedback | Code |
| 03/01/2022 | Lizui | 4 | 2 | FR-no delays but incomplete |
| 06/01/2022 | Laufenburg | 7 | 5 | FR - submission missing pages 1-5 |
| 11/01/2022 | Tegalpapak | 8 | 8 | EN -copy already sent |
| 12/01/2022 | Ar Rabiyah | 5 | 6 | EN-to complete before next month |
| 22/01/2022 | Bellegarde | 3 | 8 | ES- finding next part |
| 21/01/2022 | Gangarampur | 3 | 3 | ES - redo and submit |
| 25/02/2022 | Luntas | 1 | 1 | EN -copy already sent |
| 26/02/2022 | Frei Paulo | 6 | 5 | FR - submission missing pages 15 |
| 26/03/2022 | Seedorf | 2 | 1 | PT - no delays but missing page 5 |
| 03/04/2022 | Bellegarde | 3 | 4 | EN -copy already sent |
| 05/04/2022 | Gangarampur | 3 | 7 | PT- late submission |
| 11/04/2022 | Luntas | 1 | 2 | FR - redo and submit |
| 09/05/2022 | Cosamaloapan de Carpio | 7 | 3 | EN-to complete before next month |
| 15/05/2022 | Zagrodno | 9 | 9 | ES-next part for next week |
Hi Anonymous
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJPT8MwDMW/itXzqrXpOuAIE3BBaBqcQDu41CsRaVKlrWB8etw028KfoR2sxIffy/OLn5+jJJsm6VQkQkST6E5+9pLPGdfQ36xibaAkhdsWir4DqV9M3SjqKFpPGJ6HMPYb0kVvK27OuHKnADG0fVHLtpVGgzt1BQ1W1EIa504nTQOdR6pQNdjgGzfnvq7vIX4xzRZQWcJyCy3pbmRFwF5aWGEht/jq3587Nu4M7IxDQRtjCTR9dFAb3b06GRHKXJFS7MKWxE22s/AQw0bqcrDv4AZtByMc+r9FzSjWTW89nTmagwBLpQHU5RjJOIDIp4nYh9jrDlu+pL548GOTi3kA3liSsMReGT/1Cenne5lsJ/NA7NBu/P8PBpaPMDj/vgehEIwywyLNjgY4+/cPkzxgf+d35mzEoJC/7zDOfnVmf6YndgG40H+mnlxM+VXPLUyLNSrDS6d5TFigbaTxL2enrlCaB5JPWFlT6kHkwhcv0GFvWGDk34neovX6Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Student Name" = _t, Submissions = _t, Feedback = _t, Code = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Student Name", type text}, {"Submissions", Int64.Type}, {"Feedback", Int64.Type}, {"Code", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Code_1", each Text.Start([Code],2)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Code_2", each Text.Trim(Text.AfterDelimiter([Code],"-"))) in #"Added Custom1"
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
3 Replies
Split on delimiter "-" and then for each column apply "Trim" transformation
Hi Anonymous
Place the following M code in a blank query to see the steps.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZJPT8MwDMW/itXzqrXpOuAIE3BBaBqcQDu41CsRaVKlrWB8etw028KfoR2sxIffy/OLn5+jJJsm6VQkQkST6E5+9pLPGdfQ36xibaAkhdsWir4DqV9M3SjqKFpPGJ6HMPYb0kVvK27OuHKnADG0fVHLtpVGgzt1BQ1W1EIa504nTQOdR6pQNdjgGzfnvq7vIX4xzRZQWcJyCy3pbmRFwF5aWGEht/jq3587Nu4M7IxDQRtjCTR9dFAb3b06GRHKXJFS7MKWxE22s/AQw0bqcrDv4AZtByMc+r9FzSjWTW89nTmagwBLpQHU5RjJOIDIp4nYh9jrDlu+pL548GOTi3kA3liSsMReGT/1Cenne5lsJ/NA7NBu/P8PBpaPMDj/vgehEIwywyLNjgY4+/cPkzxgf+d35mzEoJC/7zDOfnVmf6YndgG40H+mnlxM+VXPLUyLNSrDS6d5TFigbaTxL2enrlCaB5JPWFlT6kHkwhcv0GFvWGDk34neovX6Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Student Name" = _t, Submissions = _t, Feedback = _t, Code = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type text}, {"Student Name", type text}, {"Submissions", Int64.Type}, {"Feedback", Int64.Type}, {"Code", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Code_1", each Text.Start([Code],2)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Code_2", each Text.Trim(Text.AfterDelimiter([Code],"-"))) in #"Added Custom1"
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
