Forum Discussion

Zakaria_1980's avatar
Zakaria_1980
Post Patron
4 years ago
Solved

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 ...
  • mahoneypat's avatar
    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