Forum Discussion
duplicate or parse a column with different length characters
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
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)
7 Replies
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"Feel free to mark multiple answers as a solution if they resolve your question.
Hi Anonymous
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
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)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"
