cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver III
Resolver III

Text search and count of occurrences in datarows

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

1 ACCEPTED SOLUTION
Super User III
Super User III

Hi,

 

You may refer to my solution this workbook.

 

Let me know.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Super User III
Super User III

Hi,

 

You may refer to my solution this workbook.

 

Let me know.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

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

 

How would this be implemented in Power BI?

 

- David

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

 

😉

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors