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