Forum Discussion
Search Multiple Columns all at once to Find, Locate and return Matching Data Details (Power Query)?
Thanks, with sample file it's more clear. You may simply add one more column to Names table, entire script is
let
Source = Excel.CurrentWorkbook(){[Name="Names"]}[Content],
#"Add Default Email" = Table.AddColumn(
Source,
"Default Email",
each
try List.Intersect(
{
{[Email_1],[Email_2],[Email_3]},
DefaultEmail[Default Email]
}
){0}
otherwise null
)
in
#"Add Default Email"
- Hi Sergei, thanks for your help on this. Would you please guide me on how this script can be added into a workbook that already contains other historical command scripts? (i noticed on my actual file there are other edited script content I had done to clean up the original table). Your kind help would be greatly appreciated. Thanks!
To copy/paste queries
- open file attached to my post ("Source") and your file ("Target")
- with Data->Queries and Connections open right pane in both file
- in pane of Target file right click on any empty space and create New Group, let say Dev
- right click on Names query in Source and click Copy
In target click on Dev group, right click on it and Paste
- queries shall appear here, possibly you will see an error that table Names not found
that's okay, click Ok, double click on query and on first step change in formula bar on your actual table name
- same with second query if necessary
- query Names will be loaded into new sheet. You may move the table, or right click on query, Load To as connection only, delete created sheet and Load again query to the Table.
You may do the same without creating new group, but pasted queries will be among you old ones and it will be harder to find them. After everything works you may move queries to another group or to the root.
- Hi Sergei, I just tried your script, and it worked!!! but, have a small problem.
It appears that if there are multiple matches, it seems to pick up and return only one(1) of them. Is there a way the Default Email column can return multiple matching emails, if any, by semi-colon, so that all matching emails from each row is included in that one column [Default Email]?
