So the problem I have is in the way List.Contains works compared to Text.Contains.
I'm basically trying to transform plural words to singular words and List.Contains seems to require an exact match while Text.Contains is more approximate. However, in PowerBi it's much easier at times to pass in a list of values, especially if you're using conditional logic.
What I'm trying to do, if the final character in a list of words in a cell ends in "s" is to tell PowerBi/M Language to remove that "s" from ever word. This is why Text.Contains will not work, because it will only remove the "s" from the last word in the string.
Suppose I have a column called [Keyword] with the text "mens nikes shoes" in row 1, I'm tying to use a custom column to convert that to "men nike shoe"
So, I need a List function that will iterate over every word in a string and remove the "s" IF it is at the end of the text. I just needs some ideas of what might work. I think List.Transform might work but I don't know enough about how it to use it right now.
All ideas and suggestions will be greatly appreciated.
Before getting too far into this, are you sure that all words with an s at the end are plural? What about tennis, as in 'womens tennis shoes'?
And that's ignoring that it should really be women's so removing the s from that leaves you with an apostrophe at the end of the word.
Phil
Please, always supply some dummy data so we don't have to create our own.
But, this will work
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","s "," ",Replacer.ReplaceText,{"Column1"}),
#"Inserted Trimmed Text" = Table.AddColumn(#"Replaced Value", "Trim", each Text.TrimEnd([Column1],"s"), type text),
#"Removed Columns" = Table.RemoveColumns(#"Inserted Trimmed Text",{"Column1"})
in
#"Removed Columns"
However it will remove the s from the end of every word so a word like tennis will be affected.
See attached.
Regards
Phil
Thanks for time and help, Philip. Your points are well taken.
Looks like I'll have to go back to the drawing. The only solution is probably what I was looking to avoid -- a lot nested IF statements.
Thanks again!
