There are two tables. For second table, against Emp ID, populate the names of employees. N* – Emp ID starts with N and followed by 0 and more characters: 1* = 1246, 1234 *N – Emp ID finishes with N and preceded by 0 and more characters. *8 = 5628 *N* OR N – N can be found anywhere within Emp ID *3* = 1234, 9034 3 = 1234, 9034
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 36
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Match employee names using wildcard with Power Query
Power Query solution 1 for Match employee names using wildcard, proposed by Bo Rydobon 🇹🇭:
let
RE = (regex as text, str as text) =>
let
html = "",
res = Web.Page(html)[Data]{0}[Children]{0}[Children]{1}[Text]{0}
in
res,
TB = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Emp ID", type text}}
),
Regex = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
"Emp Name",
each
let
sp = Text.Split([Emp ID], "*"),
no = Text.Remove([Emp ID], "*"),
patt = if List.Count(sp) = 2 then if sp{0} = "" then no & "$" else "^" & no else no,
Tab = Text.Combine(
Table.SelectRows(TB, each RE("/" & patt & "/", [Emp ID]) <> "null")[Emp Name],
", "
)
in
Tab
)
in
RegexPower Query solution 2 for Match employee names using wildcard, proposed by Bo Rydobon 🇹🇭:
let
Source = Table.TransformColumnTypes(
Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
{{"Emp ID", type text}}
),
Filt = Table.AddColumn(
Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
"Emp Name",
each Text.Combine(
Table.SelectRows(
Source,
(t) =>
let
sp = Text.Split([Emp ID], "*")
in
if List.Count(sp) = 2 then
if sp{0} = "" then
Text.EndsWith(t[Emp ID], sp{1})
else
Text.StartsWith(t[Emp ID], sp{0})
else
Text.Contains(t[Emp ID], Text.Replace([Emp ID], "*", ""))
)[Emp Name],
", "
)
)
in
FiltPower Query solution 3 for Match employee names using wildcard, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Custom1 = Table.AddColumn(Source, "New", each List.Count(Text.Split([Emp ID], "*"))),
Comparacion = Table.AddColumn(
Custom1,
"Emp Name",
(X) =>
if X[New] = 1 then
Text.Combine(
Table.SelectRows(Table1, each Text.Contains(_[Emp ID], X[Emp ID]))[Emp Name],
", "
)
else if X[New] = 2 and Text.EndsWith(X[Emp ID], "*") then
Text.Combine(
Table.SelectRows(Table1, each Text.StartsWith(_[Emp ID], Text.Split(X[Emp ID], "*"){0}))[
Emp Name
],
", "
)
else if X[New] = 2 and Text.StartsWith(X[Emp ID], "*") then
Text.Combine(
Table.SelectRows(Table1, each Text.EndsWith(_[Emp ID], Text.Split(X[Emp ID], "*"){1}))[
Emp Name
],
", "
)
else
Text.Combine(
Table.SelectRows(Table1, each Text.Contains(_[Emp ID], Text.Split(X[Emp ID], "*"){1}))[
Emp Name
],
","
)
)[[Emp ID], [Emp Name]]
in
ComparacionPower Query solution 4 for Match employee names using wildcard, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "EmpID"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Emp ID", type text}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Merged Queries" = Table.NestedJoin(
#"Added Index",
{"Emp ID"},
EmpName,
{"Custom"},
"EmpName",
JoinKind.LeftOuter
),
#"Expanded EmpName" = Table.ExpandTableColumn(
#"Merged Queries",
"EmpName",
{"Emp Name"},
{"Emp Name"}
),
#"Sorted Rows" = Table.Sort(#"Expanded EmpName", {{"Index", Order.Ascending}}),
#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows", {"Emp ID", "Emp Name"})
in
#"Removed Other Columns"Power Query solution 5 for Match employee names using wildcard, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name = "EmpName"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Emp ID", type text}, {"Emp Name", type text}}
),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type", {"Emp Name", "Emp ID"}),
#"Added Custom" = Table.AddColumn(
#"Reordered Columns",
"Custom",
each Text.Start([Emp ID], 1)
& ","
& Text.Middle([Emp ID], 1, 1)
& ","
& Text.Middle([Emp ID], 2, 1)
& ","
& Text.End([Emp ID], 1)
& ","
& "*"
& Text.End([Emp ID], 1)
& ","
& Text.Start([Emp ID], 1)
& "*,*"
& Text.Start([Emp ID], 1)
& "*,*"
& Text.Middle([Emp ID], 1, 1)
& "*,*"
& Text.Middle([Emp ID], 2, 1)
& "*,*"
& Text.End([Emp ID], 1)
& "*"
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"Emp Name", "Custom"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(
Table.TransformColumns(
#"Removed Other Columns",
{
{
"Custom",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),
let
itemType = (type nullable text) meta [Serialized.Text = true]
in
type {itemType}
}
}
),
"Custom"
),
#"Changed Type1" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{{"Custom", type text}}
),
#"Grouped Rows" = Table.Group(
#"Changed Type1",
{"Custom"},
{{"Emp Name", each Text.Combine([Emp Name], " ,"), type nullable text}}
)
in
#"Grouped Rows"Power Query solution 6 for Match employee names using wildcard, proposed by Matthias Friedmann:
let
Source = Excel.CurrentWorkbook(){[Name = "ID"]}[Content],
Custom = Table.AddColumn(
Source,
"Emp Name",
each [
a = Text.From([Emp ID]),
b =
if not Text.StartsWith(a, "*") and Text.EndsWith(a, "*") then
Table.SelectRows(IDname, each Text.StartsWith(Text.From([Emp ID]), Text.Start(a, 1)))
else if Text.StartsWith(a, "*") and not Text.EndsWith(a, "*") then
Table.SelectRows(IDname, each Text.EndsWith(Text.From([Emp ID]), Text.End(a, 1)))
else
Table.SelectRows(IDname, each Text.Contains(Text.From([Emp ID]), Text.Remove(a, "*"))),
c = Text.Combine(List.Transform(b[Emp Name], Text.From), ", ")
][c]
)
in
CustomPower Query solution 7 for Match employee names using wildcard, proposed by Owen Price:
https://gist.github.com/ncalm/00d1dde4a22e1d3572226acd46380b82
Power Query solution 9 for Match employee names using wildcard, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Result = Table.AddColumn(
Source,
"Emp Name",
each [
a = Text.Split([Emp ID], "*"),
b =
if List.Count(a) <> 2 then
Text.Contains
else if a{0} <> "" then
Text.StartsWith
else
Text.EndsWith,
c = Text.Remove([Emp ID], "*"),
d = Text.Combine(Table.SelectRows(Table1, each b(Text.From([Emp ID]), c))[Emp Name], ", ")
][d]
)
in
ResultPower Query solution 10 for Match employee names using wildcard, proposed by Venkata Rajesh:
let
Source = Table2,
Output = Table.AddColumn(
Source,
"Emp Name",
each
let
_a = List.Sum(Text.PositionOf([Emp ID], "*", Occurrence.All)),
_b = Text.Select([Emp ID], {"0" .. "9"})
in
Text.Combine(
Table.SelectRows(
Table1,
each
if _a = 0 then
Text.EndsWith(Text.From([Emp ID]), _b)
else if _a = 1 then
Text.StartsWith(Text.From([Emp ID]), _b)
else
Text.Contains(Text.From([Emp ID]), _b)
)[Emp Name],
", "
),
Text.Type
)
in
OutputPower Query solution 11 for Match employee names using wildcard, proposed by Thomas DUCROQUETZ:
let
Source = Table2,
GetEmps = Table.AddColumn(
Source,
"Emp Name",
each
let
FctToUse =
let
nbWithoutStars = Text.Replace([Emp ID], "*", ""),
Fct =
if not Text.Contains([Emp ID], "*") then
(x) => Text.Contains(x, [Emp ID])
else if Text.StartsWith([Emp ID], "*") and Text.EndsWith([Emp ID], "*") then
(x) => Text.Contains(x, nbWithoutStars)
else if Text.StartsWith([Emp ID], "*") then
(x) => Text.EndsWith(x, nbWithoutStars)
else
(x) => Text.StartsWith(x, nbWithoutStars)
in
Fct,
Emps = Table.SelectRows(Table1, each FctToUse([Emp ID]))
in
Text.Combine(Emps[Emp Name], ", "),
type text
)
in
GetEmpsSolving the challenge of Match employee names using wildcard with Excel
Excel solution 1 for Match employee names using wildcard, proposed by Bo Rydobon 🇹🇭:
=HSTACK(
D2:D9,
MAP(
D2:D9,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
B2:B8,
ISNUMBER(
SEARCH(
" "&TEXT(
a,
"*0*"
)&" ",
" "&A2:A8&" "
)
),
""
)
)
)
)
)
=HSTACK(
D2:D9,
MAP(
D2:D9,
LAMBDA(
a,
ARRAYTOTEXT(
FILTER(
B2:B8,
ISNUMBER(
SEARCH(
" "&IF(
ISERR(
-a
),
a,
"*"&a&"*"
)&" ",
" "&A2:A8&" "
)
),
""
)
)
)
)
)Excel solution 2 for Match employee names using wildcard, proposed by Rick Rothstein:
=LET(
d,
D2:D9,
MAP(
IF(
ISNUMBER(
0+d
),
"*"&d&"*",
d
),
LAMBDA(
x,
TEXTJOIN(
", ",
,
IF(
ISNUMBER(
SEARCH(
"|"&x&"|",
"|"&A2:A8&"|"
)
),
B2:B8,
""
)
)
)
)
)Excel solution 3 for Match employee names using wildcard, proposed by John V.:
=HSTACK(
D2:D9,
MAP(
D2:D9,
LAMBDA(
x,
TEXTJOIN(
", ",
,
BYROW(
A2:B8,
LAMBDA(
r,
IFNA(
VLOOKUP(
IFERROR(
TEXT(
--x,
"*0*"
),
x
),
r&"",
2,
),
""
)
)
)
)
)
)
)Excel solution 4 for Match employee names using wildcard, proposed by محمد حلمي:
=MAP(D2:D9,
LAMBDA(d,
ARRAYTOTEXT(
FILTER(B2:B8,
LET(a,
A2:A8,
r,
ISNUMBER(
SEARCH(
d,
a
)
),
e,
SEQUENCE(
LEN(
d
)
),
SWITCH( SUM((MID(
d,
e,
1
)="*")*e),
0,
r,
1,
RIGHT(
a
)=RIGHT(
d
),
2,
LEFT(
a
)=LEFT(
d
),
4,
r)),
""))))Excel solution 5 for Match employee names using wildcard, proposed by 🇰🇷 Taeyong Shin:
=HSTACK(
D2:D9,
MAP(
D2:D9,
LAMBDA(
x,
ARRAYTOTEXT(
FILTER(
B2:B8,
REGEXTEST(
A2:A8,
TEXT(
SUBSTITUTE(
x,
"*",
".*"
),
"^@$"
)
),
""
)
)
)
)
)
=HSTACK(
D2:D9,
MAP(
D2:D9,
LAMBDA(
x,
TEXTJOIN(
", ",
,
REGEXREPLACE(
A2:A8,
TEXT(
SUBSTITUTE(
x,
"*",
".*"
),
"(0);;;^(@)$"
)&"|.",
"${1:+"&B2:B8&"}"
)
)
)
)
)Excel solution 6 for Match employee names using wildcard, proposed by Aditya Kumar Darak 🇮🇳:
=LET(
_ei,
A2:A8,
_en,
B2:B8,
_rei,
D2:D9,
_e,
LAMBDA(
x,
TEXTJOIN(
", ",
TRUE,
MAP(
_ei,
_en,
LAMBDA(
a,
b,
XLOOKUP(
IF(
ISERR(
-x
),
x,
"*" & x & "*"
),
TRIM(
a
),
b,
"",
2
)
)
)
)
),
_c,
MAP(
_rei,
_e
),
_r,
HS&TACK(
_rei,
_c
),
_r
)Excel solution 7 for Match employee names using wildcard, proposed by Stefan Olsson:
=LAMBDA(
e,
reAry,
MAP(
reAry,
LAMBDA(
re,
{re,
TEXTJOIN(
", ",
TRUE,
IFERROR(
QUERY(
{e},
"Select Col2 where Col1 Matches '"&IF(
REGEXMATCH(
re&"",
"*"
),
REGEXREPLACE(
re,
"*",
".*"
),
".*"&re&".*"
)&"'",
0
)
)
)
}
)
)
)
(A2:B8,
D2:D9)Excel solution 8 for Match employee names using wildcard, proposed by Stefan Olsson:
=LAMBDA(
e,
reAry,
MAP(
reAry,
LAMBDA(
re,
{re,
TEXTJOIN(
", ",
TRUE,
IFERROR(
QUERY(
{e},
"Select Col2 where Col1 Like '"&
IF(
REGEXMATCH(
re,
"*"
),
REGEXREPLACE(
re,
"*",
"%"
),
"%"&re&"%"
)&"'",
0
)
)
)
}
)
)
)
(A2:B8,
D2:D9)Excel solution 9 for Match employee names using wildcard, proposed by Morteza Rahmani:
=IF(LEN(F2)>1,regex($A$2:$A$8,"^" & SUBSTITUTE(F2,"*",".*") & "$",$B$2:$B$8,,", "),regex($A$2:$A$8,F2,$B$2:$B$8,,", "))
Without hashtag#PPX:
=SUBSTITUTE(CONCAT(IF(LEN(F2)>1,IF(LEN(F2)=3,IF(IFERROR(FIND(INDEX(--MID(F2,ROW($1:$3),1),2),$A$2:$A$8),0),$B$2:$B$8&", ",""),IF(ISNUMBER(INDEX(--MID(F2,ROW($1:$3),1),1)),IF(IFERROR(FIND(INDEX(--MID(F2,ROW($1:$3),1),1),$A$2:$A$8),0)=1,$B$2:$B$8&", ",""),IF(ISNUMBER(INDEX(--MID(F2,ROW($1:$3),1),2)),IF(IFERROR(FIND(INDEX(--MID(F2,ROW($1:$3),1),2),$A$2:$A$8),0)=LEN($A$2:$A$8),$B$2:$B$8&", ", ""),""))),IF(NOT(ISERR(FIND(F2,$A$2:$A$8))),$B$2:$B$8 & ", ","")))&" ",", ","")Solving the challenge of Match employee names using wildcard with SQL
SQL solution 1 for Match employee names using wildcard, proposed by Zoran Milokanović:
WITH
DATA_PREPARATION
AS
(
SELECT
ROW_NUMBER() OVER () AS ORDINAL_NUMBER
,W.EMP_ID
,CASE
ELSE REPLACE (W.EMP_ID, '*', '%')
END AS WILDCARD
)
SELECT
W.EMP_ID
,REPLACE(LISTAGG(E.EMP_NAME), ',', ', ') AS EMP_NAME
FROM EMPLOYEES E
RIGHT JOIN DATA_PREPARATION W ON TO_CHAR(E.EMP_ID) LIKE W.WILDCARD
GROUP BY
W.ORDINAL_NUMBER
,W.EMP_ID
ORDER BY
W.ORDINAL_NUMBER
;
&&