Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
remove certain strings from the column
Input | Input | output |
Hi I am having issue with this product | am | Hi I having issue with this product |
Error product live Monday dumm cool | cool | Error product live Monday dumm |
defective product hello dummy | dude | defective product hello dummy |
issue report | issue report | |
I am having trouble dude | I having trouble | |
lovely lady | lovely lady | |
I am cool dude | I |
Solved! Go to Solution.
You can leverage the List.ReplaceMatchingItems function with Table.AddColumn.
Words to Remove
let
//Read in original data
Source = Excel.CurrentWorkbook(){[Name="InputData"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
//Read in list of words to remove
Source2 = Excel.CurrentWorkbook(){[Name="removeWords"]}[Content],
RemoveWords = Source2[Input],
//Add column with the words to remove replace by null
#"Remove Words" =
Table.AddColumn(#"Changed Type", "Output",
each Text.Combine(
List.ReplaceMatchingItems(
Text.Split([Input]," "),
List.Transform(RemoveWords, each {_, null})),
" "))
in
#"Remove Words"
Hi @dhruvgulati ,
Below is a different approach to @ronrsnfld.:
Outcome:
Code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7RCoMwDAXQX7n02d8YbA/7AvGhM5ktpEZi6+jfTzsE2VPg5lySvnf3iAd8QvBbnCfEdS2MT8wBOcQViymVMbvO+eSGrnc3M7UzhsSN8dSZfAWVlDCqyo7bODjxm8d8qLMSWEQbrjukQtzg77DxopaxL9DS62vZtLyE0SonEN1YKsRT/WsdL1zs8AU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t, Remove = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}, {"Remove", type text}}),
//Remove certain string
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Output",
each
if [Remove]=" "
then [Input]
else Text.Replace(
Text.Replace(
[Input],
[Remove],
"")
,
" ",
" "
)
)
in
#"Added Custom"
Regards
KT
You can leverage the List.ReplaceMatchingItems function with Table.AddColumn.
Words to Remove
let
//Read in original data
Source = Excel.CurrentWorkbook(){[Name="InputData"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Input", type text}}),
//Read in list of words to remove
Source2 = Excel.CurrentWorkbook(){[Name="removeWords"]}[Content],
RemoveWords = Source2[Input],
//Add column with the words to remove replace by null
#"Remove Words" =
Table.AddColumn(#"Changed Type", "Output",
each Text.Combine(
List.ReplaceMatchingItems(
Text.Split([Input]," "),
List.Transform(RemoveWords, each {_, null})),
" "))
in
#"Remove Words"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.