Text search and count of occurrences in datarows

[dgwilson]

Hi community... I seeking some guidance again.

Below is my sample data (excel), and a power BI file with that adata loaded.

https://sites.google.com/site/dgwilson65/BI%20Password%20Reset%20Example.zip?attredirects=0&d=1

I've got 10,000's of rows of data with free format text fields containing strings like "AD Account Unlock" or 'Password Reset".

I wish to report on the numbers of "account unlocks", and the number of "password resets" etc... although the search criteria may vary. I was thinking I'd have another table or list of paramaters on what a user could search on (like a slicer maybe)... but first I need a good way to search that text.

So I found the following on stackoverflow which is using M:

https://stackoverflow.com/questions/35316657/how-to-search-multiple-strings-in-a-string/35324537

I've intergrated this into the sample I've provided above... and assuming I'm heading in the right direction... I don't know how next to truely integrate this query into what I'm trying to do... (perhaps my direction is wrong?).

Any pointers would be greatfully welcomed... and hopefully others in the community will benefit.

- David

p.s. when I say unstructured... my example data is clean. Much of the real source data is messy (as you'd expect).

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution this workbook.

Let me know.

[dgwilson]

There's some excel magic happening there that I can't find. 😞

How would this be implemented in Power BI?

- David

[Ashish_Mathur]

Hi,

There is a calculated column in the PowerPivot there.  You can just import the workbook into PBI desktop.

[dgwilson]

Thank you again...

I have updated the Power BI example - it's in the attached zip file.

I created a manually input keyword table.

I added a column to hte main table with the search criteria as per the excel sample you provided.

The Power BI example is also updated with a sample chart.

https://sites.google.com/site/dgwilson65/BI%20Password%20Reset%20Example%20updated.zip?attredirects=...

Good example exercise and I will use this in my main model.

I'd still be curious to know how to integerate this type of example/query with M - if only to see how such a thing could be done. I live for working the examples.

😉

[Ashish_Mathur]

Hi,

You are welcome.  This can also be done in Power Query.  When i have some time, i will share that technique as well.