Forum Discussion

petar_kisdobran's avatar
petar_kisdobran
Frequent Visitor
3 years ago
Solved

Replace value if text contains

Hi,

 

I have a table containing column with date and g/l account code and need to have these in separate columns.

 

Columns are duplicated, however I do not know how to replace all values eg with text ending "-2022" with blank, can you please help me?

 

 

Thank you!

 

My best,

Petar

Need Help
  • mussaenda's avatar
    mussaenda
    3 years ago

    Hi petar_kisdobran ,

     

    Try this.

    Create a blank query,

    Open Advanced Editor,

    Select All,

    Paste this code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBR0FXwTUzOyMxLLVZIzEtRyMwrLknMyUksyczPK1aK1YlWAhMGlroGZrpGBkZGCDGIhImRAdCMkKLEvOKC/KISsCG5qUCeQn6aQglMGE0XVKsxSKtLakFRanIm2EYUPQSNMjbUNTBEOAqZGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account name" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each [Account name], each if Text.Contains([Account name], "-2022") then "" else [Account name], Replacer.ReplaceText,{"Account name"})
in
    #"Replaced Value"

     

    Basically the answer is coming from a previous post.

    Link is Replace based on condition 

     

    Hope this helps

     

     

  • mussaenda's avatar
    mussaenda
    3 years ago

    Hi petar_kisdobran,

     

    In that case, it will be like:

    Source = Excel.Workbook(Web.Contents("https://XYZ.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Replaced Value" = Table.ReplaceValue(Data_Sheet,each [Account name], each if Text.Contains([Account name], "-2022") then "" else [Account name], Replacer.ReplaceText,{"Account name"})
in
    #"Replaced Value"

3 Replies

  • mussaenda's avatar
    mussaenda
    Community Champion
    3 years ago

    Hi petar_kisdobran ,

     

    Try this.

    Create a blank query,

    Open Advanced Editor,

    Select All,

    Paste this code

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjBR0FXwTUzOyMxLLVZIzEtRyMwrLknMyUksyczPK1aK1YlWAhMGlroGZrpGBkZGCDGIhImRAdCMkKLEvOKC/KISsCG5qUCeQn6aQglMGE0XVKsxSKtLakFRanIm2EYUPQSNMjbUNTBEOAqZGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Account name" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,each [Account name], each if Text.Contains([Account name], "-2022") then "" else [Account name], Replacer.ReplaceText,{"Account name"})
in
    #"Replaced Value"

     

    Basically the answer is coming from a previous post.

    Link is Replace based on condition 

     

    Hope this helps

     

     

    • petar_kisdobran's avatar
      petar_kisdobran
      Frequent Visitor
      3 years ago

      Hi Mussaenda,

       

      Thanks for quick response.

       

      Unfortunately i am not knowledgable enought to apply it to my source of data.

       

      Also, not sure how to share the Excel file here, if possible please let me know or if you could rewrite the script with this source of data

       

      Source = Excel.Workbook(Web.Contents("https://XYZ.xlsx"), null, true),
      Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],

      Thanks!

       

      My best,

      Petar

      • mussaenda's avatar
        mussaenda
        Community Champion
        3 years ago

        Hi petar_kisdobran,

         

        In that case, it will be like:

        Source = Excel.Workbook(Web.Contents("https://XYZ.xlsx"), null, true),
Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
#"Replaced Value" = Table.ReplaceValue(Data_Sheet,each [Account name], each if Text.Contains([Account name], "-2022") then "" else [Account name], Replacer.ReplaceText,{"Account name"})
in
    #"Replaced Value"