For a column, find how many members of the column are contained in other columns.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 204
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Count Column Cross Matches with Power Query
Power Query solution 1 for Count Column Cross Matches, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
C = Table.ToColumns(Source),
H = Table.ColumnNames(Source),
S = Table.FromColumns(
List.TransformMany(
C,
each {
List.Select(List.RemoveItems(C, {_}), (m) => List.ContainsAny(m, List.RemoveItems(_, {""})))
},
(i, _) =>
List.Transform(
_,
each H{List.PositionOf(C, _)}
& " - "
& Text.From(List.Count(List.Intersect({List.RemoveItems(i, {""}), _})))
)
),
List.Transform(H, each _ & " Match")
)
in
SPower Query solution 2 for Count Column Cross Matches, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Cols = Table.ColumnNames(Source),
Transform = List.Transform(
Cols,
each [
T = List.Transform(
Cols,
(f) =>
[
i = List.Intersect({Table.Column(Source, f), Table.Column(Source, _)}),
s = List.RemoveMatchingItems(i, {""}),
c = List.Count(s),
r = if f = _ or c = 0 then null else f & " - " & Text.From(c)
][r]
),
R = List.RemoveNulls(T)
][R]
),
Return = Table.FromColumns(Transform, List.Transform(Cols, each _ & " Match"))
in
ReturnPower Query solution 3 for Count Column Cross Matches, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {}, "At", "Va"),
Filter = Table.SelectRows(Unpivot, each ([Va] <> "")),
Merge = Table.NestedJoin(Filter, {"Va"}, Filter, {"Va"}, "Cols", JoinKind.LeftOuter),
Merge2 = Table.AddColumn(Merge, "B", (x) => Table.SelectRows(x[Cols], each [At] <> x[At])[At]),
GR = Table.Group(
Merge2,
{"At"},
{
{
"All",
(y) =>
let
a = List.Combine(y[B]),
b = List.Distinct(a),
c = List.Sort(
List.Transform(
b,
each _ & " - " & Text.From(List.Count(List.Select(a, (x) => x = _)))
)
)
in
c
}
}
)[All],
Sol = Table.FromColumns(GR, List.Transform(Table.ColumnNames(Source), each _ & " - Match"))
in
SolPower Query solution 4 for Count Column Cross Matches, proposed by 🇵🇪 Ned Navarrete C.:
let
S = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
H = Table.ColumnNames(S),
L = List.Transform(Table.ToColumns(S), each List.Select(_, each _ <> "")),
A = List.Accumulate(
H,
{},
(s, i) =>
s
& {
[
a = List.PositionOf(H, i),
b = List.RemoveRange(L, a),
c = List.Transform(b, each Text.From(List.Count(List.Intersect({_, L{a}})))),
d = List.Select(List.Zip({List.RemoveRange(H, a), c}), each _{1} <> "0"),
e = List.Transform(d, each Text.Combine(_, " - "))
][e]
}
),
R = Table.TransformColumnNames(
Table.FromColumns(A),
each Replacer.ReplaceText(_, "Column", "Col") & " Match"
)
in
RPower Query solution 5 for Count Column Cross Matches, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData204"]}[Content],
IndexList = {0 .. Table.ColumnCount(Source) - 1},
Cols = List.Buffer(List.Transform(Table.ToColumns(Source), each List.RemoveItems(_, {null, ""}))),
NewCols = List.Transform(
IndexList,
each
let
_Values = Cols{_},
_NewValues = List.Accumulate(
List.RemoveItems(IndexList, {_}),
{},
(s, c) =>
let
_NbMatch = List.Count(List.Intersect({_Values, Cols{c}}))
in
s
& (
if _NbMatch = 0 then
{}
else
{"Col" & Text.From(c + 1) & " - " & Text.From(_NbMatch)}
)
)
in
_NewValues
),
Result = Table.FromColumns(NewCols, List.Transform(Table.ColumnNames(Source), each _ & " Match"))
in
ResultPower Query solution 6 for Count Column Cross Matches, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.FromColumns({Table.ToColumns(S)}, {"B"}),
S2 = Table.AddColumn(S1, "C", each List.Select([B], each _ <> "")),
A = Table.FromColumns({Table.ColumnNames(S), S2[C]}, {"Col", "F"}),
B = Table.FromColumns({Table.ColumnNames(S), {Table.ColumnNames(S)}}, {"B", "C"}),
C = Table.FillDown(B, {"C"}),
D = Table.ExpandListColumn(C, "C"),
E = Table.NestedJoin(D, {"B"}, A, {"Col"}, "N"),
F = Table.ExpandTableColumn(E, "N", {"F"}, {"L"}),
G = Table.NestedJoin(F, {"C"}, A, {"Col"}, "S"),
H = Table.ExpandTableColumn(G, "S", {"F"}, {"R"}),
I = Table.AddColumn(
H,
"I",
each if [B] <> [C] then List.Count(List.Intersect({[L], [R]})) else null
),
J = Table.SelectRows(I, each ([I] <> null and [I] <> 0)),
K = Table.AddColumn(J, "Z", each Text.Combine({[C], Text.From([I], "en-US")}, "-"), type text),
L = Table.SelectColumns(K, {"B", "Z"}),
M = Table.Group(L, {"B"}, {{"T", each _, type table [B = nullable text, C = text]}}),
N = Table.Sort(M, {{"B", Order.Ascending}}),
O = Table.AddColumn(N, "O", each Table.AddIndexColumn([T], "X", 1, 1)),
P = Table.SelectColumns(O, {"O"}),
Q = Table.ExpandTableColumn(P, "O", {"B", "Z", "X"}, {"B", "Z", "X"}),
R = Table.Pivot(Q, List.Distinct(Q[B]), "B", "Z"),
s = Table.RemoveColumns(R, {"X"})
in
sPower Query solution 7 for Count Column Cross Matches, proposed by Peter Tholstrup:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
unpivot = Table.UnpivotOtherColumns(Source, {}, "Col", "Fruit"),
filter = Table.SelectRows(unpivot, each [Fruit] <> ""),
crossjoin = (t) =>
[
a = Table.AddColumn(t[[Col]], "Match", each t[Col]),
b = Table.ExpandListColumn(a, "Match"),
c = Table.SelectRows(b, each [Col] <> [Match])
][c],
group_fruit = Table.Group(filter, "Fruit", {"cols", crossjoin}),
expand = Table.Combine(group_fruit[cols]),
match_count = (t) =>
[
a = Table.Group(t, "Match", {"Count", each Text.From(Table.RowCount(_))}),
b = List.Zip(Table.ToColumns(a)),
c = List.Sort(List.Transform(b, each Text.Combine(_, " - ")))
][c],
group_col = Table.Group(expand, "Col", {"Match", match_count}),
headers = List.Transform(group_col[Col], each _ & " Match"),
result = Table.FromColumns(group_col[Match], headers)
in
resultPower Query solution 8 for Count Column Cross Matches, proposed by Yaroslav Drohomyretskyi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Table = Table.FromList(
List.Generate(() => 1, each _ <= Table.ColumnCount(Source), each _ + 1),
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
Filter = Table.SelectRows(
Table.ExpandTableColumn(
Table.AddColumn(
Table.TransformColumnTypes(Table, {{"Column1", type text}}),
"Custom",
each Table.TransformColumnTypes(Table, {{"Column1", type text}})
),
"Custom",
{"Column1"},
{"Column1.1"}
),
each [Column1] <> [Column1.1]
),
Filter2 = Table.SelectRows(
Table.AddColumn(
Filter,
"Custom",
each List.Count(
List.RemoveItems(
List.Intersect(
{Table.Column(Source, "Col" & [Column1]), Table.Column(Source, "Col" & [Column1.1])}
),
{""}
)
)
),
each ([Custom] <> 0)
),
Custom = Table.AddColumn(
Filter2,
"Custom.1",
each "Col" & [Column1.1] & " - " & Text.From([Custom])
),
Group = Table.Group(Custom, {"Column1"}, {{"Count", each Text.Combine(_[Custom.1], "|")}}),
Split = Table.SplitColumn(
Group,
"Count",
Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv),
{"Count.1", "Count.2", "Count.3"}
),
Transpose = Table.Transpose(Split),
Header = Table.TransformColumnNames(
Table.PromoteHeaders(Transpose, [PromoteAllScalars = true]),
each "Col" & _ & " Match"
)
in
HeaderPower Query solution 9 for Count Column Cross Matches, proposed by Ahmed Ariem:
let
Source = Excel.CurrentWorkbook(){[Name = "tbl"]}[Content],
Headers = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
Custom1 = List.Transform(Table.ToColumns(Headers), (x) => List.Select(x, (x) => x <> "")),
#"Converted to Table" = Table.FromList(
Custom1,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
#"Added Index" = Table.AddIndexColumn(#"Converted to Table", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(
#"Added Index",
"Custom",
each [
lst = List.RemoveMatchingItems(#"Added Index"[Index], {[Index]}),
a = List.Transform(lst, (x) => #"Added Index"[Column1]{x - 1}),
b = List.Transform(
List.Transform(a, (x) => List.Select(x, (x) => List.ContainsAny([Column1], {x}))),
(x) => Text.From(List.Count(x))
),
c = List.Transform(
List.Zip({List.Transform(lst, (x) => "Col-" & Text.From(x)), b}),
(x) => Text.Combine(x, " ")
),
d = List.Select(c, (x) => not Text.Contains(x, "0"))
][d]
),
#"Removed Other Columns" = Table.FromColumns(
Table.SelectColumns(#"Added Custom", {"Custom"})[Custom],
{"Col1 Match", "Col2 Match", "Col3 Match", "Col4 Match"}
)
in
#"Removed Other Columns"Power Query solution 10 for Count Column Cross Matches, proposed by Sandeep Marwal:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
S1 = Table.ToColumns(Source),
S2 = Table.FromList(S1, Splitter.SplitByNothing(), {"main"}),
S3 = Table.AddColumn(S2, "matching", each S1),
S4 = Table.AddIndexColumn(S3, "maincol", 1, 1),
S5 = Table.ExpandListColumn(S4, "matching"),
S6 = Table.AddIndexColumn(S5, "Index.1", 0, 1),
S7 = Table.AddColumn(S6, "matching col", each 1 + Number.Mod([Index.1], 4)),
S8 = Table.AddColumn(
S7,
"Custom",
each
if [maincol] = [matching col] then
null
else
List.Count(List.RemoveNulls([main]))
- List.Count(List.RemoveNulls(List.RemoveMatchingItems([main], [matching])))
),
S9 = Table.SelectRows(S8, each ([Custom] <> null and [Custom] <> 0)),
S10 = Table.TransformColumns(
S9,
{
{"maincol", each "Col " & Text.From(_) & " Match"},
{"matching col", each "Col" & Text.From(_)}
}
),
S11 = Table.CombineColumns(
Table.TransformColumnTypes(S10, {{"Custom", type text}}, "en-IN"),
{"matching col", "Custom"},
Combiner.CombineTextByDelimiter("-", QuoteStyle.None),
"Merged"
),
S12 = Table.Group(S11, {"maincol"}, {{"Count", each _[Merged]}}),
S13 = Table.FromColumns(S12[Count], S12[maincol])
in
S13Power Query solution 11 for Count Column Cross Matches, proposed by Szabolcs Phraner:
let
Source = ...,
ColNames = Table.ColumnNames(Source),
//Function to create a Filtered Table from a Column, makes it easer to match column values
FNColTable = (ColName as text) =>
Table.SelectRows(Table.FromColumns({Table.Column(Source, ColName)}), each [Column1] <> ""),
//Iterate trough all Columns, and compare values to other columns values + format result as text
MatchColValues = List.Accumulate(
ColNames,
{},
(s, c) =>
let
CurrentCol = c,
CurrentColTable = FNColTable(CurrentCol),
OtherCols = List.Difference(ColNames, {CurrentCol}),
MatchColumns = List.Transform(
OtherCols,
each _
& " - "
& Text.From(
Table.RowCount(
Table.Join(CurrentColTable, "Column1", FNColTable(_), "Column1", JoinKind.Inner)
)
)
)
in
s & {List.Select(MatchColumns, each not Text.Contains(_, " 0"))}
),
// each Column List has to contain the same number of items
SupplementColumnLists = List.Transform(
MatchColValues,
each
let
LC = List.Count(_),
Diff = List.Count(ColNames) - LC - 1
in
List.InsertRange(_, LC, List.Repeat({""}, Diff))
),
TableFromCols = Table.FromColumns(
SupplementColumnLists,
List.Transform(ColNames, each _ & " Match")
)
in
TableFromColsSolving the challenge of Count Column Cross Matches with Excel
Excel solution 1 for Count Column Cross Matches, proposed by Bo Rydobon 🇹🇭:
=LET(z,
A2:D7,
s,
SEQUENCE(
COLUMNS(
z
),
),
IFNA(DROP(REDUCE(0,
s,
LAMBDA(a,
i,
LET(j,
INDEX(
z,
,
i
),
m,
MAP(s,
LAMBDA(c,
SUM((j=TOROW(
INDEX(
z,
,
c
)
))*(j>"")*(i<>c)))),
HSTACK(
a,
FILTER(
"Col"&s&-m,
m
)
)))),
,
1),
""))Excel solution 2 for Count Column Cross Matches, proposed by Rick Rothstein:
=LET(
f,
LAMBDA(
f,
s,
LET(
b,
BYCOL(
COUNTIF(
f,
CHOOSECOLS(
A2:D7,
s
)
),
LAMBDA(
c,
SUM(
c
)
)
),
TOCOL(
IF(
b=0,
1/0,
"Col"&s&" - "&b
),
3
)
)
),
VSTACK(
A1:D1&" Match",
IFNA(
HSTACK(
f(
A2:A7,
{2,
3,
4}
),
f(
B2:B4,
{1,
3,
4}
),
f(
C2:C5,
{1,
2,
4}
),
f(
D2:D6,
{1,
2,
3}
)
),
""
)
)
)Excel solution 3 for Count Column Cross Matches, proposed by محمد حلمي:
=DROP(
REDUCE(0,
A1:D1,
LAMBDA(a,
v,
LET(
c,
A1:D1,
x,
BYCOL(
XMATCH(
A2:D7,
TOCOL(
OFFSET(
v,
,
,
7
),
1
)
)/(c<>v),
LAMBDA(
a,
COUNT(
a
)
)),
IFNA(
HSTACK(
a,
TOCOL(
IFS(
x,
c&" - "&x
),
2
)
),
""
)))),
,
1)
Key of Solution:
#1
=XMATCH(
A2:D7,
A2:A7
)/ISNA(
XMATCH(
A1:D1,
A1
)
)
/ISNA(
XMATCH(
A1:D1,
A1
)
)
To exclude the calculation of the active column
#2
=BYCOL(
XMATCH(
A2:D7,
A2:A7
)/ISNA(
XMATCH(
A1:D1,
A1
)
),
LAMBDA(
a,
COUNT(
a
)
)
)Excel solution 4 for Count Column Cross Matches, proposed by محمد حلمي:
=DROP(REDUCE(0,
A1:D1,
LAMBDA(a,
v,
LET(
c,
A1:D1,
x,
BYCOL(XMATCH(
A2:D7,
TOCOL(
OFFSET(
v,
,
,
7
),
1
)
)/(c<>v),
COUNT),
IFNA(
HSTACK(
a,
TOCOL(
IFS(
x,
c&" - "&x
),
2
)
),
""
)))),
,
1)Excel solution 5 for Count Column Cross Matches, proposed by Julian Poeltl:
=LET(
S,
SEQUENCE(
,
4
),
T,
A2:D7,
H,
A1:D1,
VSTACK(
H&" Match",
IFNA(
DROP(
REDUCE(
0,
S,
LAMBDA(
A,
B,
HSTACK(
A,
LET(
N,
FILTER(
S,
S<>B
),
C,
CHOOSECOLS(
T,
B
),
R,
TOCOL(
CHOOSECOLS(
H,
N
)&" - "&BYCOL(
--ISNUMBER(
XMATCH(
CHOOSECOLS(
T,
N
),
IF(
C<>"",
C,
"x"
)
)
),
LAMBDA(
A,
SUM(
A
)
)
)
),
FILTER(
R,
--RIGHT(
R,
1
)>0
)
)
)
)
),
,
1
),
""
)
)
)Excel solution 6 for Count Column Cross Matches, proposed by Oscar Mendez Roca Farell:
=LET(e,
A1:D1,
VSTACK(e&" Match",
DROP(REDUCE("",
e,
LAMBDA(i,
x,
LET(a,
TOCOL(
TAKE(
x:D7,
,
1
),
1
),
b,
BYCOL(
XMATCH(
A2:D7,
a
),
LAMBDA(
c,
COUNT(
c
)
)
),
IFNA(HSTACK(i,
TOCOL(IFS(b*(e<>@a),
e&" - "&b),
2)),
"")))),
,
1)))Excel solution 7 for Count Column Cross Matches, proposed by Sunny Baggu:
=VSTACK(
A1:D1 & " Match",
IFNA(
DROP(
REDUCE(
"🌼",
A1:D1,
LAMBDA(g, h,
HSTACK(
g,
LET(
t, IF(A2:D7 = "", x, A2:D7),
_c, A1:D1 = h,
v, BYCOL(
DROP(
REDUCE(
"🤝🏻",
TOCOL(FILTER(t, _c), 3),
LAMBDA(x, y, VSTACK(x, BYCOL(N(FILTER(t, 1 - _c) = y), LAMBDA(a, SUM(TOCOL(a, 3))))))
),
1
),
LAMBDA(c, SUM(c))
),
TOCOL(IF(v, FILTER(A1:D1, 1 - _c) & " - " & v, x), 3)
)
)
)
),
,
1
),
""
)
)Excel solution 8 for Count Column Cross Matches, proposed by Sunny Baggu:
=LET(
ar,
IF(
A2:D7 = "",
x,
A2:D7
),
_k,
LAMBDA(
t,
LET(
_r1,
INDEX(
t,
1,
1
),
_r2,
INDEX(
t,
{2,
3,
4},
1
),
_r,
SORT(
TOCOL(
MAP(
_r2,
LAMBDA(
d,
LET(
_v,
SUM(
N(
TOCOL(
INDEX(
ar,
,
_r1
),
3
) = TOROW(
INDEX(
ar,
,
d
),
3
)
)
),
IF(
_v,
INDEX(
A1:D1,
,
d
) & " - " & _v,
NA()
)
)
)
),
3
)
),
_r
)
),
VSTACK(
A1:D1 & " Match",
IFNA(
HSTACK(
_k(
{1; 2; 3; 4}
),
_k(
{2; 3; 4; 1}
),
_k(
{3; 4; 1; 2}
),
_k(
{4; 1; 2; 3}
)
),
""
)
)
)Excel solution 9 for Count Column Cross Matches, proposed by Md. Zohurul Islam:
=LET(
u,
A1:D1,
v,
A2:D7,
cc,
CHOOSECOLS,
sq,
SEQUENCE(
,
COUNTA(
u
)
),
w,
IFNA(
DROP(
REDUCE(
"",
sq,
LAMBDA(
x,
y,
LET(
a,
FILTER(
sq,
sq<>y
),
b,
cc(
v,
a
),
c,
cc(
u,
a
),
d,
cc(
v,
y
),
e,
BYCOL(
b,
LAMBDA(
x,
COUNT(
XMATCH(
FILTER(
x,
x<>""
),
FILTER(
d,
d<>""
)
)
)
)
),
f,
TOCOL(
FILTER(
c&" - "&e,
e>0
)
),
g,
HSTACK(
x,
f
),
g
)
)
),
,
1
),
""
),
z,
VSTACK(
u&" Match",
w
),
z
)Excel solution 10 for Count Column Cross Matches, proposed by Hamidi Hamid:
=LET(
x,
IF(
A1:D7="",
"x",
A1:D7
),
w,
DROP(
REDUCE(
A1,
A1:D1,
LAMBDA(
a,
b,
HSTACK(
a,
TRANSPOSE(
BYCOL(
IFERROR(
MATCH(
x,
CHOOSECOLS(
$A$2:$D$7,
SEQUENCE(
,
1,
COLUMN(
b
),
)
),
0
),
""
),
LAMBDA(
a,
COUNT(
a
)
)
)
)
)
)
),
,
1
),
q,
w*IFERROR(
MOD(
SEQUENCE(
4,
4,
1,
1
)-1,
5
)^0,
0
),
z,
IF(
MAP(
q,
LAMBDA(
a,
IF(
a=0,
"",
a
)
)
),
TRANSPOSE(
A1:D1
)&"-"&MAP(
q,
LAMBDA(
a,
IF(
a=0,
"",
a
)
)
),
""
),
IFERROR(
z,
""
)
)Solving the challenge of Count Column Cross Matches with Python
Python solution 1 for Count Column Cross Matches, proposed by Konrad Gryczan, PhD:
Based on interesection of sets
import pandas as pd
path = "PQ_Challenge_204.xlsx"
input = pd.read_excel(path, usecols="A:D")
test = pd.read_excel(path, usecols="F:I", nrows=3)
def count_intersections(col_name, df):
col = df[col_name].dropna()
other_cols = df.drop(col_name, axis=1).apply(lambda x: x.dropna())
intersection_counts = other_cols.apply(lambda x: len(set(col) & set(x)))
filtered_counts = intersection_counts[intersection_counts > 0]
filtered_names = filtered_counts.index
result = [f"{name} - {count}" for name, count in zip(filtered_names, filtered_counts)]
return ", ".join(result)
result = [count_intersections(col, input) for col in input.columns]
result1 = pd.DataFrame({
"Column": [f"{col} Match" for col in input.columns],
"Intersections": result
})
result1["Intersections"] = result1["Intersections"].str.split(", ")
result1 = result1.explode("Intersections")
result1["nr"] = result1.groupby("Column").cumcount() + 1
result1 = result1.pivot(index="nr", columns="Column", values="Intersections").reset_index(drop=True)
result1.columns.name = None
print(result1.equals(test)) # True
Solving the challenge of Count Column Cross Matches with R
R solution 1 for Count Column Cross Matches, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_204.xlsx"
input = read_excel(path, range = "A1:D7")
test = read_excel(path, range = "F1:I4")
count_intersections <- function(col_name, df) {
col = df[[col_name]] %>% na.omit()
other_cols = df %>% select(-all_of(col_name)) %>% map(na.omit)
intersection_counts = other_cols %>%
map_int(~ length(intersect(col, .x)))
filtered_counts = intersection_counts[intersection_counts > 0]
filtered_names = names(filtered_counts)
map2_chr(filtered_names, filtered_counts, ~ paste(.x, "-", .y)) %>%
paste(collapse = ", ")
}
result = map_chr(names(input), ~ count_intersections(.x, input))
result1 = tibble(
Column = paste(names(input), "Match"),
Intersections = result
) %>%
separate_rows(Intersections, sep = ", ") %>%
mutate(nr = row_number(), .by = Column) %>%
pivot_wider(names_from = Column, values_from = Intersections) %>%
select(-nr)
identical(result1, test)
# [1] TRUE
R solution 2 for Count Column Cross Matches, proposed by Anil Kumar Goyal:
library(tidyverse)
library(readxl)
df <- read_excel("PQ/PQ_Challenge_204.xlsx", range = cell_cols("A:D"))
match_fun <- function(x, column){
sum(na.omit(x) %in% {{ column }}, na.rm = TRUE)
}
df |>
summarise(across(everything(), .fns = list(
COL1 = ~match_fun(., Col1),
COL2 = ~match_fun(., Col2),
COL3 = ~match_fun(., Col3),
COL4 = ~match_fun(., Col4)
))) |>
pivot_longer(
everything(),
names_sep = "_",
names_to = c(".value", "DUMMY")
) |>
mutate(across(starts_with("Col"), ~ifelse(. == 0, NA, .)),
across(starts_with("Col"), ~str_c(str_to_title(DUMMY), ., sep = " - "))) |>
mutate(across(starts_with("Col"), ~ifelse(str_detect(., cur_column()), NA, .)), .keep = "used") |>
mutate(across(everything(), ~str_sort(.))) |>
janitor::remove_empty(which = "rows")
&&