replace values with If condition

[dogburalHK82]

Hi, 

I am trying to replace values based on its own condition. 

I want [Part number] column to be [common part]. 

If the part number starts with 999, then it becomes 9xxx

If the part number starts with AT99, then it AT.. and so on. 

How can you please advise how I can acheive it?

Part Number	Common Part
999-1-00	9xxx
990-1-11	9xxx
666-1-11	6xxx
AT990-1-11	AT
ICD-0000-0000	ICD-0000-
ICD-1030-0000	ICD-1030-

[v-junyant-msft · Accepted solution]

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.

[Chakravarthy]

Hi @dogburalHK82 , Please try to use Text.StartWith function in power query. Below is the Snip of code

[lbendlin]

where is your rules table? How do we know to group 999 after the first digit but AT990 after the second?