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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
TM_Visual
Advocate III
Advocate III

Search text in column for values from a second table

 

I have a table with columns with text comments. My ideal goal would be to be able to bring up any comments that contained words flagged in a different table.

 

Image of an example table is below. I would also accept a scenario where the outcome table only matched for one of 'Content' or 'Staff', if the answer is to merge the tables with conditions.

 

 ExampleKeywordQuestion_01.PNG

 

 

 

 

 

 

I've seen this post from @ImkeF

https://community.powerbi.com/t5/Desktop/joinAlgorithm-and-keyEqualityComparers/m-p/182148#M79842

 Which was referenced here:

https://community.powerbi.com/t5/Desktop/Text-contains-search-value-from-a-second-table/m-p/304410

 

However, I am not sure:

1) If that is the best solution available for the scenario at this time (1 year later)

2) Which table and column names I should be using in the code: 

 

let
    Table1 = Table.Buffer(#table({"Key1"},{{"Auto"},{"Bus"},{"Autobus"}, {"Car"}})),
    Table2 = Table.Buffer(#table({"Key2"},{{"Auto"},{"Bus"}})),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Key1],[Key2], Comparer.OrdinalIgnoreCase))),
    #"Expanded RelativeJoin" = Table.ExpandTableColumn(RelativeMerge, "RelativeJoin", {"Key2"}, {"Key2"})
in
    #"Expanded RelativeJoin"

 

The example data is below, if anyone wants to try it on their own.

 

 

Keyword  TypeOfComment

modulecontent
seminarcontent
classcontent
lecturecontent
lessoncontent
academicstaff
administratorsstaff
staffstaff
lecturersstaff

 

 

Gender  Department   Comment

MClassicsLectures and lessons are really good.
MBiology 
MClassicsI love the staff.
FClassicsI had a fun time.
MPhysicsAcademics are great
FBiologyEnjoyable lectures
MClassicsThe staff give great seminars

 

 

 

1 ACCEPTED SOLUTION
ImkeF
Super User
Super User

Hi @TM_Visual, cannot tell if this is still state of the art, but this is the code to make it work for your use case:

 

let
    Table1 = Table.Buffer(Data Table),
    Table2 = Table.Buffer(Reference Table),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Comment],[Keyword], Comparer.OrdinalIgnoreCase))),
    AddContent = Table.AddColumn(RelativeMerge, "Content", each if List.Contains([RelativeJoin][TypeOfComment], "content") then true else false),
    AddStaff = Table.AddColumn(AddContent, "Staff", each if List.Contains([RelativeJoin][TypeOfComment], "staff") then true else false),
    Cleanup = Table.RemoveColumns(AddStaff,{"RelativeJoin"})
in
    Cleanup

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

3 REPLIES 3
ImkeF
Super User
Super User

Hi @TM_Visual, cannot tell if this is still state of the art, but this is the code to make it work for your use case:

 

let
    Table1 = Table.Buffer(Data Table),
    Table2 = Table.Buffer(Reference Table),
    RelativeMerge = Table.AddColumn(Table1, "RelativeJoin", 
            (Earlier) => Table.SelectRows(Table2, 
                         each Text.Contains(Earlier[Comment],[Keyword], Comparer.OrdinalIgnoreCase))),
    AddContent = Table.AddColumn(RelativeMerge, "Content", each if List.Contains([RelativeJoin][TypeOfComment], "content") then true else false),
    AddStaff = Table.AddColumn(AddContent, "Staff", each if List.Contains([RelativeJoin][TypeOfComment], "staff") then true else false),
    Cleanup = Table.RemoveColumns(AddStaff,{"RelativeJoin"})
in
    Cleanup

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Thanks @ImkeF , that's perfect. I've successfully adapted it to my real table.

May I ask a follow-up question?

If I wanted to run the same test and also check a second column on the same table with the same keywords list, would I:

 

1) Have to create a new query, merging on that column

(Both tables will look similar to the one you helped me create)

 

 

 

2) Insert more columns, creating new an extra set of new columns for each column checked

 

 

ExampleKeywordQuestion_02.PNG

 

 

 

 

 

 

I have one dataset where I have 8 different comments that I would like to analyse using the same keywords list.

I understand that I can create a new table for each of them, and then merge them together. However I hope that there is a more elegant solution.

 

 

Having difficulties to understand what you mean here, but all columns are in the same table already, I don't see the need to create different tables: Just perform one merge after the other on that table. (Assuming that the desired outcome is what you've shown in the picture): If you want to have just one column-pair with results for all columns, there would be a better way.

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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