Forum Discussion
duplicate or parse a column with different length 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)
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"Thanks. This seems to be the best option. It also applies to some other columns I need to split
Feel free to mark multiple answers as a solution if they resolve your question.
Glad I was able to assist Anonymous.