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.
Hello,
I am doing some text clean up work and want to dynamically delete all words with up to 3 characters. I have therefore created a dynamic list of words with more than 3 characters.
However I cannot replace it with that list using the replace button in power query. Does anyone have an idea?
Solved! Go to Solution.
See below a potential solution
Table.TransformColumns( #"name of previous step", { "columnName", each let vTextSplit = Text.Split(_, " "), vListSelect = List.Select(vTextSplit, each Text.Length(_) >3), vResult = Text.Combine(vListSelect, " ") in vResult, type text } )
Let us know if that works for you.
David
Thank you guys.
The think is there are mutliple words in one cell. So I have a text as follows "the way can be good for business" I want it to return "good business" only.
@raymond if you want the result in a different column, then use the following
Table.AddColumn( #"name of previous step", "newColumnName", each let vTextSplit = Text.Split([columnName], " "), vListSelect = List.Select(vTextSplit, each Text.Length(_) >3), vResult = Text.Combine(vListSelect, " ") in vResult, type text )
See below a potential solution
Table.TransformColumns( #"name of previous step", { "columnName", each let vTextSplit = Text.Split(_, " "), vListSelect = List.Select(vTextSplit, each Text.Length(_) >3), vResult = Text.Combine(vListSelect, " ") in vResult, type text } )
Let us know if that works for you.
David
@raymond I think you could adjust the M code with something as follow in the column where you want to get rid of your "3 characters" text:
Table.TransformColumns( #"name of previous step", { "columnName", each if Text.Length(_) <= 3 then "" else _ } )
Let us know if that works for you
David
Hi @raymond
Do you want to keep words with more than 3 chars or remove them? Your problem title and the question text say different things.
You can use a step like this to keep rows where [ColumName] contains words with 3 or fewer characters
= Table.SelectRows(#"PreviousStep", each Text.Length([ColumnName]) <= 3)
But if this isn't what you want, please post some sample data and examples of your expected result.
Regards
Phil
Proud to be a Super User!
Thanks @PhilipTreacy
you are right, could be more specific.
The sample data is as follows:
Original Column A includes:
"the way can be good for business"
Expected Column B should result:
"good business"
Delete all words with less than 3 characters and keep the ones with more.
I was thinking of you a replacer can iterate through a list of values and replaces them but wasnt able to find it. Perhaps there is another solution.
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.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |