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 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

Need Help
  • latimeria's avatar
    latimeria
    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.

3 Replies

  • latimeria's avatar
    latimeria
    Icon for Solution Specialist rankSolution Specialist
    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.

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

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

       

      Philippe