Forum Discussion
Zakaria_1980
4 years agoPost Patron
Delimeter issue
Hi, I have the following Tbl in my report and for which i need to split it into 3 columns as shown the in the 2nd tbl: IS 2.1 - SARF Received & Site Search Started_Forecast Date IS12.3 ...
- 4 years ago
I couldn't find a way to robustly do it with the splitter functions, so came up with a different approach. It looks for the position of the last digit and then makes two columns breaking the text at that position. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BCoJAGIRfZfBc4ip1l8Lw0CH30MFElvWnlnRX1n+D3j7DIIJuc5j55qvrqJRIY4E1ZF4VqEiTeVCHS0iSdAtpmCBJeX2DZOWZurZwnrSaGHvFFDWrN0OkcYadsxP7oNk4i7Pzd5yC6g0/21zznL6Do0S2XM6jYezpP1eI+KNw8C7Yztjrn9YMExuUw7gYTr9vzQs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "LastDigitPosition", each let charlist = Text.ToList([Text]), poslist = List.Positions(charlist), justnumbers = List.Select(poslist, each List.Contains({"0".."9"}, charlist{_})) in List.Max(justnumbers)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Text1", each Text.Start([Text], [LastDigitPosition] + 1)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Text2", each Text.End([Text], Text.Length([Text]) - [LastDigitPosition] - 1)), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Text1", "Text2"}), #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Text2", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Text2", "Text3"}), #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","-","",Replacer.ReplaceText,{"Text2"}), #"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Text2", Text.Trim, type text}}), #"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Text1", type text}, {"Text2", type text}, {"Text3", type text}}) in #"Changed Type1"Pat
mahoneypat
4 years agoMicrosoft Employee
I couldn't find a way to robustly do it with the splitter functions, so came up with a different approach. It looks for the position of the last digit and then makes two columns breaking the text at that position. To see how it works, just create a blank query, open the Advanced Editor and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BCoJAGIRfZfBc4ip1l8Lw0CH30MFElvWnlnRX1n+D3j7DIIJuc5j55qvrqJRIY4E1ZF4VqEiTeVCHS0iSdAtpmCBJeX2DZOWZurZwnrSaGHvFFDWrN0OkcYadsxP7oNk4i7Pzd5yC6g0/21zznL6Do0S2XM6jYezpP1eI+KNw8C7Yztjrn9YMExuUw7gYTr9vzQs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Text = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Text", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "LastDigitPosition", each let
charlist = Text.ToList([Text]),
poslist = List.Positions(charlist),
justnumbers = List.Select(poslist, each List.Contains({"0".."9"}, charlist{_}))
in
List.Max(justnumbers)),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Text1", each Text.Start([Text], [LastDigitPosition] + 1)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Text2", each Text.End([Text], Text.Length([Text]) - [LastDigitPosition] - 1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom2",{"Text1", "Text2"}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Removed Other Columns", "Text2", Splitter.SplitTextByEachDelimiter({"_"}, QuoteStyle.Csv, true), {"Text2", "Text3"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","-","",Replacer.ReplaceText,{"Text2"}),
#"Trimmed Text" = Table.TransformColumns(#"Replaced Value",{{"Text2", Text.Trim, type text}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Trimmed Text",{{"Text1", type text}, {"Text2", type text}, {"Text3", type text}})
in
#"Changed Type1"
Pat