Replace first value on department

[Anonymous]

Hi,

I want to change de fist number on al department that start with a 7 to 4. How do i do this easy?

Dept

4732

7756

64000

7550

Thanks for help

[Vera_33 · Accepted solution]

Hi @Anonymous 

Do you mean to replace the first 7 to 4? You can add a custom column or Transform the original column, and if your column is not Text, you can also wrap Text.From, paste it in Advanced Editor, it is a way in M

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE3NlKK1YlWMjc3NQMzzEwMDAwgQqamQEYsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dept = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dept", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.At( [Dept],0)="7" then "4"&Text.End([Dept],Text.Length([Dept])-1) else [Dept])
in
    #"Added Custom"