Delimeter issue

[Zakaria_1980]

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 Construction Work Quality_Actual Date

MS 3 - SAR Completed_Forecast Date

11.6 Site Grounding_Forecast Date

MS 15 Imp Starts_Actual Date

IS 2.1	SARF Received & Site Search Started	Forecast Date
IS12.3	Construction Work Quality	Actual Date
MS 3	SAR Completed	Forecast Date
11.6	Site Grounding	Actual Date
MS 15	Imp Starts	Forecast Date

thx in advance

[mahoneypat · Accepted solution]

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

[Zakaria_1980]

the output should be like this when it is splitted pls:

IS 2.1	SARF Received & Site Search Started	Forecast Date
IS12.3	Construction Work Quality	Actual Date
MS 3	SAR Completed	Forecast Date
11.6	Site Grounding	Actual Date
MS 15	Imp Starts	Forecast Date

[Zakaria_1980]