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

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
JonasM
New Member

Table.SplitColumn

I want to split a column in two when the char for slash (/) is followed by a number from 0 to 9 for example a string like this "EASYPARK/22-05-23" will be splitted in two columns one with "EASYPARK" and in the other one "/22-05-23" but I cant find the correct command and syntax, looked into Splitter.SplitTextByCharacterTransition but didnt solved it all the way. How to do?

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUMjgxwDPLWNzLSNTDVNTJWitVBiGIV1EKIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.PositionOf([Data],"/")>=0 and Value.Is(Number.FromText(Text.At([Data],Text.PositionOf([Data],"/")+1)), type number) then Text.Replace([Data],"/","~%^/") else [Data]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("~%^", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Data"})
in
    #"Removed Columns"

View solution in original post

2 REPLIES 2
AlexisOlson
Super User
Super User

I think the simplest thing to do would be to split on the "/" and then add it back if you need it.

 

Sample query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUMjgxwDPLWNzLSNTDVNTJWitVBiBoZgbmO+gZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Col1", Splitter.SplitTextByEachDelimiter({"/"}, QuoteStyle.Csv, false), {"Before/", "/After"}),
    #"Prepend Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"/After", each "/" & _, type text}})
in
    #"Prepend Text"

 

Note: This doesn't check that "/" is followed by a digit but I can't tell from a single example whether that matters or not for your real data.

Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnUMjgxwDPLWNzLSNTDVNTJWitVBiGIV1EKIxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.PositionOf([Data],"/")>=0 and Value.Is(Number.FromText(Text.At([Data],Text.PositionOf([Data],"/")+1)), type number) then Text.Replace([Data],"/","~%^/") else [Data]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Added Custom", "Custom", Splitter.SplitTextByDelimiter("~%^", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Data"})
in
    #"Removed Columns"

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.