Searching rows of text in a string

[Anonymous]

Hi All,

Hoping someone may be able to provide some guidance on the following. I have two datasets: 1) listing personnel and their authorisations, 2) Products and the necessary authorisations.

I want to be able to cross reference the two such that i can find who is authorised for what product. I had an excel macro, created by an ex-collague, that could do this but it unfortunately broke, and would like to try do this via powerbi

Below is a simplied version of the Data:

Person	Authorisation
Person 1	A
Person 2	A
Person 2	B
Person 2	C
Person 3	B
Person 3	C
Person 4	A
Person 4	B
Person 4	D

Product	Authorisation
Product 1	A
Product 2	B, C
Product 3	A, B, C
Prduct 4	A, D

From here we can see that Person 1 is authorised for product 1, Person 2 is authorised for Product 1, 2, 3, Person 3 is authorised for Product 2, and Person 4 is authorised for Product 4.

Not sure if this helps but I am able to transform the 1st table such that you have unique rows for the Person column i.e.:

Person	Authorisation
Person 1	A
Person 2	A, B, C
Person 3	B, C
Person 4	A, B, D

What i'm struggling is how to connect the 2 data sets. Any ideas would be gratelly appreciated. 

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I create a sample you can reference to modify yours. 

Split  the column by delimiter in Query Editor -- > Unpivot Columns -- > Remove Columns -- > Replace a space to none – > Close & Apply

 

Create a measure in report view

Measure = CONCATENATEX(FILTER(VALUES(Table2[Product ]),CALCULATE(ISEMPTY(EXCEPT(VALUES(Table2[Authorisation]),VALUES(Table1[Authorisation]))))),Table2[Product ],",")

https://microsoftapc-my.sharepoint.com/:u:/g/personal/v-xuding_microsoft_com/EaUxGoGMquhDhJYMEOpHfFQB4_L952QRlqmuNAi2OM1yQg?e=9EiQbc

Best Regards,

Xue Ding 

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

[Iamnvt]

@v-xuding-msft  Is there any way we can do it in Power Query entirely? This operations looks like Fuzzy Merge, with CONTAINS a text, but I don't know how to operate it yet.

[Anonymous]

Brilliant! works. Thanks @v-xuding-msft