Forum Discussion

PhilippeMuniesa's avatar
PhilippeMuniesa
Icon for Resolver I rankResolver I
3 years ago
Solved

Replacing Word in a column with condition and list

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...
  • latimeria's avatar
    3 years ago

    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.