Forum Discussion

JonasM's avatar
JonasM
New Member
4 years ago
Solved

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?

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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"

2 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Most Valuable Professional
    4 years ago

    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"
  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    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.