Filter table based on another table with multiple column/value

[Mitch81]

Hello,

i'm a newbie about PowerBI & DAX, but i hope i will learn quite a bit!

Now i would like to filter a table1 like this:

L1	L2	L3	DESCRIPTION
BLUE	BLUE	RED	THE CAR IS RED
GREEN	BLUE	BLUE	THE PEN IS ON THE TABLE
YELLOW	RED	RED	THE CAR IS RED
RED	GREEN	BLUE	THE PEN IS ON THE TABLE

based on keywords (one or more delimited by comma) value from a second table2 like this:

L1	L2	L3	KEYWORD
YELLOW	RED	RED	CAR
RED	GREEN	BLUE	PEN, TABLE

if the value (one or more delimited by comma, based on cell value) from KEYWORD column are find inside DESCRIPTION column then filter is applied extracting the records where the values ​​of the first 3 columns do not match.... like this:

L1	L2	L3	DESCRIPTION
BLUE	BLUE	RED	THE CAR IS RED
GREEN	BLUE	BLUE	THE PEN IS ON THE TABLE

I think is like a for cycle..... for every records from table1 i need to check all records/value in table2

too confused? 😞

[ImkeF]

Hi @Mitch81  

are you looking for matches on full words only or should a "THE PEN IS ON THE TABLETOP" also match with "TABLE".

Are you looking for a case sensitive version?

[Mitch81]

-word/words only

-not case sensitive

thx a lot 🙂

[ImkeF · Accepted solution]

Hi @Mitch81 

please paste this code into the advanced editor in a new query and follow the steps:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcvIJdVXSgVFBri5AMsTDVcHZMUjBM1gBJBCrE63kHuTq6odQB6VACgNc/UAK/f0UQLwQRycfV7COSFcfH/9wuJk4TYZIoJmPz2BcLg72dPdDNhiXOqi5YPHYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [L1 = _t, L2 = _t, L3 = _t, DESCRIPTION = _t]),
    Table1 = Table.TransformColumnTypes(Source,{{"L1", type text}, {"L2", type text}, {"L3", type text}, {"DESCRIPTION", type text}}),
    Custom1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WinT18fEPV9JRCnJ1gZPOjkFKsTrRUJ57kKurH5B28gl1BVIBrn46CiGOTj6uSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [L1 = _t, L2 = _t, L3 = _t, KEYWORD = _t]),
    Table2 = Table.TransformColumnTypes(Custom1,{{"L1", type text}, {"L2", type text}, {"L3", type text}, {"KEYWORD", type text}}),
    FilterOnlyNonMatching = Table.NestedJoin(Table1, {"L1", "L2", "L3"}, Table2, {"L1", "L2", "L3"}, "Table2", JoinKind.LeftAnti),
    OnlyNonMatching = Table.RemoveColumns(FilterOnlyNonMatching,{"Table2"}),
    SplitDescription = Table.AddColumn(OnlyNonMatching, "ListOfWordsInDescription", each Text.Split([DESCRIPTION], " ")),
    TableWithAllKeywords = Table.ExpandListColumn(SplitDescription, "ListOfWordsInDescription"),
    ListOfSearchWords = List.Union(List.Transform(Table2[KEYWORD], each List.Transform((Text.Split(_, ",")), Text.Trim))),
    TableOfSearchWords = Table.FromList(ListOfSearchWords, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    InnerJoinAsFilter = Table.NestedJoin(Table.TransformColumns(TableWithAllKeywords,{{"ListOfWordsInDescription", Text.Lower, type text}}), {"ListOfWordsInDescription"}, Table.TransformColumns(TableOfSearchWords,{{"Column1", Text.Lower, type text}}), {"Column1"}, "TableOfSearchWords", JoinKind.Inner),
    #"Removed Other Columns" = Table.SelectColumns(InnerJoinAsFilter,{"L1", "L2", "L3", "DESCRIPTION"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
    #"Removed Duplicates"

[Mitch81]

thx a lot @ImkeF  , now i can study your code (is M code right?) and adjust to my production data

🙂