Table 1 (T1) has got customer satisfaction responses and Table 2 (T2) has got color coding of those responses. Determine the ranking of these stores on the basis of % Green Responses. Since C has highest % Green Response, hence it has been ranked 1.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 263
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Rank stores by percentage of with Power Query
Power Query solution 1 for Rank stores by percentage of, proposed by Kris Jaganah:
let
A = (x) => Excel.CurrentWorkbook(){[Name = x]}[Content],
B = Table.AddColumn(
A("Table1"),
"Green",
each List.Count(List.Select(A("Table2")[Green], (v) => v = [Responses]))
),
C = Table.Group(B, "Store", {"All", each List.Sum([Green]) / List.Count([Store])}),
D = Table.AddRankColumn(C, "Rank", {"All", 1})[[Store], [Rank]]
in
DPower Query solution 2 for Rank stores by percentage of, proposed by Aditya Kumar Darak 🇮🇳:
let
Tbl1 = Excel.CurrentWorkbook(){[Name = "_tbl1"]}[Content],
Tbl2 = Excel.CurrentWorkbook(){[Name = "_tbl2"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Tbl2, {}, "Type", "Responses"),
Join = Table.Join(Tbl1, "Responses", Unpivot, "Responses"),
Group = Table.Group(
Join,
"Store",
{"A", each Table.RowCount(Table.SelectRows(_, (f) => f[Type] = "Green")) / Table.RowCount(_)}
),
Rank = Table.AddRankColumn(Group, "Rank", {"A", 1}, [RankKind = 2])[[Store], [Rank]]
in
RankPower Query solution 3 for Rank stores by percentage of, proposed by Luan Rodrigues:
let
T2 = Table.UnpivotOtherColumns(Tabela2, {}, "Atributo", "Valor"),
T1 = Table.Join(Tabela1, "Responses", T2, "Valor"),
grp = Table.Group(
T1,
{"Store"},
{
{
"Green",
each Table.RowCount(Table.SelectRows(_, each [Atributo] = "Green")) / Table.RowCount(_)
}
}
),
res = Table.AddRankColumn(grp, "Rank", {"Green", 1})[[Store], [Rank]]
in
resPower Query solution 4 for Rank stores by percentage of, proposed by Hussein SATOUR:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AddT2 = Table.AddColumn(
Source,
"Custom",
each List.First(
Table.SelectRows(
Table.UnpivotOtherColumns(T2, {}, "Attribute", "Value"),
(x) => x[Value] = [Responses]
)[Attribute]
)
),
Parts = Table.Group(
AddT2,
{"Store"},
{
{
"Count",
each Table.RowCount(Table.SelectRows(_, each ([Custom] = "Green"))) / Table.RowCount(_)
}
}
),
Ranking = Table.AddRankColumn(Parts, "Rank", {"Count", Order.Descending})
in
RankingPower Query solution 5 for Rank stores by percentage of, proposed by Abdallah Ally:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source("Table2"), {}, "Color", "Responses"),
Join = Table.Join(Source("Table1"), "Responses", Unpivot, "Responses"),
Group = Table.Group(
Join,
"Store",
{"Green Ratio", each List.Count(List.Select([Color], (x) => x = "Green")) / List.Count([Color])}
),
Result = Table.AddIndexColumn(Table.Sort(Group, {"Green Ratio", 1}), "Rank", 1)[[Store], [Rank]]
in
ResultPower Query solution 6 for Rank stores by percentage of, proposed by Eric Laforce:
let
fxSource = (n) => Excel.CurrentWorkbook(){[Name = n]}[Content],
_GR = List.Buffer(fxSource("tData263_2")[Green]),
Source = fxSource("tData263_1"),
Group = Table.Group(
Source,
"Store",
{
"G",
each List.Count(List.Select([Responses], each List.Contains(_GR, _)))
/ List.Count([Responses])
}
),
Rank = Table.AddRankColumn(
Group,
"Rank",
{"G", Order.Descending},
[RankKind = RankKind.Competition]
)[[Store], [Rank]]
in
RankPower Query solution 7 for Rank stores by percentage of, proposed by Meganathan Elumalai:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source("Table2"), {}, "A", "V"),
Lookup = Table.Join(Source("Table1"), "Responses", Unpivot, "V", 1),
Group = Table.Sort(
Table.Group(
Lookup,
"Store",
{{"New", each Table.RowCount(Table.SelectRows(_, (f) => f[A] = "Green")) / Table.RowCount(_)}}
),
{"New", 1}
),
Rank = Table.FromColumns(
{Group[Store], List.Transform(Group[New], each List.PositionOf(Group[New], _) + 1)},
{"Store", "Rank"}
)
in
RankPower Query solution 8 for Rank stores by percentage of, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
q = Table.NestedJoin(Source, {"Responses"}, Table2, {"Value"}, "Table2", JoinKind.LeftOuter),
w = Table.ExpandTableColumn(q, "Table2", {"Attribute"}, {"Attribute"}),
x = Table.Group(w, {"Store", "Attribute"}, {{"Count", each Table.RowCount(_)}}),
y = Table.SelectRows(x, each ([Attribute] = "Green")),
z = Table.AddColumn(
y,
"Custom",
each [Tot = Table.Group(y, {"Attribute"}, {{"Sum", each List.Sum([Count])}})]
),
a = Table.ExpandRecordColumn(z, "Custom", {"Tot"}, {"Tot"}),
b = Table.ExpandTableColumn(a, "Tot", {"Sum"}, {"Sum"}),
c = Table.AddColumn(b, "Custom", each [Perc = [Count] / [Sum]]),
d = Table.ExpandRecordColumn(c, "Custom", {"Perc"}, {"Perc"}),
f = Table.AddRankColumn(d, "Rank", {"Perc", Order.Descending}),
Result = Table.RemoveColumns(f, {"Attribute", "Count", "Sum", "Perc"})
in
ResultPower Query solution 9 for Rank stores by percentage of, proposed by Peter Krkos:
let
MergedQueries = Table.NestedJoin(T1, {"Responses"}, T2, {"Green"}, "T2", JoinKind.LeftOuter),
ExpandedGreen = Table.ExpandTableColumn(MergedQueries, "T2", {"Green"}, {"Green"}),
Grouped = Table.Group(
ExpandedGreen,
{"Store"},
{
{
"T",
each Table.RowCount(Table.SelectRows(_, (x) => x[Green] <> null)) / Table.RowCount(_),
type number
}
}
),
Ad_Rank = Table.AddColumn(Grouped, "Rank", each List.PositionOf(Grouped[T], [T]) + 1, Int64.Type)[
[Store],
[Rank]
]
in
Ad_RankPower Query solution 10 for Rank stores by percentage of, proposed by Erdit Qendro:
let
Source = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
S = Table.AddColumn(Source, "GreenOrNot", each List.ContainsAny(T2[Green], {_[Responses]})),
O = Table.Group(S, {"Store", "GreenOrNot"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
L = Table.TransformColumnTypes(O, {{"GreenOrNot", type text}}),
U = Table.Pivot(L, List.Distinct(L[GreenOrNot]), "GreenOrNot", "Count", List.Sum),
T = Table.AddColumn(U, "%", each Value.Divide([true], [true] + [false])),
I = Table.AddRankColumn(T, "Rank", {"%", Order.Descending}, [RankKind = RankKind.Competition]),
ON = Table.SelectColumns(I, {"Store", "Rank"})
in
ONPower Query solution 11 for Rank stores by percentage of, proposed by Krupesh Bhansali:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Table2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
UnpivotTable2 = Table.UnpivotOtherColumns(Table2, {}, "Colour", "Value"),
#"Merged Queries" = Table.NestedJoin(
Source,
{"Responses"},
UnpivotTable2,
{"Value"},
"UnpivotTable2",
JoinKind.LeftOuter
),
#"Expanded UnpivotTable2" = Table.ExpandTableColumn(
#"Merged Queries",
"UnpivotTable2",
{"Colour"},
{"Colour"}
),
CountwithColur = Table.Group(
#"Expanded UnpivotTable2",
{"Store"},
{
{
"Count",
each Table.RowCount(Table.SelectRows(_, each [Colour] = "Green")) / Table.RowCount(_)
}
}
),
#"Sorted Rows" = Table.Sort(CountwithColur, {{"Count", Order.Descending}}),
Rank = Table.AddIndexColumn(#"Sorted Rows", "Rank", 1, 1, Int64.Type)[[Store], [Rank]]
in
RankSolving the challenge of Rank stores by percentage of with Excel
Excel solution 1 for Rank stores by percentage of, proposed by Bo Rydobon 🇹🇭:
=LET(
g,
GROUPBY(
A2:A132,
XMATCH(
B2:B132,
D2:D7
),
LAMBDA(
x,
-COUNT(
x
)/ROWS(
x
)
),
,
0,
2
),
n,
DROP(
g,
,
1
),
HSTACK(
TAKE(
g,
,
1
),
MATCH(
n,
UNIQUE(
n
)
)
)
)
=LET(
r,
D2:F7,
c,
XLOOKUP(
B2:B132,
TOCOL(
r
),
TOCOL(
IFNA(
D1:F1,
r
)
)
),
g,
GROUPBY(
HSTACK(
A2:A132,
c
),
c,
ROWS,
,
2
),
w,
WRAPROWS(
DROP(
g,
-1,
2
),
4
),
n,
-TAKE(
w,
,
1
)/DROP(
w,
,
3
),
SORT(
HSTACK(
UNIQUE(
DROP(
g,
-1,
-2
)
),
XMATCH(
n,
SORT(
UNIQUE(
n
)
)
)
),
2
)
)Excel solution 2 for Rank stores by percentage of, proposed by Rick Rothstein:
=LET(
r,
A2:.A999,
u,
UNIQUE(
r
),
g,
D2:D7,
p,
MAP(
u,
LAMBDA(
x,
SUM(
COUNTIFS(
r,
x,
OFFSET(
r,
,
1
),
g
)
)/COUNTIF(
r,
x
)
)
),
HSTACK(
SORTBY(
u,
p,
-1
),
XMATCH(
p,
p
)
)
)Excel solution 3 for Rank stores by percentage of, proposed by Kris Jaganah:
=LET(
a,
A2:A132,
b,
--XLOOKUP(
B2:B132,
D2:D7,
C2:C7&1,
0
),
c,
UNIQUE(
a
),
d,
-MAP(
c,
LAMBDA(
x,
SUM(
(
a=x
)*b
)/SUM(
N(
a=x
)
)
)
),
HSTACK(
SORTBY(
c,
d
),
XMATCH(
d,
d,
,
2
)
)
)Excel solution 4 for Rank stores by percentage of, proposed by Oscar Mendez Roca Farell:
=LET(
g,
GROUPBY(
A2:A132,
COUNTIF(
D2:D7,
B2:B132
),
AVERAGE,
,
0,
-2
),
p,
DROP(
g,
,
1
),
HSTACK(
TAKE(
g,
,
1
),
XMATCH(
p,
p
)
)
)
Or altenatively:
=LET(
a,
A2:A132,
t,
UNIQUE(
a
),
p,
BYCOL(
COUNTIFS(
a,
TOROW(
t
),
B2:B132,
D2:D7
),
SUM
)/BYCOL(
COUNTIF(
a,
t
),
SUM
),
HSTACK(
t,
TOCOL(
XMATCH(
p,
p
)
)
)
)Excel solution 5 for Rank stores by percentage of, proposed by Duy Tùng:
=LET(
a,
GROUPBY(
A2:A132,
MATCH(
B2:B132,
D2:D7,
),
LAMBDA(
x,
COUNT(
x
)/ROWS(
x
)
),
,
0,
-2
),
b,
DROP(
a,
,
1
),
HSTACK(
TAKE(
a,
,
1
),
MAP(
b,
LAMBDA(
v,
SUM(
N(
b>v
)
)
)
)+1
)
)Excel solution 6 for Rank stores by percentage of, proposed by Sunny Baggu:
=LET(
_a,
MAP(
B2:B132,
LAMBDA(
b,
FILTER(
D1:F1,
BYCOL(
b = D2:F7,
LAMBDA(
a,
OR(
a
)
)
)
)
)
),
_u,
UNIQUE(
A2:A132
),
_b,
MAP(
_u,
LAMBDA(b,
SUM((_a = D1) * (A2:A132 = b)) / SUM(--(A2:A132 = b)))
),
_c,
SORT(
UNIQUE(
_b
),
,
-1
),
_d,
XMATCH(
_b,
_c
),
SORTBY(
HSTACK(
_u,
_d
),
_d,
1
)
)Excel solution 7 for Rank stores by percentage of, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A132,
v,
B2:B132,
w,
D2:D7,
hdr,
{"Store",
"Rank"},
z,
UNIQUE(
u
),
a,
MAP(
v,
LAMBDA(
x,
SUM(
ABS(
w=x
)
)
)
),
b,
MAP(
z,
LAMBDA(
x,
LET(
p,
ABS(
u=x
),
SUM(
a*p
)/SUM(
p
)
)
)
),
c,
SORT(
HSTACK(
z,
XMATCH(
b,
b
)
),
2,
1
),
d,
VSTACK(
hdr,
c
),
d
)Excel solution 8 for Rank stores by percentage of, proposed by Pieter de B.:
=LET(
a,
B2:B132,
g,
GROUPBY(
A2:A132,
COUNTIF(
D2:D7,
a
),
LAMBDA(
x,
SUM(
x
)/ROWS(
x
)&
),
,
0,
-2
),
p,
DROP(
g,
,
1
),
HSTACK(
TAKE(
g,
,
1
),
XMATCH(
p,
p
)
)
)Excel solution 9 for Rank stores by percentage of, proposed by Asheesh Pahwa:
=LET(
s,
A2:A132,
r,
B2:B132,
u,
UNIQUE(
s
),
d,
DROP(
REDUCE(
"",
u,
LAMBDA(
y,
x,
VSTACK(
y,
LET(
f,
FILTER(
r,
s=x
),
m,
--ISNUMBER(
XMATCH(
f,
D2:D7
)
),
HSTACK(
x,
SUM(
m
)/COUNT(
m
)
)
)
)
)
),
1
),
t,
TAKE(
d,
,
-1
),
HSTACK(
TAKE(
d,
,
1
),
XMATCH(
t,
t
)
)
)Excel solution 10 for Rank stores by percentage of, proposed by Asheesh Pahwa:
=LET(
s,
A2:A132,
r,
B2:B132,
u,
UNIQUE(
s
),
d,
DROP(
REDUCE(
"",
u,
LAMBDA(
y,
x,
VSTACK(
y,
LET(
f,
FILTER(
r,
s=x
),
m,
--ISNUMBER(
XMATCH(
f,
D2:D7
)
),
HSTACK(
x,
SUM(
m
)/COUNT(
m
)
)
)
)
)
),
1
),
t,
TAKE(
d,
,
-1
),
_u,
SORT(
UNIQUE(
t
),
,
-1
),
sq,
SEQUENCE(
ROWS(
_u
)
),
h,
HSTACK(
TAKE(
d,
,
1
),
XLOOKUP(
t,
_u,
sq
)
),
h
)Excel solution 11 for Rank stores by percentage of, proposed by Imam Hambali:
=LET(
d,
XLOOKUP(
B2:B132,
D2:D7,
IF(
D2:D7>0,
"G"
),
"O"
),
p,
DROP(
PIVOTBY(
A2:A132,
d,
d,
COUNTA,
0,
0
),
1
),
cc,
CHOOSECOLS,
pc,
cc(
p,
2
)/cc(
p,
-1
),
pcs,
SORT(
pc,
1,
-1
),
VSTACK(
{"Store",
"Rank"},
HSTACK(
SORTBY(
cc(
p,
1
),
pc,
-1
),
XMATCH(
pcs,
pcs
)
)
)
)Excel solution 12 for Rank stores by percentage of, proposed by Dominic Walsh:
=LET(
a,
A2:A132,
b,
B2:B132,
c,
D2:D7,
d,
COUNTIF(
c,
b
),
e,
GROUPBY(
a,
d,
AVERAGE,
,
0,
-2
),
HSTACK(
TAKE(
e,
,
1
),
XMATCH(
DROP(
e,
,
1
),
DROP(
e,
,
1
)
)
)
)Solving the challenge of Rank stores by percentage of with Python
Python solution 1 for Rank stores by percentage of, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_263.xlsx"
input1 = pd.read_excel(path, sheet_name=0, usecols="A:B", nrows=131)
input2 = pd.read_excel(path, sheet_name=0, usecols="D:F", nrows=6)
test = pd.read_excel(path, sheet_name=0, usecols="D:E", skiprows=11, nrows=5)
input2_long = input2.melt(var_name="Attitude", value_name="Response").dropna()
result = input2_long.merge(input1, left_on="Response", right_on="Responses", how="left")
result = result.groupby(['Store', 'Attitude']).size().reset_index(name='count')
result['Rank'] = result.groupby('Attitude')['count'].rank(method='dense', ascending=False)
result = result[result['Attitude'] == 'Green'][['Store', 'Rank']].sort_values(by = "Rank").reset_index(drop=True)
print(result)
Python solution 2 for Rank stores by percentage of, proposed by Luan Rodrigues:
import pandas as pd
file = "PQ_Challenge_263.xlsx"
df1 = pd.read_excel(file,usecols="A:B")
df2 = pd.read_excel(file,usecols="D:F",nrows=6)
df2 = pd.melt(df2,id_vars=[],var_name='Cor',value_name='Responses' ).dropna()
merge = pd.merge(df1,df2,on='Responses')
grp = merge.groupby('Store').agg(
total=pd.NamedAgg(column='Cor', aggfunc='count'),
green=pd.NamedAgg(column='Cor', aggfunc=lambda x: x[x == 'Green'].count())
)
grp['Rank'] = grp['green'] / grp['total']
grp = grp.sort_values(by='Rank',ascending=0).reset_index()
grp['Rank'] = grp['Rank'].rank(ascending=0).map(int)
print(grp[['Store','Rank']])
Python solution 3 for Rank stores by percentage of, proposed by Abdallah Ally:
import pandas as pd
# Load data from Excel
file_path = 'PQ_Challenge_263.xlsx'
df1 = pd.read_excel(io=file_path, usecols='A:B')
df2 = pd.read_excel(io=file_path, usecols='D:F', nrows=6)
# Perform data manipulation
df2 = df2.melt(value_vars=df2.columns, var_name='Color', value_name='Responses').dropna()
df = (
df1.merge(df2, on='Responses', how='inner')
.groupby('Store')['Color']
.agg(lambda x: sum(x == 'Green')/len(x))
.rename('GreenRatio')
.reset_index()
.assign(Rank = lambda df: df.GreenRatio.rank(method='first', ascending=False).map(int))
.sort_values(by='Rank', ignore_index=True)[['Store', 'Rank']]
)
df
Solving the challenge of Rank stores by percentage of with Python in Excel
Python in Excel solution 1 for Rank stores by percentage of, proposed by Alejandro Campos:
df1, df2 = xl("A1:B132", headers=True), xl("D1:F7", headers=True)
ranking = (df1['Store']
.where(df1['Responses'].isin(df2['Green'].dropna()), None)
.value_counts() / df1['Store'].value_counts() * 100
).sort_values(ascending=False).reset_index()
ranking.columns = ['Store', 'Green_Percentage']
ranking['Rank'] = ranking['Green_Percentage'].rank(method='min', ascending=False).astype(int)
ranking
Solving the challenge of Rank stores by percentage of with R
R solution 1 for Rank stores by percentage of, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_263.xlsx"
input1 = read_excel(path, range = "A1:B132")
input2 = read_excel(path, range = "D1:F7")
test = read_excel(path, range = "D12:E16")
result = input2 %>%
pivot_longer(everything(), names_to = "Attitude", values_to = "Response", values_drop_na = TRUE) %>%
left_join(input1, by = c("Response" = "Responses")) %>%
summarise(count = n(), .by = c(Store, Attitude)) %>%
mutate(Rank = dense_rank(desc(count)), .by = Attitude) %>%
filter(Attitude == "Green" ) %>%
select(Store, Rank)
&&