Replace Last Character from Every Word in a List

[TheOriginalDeeb]

I started working on a solution for a problem a while back but then I put it on the back-burner until now. For background purposes, see: http://community.powerbi.com/t5/Desktop/Find-amp-Count-Matching-Words-in-Lists/m-p/378251

The basic problem is that I'm trying to compare 2 columns of a table to see how many words match. I got a lot of help with the M code (thanks everybody) and it works..... almost. The problem is that theres no way to match plural words (specifically words ending in 's') to singular.

Ex. Column 1 = "Original Phrase" / Column 2 = "Words to Match"

If the "Original Phrase" = 'workout shirt for men' & "Words to Match" = 'men workout shirt' then there's a 100% match (All Good!). But it screws up if with one column has all or partial plurals --- 'workout shirt for men' will not match any for 'mens workouts shirts'

So I'm thinking that a workaround is to create some conditional logic to to kinda stem all plural words in each column.

I need some code that evaluates each word in both lists and for each word ending in 's', remove that 's'

I've been fiddling with .Text.EndsWith but can't get the logic working to remove or replace the 's'

Any help would be gladly appreciated. Thanks!!!

[v-juanli-msft]

Hi @TheOriginalDeeb

Test with this simple data in Power Query

M code in advanced editor

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKs8vys4vLVEozsgsKlFIyy9SyE3NU9JRApLFClDJYohssVKsTrRSemqJQmV+aZFCen5iDlAhkF+MEACqiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [list1 = _t, list2 = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"list1", type text}, {"list2", type text}}),

    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "list2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"list2.1", "list2.2", "list2.3"}),

    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"list2.1", type text}, {"list2.2", type text}, {"list2.3", type text}}),

    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Text.Trim([list2.1],"s")),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Trim([list2.2],"s")),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each Text.Trim([list2.3],"s")),

    #"Merged Columns" = Table.CombineColumns(#"Added Custom2",{"Custom", "Custom.1", "Custom.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"),

    #"Removed Columns" = Table.RemoveColumns(#"Merged Columns",{"list2.3", "list2.2", "list2.1"})

in

#"Removed Columns"

final output

Best Regards

Maggie

[TheOriginalDeeb]

Thanks so much Maggie for your help.

Unfortunately your solution does not work 100%.

It removes the beginning 'S' on some words, which is not desired.

But thanks again!

[v-juanli-msft · Accepted solution]

Hi @TheOriginalDeeb

Use Text.TrimEnd instead of Text.Trim in code above.

Best Regards

Maggie

[TheOriginalDeeb]

Maggie,

How do I change the Source (1st step)? I have no idea what you are doing with the source.

I want to be able to connect to a table inside Power BI (without JSON).

Thanks!

[TheOriginalDeeb]

Edited the title to make it clearer about the problem that I'm hoping to solve.

[TheOriginalDeeb]

By the way, I'm open to a DAX solution. But I'd prefer M Code.

Thanks!