Replace value if text contains

[petar_kisdobran]

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

[mussaenda · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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"