Replacing Word in a column with condition and list

[PhilippeMuniesa]

Hi there

French user, excuse my English

I have a list that corresponds to the root of accounts that I want to process

List
401
404

I have a table that contains all the accounts of a series of entries

CompteNum is a Text Column

CompteNum CompteLib
401100000 FOURNISSEURS
618400000 COTISATIONS DIVERSES
401100000 FOURNISSEURS
401100000 FOURNISSEURS
404100000 FOURNISSEURS D'IMMOBILISATION
401100000 FOURNISSEURS
401100000 FOURNISSEURS
486000000 CHARGES CONSTATEES D'AVANCE
486000000 CHARGES CONSTATEES D'AVANCE
613220000 LOCATION LOCAUX D'EXPLOITATION

I would like to replace in the table, the values off CompteNum which start with one of the values ​​of the list (401 or 404), by "PIVOT", As the elements of the list are brought to vary, I cannot use

The result must be

CompteNum CompteLib
PIVOT FOURNISSEURS
618400000 COTISATIONS DIVERSES
PIVOT FOURNISSEURS
PIVOT FOURNISSEURS
PIVOT FOURNISSEURS D'IMMOBILISATION
PIVOT FOURNISSEURS
PIVOT FOURNISSEURS
486000000 CHARGES CONSTATEES D'AVANCE
486000000 CHARGES CONSTATEES D'AVANCE
613220000 LOCATION LOCAUX D'EXPLOITATION

I've tried several solutions, but can't seem to get by.
I ask for help

thank you

Philippe

[latimeria · Accepted solution]

@PhilippeMuniesa ,

Try this, 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pU9BDoIwEPxK0zOHthLkWsuqm5TWdAshgR59gfh/RYF4MJroniaZmZ2Zvue5kFJMxzO+901wSARNIJ6ynheyzBfS+IikI3pHrMIWAsFT9PHDFzJ/S7JquAqhtljXfod2jv03rCzEuuWowwGImfuYqCPAGqlb7Qz8ZijkRqnZYL15lGYTaLpFDd3Jeoyve9RaeTxfRp7SDQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CompteNum = _t, CompteLib = _t]),
    AccountToModified = {"401", "402"},
    #"Replaced Value" = Table.ReplaceValue(
        Source,
        each [CompteNum],
        //each if Text.Start([CompteNum], 3) = "401" or Text.Start([CompteNum], 3) = "402" then "1" else [CompteNum],
        each if List.Contains(AccountToModified , Text.Start([CompteNum], 3) ) then "PIVOT" else [CompteNum],
        Replacer.ReplaceValue,{"CompteNum"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"CompteNum", type text}})
in
    #"Changed Type"

Copier et coller dans un powerquery vierge.

[PhilippeMuniesa]

Thank you very much Latimeria , that's exactly what I was looking for.

Philippe

[Madhukar7]

Hi,

Please refer below steps in power query.