Transpose the table as shown.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 273
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Transpose the table as shown with Power Query
Power Query solution 1 for Transpose the table as shown, proposed by Kris Jaganah:
Table.Combine(
a = Table.ExpandListColumn( Table.TransformColumns(
v ,{"Data2" , each try Text.Split( _ ,", " ) otherwise { _} }) ,"Data2") ,
b = List.Distinct( a[Data1]) ,
c = List.Accumulate(b , a , (x,y)=> Table.AddColumn(x, y , each if [Data1] = y then [Data2] else null)) ,
d = Table.SelectColumns(c,b) ,
e = Table.FillDown(d,{"Store"}) ,
f = Table.FillUp(e,{"Visit Date"}) ,
g = Table.SelectRows(f, each ([Customers] <> null))] [g] },0,(x,y)=> Number.From( y = "Store" ))[All])
Power Query solution 2 for Transpose the table as shown, proposed by Luan Rodrigues:
let
fx = (transform) =>
let
exp = Table.ExpandListColumn(
Table.TransformColumns(transform, {"Data2", each try Text.Split(_, ", ") otherwise {_}}),
"Data2"
),
cab = {"Store", "Customers", "Visit Date"},
add = List.Accumulate(
{0 .. 2},
exp,
(s, c) => Table.AddColumn(s, cab{c}, each if [Data1] = cab{c} then [Data2] else null)
),
pb = Table.FillUp(add, {"Visit Date"}),
pc = Table.FillDown(pb, {"Store"}),
sel = Table.SelectRows(pc, each ([Customers] <> null))[[Store], [Customers], [Visit Date]]
in
sel,
trf = Table.Group(Tabela1, "Data1", {"tab", each fx(_)}, 0, (a, b) => Number.From(b = "Store"))[
tab
],
cmb = Table.Combine(trf)
in
cmbPower Query solution 3 for Transpose the table as shown, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
TAC = Table.AddColumn,
a = TAC(S, "S", each if [Data1] = "Store" then [Data2] else null),
b = TAC(a, "C", each if [Data1] = "Customers" then Text.Split([Data2], ", ") else null),
c = TAC(
Table.ExpandListColumn(b, "C"),
"V",
each if [Data1] = "Visit Date" then [Data2] else null
)[[S], [C], [V]],
d = Table.SelectRows(Table.FillUp(Table.FillDown(c, {"S"}), {"V"}), each [C] <> null),
Sol = Table.RenameColumns(d, List.Zip({Table.ColumnNames(d), List.Distinct(S[Data1])}))
in
SolPower Query solution 4 for Transpose the table as shown, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData273"]}[Content],
fxTransform = (t as table) =>
Table.Pivot(
t,
List.Distinct(t[Data1]),
"Data1",
"Data2",
each if (List.Count(_) > 1) then Text.Combine(_, ", ") else _{0}
),
Group = Table.Group(
Table.AddIndexColumn(Source, "Idx"),
{"Idx"},
{"G", each fxTransform(Table.RemoveColumns(_, "Idx"))},
0,
(x, y) => Byte.From(Source[Data1]{y[Idx] - 1} = "Visit Date")
),
Combine = Table.FillDown(Table.Combine(Group[G]), {"Store"}),
SplitCust = Table.ExpandListColumn(
Table.TransformColumns(Combine, {"Customers", each Text.Split(_, ", ")}),
"Customers"
)
in
SplitCustPower Query solution 5 for Transpose the table as shown, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(Source, "Data1", {"A", Fun_1}, 0, (x, y) => Number.From(y = "Store"))[A],
Fun_1 = each Table.Group(_, "Data1", {"B", each [Data2]}, 0)[B],
Rows = List.TransformMany(Group, each List.Split(List.Skip(_), 2), Fun_2),
Fun_2 = (x, y) => {x{0}{0}, Text.Split(Text.Combine(y{0}, ", "), ", "), y{1}{0}},
Res = Table.ExpandListColumn(
Table.FromRows(Rows, {"Store", "Customer", "Visit Date"}),
"Customer"
)
in
ResPower Query solution 6 for Transpose the table as shown, proposed by Antriksh Sharma:
let
Source = Table,
Group = Table.Group(
Source,
"Data1",
{
{
"T",
each
let
a = Table.Skip(_),
b = List.RemoveLastN(
List.Accumulate(
a[Data1],
{0},
(s, c) => s & (if c = "Customers" then {List.Max(s)} else {List.Max(s) + 1})
),
1
),
c = Table.FromColumns({b} & Table.ToColumns(a), {"I", "Data1", "Data2"}),
d = Table.Group(
c,
"I",
{
{
"T",
each
let
Customers = Table.SelectRows(_, each [Data1] = "Customers")[Data2],
VisitDate = Table.SelectRows(_, each [Data1] = "Visit Date")[Data2],
t = List.Combine(List.Transform(Customers, (x) => Text.Split(x, ", "))),
d = List.Repeat(VisitDate, List.Count(t)),
e = Table.FromColumns(
{t} & {d},
type table [Customer = text, Visit Date = text]
)
in
e
}
}
)[T],
e = Table.Combine(d),
StoreName = Table.First(_)[Data2],
Result = Table.TransformColumns(
Table.AddColumn(e, "Store", each StoreName, type text),
{"Visit Date", Date.FromText}
)
in
Result
}
},
GroupKind.Local,
(x, y) => Byte.From(y = "Store")
),
Combine = Table.SelectColumns(Table.Combine(Group[T]), {"Store", "Customer", "Visit Date"})
in
CombinePower Query solution 7 for Transpose the table as shown, proposed by Peter Krkos:
PowerQuery solution:
Ad_Store = Table.SelectRows(Table.FillDown(Table.AddColumn(Source, "Store", each if [Data1] = "Store" then [Data2] else null, type text), {"Store"}), each [Data1] <> "Store"),
Ad_VisitDate = Table.SelectRows(Table.FillUp(Table.AddColumn(Ad_Store, "Visit Date", each if [Data1] = "Visit Date" then Date.From([Data2], "en-US") else null, type date), {"Visit Date"}), each [Data1] <> "Visit Date"),
Result = Table.FromRows(List.TransformMany(Table.ToRows(Table.SelectColumns(Ad_VisitDate,{"Store", "Data2", "Visit Date"})),
each Text.Split(_{1}, ", "),
(x,y)=> {x{0}, y, x{2}}), type table[Store=text, Customer=text, Visit Date=date])
in
Result
Power Query solution 8 for Transpose the table as shown, proposed by Francesco Bianchi 🇮🇹:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
AS = Table.AddColumn(
S,
"Store",
each if Text.EndsWith(Text.From([Data2]), "Avenue") then [Data2] else null
),
FD = Table.FillDown(AS, {"Store"}),
FS = Table.SelectRows(FD, each [Store] <> [Data2]),
AD = Table.AddColumn(FS, "Visit Date", each try Date.From([Data2]) otherwise null),
FU = Table.FillUp(AD, {"Visit Date"}),
FC = Table.SelectRows(FU, each [Data2] is text),
CL = Table.TransformColumns(FC, {{"Data2", each Text.Split(_, ", "), type any}}),
ER = Table.ExpandListColumn(CL, "Data2"),
RC = Table.SelectColumns(ER, {"Store", "Data2", "Visit Date"}),
RE = Table.RenameColumns(RC, {{"Data2", "Customer"}})
in
REPower Query solution 9 for Transpose the table as shown, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
addCustomColumn = Table.AddColumn(
source,
"Store",
each [
Store = if [Data1] = "Store" then [Data2] else null,
Date = if [Data1] = "Visit Date" then [Data2] else null
]
),
expandRecord = Table.ExpandRecordColumn(
addCustomColumn,
"Store",
{"Store", "Date"},
{"Store", "Visit Date"}
),
fill = Table.FillUp(Table.FillDown(expandRecord, {"Store"}), {"Visit Date"}),
selectRows = Table.SelectRows(fill, each ([Data1] = "Customers")),
grouping = Table.Group(
selectRows,
{"Store", "Visit Date"},
{{"Customer", each Text.Combine(_[Data2], ", ")}}
),
splitColumnToRows = Table.ExpandListColumn(
Table.TransformColumns(
grouping,
{{"Customer", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv)}}
),
"Customer"
),
reorderColumns = Table.ReorderColumns(splitColumnToRows, {"Store", "Customer", "Visit Date"}),
changeDataTypes = Table.TransformColumnTypes(
reorderColumns,
{{"Visit Date", type date}, {"Store", type text}}
)
in
changeDataTypesPower Query solution 10 for Transpose the table as shown, proposed by Fredson Alves Pinho:
let
Fonte = Excel.CurrentWorkbook(){[Name = "Table"]}[Content],
Index = Table.AddIndexColumn(Fonte, "Index"),
pvt = Table.Pivot(Index, List.Distinct(Index[Data1]), "Data1", "Data2"),
fill = Table.FillUp(Table.FillDown(pvt, {"Store"}), {"Visit Date"}),
div = Table.SplitColumn(
fill,
"Customers",
Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv),
{"C1", "C2", "C3"}
),
unpvt = Table.Unpivot(div, {"C1", "C2", "C3"}, "Atributo", "Customers")[
[Store],
[Customers],
[Visit Date]
]
in
unpvtPower Query solution 11 for Transpose the table as shown, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
{"Data1"},
{{"Store", each _[Data2]{0}}, {"All", each Table.SelectRows(_, (z) => z[Data1] <> "Store")}},
GroupKind.Local,
(x, y) => Byte.From(x = y)
),
Trs = Table.TransformColumns(
Grp,
{
"All",
each Table.ExpandListColumn(
Table.SelectRows(
Table.FillUp(
Table.ExpandRecordColumn(
Table.AddColumn(
_,
"R",
(x) => [
Customer = if x[Data1] = "Customers" then Text.Split(x[Data2], ",") else null,
Visit Date = if x[Data1] = "Visit Date" then x[Data2] else null
]
),
"R",
{"Customer", "Visit Date"}
),
{"Visit Date"}
),
(k) => k[Customer] <> null
),
"Customer"
)
}
),
Res = Table.RemoveColumns(
Table.ExpandTableColumn(Trs, "All", {"Customer", "Visit Date"}),
"Data1"
)
in
ResPower Query solution 12 for Transpose the table as shown, proposed by Le Ngoc Tinh:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
LA = Table.FillUp(Table.FillDown(List.Accumulate({"Store","Visit Date"},Source,(x,y)=>Table.AddColumn(x,y, each if [Data1]= y then [Data2] else null)),{"Store"}),{"Visit Date"}),
FR = Table.ToRows(Table.RemoveColumns(Table.SelectRows(LA, each ([Data1] = "Customers")),"Data1")),
RE = hashtag#table(List.Distinct(Source[Data1]), List.TransformMany(FR, each Text.Split(_{0},", "),(x,y)=>{x{1},y,x{2}}))
in
RE
Power Query solution 13 for Transpose the table as shown, proposed by Le Ngoc Tinh:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
GR = Table.Combine(
Table.Group(
Source,
"Data1",
{
"T",
(x) =>
let
Ad1 = Table.Skip(Table.AddColumn(x, "Store", each x[Data2]{0})),
Ad2 = Table.AddColumn(
Ad1,
"Visit Date",
each if Text.StartsWith([Data1], "Visit") then Date.From([Data2], "fr") else null
),
FU = Table.FillUp(Ad2, {"Visit Date"}),
SR = Table.SelectRows(FU, each ([Data1] = "Customers")),
RC = Table.TransformColumns(
Table.RemoveColumns(SR, {"Data1"}),
{"Data2", each Text.Split(_, ", ")}
),
EX = Table.RenameColumns(Table.ExpandListColumn(RC, "Data2"), {"Data2", "Customers"}),
TR = Table.ReorderColumns(EX, {"Store", "Customers", "Visit Date"})
in
TR
},
0,
(x, y) => Number.From(y = "Store")
)[T]
)
in
GRPower Query solution 14 for Transpose the table as shown, proposed by Nelson Mwangi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
VisitDateCol = Table.AddColumn(
Source,
"Visit Date",
each if [Data1] = "Visit Date" then [Data2] else null
),
StoreCol = Table.AddColumn(
VisitDateCol,
"Store",
each if [Data1] = "Store" then [Data2] else null
),
FillColumns = {"Visit Date", "Store"},
FillVDate = Table.FillUp(StoreCol, {"Visit Date"}),
FillStore = Table.FillDown(FillVDate, {"Store"}),
Filter = Table.SelectRows(FillStore, each ([Data1] = "Customers")),
SelectColumns = Table.SelectColumns(Filter, {"Store", "Data2", "Visit Date"}),
SplitCustomers = Table.TransformColumns(SelectColumns, {"Data2", each Text.Split(_, ", ")}),
Expand_RenameCol = Table.ExpandListColumn(
Table.RenameColumns(SplitCustomers, {"Data2", "Customers"}),
"Customers"
)
in
Expand_RenameColSolving the challenge of Transpose the table as shown with Excel
Excel solution 1 for Transpose the table as shown, proposed by Bo Rydobon 🇹🇭:
=LET(h,
A2:A20,
z,
B2:B20,
r,
SEQUENCE(
ROWS(
z
)
),
REDUCE(TOROW(
A2:A4
),
FILTER(
r,
h=A3
),
LAMBDA(a,
i,
VSTACK(a,
CHOOSE({1,
2,
3},
LOOKUP(i,
r/(h=A2),
z),
TEXTSPLIT(
INDEX(
z,
i
),
,
", "
),
XLOOKUP(i,
r/(z<""),
z,
,
1))))))
=LET(
h,
A2:A20,
z,
B2:B20,
l,
TAKE(
h,
-1
),
f,
FILTER(
HSTACK(
SCAN(
,
z,
LAMBDA(
a,
v,
IF(
@+l:v="Store",
v,
a
)
)
),
DROP(
VSTACK(
0,
SCAN(
0,
z,
LAMBDA(
a,
v,
IF(
LEFT(
@+l:v
)="c",
TEXTJOIN(
", ",
,
a,
T(
v
)
),
""
)
)
)
),
-1
),
z
),
z<""
),
REDUCE(
TOROW(
UNIQUE(
h
)
),
SEQUENCE(
ROWS(
f
)
),
LAMBDA(
a,
i,
VSTACK(
a,
CHOOSE(
{1,
2,
3},
INDEX(
f,
i,
1
),
TEXTSPLIT(
INDEX(
f,
i,
2
& ),
,
", "
),
INDEX(
f,
i,
3
)
)
)
)
)
)Excel solution 2 for Transpose the table as shown, proposed by Duy Tùng:
=LET(I,
INDEX,
H,
HSTACK,
X,
XLOOKUP,
a,
A2:A20,
b,
B2:B20,
c,
ROW(
b
),
d,
FILTER(H(X(c,
c/(a=A2),
b,
,
-1),
b,
X(c,
c/(b<""),
b,
,
1)),
a=A3),
e,
TEXTSPLIT(
TEXTJOIN(
"/",
,
I(
d,
,
2
)
),
", ",
"/"
),
f,
LAMBDA(
v,
TOCOL(
IFS(
e>0,
v
),
3
)
),
H(
f(
I(
d,
,
1
)
),
f(
e
),
f(
I(
d,
,
3
)
)
))Excel solution 3 for Transpose the table as shown, proposed by Duy Tùng:
=LET(
I,
INDEX,
b,
FILTER(
HSTACK(
SCAN(
,
B2:B20,
LAMBDA(
x,
y,
IF(
@+A20:y=A2,
y,
x
)
)
),
B2:B20,
SCAN(
0,
B2:B20,
LAMBDA(
x,
y,
IF(
ISTEXT(
y
),
I(
B20:y,
MATCH(
1=1,
ISNUMBER(
B20:y
),
)
),
x
)
)
)
),
A2:A20=A3
),
c,
TEXTSPLIT(
TEXTJOIN(
"/",
,
I(
b,
,
2
)
),
", ",
"/"
),
f,
LAMBDA(
v,
TOCOL(
IFS(
c>0,
v
),
3
)
),
HSTACK(
f(
I(
b,
,
1
)
),
f(
c
),
f(
I(
b,
,
3
)
)
)
)Excel solution 4 for Transpose the table as shown, proposed by Sunny Baggu:
=LET(
_st, SCAN("", IF(A2:A20 = A2, B2:B20, ""), LAMBDA(a, v, IF(v = "", a, v))),
_ist, UNIQUE(_st),
REDUCE(
HSTACK(A2, "Customer", A4),
_ist,
LAMBDA(g, h,
VSTACK(
g,
LET(
_f, DROP(FILTER(B2:B20, _st = h), 1),
_s, SEQUENCE(ROWS(_f)),
_sa, N(ISERR(RIGHT(_f) + 0)),
_sb, FILTER(_s, _sa = 0),
_sc, XMATCH(_s, _sb, 1),
_sd, UNIQUE(_sc),
DROP(
REDUCE(
"🌼",
_sd,
LAMBDA(x, y,
VSTACK(
x,
LET(
_t, FILTER(_f, _sc = y),
_tc, TEXTSPLIT(ARRAYTOTEXT(DROP(_t, -1)), , ", "),
_td, IF(_tc <> "", TAKE(_t, -1)),
IFNA(HSTACK(h, _tc, _td), h)
) ) ) ), 1 ) ) ) ) ))Excel solution 5 for Transpose the table as shown, proposed by LEONARD OCHEA 🇷🇴:
=LET(
a,
A2:A20,
b,
B2:B20,
k,
", ",
s,
SCAN(
,
IF(
a=A2,
b,
),
LAMBDA(
i,
j,
IF(
j>"",
j,
i
)
)
),
d,
MAP(
b,
LAMBDA(
x,
XLOOKUP(
1,
--ISNUMBER(
x:B20
),
x:B20
)
)
),
c,
IFS(
a=A3,
b&k,
1,
""
),
f,
CHOOSECOLS(
TEXTSPLIT(
CONCAT(
SUBSTITUTE(
c,
k,
k&s&k&d&"|"
)
),
k,
"|",
1
),
2,
1,
3
),
IFERROR(
--f,
f
)
)Excel solution 6 for Transpose the table as shown, proposed by Md. Zohurul Islam:
=LET(
z,
A2:B20,
hdr,
HSTACK(
"Store",
"Customer",
"Visit Date"
),
u,
IF(
TAKE(
z,
,
1
)="Store",
DROP(
z,
,
1
),
""
),
v,
SCAN(
,
u,
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
w,
REDUCE(
hdr,
UNIQUE(
v
),
LAMBDA(
x,
y,
LET(
a,
DROP(
FILTER(
z,
v=y
),
1
),
b,
SCAN(
1,
ABS(
ISNUMBER(
ABS(
DROP(
a,
,
1
)
)
)
),
SUM
),
n,
IF(
TAKE(
a,
,
1
)="Visit Date",
b-1,
b
),
d,
DROP(
REDUCE(
"",
UNIQUE(
n
),
LAMBDA(
p,
q,
LET(
j,
FILTER(
a,
n=q
),
k,
TEXTSPLIT(
ARRAYTOTEXT(
DROP(
j,
-1,
1
)
),
,
", "
),
dt,
TAKE(
j,
-1,
-1
),
m,
IFNA(
HSTACK(
k,
dt
),
dt
),
VSTACK(
p,
m
)
)
)
),
1
),
e,
IFNA(
HSTACK(
y,
d
),
y
),
f,
VSTACK(
x,
e
),
f
)
)
),
w
)Excel solution 7 for Transpose the table as shown, proposed by Pieter de B.:
=LET(a,
A2:A20,
b,
B2:B20,
c,
UNIQUE(
a
),
d,
TEXTSPLIT(
TEXTAFTER(
", "&b,
", ",
SEQUENCE(
,
MAX(
LEN(
b
)-LEN(
SUBSTITUTE(
b,
",",
)
)+1
)
)
),
", "
),
e,
INDEX(
c,
2
),
L,
LAMBDA(v,
w,
TOCOL(IFS((a=e)*(1-ISNA(
d
)),
SCAN(
@TOCOL(
IFS(
a=v,
b
),
2
),
b,
LAMBDA(
x,
y,
IF(
@TAKE(
+A2:y,
-1
)=v,
INDEX(
w,
ROW(
y
)-1
),
x
)
)
)),
2)),
HSTACK(
L(
@c,
b
),
TOCOL(
FILTER(
d,
a=e
),
2
),
L(
e,
MAP(
b,
LAMBDA(
m,
XLOOKUP(
1,
1-ISTEXT(
m:B20
),
m:B20
)
)
)
)
))Excel solution 8 for Transpose the table as shown, proposed by Hamidi Hamid:
=LET(
bb,
B2:B20,
x,
SCAN(
0,
IF(
ISNUMBER(
SEARCH(
A2,
A2:A20
)
),
bb,
""
),
LAMBDA(
a,
b,
IF(
b>"",
b,
a
)
)
),
y,
MAP(
bb,
LAMBDA(
a,
XLOOKUP(
1,
ISNUMBER(
a:B20
)*1,
a:B20,
0
)
)
)*1,
zz,
IF(
bb=x,
"",
bb
),
t,
HSTACK(
x,
y,
zz
),
g,
FILTER(
t,
ISTEXT(
TAKE(
t,
,
-1
)
)
),
f,
DROP(
TEXTSPLIT(
CONCAT(
"_"&TAKE(
g,
,
-1
)
),
", ",
"_"
),
1
),
p,
LAMBDA(
cc,
TOCOL(
IF(
f>0,
cc,
""
),
3
)
),
gu,
p(
TAKE(
g,
,
1
)
),
gd,
p(
CHOOSECOLS(
g,
2
)
),
r,
HSTACK(
gu,
gd,
p(
f
)
),
CHOOSECOLS(
FILTER(
r,
TAKE(
r,
,
-1
)<>""
),
1,
3,
2
)
)Excel solution 9 for Transpose the table as shown, proposed by Asheesh Pahwa:
=LET(
sc,
SCAN(
"",
IF(
A2:A23="Store",
B2:B23,
""
),
LAMBDA(
x,
y,
IF(
y<>"",
y,
x
)
)
),
REDUCE(
D1:F1,
UNIQUE(
sc
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
B2:B23,
sc=y
),
d,
DROP(
f,
1
),
I,
INDEX(
d,
SEQUENCE(
ROWS(
d
),
,
ROWS(
d
),
-1
)
),
s,
SORT(
SCAN(
"",
I,
LAMBDA(
a,
v,
IF(
ISNUMBER(
v
),
v,
a
)
)
)
),
IFNA(
HSTACK(
y,
DROP(
REDUCE(
"",
UNIQUE(
s
),
LAMBDA(
z,
p,
VSTACK(
z,
LET(
f,
FILTER(
d,
s=p
),
d,
DROP(
f,
-1
),
IFNA(
HSTACK(
DROP(
REDUCE(
"",
d,
LAMBDA(
a,
v,
VSTACK(
a,
TEXTSPLIT(
v,
,
", "
)
)
)
),
1
),
p
),
p
)
)
)
)
),
1
)
),
y
)
)
)
)
)
)Excel solution 10 for Transpose the table as shown, proposed by red craven:
=LET(a,
A2:A20,
b,
B2:B20,
r,
ROW(
a
),
d,
", ",
s,
IFS(
a=A3,
TEXTSPLIT(
TEXTAFTER(
d&b,
d,
{1,
2,
3}
),
d
)
),
F,
LAMBDA(
x,
TOCOL(
IF(
s>0,
x
),
2
)
),
HSTACK(F(LOOKUP(r,
r/(a=A2),
b)),
F(
s
),
F(XLOOKUP(r,
r/(a=A4),
b,
,
1))))
old formula:
=LET(a,
A2:A20,
b,
B2:B20,
I,
INDEX,
H,
HSTACK,
r,
ROW(
b
),
L,
LAMBDA(x,
y,
XLOOKUP(r,
r/(a=x),
b,
,
y)),
c,
FILTER(
H(
L(
A2,
-1
),
b,
L(
A4,
1
)
),
a=A3
),
s,
TEXTSPLIT(
CONCAT(
I(
c,
,
2
)&"|"
),
", ",
"|",
1
),
F,
LAMBDA(
m,
TOCOL(
IF(
s>0,
m
),
2
)
),
H(
F(
I(
c,
,
1
)
),
F(
s
),
F(
I(
c,
,
3
)
)
))Solving the challenge of Transpose the table as shown with Python
Python solution 1 for Transpose the table as shown, proposed by Konrad Gryczan, PhD:
import pandas as pd
path = "PQ_Challenge_273.xlsx"
input = pd.read_excel(path, sheet_name=0, usecols="A:B", nrows=20)
test = pd.read_excel(path, sheet_name=0, usecols="D:F", nrows=17)
input.columns = ["data1", "data2"]
input["store"] = input["data2"].where(input["data1"] == "Store").ffill()
input["visit_date"] = input["data2"].where(input["data1"] == "Visit Date").bfill()
input = input[~input["data1"].isin(["Store", "Visit Date"])]
result = (
input.assign(dat&a2=input["data2"].str.split(", "))
.explode("data2")[["store", "data2", "visit_date"]]
.rename(columns={"data2": "Customer", "store": "Store", "visit_date": "Visit Date"})
.reset_index(drop=True)
)
print(result.equals(test)) # True
Python solution 2 for Transpose the table as shown, proposed by Luan Rodrigues:
import pandas as pd
import numpy as np
file = r"PQ_Challenge_273.xlsx"
df = pd.read_excel(file,usecols="A:B")
df['Data2'] = df['Data2'].astype('str').str.split(', ')
df = df.explode('Data2')
lista = ['Store','Customers','Visit Date']
for i in lista:
df[i] = np.where(df['Data1'].eq(i),df['Data2'],None)
df['Visit Date'] = df['Visit Date'].bfill()
df['Store'] = df['Store'].ffill()
df_filtro = df[df['Customers'].notna()]
print(df_filtro[['Store','Customers','Visit Date']])
Solving the challenge of Transpose the table as shown with Python in Excel
Python in Excel solution 1 for Transpose the table as shown, proposed by Alejandro Campos:
df = xl("A1:B20", headers=True)
data, store, date, customers = [], None, None, []
for _, row in df.iterrows():
k, v = row["Data1"], row["Data2"]
if k == "Store": store = v
elif k == "Customers": customers.extend(v.split(", "))
elif k == "Visit Date":
data += [[store, c, v] for c in customers]
customers.clear()
normalized_df = pd.DataFrame(data, columns=["Store", "Customer", "Visit Date"])
Python in Excel solution 2 for Transpose the table as shown, proposed by Francesco Bianchi 🇮🇹:
Hello, my python in excel solution:
df = xl("Sheet1!$A$1:$B$20", headers=True)
df['Store'] = [x if y == 'Store' else np.nan for x, y in zip(df['Data2'], df['Data1'])]
df['Store']= df['Store'].ffill()
df['Visit Date'] = [x if y == 'Visit Date' else np.nan for x, y in zip(df['Data2'], df['Data1'])]
df['Visit Date']= df['Visit Date'].bfill()
df['Customer'] = [x.split(", ") if y == 'Customers' else np.nan for x, y in zip(df['Data2'], df['Data1'])]
df = df[df['Customer'].notna()]
df = df[['Store','Customer','Visit Date']].explode('Customer')
df.reset_index(drop=True)
Solving the challenge of Transpose the table as shown with R
R solution 1 for Transpose the table as shown, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
library(janitor)
path = "Power Query/PQ_Challenge_273.xlsx"
input = read_excel(path, range = "A1:B20")
test = read_excel(path, range = "D1:F18")
result = input %>%
mutate(Store = ifelse(Data1 == "Store", Data2, NA)) %>%
fill(Store) %>%
filter(Data1 != "Store") %>%
mutate(`Visit Date` = ifelse(Data1 == "Visit Date", Data2, NA)) %>%
fill(`Visit Date`, .direction = "up") %>%
filter(Data1 != "Visit Date") %>%
mutate(`Visit Date` = excel_numeric_to_date(as.numeric(`Visit Date`)) %>% as.POSIXct()) %>%
separate_rows(Data2, sep = ", ") %>%
select(Store, Customer = Data2, `Visit Date`)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&