Power Query to count the occurrence of a "fs -","ss -", "ff -", "sf -"

[aaewalsh]

I have a column with data like below
A1000 ff, A2000 fs -7, A2010 ss -4

And so on.

What I need is a query that will query the column and return a combined count of the occurrence of "fs -","ss -", "ff -", "sf -".

Any help is much appreciated.

 

[arthurbr]

Are there different strings in the same cell separated by a comma as you indicated, or one string per cell in a column, or an entire range with strings?

 

[aaewalsh]

Yses this is an example of a cell, they are all separated with a comma BD-1020: FF -3, BD-1067: FS -1, BD-1050: FS, BD-1085: SF 5d,

 

[JGordon11]

Here's a custom function you could use

(tbl as table, col as text, find as list) =>
let
    CountOfOneText = (t as text, find as text) => (Text.Length(t) - Text.Length(Text.Replace(t,find,"")))/Text.Length(find),
    CountMultiple = (t as text, find as list) => List.Accumulate(find, 0, (s,c)=> s + CountOfOneText(t,c)),
    Result = List.Accumulate(Table.Column(tbl, col), 0, (s,c)=> s + CountMultiple(c, find))
in
    Result

say you call it CountOccurrences, then if you have a table (step) name say Source with column named Data then you would call the custom function by the following syntax

CountOccurrence(Source, "Data", {"ss -", "ff -", "sf -", "fs -"})