Count recurrence text in a every row of a column

[Flavio2021]

Hi 

I would like to reach the follow result: in a text colum where every row contain a special phrase, count the time is present it

in the table below an example: in the first row is present  2

Reference id	Notes History	Count Solution given
5658	Lorem ipsum dolor sit amet. Non ipsa saepe quo magnam omnis ut minus labore non assumenda galisum aut rerum rerum et deleniti vitae et odit nemo. **bleep** consectetur rep Action Type : Solution Given non quisquam voluptatem ut unde veritatis? Eum quis nisi eum quasi ipsa et culpa incidunt. Et laudantium sunt au Action Type : Solution Given	2
5599	blanditiis neque aut cumque quaerat est esse obcaecati quo ratione sint. Rem quia dolorem et praesentium possimus qui optio fugiat ea error numquam et doloribus harum. Et perferendis optio et autem aspernatur a voluptas officiis et galisum sit laudantium soluta. Ut officiis dicta nam inventore est quam omnis eos maxime ut sunt rerum sit dicta quasi aut dolores fuga. Rem fugit possimus sit itaque Quis non ratione rerum sed galisum ipsum nam voluptatibus sequi! Sed fugiat tempore sed inventore dolorem aut dolorum minima nostrum tempora sit perspiciatis quos? Et rerum expedita in ipsam quisquam Action Type : Solution Given	1
9653	Vel magni iste vel labore unde sit accusamus atque corrupti voluptatem et reprehenderit error et facilis Quis. 33 molestiae dolorem ea reiciendis laboriosam aut esse veritatis nam itaque quod.	0

Have you suggestion how to create a measure or a power query column ?   Else, because this manipulation are not yet my friends, can you suggest some resources ?

Thank you in advance !

Fla

[edhans · Accepted solution]

See the following code. It returns this:

1. Text.Split breaks each line at a carraige return.
2. List.FindText returns all lines with the desired text - "Solution Given" in this case.
3. List.Count counts the lines.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVNLbxNBDP4rJseoyqVKRbkgDhUXhAQFLk0PzqzTWtrxbOYRwb/n80zT9IRQlWp3x2N/Lz88rLY32/erq9WXlCWSLqVFmtKcMhWtxFHqhr4m8xOmwrIIHVuiyE/GkVI0LdQqRbVWaOY92pChngs6iU1MTzyrd2WUZcl4Gv+l0iSzmFalk1YW/5ImTDWJaUPr9X4WWdZrCsmKhCq1ZdxddvYpVMWMH3+A5gPdp7n19896EtuZjz82LccGhCccLpUryGF+s0noJBnjqpaPO7sDEK8l8FCS/sZ46nSBJ7R5YVILOjWDFHcVJNvEVhW1Bd/A69+AVo9XUHl7ewuV9zMbGKoPlGOTLkpo0R8xWDJXkuK/IpT2gSUAaFccR2gpsMVxfJeOm4dX0tVcMkuRgWxJpWiEJSiitOAqHdqTenvwyhn+mo/l4YM30T3KnxnWdJqL5INkOAiso4HUnQEvhnHBqbH7wWeFUXU4aHBq6Hg23UP0VjBXhjf0s16qJw2VydOkBrmqJ8g16OBGwCQVJO63RnETu+ojQ95+3B+2uZxDkeJ8ebMzV8qp14smfgsJcNG/de9h1Vnel74yvVIYO2FvstSVKvBP39E9Kl+UhTKLg/fLFypng16hoRm2RSM4p1LxurP/CNDtzfYaAfolc989JLRUj/J8Xroe7b6zIbTCzpOrUwwpZ8DWt6sgLuCS5Rn++ja8RAKfDxwUvLswG7q+pphmuKF8YYIEZYF3Ixt9vCZM7BR7cl83bNg6pEaIp41z9b/V4+Nf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference id" = _t, Text = _t]),
    #"Added Count" = 
        Table.AddColumn(
            Source, 
            "Solution Given", 
            each 
                List.Count(
                    List.FindText(
                        Text.Split([Text], Character.FromNumber(10)),
                        "Solution Given"
                    )
                )
        )
in
    #"Added Count"

Note that Power Query is case sensitive, so "Solution Given" will not match "SOLUTION GIVEN"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Flavio2021 · Accepted solution]

Hi Edhans 

apologize for the delay of my reply but has been necessary to me learn a little more on how you have reached the solution.  thank you a lot for you support !!

[edhans · Accepted solution]

See the following code. It returns this:

1. Text.Split breaks each line at a carraige return.
2. List.FindText returns all lines with the desired text - "Solution Given" in this case.
3. List.Count counts the lines.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jVNLbxNBDP4rJseoyqVKRbkgDhUXhAQFLk0PzqzTWtrxbOYRwb/n80zT9IRQlWp3x2N/Lz88rLY32/erq9WXlCWSLqVFmtKcMhWtxFHqhr4m8xOmwrIIHVuiyE/GkVI0LdQqRbVWaOY92pChngs6iU1MTzyrd2WUZcl4Gv+l0iSzmFalk1YW/5ImTDWJaUPr9X4WWdZrCsmKhCq1ZdxddvYpVMWMH3+A5gPdp7n19896EtuZjz82LccGhCccLpUryGF+s0noJBnjqpaPO7sDEK8l8FCS/sZ46nSBJ7R5YVILOjWDFHcVJNvEVhW1Bd/A69+AVo9XUHl7ewuV9zMbGKoPlGOTLkpo0R8xWDJXkuK/IpT2gSUAaFccR2gpsMVxfJeOm4dX0tVcMkuRgWxJpWiEJSiitOAqHdqTenvwyhn+mo/l4YM30T3KnxnWdJqL5INkOAiso4HUnQEvhnHBqbH7wWeFUXU4aHBq6Hg23UP0VjBXhjf0s16qJw2VydOkBrmqJ8g16OBGwCQVJO63RnETu+ojQ95+3B+2uZxDkeJ8ebMzV8qp14smfgsJcNG/de9h1Vnel74yvVIYO2FvstSVKvBP39E9Kl+UhTKLg/fLFypng16hoRm2RSM4p1LxurP/CNDtzfYaAfolc989JLRUj/J8Xroe7b6zIbTCzpOrUwwpZ8DWt6sgLuCS5Rn++ja8RAKfDxwUvLswG7q+pphmuKF8YYIEZYF3Ixt9vCZM7BR7cl83bNg6pEaIp41z9b/V4+Nf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Reference id" = _t, Text = _t]),
    #"Added Count" = 
        Table.AddColumn(
            Source, 
            "Solution Given", 
            each 
                List.Count(
                    List.FindText(
                        Text.Split([Text], Character.FromNumber(10)),
                        "Solution Given"
                    )
                )
        )
in
    #"Added Count"

Note that Power Query is case sensitive, so "Solution Given" will not match "SOLUTION GIVEN"

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[Flavio2021]

hi edhans

Excelent job  and clear information...but a little to high level for me so far . I have replicate your example and it work, now how I can change the source where to apply it ?  Power Query is not yet my friend and Advance editor is the first time that I am looking in.  How i can change the source taken in consideration that my data are in an excel file, where other the several columns is present also Count Solution given

thak you in advance for this additional step....in meantime I a mgoing to learn that area 

[edhans]

See the link in my answer in step 5. It is an article and even video on how to move the logic of what I did to connect with your M code.