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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
jerryr
New Member

Assign a category based upon a text search in another column

Hi - I am looking to do the following (example):

 

Table 1

IDFlavor
1Vanilla
2Chocolate
3Rocky Road
4Cookies and Cream

 

 

Table 2

Feedback IDFeedback Text
1001Vanilla is the best
1002I like Cholocate ice cream
1003my favorite is Cookies and Cream
1004We enjoy eating vanilla ice cream during the summer
1005I like ice cream



Table 2 would search Table 1 based upon the Feedback Text (in Table 2) to see if it finds the word in Table 1 (Flavor).

 

The end result would be something like this:

 

Table 2 (new column - flavor)

Feedback IDFeedback TextFlavor (NEW COLUMN)
1001Vanilla is the bestVanilla
1002I like Cholocate ice creamChocolate
1003my favorite is Cookies and CreamCookies and Cream
1004We enjoy eating vanilla ice cream during the summerVanilla
1005I like ice creamUnknown

 

I am looking to do something in Power Query as the data loads from the two tables.

After the dataloads, table 1 is no long needed.

 

Any thoughts ?

 

Thanks - Jerry

 

2 ACCEPTED SOLUTIONS
AlienSx
Super User
Super User

@jerryr Cholocate <> Chocolate. Other than that 

    Table.AddColumn(
        Table2,
        "Flavor", 
        (w) => 
            try Table1[Flavor]
                {List.PositionOf(
                    Table1[Flavor], 
                    w[Feedback Text], 
                    Occurrence.First, 
                    (x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
                )}
            otherwise "Unknown"
    )

View solution in original post

Anonymous
Not applicable

Hi,

Thanks for the solution @AlienSx  provided, and i want to offer some more information for user to refer to.

hello @jerryr , you can add a custom column.

let a=List.Transform(#"Table 1"[Flavor],each Text.Lower(_)),
b=Text.Lower([Feedback Text])
in try Text.Proper( List.Select(a,each Text.Contains(b,_)){0}) otherwise "Unknown"

Output

vxinruzhumsft_0-1719798369481.png

And you can refer to the attachment.

 

Best Regards!

Yolo Zhu

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

2 REPLIES 2
Anonymous
Not applicable

Hi,

Thanks for the solution @AlienSx  provided, and i want to offer some more information for user to refer to.

hello @jerryr , you can add a custom column.

let a=List.Transform(#"Table 1"[Flavor],each Text.Lower(_)),
b=Text.Lower([Feedback Text])
in try Text.Proper( List.Select(a,each Text.Contains(b,_)){0}) otherwise "Unknown"

Output

vxinruzhumsft_0-1719798369481.png

And you can refer to the attachment.

 

Best Regards!

Yolo Zhu

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

AlienSx
Super User
Super User

@jerryr Cholocate <> Chocolate. Other than that 

    Table.AddColumn(
        Table2,
        "Flavor", 
        (w) => 
            try Table1[Flavor]
                {List.PositionOf(
                    Table1[Flavor], 
                    w[Feedback Text], 
                    Occurrence.First, 
                    (x, y) => Text.Contains(y, x, Comparer.OrdinalIgnoreCase)
                )}
            otherwise "Unknown"
    )

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors
Top Kudoed Authors