Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Searching rows of text in a string

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:

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

 

Product Authorisation
Product 1A
Product 2B, C
Product 3A, B, C
Prduct 4A, 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.:

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

 

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

1 ACCEPTED SOLUTION
v-xuding-msft
Community Support
Community Support

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

1.png2.png3.png4.png 1111.png

Create a measure in report view

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

Capture.PNG

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.

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.

View solution in original post

3 REPLIES 3
v-xuding-msft
Community Support
Community Support

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

1.png2.png3.png4.png 1111.png

Create a measure in report view

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

Capture.PNG

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.

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
Continued Contributor
Continued Contributor

@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
Not applicable

Brilliant! works. Thanks @v-xuding-msft 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.