Check for character repetition

[BeLocke]

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

[ZhangKun · Accepted solution]

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))

[Anonymous]

Hi @BeLocke ,

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.

[BeLocke]

No, these replies are not quite what I am looking for. I need to clarify my question. I am looking for word repetition, preferably after three words. I'm looking at application data and want to check the applicants' adding filler to make the paragraph longer. 

[Anonymous · Accepted solution]

Hi @BeLocke ,

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.

[Omid_Motamedise]

If your desired character you want to count is x, use the below formula

Text.Length([column])- Text.Length(Text.Replace([column],"x"))

[ZhangKun · Accepted solution]

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))

[rohit1991]

Hi @BeLocke 

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.