Coordinates for points are given in x, y format. Populate Yes if points are connected otherwise No. Points are connected if y value of a point is same as x value of next point for all points. For first point’s x and for last point’s y, this rule will not be applicable. Ex. (3, 4), (4, 8), (8,10) => These 3 points are connected as x=4 and y=4 are same for first two points and x=8 and y=8 are same for last two points.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 395
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Validate Point Connectivity Rule with Power Query
Power Query solution 1 for Validate Point Connectivity Rule, proposed by John V.:
let
S = Excel.CurrentWorkbook(){0}[Content],
T = List.Transform,
R = Table.AddColumn(S, "R", each
let
a = T(List.RemoveNulls(Record.ToList(_)), each Text.Split(_, ", ")),
b = T({0..List.Count(a) - 2}, each a{_}{1} = a{1 + _}{0})
in
if List.AllTrue(b) then "Yes" else "No"
)[[R]]
in
R
Blessings!
Power Query solution 2 for Validate Point Connectivity Rule, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.TransformRows(
Source,
each
let
l = List.Combine(
List.Transform(List.RemoveNulls(Record.ToList(_)), each Text.Split(_, ", "))
)
in
{"No", "Yes"}{
Number.From(
List.Accumulate(
List.RemoveLastN(List.Positions(l), 2),
true,
(s, c) => s and (Number.IsEven(c) or (l{c} = l{c + 1}))
)
)
}
)
in
SPower Query solution 3 for Validate Point Connectivity Rule, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each
let
a = Record.ToList(_),
b = List.Transform(a, each try Text.Split(_, ", ") otherwise {}),
c = List.Combine(List.Select(b, each not List.IsEmpty(_))),
d = List.RemoveLastN(List.Skip(c)),
e = List.Split(d, 2),
f = List.Transform(e, each _{0} = _{1}),
g = if List.AllTrue(f) then "Yes" else "No"
in
g
)[[Answer]]
in
SolPower Query solution 4 for Validate Point Connectivity Rule, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each [
a = List.TransformMany(
Record.FieldValues(_),
each try Text.Split(_, ", ") otherwise {null},
(a, b) => b
),
b = List.AnyTrue(
List.Transform(
List.Split(List.RemoveLastN(List.RemoveFirstN(List.RemoveNulls(a))), 2),
List.IsDistinct
)
),
c = if b then "No" else "Yes"
][c]
)
in
resPower Query solution 5 for Validate Point Connectivity Rule, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
a = List.Transform(Table.ToRows(S), each Text.Combine(_, ", ")),
b = List.Transform(a, each List.RemoveLastN(List.RemoveFirstN(Text.Split(_, ", ")))),
c = List.Transform(b, each Table.FromColumns({_})),
d = List.Transform(
List.Transform(c, each Table.Group(_, {"Column1"}, {{"G", each _}})),
each [[G]]
),
e = List.Transform(d, each Table.TransformColumns(_, {"G", each Table.RowCount(_)})[G]),
f = List.Transform(e, each if List.Contains(_, 1) then "No" else "Yes"),
Sol = Table.FromColumns(Table.ToColumns(S) & {f}, Table.ColumnNames(S) & {"Answer Expected"})
in
SolPower Query solution 6 for Validate Point Connectivity Rule, proposed by Rafael González B.:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.AddColumn(Source, "Answer Expected", each
let
Rec = Record.ToList(_),
Delete = List.RemoveNulls(Rec),
Split = List.Transform(Delete, each Text.Split(_,", ")),
Count = List.Count(Split)-1,
LA = List.Accumulate(
{0..Count},
{true},
(s,c) =>
let
a = try Split{c}{1} = Split{c + 1}{0} otherwise true,
b = s & {a}
in
b )
in
if List.AllTrue(LA) then "Yes" else "No"
)[[Answer Expected]]
in
Result
🧙♂️🧙♂️🧙♂️
Power Query solution 7 for Validate Point Connectivity Rule, proposed by Glyn Willis:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"Coord1", type text},
{"Coord2", type text},
{"Coord3", type text},
{"Coord4", type text},
{"Answer Expected", type text}
}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each
let
l = List.RemoveMatchingItems(
Text.Split(Text.Combine({[Coord1], ", ", [Coord2], ", ", [Coord3], ", ", [Coord4]}), ", "),
{""}
)
in
if List.AllTrue(
List.Transform(
List.Zip(
{
List.RemoveFirstN(List.Alternate(l, 1, 1, 1), 1),
List.RemoveLastN(List.Alternate(l, 1, 1), 1)
}
),
(x) => Number.From(Text.Trim(x{0})) = Number.From(Text.Trim(x{1}))
)
)
then
"Yes"
else
"No"
)
in
#"Added Custom"Solving the challenge of Validate Point Connectivity Rule with Excel
Excel solution 1 for Validate Point Connectivity Rule, proposed by Bo Rydobon 🇹🇭:
=BYROW(
A2:D8,
LAMBDA(
a,
LET(
t,
TEXTSPLIT(
TEXTJOIN(
"_",
,
a
),
", ",
"_"
),
IF(
AND(
DROP(
t,
-1,
1
)=DROP(
t,
1,
-1
)
),
"Yes",
"No"
)
)
)
)Excel solution 2 for Validate Point Connectivity Rule, proposed by Rick Rothstein:
=BYROW(
A2:D8,
LAMBDA(
r,
LET(
s,
SUM(
IFERROR(
DROP(
TEXTAFTER(
r,
",",
,
,
,
0
),
,
-1
)/DROP(
TEXTBEFORE(
r,
",",
-1,
,
,
0
),
,
1
),
)
),
IF(
s=INT(
s
),
"Yes",
"No"
)
)
)
)Excel solution 3 for Validate Point Connectivity Rule, proposed by Rick Rothstein:
=BYROW(
A2:D8,
LAMBDA(
x,
LET(
d,
DROP(
DROP(
TEXTSPLIT(
CONCAT(
x
),
", "
),
,
1
),
,
-1
),
w,
LEN(
d
)/2,
IF(
SUM(
LEFT(
d,
w
)-RIGHT(
d,
w
)
),
"No",
"Yes"
)
)
)
)Excel solution 4 for Validate Point Connectivity Rule, proposed by John V.:
=IF(BYROW(IFNA(TEXTAFTER(A2:C8,", ")=TEXTBEFORE(B2:D8,","),""),AND),"Yes","No")Excel solution 5 for Validate Point Connectivity Rule, proposed by محمد حلمي:
=BYROW(
A2:D8,
LAMBDA(
a,
LET(
i,
TOCOL(
a,
1
),
IF(
AND(
DROP(
TEXTSPLIT(
i,
","
),
1
)=DROP(
TEXTAFTER(
i,
" "
),
-1
)
),
"Yes",
"No"
)
)
)
)Excel solution 6 for Validate Point Connectivity Rule, proposed by 🇰🇷 Taeyong Shin:
=LET(
f,
LAMBDA(
fn,
i,
x,
fn(
DROP(
TOCOL(
x,
1
),
i
),
", "
)
),
func,
LAMBDA(
x,
IF(
AND(
f(
TEXTBEFORE,
1,
x
)=f(
TEXTAFTER,
-1,
x
)
),
"Yes",
"No"
)
),
BYROW(
A2:D8,
func
)
)Excel solution 7 for Validate Point Connectivity Rule, proposed by Kris Jaganah:
=BYROW(
A2:D8,
LAMBDA(
x,
LET(
a,
BYROW(
WRAPCOLS(
DROP(
DROP(
TEXTSPLIT(
ARRAYTOTEXT(
x
),
,
", ",
1
),
1
),
-1
),
2
),
CONCAT
),
IF(
@a=DROP(
a,
1
),
"Yes",
"No"
)
)
)
)Excel solution 8 for Validate Point Connectivity Rule, proposed by Julian Poeltl:
=BYROW(
A2:D8,
LAMBDA(
ARR,
IF(
COUNT(
--UNIQUE(
IFERROR(
DROP(
TEXTBEFORE(
ARR,
","
),
,
1
)=DROP(
TEXTAFTER(
ARR,
", "
),
,
-1
),
""
),
TRUE
)
)=1,
"Yes",
"No"
)
)
)Excel solution 9 for Validate Point Connectivity Rule, proposed by Timothée BLIOT:
=BYROW(
A2:D8,
LAMBDA(
z,
LET(
A,
--TEXTSPLIT(
TEXTJOIN(
"|",
,
z
),
", ",
"|",
1
),
IF(
PRODUCT(
--MAP(
SEQUENCE(
ROWS(
A
)-1
),
LAMBDA(
x,
INDEX(
A,
x+1,
1
)=INDEX(
A,
x,
2
)
)
)
),
"Yes",
"No"
)
)
)
)Excel solution 10 for Validate Point Connectivity Rule, proposed by Oscar Mendez Roca Farell:
=BYROW(A2:D8, LAMBDA(r, LET(_t, DROP(DROP(TEXTSPLIT(CONCAT(r&"|"), "|", ", ", 1), 1), -1), IF(AND(TAKE(_t, ,1)=DROP(_t, ,1)), "Yes", "No"))))Excel solution 11 for Validate Point Connectivity Rule, proposed by Sunny Baggu:
=IF(
MAP(
BYROW(
A2:D8,
LAMBDA(
a,
TEXTJOIN(
", ",
,
a
)
)
),
LAMBDA(
x,
LET(
_a,
WRAPROWS(
TEXTSPLIT(
TEXTJOIN(
", ",
,
x
),
,
", "
),
2
),
AND(
DROP(
_a,
1,
-1
) = DROP(
TAKE(
_a,
,
-1
),
-1
)
)
)
)
),
"Yes",
"No"
)Excel solution 12 for Validate Point Connectivity Rule, proposed by LEONARD OCHEA 🇷🇴:
=IF(BYROW(IFNA(DROP(TEXTBEFORE(A2:D8,","),,1)=DROP(TEXTAFTER(A2:D8," "),,-1),1),AND),"Yes","No")Excel solution 13 for Validate Point Connectivity Ru&le, proposed by Abdallah Ally:
=BYROW(
A2:D8,
LAMBDA(
x,
LET(
a,
TOROW(
x,
1
),
b,
AND(
TEXTAFTER(
DROP(
a,
,
-1
),
", "
)=TEXTBEFORE(
DROP(
a,
,
1
),
", "
)
),
IF(
b,
"Yes",
"No"
)
)
)
)Excel solution 14 for Validate Point Connectivity Rule, proposed by 🇵🇪 Ned Navarrete C.:
=BYROW(
A2:D8,
LAMBDA(
r,
LET(
i,
TEXTSPLIT(
TEXTJOIN(
"*",
,
r
),
", ",
"*"
),
f,
{1,
-1},
j,
TAKE(
i,
f,
f
),
c,
SUM(
i*f,
j*f*-1
),
IF(
c,
"No",
"Yes"
)
)
)
)Excel solution 15 for Validate Point Connectivity Rule, proposed by Pieter de B.:
=IF(MMULT(--IFERROR(--TEXTAFTER(A2:C8,",")=--TEXTSPLIT(B2:D8,","),1),{1;1;1})=3,"Yes","No")Excel solution 16 for Validate Point Connectivity Rule, proposed by Thang Van:
=MAP(
BYROW(
A2:D8,
LAMBDA(
x,
TEXTJOIN(
", ",
,
x
)
)
),
LAMBDA(
_each,
LET(
a,
WRAPROWS(
TEXTSPLIT(
_each,
", "
),
2
),
IF(
AND(
DROP(
a,
1,
-1
)=DROP(
a,
-1,
1
)
),
"YES",
"NO"
)
)
)
)Excel solution 17 for Validate Point Connectivity Rule, proposed by Charles Roldan:
=LET(
Spl,
LAMBDA(
x,
TEXTSPLIT(
TEXTJOIN(
", ",
TRUE,
x
),
,
", "
)
),
Pair,
LAMBDA(
x,
WRAPROWS(
DROP(
DROP(
x,
1
),
-1
),
2
)
),
Same,
LAMBDA(
x,
INDEX(
x,
1
) = INDEX(
x,
2
)
),
Out,
LAMBDA(
x,
IF(
x,
"Yes",
"No"
)
),
Mp,
LAMBDA(
f,
LAMBDA(
x,
BYROW(
x,
f
)
)
),
And,
LAMBDA(
x,
AND(
x
)
),
B,
LAMBDA(
f,
LAMBDA(
g,
LAMBDA(
x,
f(
g(
x
)
)
)
)
),
B(
Out
)(Mp(B(
And
)(B(
Mp(
Same
)
)(B(
Pair
)(Spl)))))
)(A2:D8)Excel solution 18 for Validate Point Connectivity Rule, proposed by Andy Heybruch:
=BYROW(A2:D8,
LAMBDA(v,
LET(
a,
TEXTSPLIT(
TEXTJOIN(
"|",
1,
v
),
",",
"|"
),
x,
--DROP(
TAKE(
a,
,
1
),
1
),
y,
--DROP(
TAKE(
a,
,
-1
),
-1
),
IF(PRODUCT(--(x=y))=1,
"Yes",
"No"))))Excel solution 19 for Validate Point Connectivity Rule, proposed by JvdV –:
=IF(BYROW(IFNA(TEXTBEFORE(B2:D8,",")=TEXTAFTER(A2:C8," "),1),AND),"Yes","No")Excel solution 20 for Validate Point Connectivity Rule, proposed by Nicolas Micot:
=SI(
JOINDRE.TEXTE(
", ";
VRAI;
TEXTE.APRES(
DECALER(
A2;
0;
0;
1;
NBVAL(
A2:D2
)-1
);
", "
)
) = JOINDRE.TEXTE(
", ";
VRAI;
TEXTE.AVANT(
DECALER(
B2;
0;
0;
1;
NBVAL(
A2:D2
)-1
);
", "
)
);
"Yes";
"No"
)Excel solution 21 for Validate Point Connectivity Rule, proposed by Anup Kumar:
=BYROW(
A2:D8,
LAMBDA(
rng,
LET(
cor,
TEXTSPLIT(
TEXTJOIN(
", ",
TRUE,
rng
),
", "
),
chk,
INDEX(
cor,
1,
SEQUENCE(
COUNTA(
cor
)/2-1,
,
2,
2
)
)=INDEX(
cor,
1,
SEQUENCE(
COUNTA(
cor
)/2-1,
,
3,
2
)
),
IF(
AND(
chk
),
"Yes",
"No"
)
)
)
)Excel solution 22 for Validate Point Connectivity Rule, proposed by Diarmuid Early:
=IF(
AND(
DROP(
TOROW(
TEXTBEFORE(
A2:D2,
","
),
3
),
,
1
)=
DROP(
TOROW(
TEXTAFTER(
A2:D2,
" "
),
3
),
,
-1
)
),
"Yes",
"No"
)
* TEXTBEFORE / AFTER extracts the first / last coordinate (before a comma or after a space),
and throws an error for empty cells.
* TOROW with optional second argument drops any errors (so the same formula can deal with rows with different numbers of entries).
* DROP removes the first or last argument,
so we get the list of 'in between' coordinates to compare.
* AND returns true if all the comparisons match,
and false otherwise.
For all the rows in one formula,
you can turn this into a LAMBDA and use BYROW:
=BYROW(
A2:D8,
LAMBDA(
rw,
IF(
AND(
DROP(
TOROW(
TEXTBEFORE(
rw,
","
),
3
),
,
1
)=
DROP(
TOROW(
TEXTAFTER(
rw,
" "
),
3
),
,
-1
)
),
"Yes",
"No"
)
)
)Excel solution 23 for Validate Point Connectivity Rule, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=IF(
BYROW(
A2:D8,
LAMBDA(
f,
AND(
DROP(
TOROW(
TEXTBEFORE(
f,
","
),
2
),
,
1
)=DROP(
TOROW(
TEXTAFTER(
f,
", "
),
2
),
,
-1
)
)
)
),
"Yes",
"No"
)Excel solution 24 for Validate Point Connectivity Rule, proposed by Josh Brodrick:
=LET(
array,
A2:D2,
firstline,
MAP(
array,
LAMBDA(
x,
TEXTBEFORE(
x,
", "
)
)
),
secondline,
MAP(
array,
LAMBDA(
x,
TEXTAFTER(
x,
", "
)
)
),
newshape,
VSTACK(
DROP(
firstline,
,
1
),
DROP(
secondline,
,
-1
)
),
crosscheck,
IFNA(
IF(
DROP(
newshape,
-1
)=DROP(
newshape,
1
),
"Yes",
"No"
),
""
),
IFNA(
IF(
XMATCH(
"No",
crosscheck,
0
)>0,
"No"
),
"Yes"
)
)Excel solution 25 for Validate Point Connectivity Rule, proposed by Tyler Cameron:
=LET(
a,
A2:D8,
b,
IF(
IFNA(
DROP(
TEXTBEFORE(
a,
", "
),
,
1
)=DROP(
TEXTAFTER(
a,
", "
),
,
-1
),
TRUE
),
1,
0
),
BYROW(
b,
LAMBDA(
x,
IF(
SUM(
x
)=3,
"Yes",
"No"
)
)
)
)Excel solution 26 for Validate Point Connectivity Rule, proposed by Behrooz Kazeminik:
=AND(IFERROR((LEFT(B1:D1,(LEN(B1:D1)-1)/2)=RIGHT(A1:D1,(LEN(A1:D1)-1)/2)),TRUE))Solving the challenge of Validate Point Connectivity Rule with Python in Excel
Python in Excel solution 1 for Validate Point Connectivity Rule, proposed by Abdallah Ally:
import pandas as pd
file_path = 'Excel_Challenge_395 - Connected Points.xlsx'
df = pd.read_excel(file_path)
def connected_points(col1, col2, col3, col4):
values = [x for x in (col1, col2, col3, col4) if pd.notnull(x)]
value, step = True, 0
while step < len(values) - 1:
value = value and values[step].split(', ')[1] == values[step + 1].split(', ')[0]
step += 1
return 'Yes' if value else 'No'
df['My Answer'] = df.apply(lambda x: connected_points(x['Coord1'], x['Coord2'], x['Coord3'], x['Coord4']), axis=1)
print(df)
https://github.com/mathematiciantz/Excel_BI_Challenges/blob/main/Excel_Challenge_395_Connected_Points.py
Solving the challenge of Validate Point Connectivity Rule with R
R solution 1 for Validate Point Connectivity Rule, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Excel/395 Connected Points.xlsx", range = "A1:D8")
test = read_excel("Excel/395 Connected Points.xlsx", range = "E1:E8")
result = input %>%
mutate(figure = row_number()) %>%
pivot_longer(-figure, names_to = "Coord", values_to = "value") %>%
separate(value, into = c("x", "y"), sep = ", ") %>%
group_by(figure) %>%
mutate(is_connected = ifelse(x == lag(y), "Yes", "No")) %>%
na.omit(is_connected) %>%
summarise(`Answer Expected` = ifelse(all(is_connected == "Yes"), "Yes", "No")) %>%
select(-figure) %>%
ungroup()
Solving the challenge of Validate Point Connectivity Rule with DAX
DAX solution 1 for Validate Point Connectivity Rule, proposed by Zoran Milokanović:
EVALUATE
ADDCOLUMNS(Input, "Answer Expected",
VAR C1 = Input[Coord1] VAR P1 = SEARCH(",", C1, 1, 0) VAR S1 = MID(C1, P1 + 2, LEN(C1))
VAR C2 = Input[Coord2] VAR P2 = SEARCH(",", C2, 1, 0) VAR F2 = MID(C2, 1, P2 - 1) VAR S2 = MID(C2, P2 + 2, LEN(C2))
VAR C3 = Input[Coord3] VAR P3 = SEARCH(",", C3, 1, 0) VAR F3 = MID(C3, 1, P3 - 1) VAR S3 = MID(C3, P3 + 2, LEN(C3))
VAR C4 = Input[Coord4] VAR P4 = SEARCH(",", C4, 1, 0) VAR F4 = MID(C4, 1, P4 - 1)
RETURN IF((ISBLANK(C4) || F4 = S3) && (ISBLANK(C3) || F3 = S2) && (ISBLANK(C2) || F2 = S1) , "Yes", "No")
)
&&