replace values with If condition

Hi dogburalHK82 ,

Here's the rule as I understand it: if the first "-" is preceded by numbers, return the first number as well as "xxx"; if the first "-" is preceded by both numbers and letters, return all of the letters; if the first "-" is preceded by all of the letters, return the entirety of what's preceded by the second "-".
You can put all of these M functions into the Advanced Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrS01DXUNTBQitUBcQyAHENDMMfMzAzBcQxBkfN0dgHqMTAAE3ARQwNjmEgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Part Number", type text}}),
    AddedCustom = Table.AddColumn(#"Changed Type", "Custom", each 
        let 
            PartNumber = [Part Number],
            DashIndex = Text.PositionOf(PartNumber, "-"),
            BeforeDash = Text.Start(PartNumber, DashIndex),
            IsDigit = Text.Contains(Text.Select(BeforeDash, {"0".."9"}), "-") = false and Text.Length(Text.Select(BeforeDash, {"0".."9"})) = Text.Length(BeforeDash),
            IsLetter = Text.Contains(Text.Select(BeforeDash, {"A".."Z","a".."z"}), "-") = false and Text.Length(Text.Select(BeforeDash, {"A".."Z","a".."z"})) = Text.Length(BeforeDash),
            FirstChar = Text.Start(BeforeDash, 1),
            FirstCharIsLetter = Text.Middle(BeforeDash, 0, 1) <> " " and List.Contains({"A".."Z","a".."z"}, FirstChar),
            Result = if IsDigit then Text.Start(BeforeDash, 1) & "xxx" else if not IsDigit and FirstCharIsLetter and not IsLetter then Text.Select(BeforeDash,{"a".."z","A".."Z"})
            else if IsLetter then let SecondDashIndex = Text.PositionOf(Text.Middle(PartNumber, DashIndex + 1), "-") in Text.Start(PartNumber, DashIndex + SecondDashIndex +1) & "-"
            else null
        in Result
    )
in
    AddedCustom

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.