Text split, comparison logic

[Mechi]

 Is it possible to create a logic in "Edit Queries" --> "Custom Column" using M codes  

1. It should split the Texts by comma. Furnitures, Sweets, Toys, Drinks, Clothes, Vegetables, People, Animals, Places --> Furnitures Sweets

.

.

Places

2.  Then it should count the no. of strings. e.g. 9 strings

3. Then it should compare each string (Matching the exact characters) from Table 1[ITEM]  wit Table 2[LIST 1]

4. Finally if all the string matches it should be OK, else Not OK

[dax]

Hi Mechi,

If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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

[Mechi]

Hello Zoe Zhi,

I had attached the sample snaps and expected result in Pic for your ref

Input Table 1[ITEMS] Table 2[LIST]

Expected Result

Could you help to change the M code?

BR

Mechi 🔧

[Anonymous]

Hi @Mechi ,

The issue was that Text.Split does not trim the spaces at the beginning/end of the strings that come out of the split function.

I added a custom comparer to the code, it should work now:

let
    Source = Table_Items,
    ToList = Table.AddColumn(Source, "ToList", each Text.Split([ITEMS], ",")),
    Output = Table.AddColumn(ToList, "Match", each if List.ContainsAll(Table_List[LIST], [ToList], (x, y)=> Text.Trim(x)=Text.Trim(y)) then "X" else "")
in
    Output

Just in case this is the code for Table_Items:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5P1U0tKdYJS01PLUlMykkt1nErKs0EivimJpYoxepEK7nl5JenFhXrKITkVwJJl6LMvOxiiExpUV5mSWkRUJOCc05+SUYqRBykVUcBrg8qBTEAQyPQBSAHoJiOpAfhMPJ16igEpOYX5KTqKDjmZeYm5gCNigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ITEMS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ITEMS", type text}})
in
    #"Changed Type"

and Table_List:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Nc2xDoMwDATQf8lcvgKJrRMVS8RgyolGdWPkOkL8PQiH7d1JPscY+g0N7B/GRwydlnSzaE5WFB5bFvtUD1hgNHGNT5D5CcsG9fIlu2PWlL/OFbIyLlJOP+JaMzXvutXJ+fBcGw8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [LIST = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"LIST", type text}})
in
    #"Changed Type"

The comparer is case-sensitive as you needed, to make it case-insensitive you can wrap Text.Trim into Text.Lower:

(x, y)=> Text.Lower(Text.Trim(x))=Text.Lower(Text.Trim(y))

Kind regards,

JB

[Anonymous]

Hi  
 
as discussed, the final version for your case:let
Source = Excel.Workbook(File.Contents("C:\Users\...\Desktop\Test.xlsx"), null, true),
#"Table 1_Sheet" = Source{[Item="Table 1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Table 1_Sheet",{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEMS", type text}}),
ToList = Table.AddColumn(#"Changed Type1", "ToList", each Text.Split([ITEMS], ",")),
Output = Table.AddColumn(ToList, "Match", each if List.ContainsAll(#"Table 2"[LIST], [ToList], (x, y)=> Text.Lower(Text.Trim(x))=Text.Lower(Text.Trim(y))) then "X" else "")
in
Output
Please note that this is a case-insensitive version. To make it case-sensitive, please remove the call to Text.Lower in the Output step.
 
Kind regards,
JB

[Mechi · Accepted solution]

Hello JB,

 Thanks for your support 😊 👍

 I had slightly changed the code for the last two lines in order to avoid one more column in my result "ToList"

 let
Source = Excel.Workbook(File.Contents("C:\Users\XXXX\Desktop\Test.xlsx"), null, true),
#"Table 1_Sheet" = Source{[Item="Table 1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(#"Table 1_Sheet",{{"Column1", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"ITEMS", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Result", each let
Match=if List.ContainsAll(#"Table 2"[LIST], Text.Split([ITEMS], ","), (x, y)=> Text.Lower(Text.Trim(x))=Text.Lower(Text.Trim(y))) then "X" else ""
in
Match)
in
#"Added Custom"

BR

Mechi 🔧

[Anonymous]

Hi @Mechi ,

Somthing similar to this?

let 

    Table2_List_Long = Text.Split("Furnitures, Sweets, Toys, Drinks, Clothes, Vegetables, People, Animals, Places, Cities", ","),
    Table2_List_Short = Text.Split("Furnitures, Toys, Drinks, Clothes, Vegetables, People, Animals, Places, Cities", ","),
    Texts = "Furnitures, Sweets, Toys, Drinks, Clothes, Vegetables, People, Animals, Places",
    List = Text.Split(Texts, ","),
    Count = List.Count(List), // Counts number of items in the Texts
    Result_Long = List.ContainsAll(Table2_List_Long, List), // check that every item in Texts is present in the "long" filter
    Result_Short = List.ContainsAll(Table2_List_Short, List), // check that every item in Texts is present in the "short" filter
    Output = {Count, Result_Long, Result_Short}

in Output

This is an example for a single list only. If you need it to be applied to all table rows - convert it to a function and use Table.AddColumn().

Kind regards,

JB