Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

 

DateStudent NameSubmissionsFeedbackCode
03/01/2022Lizui42FR-no delays but incomplete
06/01/2022Laufenburg75FR - submission missing pages 1-5
11/01/2022Tegalpapak88EN -copy already sent
12/01/2022Ar Rabiyah56EN-to complete before next month
22/01/2022Bellegarde38ES- finding next part 
21/01/2022Gangarampur33ES -  redo and submit
25/02/2022Luntas11EN  -copy already sent
26/02/2022Frei Paulo65FR - submission missing pages 15
26/03/2022Seedorf21PT  -  no delays but missing page 5
03/04/2022Bellegarde34EN -copy already sent
05/04/2022Gangarampur37PT- late submission
11/04/2022Luntas12FR - redo  and submit
09/05/2022Cosamaloapan de Carpio73EN-to complete before next month
15/05/2022Zagrodno99ES-next part for next week
1 ACCEPTED SOLUTION
AlB
Super User
Super User

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"

 


SU18_powerbi_badge

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.

 

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

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"

 


SU18_powerbi_badge

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.

 

freginier
Solution Specialist
Solution Specialist

Thank you @AlB

that's what I say, split on delimiter "-" and trim columns 🙂 

freginier
Solution Specialist
Solution Specialist

Split on delimiter "-" and then for each column apply "Trim" transformation 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors