Forum Discussion
Solved
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 CA...
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"
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?
-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"
