duplicate or parse a column with different length characters

[sb14415]

I have a column with an assortment of the following example data.

null

R266A0744

S2370B1030

S4273A0940

This data represents

(Model R,S2,S4)(Julian date 3 char)(Machine 1 char)(Time 4 char)

I would like to parse this into 4 columns for the 4 parts of data. The issue is the Model may be 1 or 2 characters

[CNENFRNL]

let
    RE = (regex as text, str as text) =>
    let
        html = "<script>var regex = " & regex & "; var str = """ & str & """; var res = regex.exec(str).slice(1).join('|'); document.write(res)</script>",
        res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
    in res,

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjIyM3M0MDcxUYrViVYKNjI2N3AyNDA2gHBNjMyNHQ0sTYDcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Str = _t]),

    Match = Table.AddColumn(Source, "Match", each RE("/([A-Z]\d*)(\d{3})([A-Z])(.+)/gi", [Str])),
    #"Split Column by Delimiter" = Table.SplitColumn(Match, "Match", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Model", "Date", "Machine", "Time"})
in
    #"Split Column by Delimiter"

[AlexisOlson]

Everyone seems to be making this harder than it needs to be.

You can split by position from the right instead of the left using the optional startAtEnd argument:

Just type that ", true" into the formula box after clicking Split Column > By Position in the GUI:

Here's a full sample query you can paste into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVYKMjIzczQwNzEB84KNjM0NnAwNjA0gXBMjc2NHA0sTIDcWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
    #"Split Column by Positions" = Table.SplitColumn(Source, "Code", Splitter.SplitTextByPositions({0, 4, 5, 8}, true), {"Model", "Date", "Machine", "Time"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Model", type text}, {"Date", Int64.Type}, {"Machine", type text}, {"Time", type time}})
in
    #"Changed Type"

[sb14415]

Thanks. This seems to be the best option. It also applies to some other columns I need to split

[edhans]

Glad I was able to assist @sb14415.

[AlexisOlson]

Feel free to mark multiple answers as a solution if they resolve your question.

[edhans · Accepted solution]

You would need to write something along the lines of this:

if Text.Length([data column]) = 9 then Text.Start([data column],1) else Text.Start([data column]2)

Then do the same fo rthe other 3 parts, but using Text.Middle([data column],2,3) or whatever.

Just remember in Power Query, it starts counting at 0, so to get the 2nd and 3rd char, it would be Text.Middle([data column], 1, 2) (2nd column, 2 chars)

[Thingsclump]

Hi @sb14415 

Here you go. 

Input

Output

Code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjIyM3M0MDcxUYrViVYKNjI2N3AyNDA2gHBNjMyNHQ0sTYDcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Length" = Table.AddColumn(#"Changed Type", "Length", each Text.Length([Column1]), Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Text Length", "Column1", "Column1 - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1 - Copy.2", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Character Transition1",{{"Column1 - Copy.2.2", "Time"}, {"Column1 - Copy.2.1", "Machine"}, {"Column1 - Copy.1", "Model,date"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Length] = 9 then Text.Start([#"Model,date"], 1) else Text.Start([#"Model,date"], 2)),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each Text.End([#"Model,date"], 3)),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Model"}, {"Custom.1", "date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Model,date", "Length"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "Model", "Machine", "Time", "date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Machine", type text}, {"Time", type text}, {"date", type text}, {"Model", type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Column1", "Model", "date", "Machine", "Time"})
in
    #"Reordered Columns1"

Please accept it as solution if it solves your issue.

Thanks

www.thingsclump.com