Power Query: Count string of words in a cell.

[bdpate09]

I'm looking to write a power query function for counting the number of times the string "auto auction" is found within a specific cell. Can someone help me out with this? I can write it in excel, but I'd like to be able to write it in PQ for the obvious reasons.

Cheers and thanks,

Brian

[CK76]

I'd recommend uploading sample workbook.

Without it, it is a bit of guess works as to what your data may look like.

General concept:
Assuming that you want to match "auto auction" exactly (i.e. case sensitive).
1. Replace "auto auction" with blank string.

Please Login or Register  to view this content.

2. Subtract length of result from original string length.

Please Login or Register  to view this content.

3. Divide Step 2 result with length of "auto auction".

Combine it all in one and...
Add custom column.

Please Login or Register  to view this content.

0.JPG

[CK76]

Oh, I forgot about another, probably easier method.

Add custom column: Split string by "auto auction". Count list and subtract 1.

Please Login or Register  to view this content.