Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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"
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.