Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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
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"
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
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
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"