Redacting based on table value

[Hickling]

Hi team. I use Power BI to report safety incidents. We have an issue with incident descriptions containing people's names. I was wondering if the following is possible:

Have a table that contains all first names and last names in the business. Using that name table, in my incident description column, tell it if it finds any of those names in the incident text to replace each one with <redacted>. We're talking tens of thousands of incidents and over 2000 employees so doing things manually isn't an option. Can't manipulate the source data either as it is a scheduled dump out of the incident database.

[Hickling]

Hi, thanks I will post some dummy data and expected response.

In table A I have incident numbers and incident descriptions:

Inc Number	Inc Description
501	David fell and broke his wrist
502	When doing something, Thomas Smith hurt his back, Karen assisted with first aid

In table B I have the same incident numbers and people listed as involved in the confidential people section

Inc Number	Name
501	Baker, David
501	Davis, Alan
502	Brown, Karen
502	Smith, Thomas

My expected outcome in table A would be:

Inc Number	Inc Description
501	<Redacted> fell and broke his wrist
502	When doing something, <Redacted> <Redacted> hurt his back, <Redacted> assisted with first aid

Hopefully that makes it clearer. I have tens of thousands of rows of incident descriptions, so if possible I would like it to use the common incident number between tables and just search for the names from table B in the description in table A on a row by row basis in table A to save on refresh times.

[v-xulin-mstf]

Hi @Hickling

Maybe you can implement this feature in power query.

Could you provide sample data and expected output after removing sensitive data?
Sample data and expected output would help tremendously.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Pragati11]

HI @Hickling ,

Yes it is possible to do this with a different table having Names and then doing a SEARCH and REPLACE on the DESCRIPTION column in the other table.

Thanks,

Pragati