Correct Use of Text.Contains in Combination with List.Contains??

[thomasreick]

Hi there, I would like to aggregate Item from Contoso Products to "ProductGroups" using

CategorizeItem = if Text.Contains( [Item], List.Contains({"Gloves", "Vest", "Shorts"}, [Item] ) ) then "Clothes" else "Other".

AdvancedEditor shows no error, but I receive an Error Message

Expression.Error: Unbekannter Bezeichner. Haben Sie die Kurznotation "[field]" für "_[field]" außerhalb eines each-Ausdrucks verwendet?

Can you please give a hint on what am I doing wrong?

thank you very much

Thomas

[techies · Accepted solution]

Hi @thomasreick pls try this custom column

= if List.AnyTrue(List.Transform({"Gloves", "Vest", "Shorts"},
(x) => Text.Contains([item], x, Comparer.OrdinalIgnoreCase)))
then "Clothes"
else "Other"

[lbendlin · Accepted solution]

That's a well-known "feature" aka bug.  The column statistics are cached and remember prior errors. Switch them off and on again to reset.

[v-achippa]

Hi @thomasreick,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin, @ronrsnfld, @Omid_Motamedise and @techies for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user resolved your issue? or let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[thomasreick]

thx for reminding

Thomas

[Omid_Motamedise]

You can use the following formula instead 

CategorizeItem = if List.AnyTrue( List.Transform({"Gloves", "Vest", "Shorts"}, each Text.Contains([Item], _)) ) 
then "Clothes" 
else "Other"

[thomasreick]

Hi Omid,

thx for your input, but List.Transform works with Integers/Numbers only, not with Text.

Any further Idea?

[Omid_Motamedise]

Hi @thomasreick 

you can use List.Transform for any type of data, look at the following video of mine for more clarification

https://youtu.be/W-hTgciYtew?si=uJFIvQUhAMesb4lN

[techies · Accepted solution]

Hi @thomasreick pls try this custom column

= if List.AnyTrue(List.Transform({"Gloves", "Vest", "Shorts"},
(x) => Text.Contains([item], x, Comparer.OrdinalIgnoreCase)))
then "Clothes"
else "Other"

[thomasreick]

Hi techies,

thx for your Input. It seems to work, but Column-Quality Statistics shows 100% Error. After Renaming the Default Column Name the error disappeared And I was able to nest your code to cover the other Product terms.

What makes me wonder... is there a smarter way of coding instead of mine attached here? I am not sure on defining the same function over and over instead of define once call often

Any hint on that?

<pre>

AddedProductCategories_techies =
Table.AddColumn(AddingItemCategory, "techies", each if List.AnyTrue(List.Transform({"Cap", "Sleeve", "Gloves", "Vest", "Shorts", "Socks"},
(x) => Text.Contains([Item], x, Comparer.OrdinalIgnoreCase)))
then "Clothes"
else if List.AnyTrue(List.Transform({"Mountain-", "Road-", "Race-", "Touring-"},
(x) => Text.Contains([Item], x, Comparer.OrdinalIgnoreCase)))
then "Bikes"
else if List.AnyTrue(List.Transform({"Cage", "Dissolver", "Stand", "Rack", "Helmet", "Bottle", "Fender"},
(x) => Text.Contains([Item], x, Comparer.OrdinalIgnoreCase)))
then "Accessories"
else if List.AnyTrue(List.Transform({"Tire", "Patches"},
(x) => Text.Contains([Item], x, Comparer.OrdinalIgnoreCase)))
then "Spare Parts"
else "Nutrition", type text)
in
AddedProductCategories_techies

</pre>

[techies · Accepted solution]

Hi @thomasreick please check this, in the power query, on the advanced editor

let

CategoryMappings = [
Clothes = {"Cap", "Sleeve", "Gloves", "Vest", "Shorts", "Socks"},
Bikes = {"Mountain-", "Road-", "Race-", "Touring-"},
Accessories = {"Cage", "Dissolver", "Stand", "Rack", "Helmet", "Bottle", "Fender"},
SpareParts = {"Tire", "Patches"},
Nutrition = {} 
],

GetCategory = (item as text) as text =>
let
Category = List.First(
List.Select(
Record.FieldNames(CategoryMappings),
each List.AnyTrue(
List.Transform(Record.Field(CategoryMappings, _),
(x) => Text.Contains(item, x, Comparer.OrdinalIgnoreCase))
)
)
)
in
if Category = null then "Nutrition" else Category,

AddedProductCategories_techies = Table.AddColumn(#"Changed Type1", "techies", each GetCategory([Item]), type text)

in
AddedProductCategories_techies

[thomasreick]

Good Morning techies, thank you very much, learnt a lot from this. Iam not used to define 'Inline'-Definition of functionalities, need to check on this on my own.

[ronrsnfld]

The error message is because of your syntax. [Item] as written refers to the contents of a field or record. But in your code it is out of context.

In the Code below, I use Item to refer to a named variable that contains the text string being tested.

I think you want something like:

 CategorizeItem = if List.AnyTrue(List.Accumulate(
                        {"Gloves","Vest","Shorts"},
                        {},
                        (s,c)=> s & {Text.Contains(Item,c,Comparer.OrdinalIgnoreCase)})) 
                    then "Clothes" 
                    else "Other"

[thomasreick]

Hi ronrsnfld,

thx for your Input. but the result ahs 100% Error although no rows are shown with 'Error' inside.

After Renaming the column Quality Column-Statistic went to green without Error. Will check the other approaches accordingly...

[lbendlin · Accepted solution]

That's a well-known "feature" aka bug.  The column statistics are cached and remember prior errors. Switch them off and on again to reset.

[lbendlin]

did you mean to say

CategorizeItem = if List.Contains({"Gloves", "Vest", "Shorts"}, [Item] )  then "Clothes" else "Other"

?

[thomasreick]

Good evening Ibendin,

thx for your answer. Instead of using multiple times Text.Contains( [Item], "whatever" ) connected with "or" I would like to search parts of the string in field [Item]. Means "Gloves" is only part of string in Field Item.

Any Idea?

Forgot to mention: The List.Contains ( { CommaSeparatedItemsInList}, [Item] ) results in Other for all records, due to missing Text.Contains()-Variation which seems to looks for parts of the string.

So how to combine both Commands?

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.