Forum Discussion
Filter table based on another table with multiple column/value
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? 😞
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"
4 Replies
-word/words only
-not case sensitive
thx a lot 🙂
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"
