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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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