From tables T1 and T2, generate the result table. Here, all Yes are aligned first and then No. %age column is %age for Yes and No respectively.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 205
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Pivot Yes No Percentages with Power Query
Power Query solution 1 for Pivot Yes No Percentages, proposed by Zoran Milokanović:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T = Table.FromColumns(
{Source("Table1")[Value]} & Table.ToColumns(Source("Table2")),
{"V", "I", "F"}
),
S = Table.FromRows(
List.TransformMany(
List.Distinct({"Yes", "No"}),
each
let
t = Table.SelectRows(T, (r) => r[F] = _)
in
List.TransformMany(
List.Split(t[I], 2),
each {List.FirstN(_ & {null}, 2)},
(i, _) =>
let
t2 = Table.SelectRows(T, (r) => List.Contains(_, r[I])),
s = each List.Sum([V])
in
{t[F]{0}} & _ & {s(t2)} & {s(t2) / s(t)}
),
(i, _) => _
),
{"YesNo", "Item1", "Item2", "Sum", "%age"}
)
in
SPower Query solution 2 for Pivot Yes No Percentages, proposed by Kris Jaganah:
let
Source = each Excel.CurrentWorkbook(){[Name = _]}[Content],
T2 = Source("Table2"),
Grp = Table.Group(
T2,
{"YesNo"},
{
"All",
each Table.AddColumn(
Table.AddIndexColumn(_, "Index", 1, 1),
"It",
each "Item" & Text.From(Number.Mod([Index] - 1, 2) + 1)
)
}
),
Xpan = Table.ExpandTableColumn(Grp, "All", {"Item", "Index", "It"}),
Roun = Table.TransformColumns(Xpan, {"Index", each Number.RoundUp(_ / 2)}),
Pivot = Table.Pivot(Roun, List.Distinct(Roun[It]), "It", "Item"),
Sort = Table.Sort(Pivot, {{"YesNo", 1}, {"Index", 0}}),
Tot = Table.AddColumn(
Sort,
"Sum",
each List.Sum(
Table.SelectRows(Source("Table1"), (x) => x[Item] = [Item1] or x[Item] = [Item2])[Value]
)
),
Per = Table.AddColumn(
Tot,
"%age",
each [Sum] / List.Sum(Table.SelectRows(Tot, (y) => y[YesNo] = [YesNo])[Sum])
),
Type = Table.TransformColumnTypes(Per, {"%age", Percentage.Type}),
Remove = Table.RemoveColumns(Type, {"Index"})
in
RemovePower Query solution 3 for Pivot Yes No Percentages, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
T1 = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T2 = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Merge = Table.AddColumn(T2, "A", (x) => Table.SelectRows(T1, each [Item] = x[Item]){0}[Value]),
Group = Table.Group(
Merge,
{"YesNo"},
{
{
"B",
each
let
a = _,
b = List.Split([Item], 2),
c = List.Split([A], 2),
d = List.Transform(c, each List.Sum(_)),
e = List.Sum(d),
f = List.Transform(d, each _ / e),
g = Table.FromColumns(List.Zip(b) & {d} & {f}, {"Item1", "Item2", "Sum", "%age"})
in
g
}
}
),
Sol = Table.Sort(
Table.ExpandTableColumn(Group, "B", Table.ColumnNames(Group[B]{0})),
{{"YesNo", 1}}
)
in
SolPower Query solution 4 for Pivot Yes No Percentages, proposed by Eric Laforce:
let
Sources = Table.SelectRows(Excel.CurrentWorkbook(), each Text.StartsWith([Name], "tData205"))[
Content
],
SplitSize = 2,
Join = Table.Join(Sources{1}, "Item", Sources{0}, "Item"),
ItemCols = List.Transform({1 .. SplitSize}, each "Item" & Text.From(_)),
Group = Table.Group(
Join,
"YesNo",
{
"All",
each
let
_Total = List.Sum(_[Value]),
_Split = List.Transform(
Table.Split(_, SplitSize),
each
let
_Sum = List.Sum(_[Value]),
_RecI = Record.FromList(_[Item], List.FirstN(ItemCols, List.Count(_[Item])))
in
Table.FromRecords(
{[YesNo = _[YesNo]{0}] & _RecI & [Sum = _Sum, #"%age" = _Sum / _Total]}
)
)
in
Table.Combine(_Split)
}
),
CombineAndSort = Table.Sort(Table.Combine(Group[All]), {"YesNo", Order.Descending})
in
CombineAndSortPower Query solution 5 for Pivot Yes No Percentages, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
S1 = Excel.CurrentWorkbook(){[Name = "T_1"]}[Content],
S2 = Excel.CurrentWorkbook(){[Name = "T_2"]}[Content],
a = Table.NestedJoin(S1, {"Item"}, S2, {"Item"}, "N"),
b = Table.ExpandTableColumn(a, "N", {"YesNo"}, {"YesNo"}),
c = Table.Group(
b,
{"YesNo"},
{{"Tbl", each _, type table [Item = text, Value = number, YesNo = text]}}
),
MF = (Tbl) =>
let
A = Table.FromColumns({Table.Split(Tbl, 2)}, {"L"}),
B = Table.AddColumn(
A,
"Item",
each Table.FromRecords(
{
[
YesNo = [L][YesNo]{0},
Item1 = [L][Item]{0},
Item2 = [L][Item]{1},
Sum = List.Sum([L][Value])
]
}
)
),
C = Table.SelectColumns(B, {"Item"}),
D = Table.ExpandTableColumn(
C,
"Item",
{"YesNo", "Item1", "Item2", "Sum"},
{"YesNo", "Item1", "Item2", "Sum"}
),
E = Table.ReplaceErrorValues(D, {{"Item2", null}}),
F = Table.AddColumn(E, "%age", each [Sum] / List.Sum(E[Sum])),
G = Table.TransformColumnTypes(F, {{"%age", Percentage.Type}})
in
G,
d = Table.AddColumn(c, "MF", each MF([Tbl])),
e = Table.SelectColumns(d, {"MF"}),
f = Table.ExpandTableColumn(
e,
"MF",
{"YesNo", "Item1", "Item2", "Sum", "%age"},
{"YesNo", "Item1", "Item2", "Sum", "%age"}
),
g = Table.TransformColumnTypes(f, {{"%age", Percentage.Type}})
in
gPower Query solution 6 for Pivot Yes No Percentages, proposed by Ahmed Ariem:
let
f = (x)=>[
a = Table.FromList( List.Split( List.Combine( Table.ToRows(x)),6),(x)=>x,{"Item1","YesNo","Value1","Item2","fdeled","Value2"} ),
b = Table.ReplaceValue(a,null,0,Replacer.ReplaceValue,{"Value1","Value2"}),
c = Table.AddColumn(b, "Sum", each[Value1]+[Value2]),
d = Table.RemoveColumns(c,{"fdeled","Value1","Value2"}),
f = Table.AddColumn(d,"%Age", (x)=>Number.RoundDown( x[Sum] / List.Sum( c[Sum]),3)),
e = Table.TransformColumnTypes(f,{{"%Age", Percentage.Type}})][e],
Source= Excel.CurrentWorkbook(){[Name="tbl_2"]}[Content],
Types = Table.TransformColumnTypes(Source,{{"Item", type text}, {"YesNo", type text}}),
from = Table.Sort(Types,{{"YesNo", Order.Descending}, {"Item", Order.Ascending}}),
to = Record.FromList( tbl_1[Value] , tbl_1[Item]),
add = Table.AddColumn(from, "Value", each Record.FieldOrDefault(to,[Item])),
Group= Table.Group(add, {"YesNo"}, {{"tbl",f }}),
ExpandT = Table.ExpandTableColumn(Group, "tbl", {"Item1", "Item2", "Sum", "%Age"})
in
ExpandT
file atached
https://1drv.ms/x/s!AiUZ0Ws7G26Rj2P3vbaXI6OWPEyz?e=GBs1Mt
Power Query solution 7 for Pivot Yes No Percentages, proposed by Szabolcs Phraner:
InnerJoin = Table.Join( T1, "Item", T2, "Item", JoinKind.Inner ),
RowIndex = Table.AddIndexColumn(InnerJoin, "Row Index", 0, 1, Int64.Type),
//Split tables to add Item Index
SplitItems = Table.Split( RowIndex,2 ),
ItemIndex = Table.Combine(
List.Transform( List.Zip({SplitItems,List.Positions(SplitItems)}), (O) => Table.AddColumn(O{0},"ItemIndex", each O{1} ,Int64.Type ) )
),
ItemNo = Table.AddColumn(ItemIndex, "ItemNo", each if Number.IsEven([ItemIndex]) then "Item1" else "Item2",type text),
JoinIndex = Table.AddColumn(ItemNo, "JoinIndex", each if Number.IsOdd([ItemIndex]) and Number.IsEven([Row Index]) then [ItemIndex] -1 else if Number.IsEven([ItemIndex]) and Number.IsOdd([Row Index]) then [ItemIndex] + 1 else [ItemIndex],Int64.Type),
...
Power Query solution 8 for Pivot Yes No Percentages, proposed by Szabolcs Phraner:
GroupItems = Table.Group(JoinIndex, {"ItemNo"}, {{"Table", each
Table.RenameColumns(Sort, {{"Item", _{0}[ItemNo]} })
}}),
JoinItemGroups = Table.NestedJoin( GroupItems{0}[Table], "JoinIndex", GroupItems{1}[Table], "JoinIndex", "Item2 Table", JoinKind.LeftOuter ),
ExpandItem2 = Table.ExpandTableColumn(JoinItemGroups, "Item2 Table", {"Item2", "Value"}, {"Item2", "Value.1"}),
SumCol = Table.AddColumn(ExpandItem2, "Sum", each if [Value.1] is null then [Value] else [Value] + [Value.1], Int64.Type) [[YesNo], [Item1],[Item2],[Sum]],
//Used to calculate YesNo Percentage
SumsTotals = Table.Group(SumCol, {"YesNo"}, {{"SumTotal", each List.Sum([Sum]), type number}}),
Percentage = Table.AddColumn( SumCol, "%age", each Number.Round( [Sum] / SumsTotals{[YesNo = [YesNo]]}[SumTotal],2) , Percentage.Type )
in
Percentage
Solving the challenge of Pivot Yes No Percentages with Excel
Excel solution 1 for Pivot Yes No Percentages, proposed by محمد حلمي:
=LET(
i,
D3:D13,
r,
LAMBDA(
x,
LET(
u,
WRAPROWS(
FILTER(
i,
E3:E13=x
),
2,
""
),
s,
MMULT(
XLOOKUP(
u,
i,
B3:B13,
0
),
{1;1}
),
HSTACK(
IF(
s,
x
),
u,
s,
s/SUM(
s
)
)
)
),
VSTACK(
r(
"Yes"
),
r(
"No"
)
)
)Excel solution 2 for Pivot Yes No Percentages, proposed by Julian Poeltl:
=LET(
A,
A3:A13,
B,
B3:B13,
C,
D3:D13,
D,
E3:E13,
REDUCE(
HSTACK(
"YesNo",
"Item1",
"Item2",
"Sum",
"%age"
),
{"Yes",
"No"},
LAMBDA(
F,
G,
VSTACK(
F,
LET(
W,
WRAPROWS(
FILTER(
C,
D=G
),
2,
""
),
B,
BYROW(
XLOOKUP(
W,
A,
B,
0
),
LAMBDA(
A,
SUM(
A
)
)
),
P,
B/SUM(
B
),
HSTACK(
XLOOKUP(
TAKE(
W,
,
1
),
C,
D
),
W,
B,
P
)
)
)
)
)
)Excel solution 3 for Pivot Yes No Percentages, proposed by Oscar Mendez Roca Farell:
=REDUCE(
HSTACK(
E2,
D2&{1,
2},
"Sum",
"%age"
),
{"Yes",
"No"},
LAMBDA(
i,
x,
LET(
w,
WRAPROWS(
FILTER(
A3:A13,
E3:E13=x
),
2,
""
),
m,
MMULT(
XLOOKUP(
w,
A3:A13,
B3:B13,
0
),
{1; 1}
),
VSTACK(
i,
IFNA(
HSTACK(
x,
w,
m,
m/SUM(
m
)
),
x
)
)
)
)
)Excel solution 4 for Pivot Yes No Percentages, proposed by Sunny Baggu:
=REDUCE(
{"YesNo", "Item1", "Item2", "Sum", "%age"},
SORT(UNIQUE(E3:E13), , -1),
LAMBDA(x, y,
VSTACK(
x,
LET(
_f, FILTER(D3:D13, E3:E13 = y),
_a, WRAPROWS(_f, 2, ""),
_b, N("💗🌈🎹") +
BYROW(
WRAPROWS(XLOOKUP(_f, A3:A13, B3:B13), 2, 0),
LAMBDA(a, SUM(a))
),
_c, TEXT(_b / SUM(_b), "##%"),
IFNA(HSTACK(y, _a, _b, _c), y)
)
)
)
)Excel solution 5 for Pivot Yes No Percentages, proposed by LEONARD OCHEA 🇷🇴:
=LET(
i,
A3:A13,
v,
B3:B13,
b,
E3:E13,
F,
LAMBDA(
x,
y,
WRAPROWS(
TOCOL(
IF(
b=x,
y,
z
),
3
),
2,
""
)
),
REDUCE(
H2:L2,
SORT(
UNIQUE(
b
),
,
-1
),
LAMBDA(
m,
n,
LET(
r,
BYROW(
F(
n,
v
),
SUM
),
VSTACK(
m,
HSTACK(
IF(
r,
n
),
F(
n,
i
),
r,
r/SUM(
r
)
)
)
)
)
)
)Excel solution 6 for Pivot Yes No Percentages, proposed by Md. Zohurul Islam:
=LET(
u,
A3:A13,
v,
B3:B13,
w,
E3:E13,
hdr,
HSTACK(
E2,
A2&1,
A2&2,
"Sum",
"%age"
),
z,
REDUCE(
hdr,
SORT(
UNIQUE(
w
),
,
-1
),
LAMBDA(
x,
y,
LET(
a,
IFNA(
WRAPROWS(
FILTER(
u,
w=y
),
2
),
""
),
b,
BYROW(
IFNA(
WRAPROWS(
FILTER(
v,
w=y
),
2
),
0
),
SUM
),
c,
MAP(
b,
LAMBDA(
x,
PERCENTOF(
x,
& SUM(
b
)
)
)
),
d,
IFNA(
HSTACK(
y,
a,
b,
c
),
y
),
e,
VSTACK(
x,
d
),
e
)
)
),
z
)Excel solution 7 for Pivot Yes No Percentages, proposed by Asheesh Pahwa:
=DROP(
REDUCE(
"",
{"Yes";"No"},
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
D3:D13,
E3:E13=y
),
wr,
WRAPROWS(
f,
2,
""
),
xl,
XLOOKUP(
f,
A3:A13,
B3:B13,
""
),
w,
WRAPROWS(
xl,
2,
""
),
b,
BYROW(
w,
LAMBDA(
x,
SUM(
x
)
)
),
s,
SUM(
b
),
d,
TEXT(
b/s,
"##%"
),
IFNA(
HSTACK(
y,
wr,
w,
b,
d
),
y
)
)
)
)
),
1
)Excel solution 8 for Pivot Yes No Percentages, proposed by Bilal Mahmoud kh.:
=LET(
a,
LAMBDA(
x,
HSTACK(
TAKE(
WRAPROWS(
FILTER(
E2:E13,
E2:E13=x
),
2,
""
),
,
1
),
WRAPROWS(
FILTER(
D2:D13,
E2:E13=x
),
2,
""
),
LET(
n,
WRAPROWS(
FILTER(
B2:B13,
E2:E13=x
),
2,
""
),
m,
BYROW(
n,
LAMBDA(
s,
SUM(
s
)
)
),
t,
BYROW(
n,
LAMBDA(
e,
SUM(
e
)/2
)
),
HSTACK(
m,
t
)
)
)
),
VSTACK(
a(
"Yes"
),
a(
"No"
)
)
)Excel solution 9 for Pivot Yes No Percentages, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
q,
WRAPROWS(
FILTER(
D3:D13,
E3:E13="No"
),
2
),
w,
WRAPROWS(
FILTER(
D3:D13,
E3:E13="Yes"
),
2
),
HSTACK(
VSTACK(
LET(
a,
IFERROR(
TAKE(
IF(
w<>"",
FILTER(
E3:E13,
E3:E13="Yes"
),
""
),
,
1
),
""
),
FILTER(
a,
a<>""
)
),
LET(
a,
IFERROR(
TAKE(
IF(
w<>"",
FILTER(
E3:E13,
E3:E13="No"
),
""
),
,
1
),
""
),
FILTER(
a,
a<>""
)
)
),
VSTACK(
w,
q
),
BYROW(
IFERROR(
MAP(
VSTACK(
w,
q
),
LAMBDA(
b,
SUM(
FILTER(
B3:B13,
BYROW(
IF(
b=A3:A13,
1,
0
),
LAMBDA(
a,
SUM(
a
)
)
)=1
)
)
)
),
0
),
LAMBDA(
c,
SUM(
c
)
)
),
MAP(
VSTACK(
LET(
a,
IFERROR(
TAKE(
IF(
w<>"",
FILTER(
E3:E13,
E3:E13="Yes"
),
""
),
,
1
),
""
),
FILTER(
a,
a<>""
)
),
LET(
a,
IFERROR(
TAKE(
IF(
w<>"",
FILTER(
E3:E13,
E3:E13="No"
),
""
),
,
1
),
""
),
FILTER(
a,
a<>""
)
)
),
BYROW(
IFERROR(
MAP(
VSTACK(
w,
q
),
LAMBDA(
b,
SUM(
FILTER(
B3:B13,
BYROW(
IF(
b=A3:A13,
1,
0
),
LAMBDA(
a,
SUM(
a
)
)
)=1
)
)
)
),
0
),
LAMBDA(
c,
SUM(
c
)
)
),
LAMBDA(
i,
j,
IFS(
i="Yes",
j/SUM(
FILTER(
B3:B13,
E3:E13="Yes"
)
),
i="No",
j/SUM(
FILTER(
B3:B13,
E3:E13="No"
)
)
)
)
)
)
)Excel solution 10 for Pivot Yes No Percentages, proposed by Eddy Wijaya:
=VSTACK(
H2:L2,
DROP(
LET(
raw,
D3:E13,
sortedRaw,
SORT(
raw,
2,
-1
),
YesNoCat,
UNIQUE(
CHOOSECOLS(
sortedRaw,
-1
)
),
REDUCE(
0,
YesNoCat,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
wrappedDb,
IFNA(
WRAPROWS(
DROP(
FILTER(
sortedRaw,
CHOOSECOLS(
sortedRaw,
-1
)=v
),
,
-1
),
2
),
""
),
YesNoCol,
VLOOKUP(
CHOOSECOLS(
wrappedDb,
1
),
sortedRaw,
2,
0
),
calcBasis,
IFNA(
XLOOKUP(
wrappedDb,
$A$3:$A$13,
$B$3:$B$13
),
0
),
total_per_row,
BYROW(
calcBasis,
LAMBDA(
r,
SUM(
r
)
)
),
pct_age,
total_per_row/SUM(
calcBasis
),
HSTACK(
YesNoCol,
wrappedDb,
total_per_row,
pct_age
)
)
)
)
)
),
1
)
)Solving the challenge of Pivot Yes No Percentages with Python in Excel
Python in Excel solution 1 for Pivot Yes No Percentages, proposed by Alejandro Campos:
df = xl("A2:B13", headers=True).merge(xl("D2:E13", headers=True), on='Item')
def process(flag):
filtered = df[df['YesNo'] == flag].reset_index(drop=True)
pairs = [[filtered.loc[i, 'Item'],
filtered.loc[i+1, 'Item'] if i+1 < len(filtered) else '',
filtered.loc[i, 'Value'] + (filtered.loc[i+1, 'Value'] if i+1 < len(filtered) else 0)]
for i in range(0, len(filtered), 2)]
total = sum(x[2] for x in pairs)
return [[flag] + x + [f"{x[2] / total:.2%}"] for x in pairs]
df_result = pd.DataFrame(process('Yes') + process('No'), columns=['YesNo', 'Item1', 'Item2', 'Sum', '%age'])
df_result
Python in Excel solution 2 for Pivot Yes No Percentages, proposed by Abdallah Ally:
df1 = xl("A2:B13", headers=True)
df2 = xl("D2:E13", headers=True)
# Perform data munging
df3 = pd.merge(df2, df1)
df3 = df3.sort_values(by='YesNo', ascending=False, ignore_index=True)
df = pd.DataFrame(columns=['YesNo', 'Item', 'Sum'])
for value in df3['YesNo'].unique():
dfn = df3[df3['YesNo'] == value].reset_index(drop=True)
items = [dfn['Item'].tolist()[i: i + 2] for i in range(0, len(dfn), 2)]
items = [x + [''] if len(x) == 1 else x for x in items]
values = [sum(dfn['Value'].tolist()[i: i + 2]) for i in range(0, len(dfn), 2)]
df.loc[len(df)] = [value, items, values]
df = df.explode(column=['Item', 'Sum'], ignore_index=True)
df[['Item1', 'Item2']] = df['Item'].tolist()
df['Cumsum'] = df.groupby('YesNo')['Sum'].transform('sum')
df['%age'] = df.apply(lambda x: f'{x.Sum / x.Cumsum:.0%}', axis=1)
df = df.iloc[:, [0, 3, 4, 2, 6]]
df
Python in Excel solution 3 for Pivot Yes No Percentages, proposed by Owen Price:
Solving the challenge of Pivot Yes No Percentages with R
R solution 1 for Pivot Yes No Percentages, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_205.xlsx"
input1 = read_excel(path, range = "A2:B13")
input2 = read_excel(path, range = "D2:E13")
test = read_excel(path, range = "H2:L8")
input = left_join(input1, input2, by = "Item")
result = input %>%
arrange(desc(YesNo), Item) %>%
mutate(nr = row_number(), .by = YesNo) %>%
mutate(nr_rem = nr %% 2,
nr_int = ifelse(nr_rem == 1, nr %/% 2 + 1, nr %/% 2)) %>%
select(-nr) %>%
pivot_wider(names_from = nr_rem, values_from = c(Item, Value), values_fill = list(Value = 0)) %>%
mutate(Sum = Value_0 + Value_1) %>%
select(YesNo, Item1 = Item_1, Item2 = Item_0, Sum) %>%
mutate(`%age` = Sum/sum(Sum), .by = YesNo)
identical(result, test)
# [1] TRUE
&&