Log In
Register
Home
New Member
July 23, 2021
Offline
Hello,
I am not an expert with PQ and struggle to filter a table based on a list already created.
I have a table containing contacts and a list of specific contacts. I want to filter my table Contact with this list BUT with an equivalent of contains or wild characters as it might not be a 100% match.
As an exemple if my list has "Air France" i want to find any expression in the Contact table containing "Air France" which could then be:
Air France
Air France Industries -KLM Engineering
Air France - KLM
Not sure i was very clear.
Any clue to solve this as I am stuck
Thznks for your help
JC
August 21, 2019
Offline
Hi JC,
not sure this is what you want
create a lookup and input all the keywords of your contact description (Air France, Air UK etc), bring this table to Power Query and name the table as Lookup (or whatever name you want), bring in the contact table to Power query too, create a custom column and merge the Lookup table, then create another custom table to match with each keywords, then filter off null and all your contact description is now tag with a category
Complete code as below
let
Source = Excel.CurrentWorkbook(){[Name="Contacts"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contact Description", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Lookup),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Lookup"}, {"Lookup"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Custom", each if Text.Contains([Contact Description],[Lookup]) then [Lookup] else null),
#"Filtered Rows" = Table.SelectRows(#"Added Custom1", each ([Custom] <> null)),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Custom", "Category"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Lookup"})
in
#"Removed Columns"
New Member
July 23, 2021
Offline
Hi Chris,
First of all thanks a lot for your help.
Unfortunately it doesn't work as expected. My contact Table has 2682 rows and my Lookup Table 119 rows and i only get 23 rows matching which isn't correct.
My Lookup items are in uppercase while my Contacts have only an uppercase at the beginning of each words. Not sure this is making the difference
Any idea? I attached my file.
Thanks
August 21, 2019
Offline
Hi JC,
Added Text.Upper() inside the M Code, in the query will convert both contacts and match lookup all in Upper Case, as for output I am not sure whether you want to be upper case or only first character upper only, if it is the latter then apply Text. Proper( )
by the way, you didn't attach your file.
1 Guest(s)
