Clean Up Messy Column

Hi Anonymous ,

if format is fixed as you show, this this can be done as below:

1. Split with space delimiter.

2. Add conditional column with checking the middle column generated in above for containing % and if now % symbol, then its lettering column.

3, Add one more conditional column with checking midle column for containing % and state reverse condition of 2 above, this is % output.

Done.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZBNCsQgDIWvEgruymA1/sxel0L3pfe/xhgjU6MtWJ7vfcbE69rOw1h02/4IaOveaxYDeguZUpZV5TlOT5xk/o3GIxQ61XVVZamPWr3cQa6gnKD+mFOiV4harRSZE/UJrxzZRGqH1rULWYnJ2EpjmKYwg2mNPKfpZ/RYPdHocxEUSOxvI5qIAglera1Uk5H6HWCwjsV12r7XafaAZbDcD+06Y/VIlLFE4ZHvHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"OLD INPUT" = _t, #"REQUIRED OUTPUT (ID)" = _t, #"REQUIRED OUTPUT (LETTERING)" = _t, #"REQUIRED OUTPUT (%)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OLD INPUT", type text}, {"REQUIRED OUTPUT (ID)", type text}, {"REQUIRED OUTPUT (LETTERING)", type text}, {"REQUIRED OUTPUT (%)", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"REQUIRED OUTPUT (ID)", "REQUIRED OUTPUT (LETTERING)", "REQUIRED OUTPUT (%)"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "OLD INPUT", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"OLD INPUT.1", "OLD INPUT.2", "OLD INPUT.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"OLD INPUT.1", type text}, {"OLD INPUT.2", type text}, {"OLD INPUT.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"OLD INPUT.1", "ID"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",null,"",Replacer.ReplaceValue,{"OLD INPUT.2"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"OLD INPUT.3"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value1", "Lettering", each if Text.Contains([OLD INPUT.2], "%") then [OLD INPUT.3] else [OLD INPUT.2]),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Output%", each if Text.Contains([OLD INPUT.2], "%") then [OLD INPUT.2] else [OLD INPUT.3]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Conditional Column1",{{"Lettering", type text}, {"Output%", Percentage.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type2",{"OLD INPUT.2", "OLD INPUT.3"})
in
    #"Removed Columns1"

Hope it helps.