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" ...
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"
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"