For Table T2, look for value column of Table T1 in City/Country and Text columns of Table T2 and prepare the mapping shown in Result table.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 130
Challenge Difficulty: ⭐️⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Text Mapping Between Tables with Power Query
Power Query solution 1 for Text Mapping Between Tables, proposed by Zoran Milokanović:
let
Source = Table.UnpivotOtherColumns(G("Table2"), {"Emp ID", "Name"}, "Column Name", "V"),
G = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T = Table.SelectRows,
S = Table.Sort(
T(
Table.ExpandListColumn(
Table.AddColumn(
Source,
"No",
each T(
G("Table1"),
(r) => Text.PositionOf([V], r[Value], 0, Comparer.OrdinalIgnoreCase) >= 0
)[No]
),
"No"
)[[Emp ID], [No], [Column Name]],
each [No] <> null
),
{"Emp ID", "No"}
)
in
SPower Query solution 2 for Text Mapping Between Tables, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Pivot = Table.Unpivot(Source, List.Skip(Table.ColumnNames(Source), 2), "Column Name", "B"),
Col = Table.Sort(
Table.SelectRows(
Table.ExpandListColumn(
Table.AddColumn(
Pivot,
"No",
(x) =>
let
a = List.PositionOf(
List.Transform(
Table1[Value],
each Text.Contains(x[B], _, Comparer.OrdinalIgnoreCase)
),
true,
2
),
b = List.Transform(a, each Table1[No]{_})
in
b
),
"No"
),
each [No] <> null
),
{"Emp ID", "No"}
)[[Emp ID], [No], [Column Name]]
in
ColPower Query solution 3 for Text Mapping Between Tables, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Pros = Table.AddColumn(
Source,
"A",
(x) =>
let
l = List.Transform,
o = Comparer.OrdinalIgnoreCase,
p = each not List.IsEmpty(_),
a = List.Skip(Record.FieldNames(x), 2),
b = l(a, each {Record.Field(x, _)} & {_}),
c = l(Table1[Value], (z) => List.Select(b, (y) => Text.Contains(y{0}, z, o))),
d = l(c, p),
e = l(List.Select(c, p), each _{0}{1}),
f = l(List.PositionOf(d, true, 2), each _ + 1),
g = Table.Sort(Table.FromRows(List.Zip({f, e}), {"No", "Column Name"}), "No")
in
g
)[[Emp ID], [A]],
Sol = Table.ExpandTableColumn(Pros, "A", Table.ColumnNames(Pros[A]{0}))
in
SolPower Query solution 4 for Text Mapping Between Tables, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Pros = Table.AddColumn(
Source,
"A",
each
let
m = List.PositionOf,
n = Text.Contains,
o = Comparer.OrdinalIgnoreCase,
a = Table1[Value],
b = m(List.Transform(a, (x) => n([#"City / Country"], x, o)), true, 2),
c = List.Transform(b, each {_ + 1} & {"City / Country"}),
d = m(List.Transform(a, (x) => n([Text], x, o)), true, 2),
e = Table.Sort(
Table.FromRows(c & List.Transform(d, each {_ + 1} & {"Text"}), {"No", "Column Name"}),
"No"
)
in
e
)[[Emp ID], [A]],
Sol = Table.ExpandTableColumn(Pros, "A", Table.ColumnNames(Pros[A]{0}))
in
SolPower Query solution 5 for Text Mapping Between Tables, proposed by Luan Rodrigues:
let
Fonte = Tabela2,
tab = Table.AddColumn(
Fonte,
"tab",
each [
a = Table.Combine(
List.Transform(
Table.SelectRows(
Tabela1,
(x) => Text.Contains([#"City / Country"], x[Value], Comparer.OrdinalIgnoreCase)
)[No],
(y) => Table.FromRows({{y} & {"City / Country"}}, {"No", "Column Name"})
)
),
b = Table.Combine(
List.Transform(
Table.SelectRows(
Tabela1,
(x) => Text.Contains([Text], x[Value], Comparer.OrdinalIgnoreCase)
)[No],
(y) => Table.FromRows({{y} & {"Text"}}, {"No", "Column Name"})
)
),
c = Table.Sort(Table.Combine({a, b}), {each [No], 0})
][c]
)[[Emp ID], [tab]],
res = Table.ExpandTableColumn(tab, "tab", Table.ColumnNames(tab[tab]{0}))
in
resSolving the challenge of Text Mapping Between Tables with Excel
Excel solution 1 for Text Mapping Between Tables, proposed by Bo Rydobon 🇹🇭:
=LET(a,F2:G6,L,LAMBDA(x,TOCOL(IF(SEARCH(B2:B12,TOROW(a)),x),3)),
VSTACK(HSTACK(D1,A1,"Column Name"),SORT(HSTACK(L(TOROW(IFNA(D2:D6,a))),L(A2:A12),L(TOROW(IFNA(F1:G1,a)))))))Excel solution 2 for Text Mapping Between Tables, proposed by محمد حلمي:
=REDUCE(HSTACK(D1,A1,"Column Name"),D2:D6,LAMBDA(
a,d,LET(x,SEARCH(B2:B12,TAKE(d:G6,1)),VSTACK(a,IFNA(HSTACK(d,TOCOL(IF(x,A2:A12),2),TOCOL(IF(x,D1:G1),2)),d)))))Excel solution 3 for Text Mapping Between Tables, proposed by Sunny Baggu:
=LET(
_tbl, REDUCE(
"Happy Diwali Vijay Sir🍘🌟🕯",
SEQUENCE(ROWS(D2:D6)),
LAMBDA(a, v,
VSTACK(a, TOCOL(IF(ISNUMBER(SEARCH(B2:B12, INDEX(F2:G6, v, ))), v & "|" & A2:A12 & "|" & F1:G1, 1 / x), 3))
)
),
IFNA(TEXTSPLIT(TEXTJOIN(",", , _tbl), "|", ","), "🪔")
)Solving the challenge of Text Mapping Between Tables with R
R solution 1 for Text Mapping Between Tables, proposed by Konrad Gryczan, PhD:
library(tidytext)
library(tidyverse)
library(readxl)
T1 = read_excel("PQ_Challenge_130.xlsx", range = "A1:B12")
T2 = read_excel("PQ_Challenge_130.xlsx", range = "D1:G6")
Test = read_excel("PQ_Challenge_130.xlsx", range = "D11:F23")
R1 = T2 %>%
unnest_tokens(word, Text) %>%
ungroup() %>%
mutate(`Column Name` = "Text")
R2 = T2 %>%
unnest_tokens(word, `City / Country`) %>%
ungroup() %>%
mutate(`Column Name` = "City / Country")
Result = R1 %>%
bind_rows(R2)
T1R = T1 %>%
unnest_tokens(word, Value) %>%
ungroup()
Res1 = Result %>%
left_join(T1R, by = "word") %>%
select(`Emp ID`, No, `Column Name`) %>%
filter(!is.na(No)) %>%
arrange(`Emp ID`, No) %>%
unique()
Solving the challenge of Text Mapping Between Tables with DAX
DAX solution 1 for Text Mapping Between Tables, proposed by Zoran Milokanović:
DEFINE
VAR t = UNION(SELECTCOLUMNS(Table2, "Emp ID", Table2[Emp ID], "Text", Table2[Text], "Column Name", "Text"), SELECTCOLUMNS(Table2, "Emp ID", Table2[Emp ID], "Text", Table2[City / Country], "Column Name", "City / Country"))
EVALUATE
SELECTCOLUMNS(GENERATE(t, FILTER(Table1, SEARCH(Table1[Value], [Text], 1, 0) > 0)), "Emp ID", [Emp ID], "No", [No], "Column Name", [Column Name])
ORDERBY
[Emp ID], [No]
&&&