Forum Discussion
Check for character repetition
I am attempting to create a column that checks for character repetition and returns True or False. The value column is made up of sentences. Below is a record of what a fail record in the value column would look like.
My name is ibrahim ghazi mohammed al barqi im fresh graduated from king khaled university. This course will help me to get a jop gdh hshd jdjd jdjd jdid j i i i j i i i i j i i i j j j i i i i j i i i i j j j j j j j j jj j j j j jj j j j j j j jj j j j j j jj j j j jj j j j j j j j j j j jj j j j j
If you mean to check for repeated words, you can split the sentence based on spaces and then check the number of items in the original list and after removing duplicate values.
let a = Text.Split([value], " ") in List.Count(a) <> List.Count(List.Distinct(a))Hi Anonymous ,
Thanks for your reply.
Well I think ZhangKun's method is good.
Here's an example based on his code.
let words = Text.Split([value], " "), distinctWords = List.Distinct(words) in List.Count(words) <> List.Count(distinctWords)However, I noticed that you want to start with the fourth word of each row of text and check if there are any words repetition. Please use the following code:
let text = [value], words = Text.Split(text, " "), wordsToCheck = List.Skip(words, 3), distinctWords = List.Distinct(wordsToCheck) in List.Count(wordsToCheck) <> List.Count(distinctWords)Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
If you mean to check for repeated words, you can split the sentence based on spaces and then check the number of items in the original list and after removing duplicate values.
let a = Text.Split([value], " ") in List.Count(a) <> List.Count(List.Distinct(a))Hi Anonymous
Here's the direct M code to check for character repetition in Power Query:
1. Go to Power Query Editor: Home >> Transform Data >> Transform Data.
2. Add a Custom Column:
a. Click Add Column >> Custom Column.
b. Use this formula:
if Text.Contains([value], Text.Repeat("j", 3)) or Text.Contains([value], Text.Repeat("i", 3)) then true else false.c. Adjust for Other Repetitions (Optional):
i. Replace "j" or "i" with the characters you want to check.
3. Save and Apply:
a. Click Close & Apply to return to Power BI.
If your desired character you want to count is x, use the below formula
Text.Length([column])- Text.Length(Text.Replace([column],"x"))
Hi Anonymous ,
Did the above replies help you? If so, please accept them.
If not, I need to make sure, do you expect the result below to be what you want?
The third row repeats "a" twice, so it returns false, and the second row doesn't repeat the letter, so it's true.
If the logic of the return result is not like this, please provide an explanation, thank you. Looking forward to hearing from you.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Thanks for your reply.
Well I think ZhangKun's method is good.
Here's an example based on his code.
let words = Text.Split([value], " "), distinctWords = List.Distinct(words) in List.Count(words) <> List.Count(distinctWords)However, I noticed that you want to start with the fourth word of each row of text and check if there are any words repetition. Please use the following code:
let text = [value], words = Text.Split(text, " "), wordsToCheck = List.Skip(words, 3), distinctWords = List.Distinct(wordsToCheck) in List.Count(wordsToCheck) <> List.Count(distinctWords)Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
