Transpose the given data from Problem Table into Result table as shown
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 261
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Transpose the given data from with Power Query
Power Query solution 1 for Transpose the given data from, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = List.Distinct(A[Data1]),
C = Table.ExpandListColumn(
Table.TransformColumns(A, {"Data2", each Text.Split(_, ", ")}),
"Data2"
),
D = List.Accumulate(
B,
C,
(x, y) => Table.AddColumn(x, y, each if [Data1] = y then [Data2] else null)
),
E = Table.FillDown(D, List.RemoveLastN(B)),
F = List.Select(Table.ToRows(E), each List.Last(_) <> null),
G = List.Skip(List.Zip(F), 2),
H = Table.FromColumns(
List.Split(
List.TransformMany(
G,
each List.Positions(F),
(v, w) => if v{w} = (try v{w - 1} otherwise 0) then null else v{w}
),
List.Count(F)
),
B
)
in
HPower Query solution 2 for Transpose the given data from, proposed by Luan Rodrigues:
let
Fonte = Table.TransformColumns(Tabela1, {"Data2", each Text.Split(_, ", ")}),
exp = Table.ExpandListColumn(Fonte, "Data2"),
Ind = Table.AddIndexColumn(exp, "Índice", 0, 1, Int64.Type),
pb = Table.Pivot(Ind, List.Distinct(Ind[Data1]), "Data1", "Data2"),
rem = Table.RemoveColumns(pb, {"Índice"}),
grp = Table.Group(
rem,
{"Country"},
{
{
"tab",
each
let
a = Table.FillUp(_, {"Cities"}),
b = Table.Group(
a,
{"Cities"},
{{"Contagem", each Table.Distinct(Table.FillUp(_, {"State"}), {"Cities"})}}
)
in
Table.Combine(b[Contagem])
}
},
0,
(a, b) => Number.From(b[Country] <> null)
)[tab],
res = Table.Combine(grp)
in
resPower Query solution 3 for Transpose the given data from, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
Table.Group(
Source,
"Data1",
{
"Data",
each [
a = Table.ToRows(Table.Transpose(_)){1},
b = List.Split(List.Skip(a), 2),
c = List.Transform(b, (x) => List.Zip({{x{0}}, Text.Split(x{1}, ", ")})),
d = List.Combine(c),
e = {0 .. List.Count(d) - 1},
f = List.Transform(e, (x) => (if x = 0 then {a{0}} else {null}) & d{x}),
g = Table.FromRows(f, {"Country", "State", "Cities"})
][g]
},
0,
(x, y) => Byte.From(y = "Country")
)[Data]
)
in
ResultPower Query solution 4 for Transpose the given data from, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
TAC = Table.AddColumn,
a = Table.ExpandListColumn(
Table.TransformColumns(S, {"Data2", Splitter.SplitTextByDelimiter(", ")}),
"Data2"
),
b = TAC(a, "Country", each if [Data1] = "Country" then [Data2] else null),
c = TAC(b, "S", each if [Data1] = "State" then [Data2] else null),
d = Table.RemoveColumns(
TAC(c, "C", each if [Data1] = "Cities" then [Data2] else null),
{"Data1", "Data2"}
),
e = TAC(Table.AddIndexColumn(d, "I"), "State", each try d{[I] + 1}[S] otherwise null),
f = TAC(e, "Cities", each try e{[I] + 2}[C] otherwise null)[[Country], [State], [Cities]],
Sol = Table.SelectRows(f, each [Country] <> null or [State] <> null or [Cities] <> null)
in
SolPower Query solution 5 for Transpose the given data from, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData261"]}[Content],
//-- From record with [1:list of new records + 2:record of current row in progress] => next record
fxAccumulate = (s as record, v as list) as record =>
let
nr = Record.AddField(s[r], v{0}, v{1})
in
if Record.HasFields(nr, "Cities") then [lr = s[lr] & {nr}, r = []] else [lr = s[lr], r = nr],
Transform = List.Accumulate(
Table.ToRows(Source),
[lr = {}, r = []],
(s, v) =>
if (v{0} <> "Cities") then
fxAccumulate(s, v)
else
List.Accumulate(Text.Split(v{1}, ", "), s, (s, c) => fxAccumulate(s, {v{0}, c}))
),
Result = Table.FromRecords(Transform[lr], List.Distinct(Source[Data1]), MissingField.UseNull)
in
ResultPower Query solution 6 for Transpose the given data from, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Rows = List.TransformMany(
Table.ToRows(Source),
(x) => Text.Split(x{1}, ", "),
(x, y) => {x{0}, y}
),
Recs = List.Accumulate(
Rows,
{[]},
(a, v) =>
if Record.HasFields(List.Last(a), "Cities") then
a & {Record.AddField([], v{0}, v{1})}
else
List.RemoveLastN(a) & {Record.AddField(List.Last(a), v{0}, v{1})}
),
Res = Table.FromRecords(Recs, List.Distinct(Source[Data1]), MissingField.UseNull)
in
ResPower Query solution 7 for Transpose the given data from, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.Combine(
Table.Group(
Source,
"Data1",
{
{
"New",
each [
Tbl = Table.Combine(
List.Transform(
List.Split(List.Skip(_[Data2]), 2),
(f) => Table.FromRows(List.Zip({{f{0}}, Text.Split(f{1}, ", ")}))
)
),
fin = Table.FromColumns(
{{[Data2]{0}} & List.Repeat({null}, Table.RowCount(Tbl) - 1)} & Table.ToColumns(Tbl),
List.Distinct(Source[Data1])
)
][fin]
}
},
0,
(x, y) => Number.From(y = "Country")
)[New]
)
in
ResultPower Query solution 8 for Transpose the given data from, proposed by Peter Krkos:
PowerQuery solution:
R = [ CSC = List.Buffer(List.Distinct(Source[Data1])),
L = List.Buffer(List.Repeat({null}, List.Count(CSC)-1))
],
Transformed = Table.Combine(Table.Group(Source, "Data1", {{"T", each
[ T = _,
Tbl = Table.FromColumns({{T{0}[Data2]}} & Table.ToColumns(Table.Combine(List.Transform(List.Split(List.Transform(List.Skip(Table.ToRows(T)), (x)=> List.Transform(List.RemoveNulls(List.ReplaceRange(R[L], List.PositionOf(R[CSC], x{0})-1, 1, {x{1}})), (y)=> Text.Split(y, ", "))), 2), (z)=> Table.FromColumns(List.Combine(z))))), R[CSC])
][Tbl], type table}}, 0, (x,y)=> Byte.From(y = "Country"))[T])
in
Transformed
Power Query solution 9 for Transpose the given data from, proposed by Alexandre Garcia:
let
H = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
P = List.Distinct(H[Data1]),
L = Table.AddColumn(
H,
"x",
(x) =>
[
M = Table.SelectRows(
Table.FirstN(Table.Skip(H, Table.PositionOf(H, x) + 1), each [Data1] <> x[Data1]),
each [Data1] = P{2}
)[Data2],
S = Text.Split(x[Data2], ", ")
& List.Repeat({null}, Text.Length(Text.Select(Text.Combine(M, ", "), ",")))
][S]
),
C = Table.FromColumns(Table.Group(L, "Data1", {"x", each List.Combine([x])})[x], P)
in
CPower Query solution 10 for Transpose the given data from, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
A = Table.AddIndexColumn(Source, "Indeks", 1, 1),
B = Table.Pivot(A, List.Distinct(A[Data1]), "Data1", "Data2"),
C = Table.TransformColumns(B, {"Cities", each try Text.Split(_, ", ") otherwise {null}}),
D = Table.ExpandListColumn(C, "Cities"),
E = Table.RemoveColumns(D, {"Indeks"}),
F = Table.ToColumns(E),
G = List.Transform(F, each List.Skip(_, List.PositionOf(F, _))),
H = Table.FromColumns(G, Table.ColumnNames(E)),
Result = Table.SelectRows(H, each List.Count(List.RemoveNulls(Record.ToList(_))) > 0)
in
ResultPower Query solution 11 for Transpose the given data from, proposed by Sergei Baklan:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
tbl = Table.PromoteHeaders(Source, [PromoteAllScalars = true]),
SplitData2 = Table.FromColumns(
{tbl[Data1], List.Transform(tbl[Data2], (q) => Text.Split(q, ", "))}
),
Expand = Table.ExpandListColumn(SplitData2, "Column2"),
AddIndex = Table.AddIndexColumn(Expand, "Index", 0, 1, Int64.Type),
Pivot = Table.Pivot(AddIndex, List.Distinct(AddIndex[Column1]), "Column1", "Column2"),
Columns = List.Skip(Table.ToColumns(Pivot)),
Final = Table.SelectRows(
Table.FromColumns(
List.Transform(Columns, (q) => List.Skip(q, List.PositionOf(Columns, q))),
List.Skip(Table.ColumnNames(Pivot))
),
each [Cities] <> null
)
in
FinalPower Query solution 12 for Transpose the given data from, proposed by Oleksandr Mynka:
let
src = Excel.CurrentWorkbook(){[Name="SourceTable"]}[Content],
spl = Table.TransformColumns(src, {"Data2", (x)=>Text.Split(x,", ")}),
exp = Table.ExpandListColumn(spl, "Data2"),
lst = Table.ToRows(Table.ReverseRows(exp)),
res = List.Accumulate(
lst,
hashtag#table({"Country","State","Cities"},{}),
(s,c)=>
if c{0}="Cities" then
Table.InsertRows(s,0,{[Country=null,State=null,Cities=c{1}]})
else
if c{0}="State" then
Table.InsertRows(Table.Skip(s),0,
{s{0} & [Contry=null,State=c{1},Cities=s{0}[Cities]]}
)
else
Table.InsertRows(Table.Skip(s),0,
{s{0} & [Country=c{1},State=s{0}[State],Cities=s{0}[Cities]]}
)
)
in
res
Solving the challenge of Transpose the given data from with Excel
Excel solution 1 for Transpose the given data from, proposed by Bo Rydobon 🇹🇭:
=LET(
h,
TOROW(
UNIQUE(
A2:A13
)
),
REDUCE(
h,
B2:B13,
LAMBDA(
a,
v,
LET(
n,
XMATCH(
@+A13:v,
h
),
IFNA(
IF(
@TAKE(
a,
-1,
-1
)="",
VSTACK(
DROP(
a,
-1
),
HSTACK(
TAKE(
a,
-1,
n-1
),
TEXTSPLIT(
v,
,
", "
)
)
),
VSTACK(
a,
REPT(
v,
SEQUENCE(
,
n
)=n
)
)
),
""
)
)
)
)
)
=LET(
a,
A2:A13,
TEXTSPLIT(
CONCAT(
A2:A4&{0;0;1}
)&TEXTJOIN(
REPT(
1,
a=A4
),
,
REPT(
0,
a<>A2
)&SUBSTITUTE(
B2:B13,
", ",
100
)
),
0,
1
)
)Excel solution 2 for Transpose the given data from, proposed by Kris Jaganah:
=LET(p,
A2:A13,
q,
B2:B13,
r,
UNIQUE(
p
),
s,
TAKE(
r,
1
),
u,
SCAN(
0,
N(
p=s
),
SUM
),
REDUCE(TOROW(
r
),
UNIQUE(
u
),
LAMBDA(x,
y,
VSTACK(x,
LET(a,
FILTER(q,
(u=y)*(p="cities")),
b,
TEXTSPLIT(
CONCAT(
a&", "
),
,
", ",
1
),
c,
XLOOKUP(
b,
VSTACK(
DROP(
q,
1
),
""
),
q,
,
3
),
IFNA(
HSTACK(
INDEX(
FILTER(
q,
p=s
),
y,
),
IFS(
c<>VSTACK(
"",
DROP(
c,
-1
)
),
c
),
b
),
""
))))))Excel solution 3 for Transpose the given data from, proposed by Oscar Mendez Roca Farell:
=LET(a,
A2:A13,
b,
B2:B13,
r,
ROW(
a
),
REDUCE(TOROW(
UNIQUE(
a
)
),
FILTER(
b,
a=A2
),
LAMBDA(i,
x,
LET(m,
FILTER(b,
LOOKUP(r,
r/(a=A2),
b)=x),
w,
WRAPROWS(
DROP(
m,
1
),
2
),
d,
DROP(
w,
,
1
),
t,
TEXTSPLIT(
CONCAT(
d&", "
),
,
", ",
1
),
IFNA(
VSTACK(
i,
HSTACK(
@m,
XLOOKUP(
t&"*",
d,
TAKE(
w,
,
1
),
,
2
),
t
)
),
""
)))))Excel solution 4 for Transpose the given data from, proposed by Oscar Mendez Roca Farell:
=LET(
a,
A2:A13,
b,
B2:B13,
c,
TEXTSPLIT(
CONCAT(
TOCOL(
IFS(
a=A4,
b
),
2
)&", "
),
,
", ",
1
),
F,
LAMBDA(
x,
y,
[j],
XLOOKUP(
x,
DROP(
b,
1
),
DROP(
y,
-1
),
,
j
)
),
s,
F(
c&"*",
b,
2
),
VSTACK(
TOROW(
UNIQUE(
a
)
),
IFNA(
HSTACK(
F(
s,
IFS(
a=A2,
b
)
),
s,
c
),
""
)
)
)Excel solution 5 for Transpose the given data from, proposed by Duy Tùng:
=LET(a,
A2:A13,
b,
B2:B13&,
H,
HSTACK,
I,
INDEX,
f,
LAMBDA(
v,
SCAN(
,
b,
LAMBDA(
x,
y,
IF(
@+A13:y=v,
y,
x
)
)
)
),
c,
FILTER(H(
f(
"country"
),
f(
"state"
),
b
),
(a<>"Country")*(a<>"State")),
d,
TEXTSPLIT(
TEXTJOIN(
", ",
,
I(
c,
,
3
)
),
,
", "
),
l,
LAMBDA(
v,
LET(
u,
MAP(
d,
LAMBDA(
x,
LOOKUP(
2,
0/SEARCH(
x,
I(
c,
,
3
)
),
v
)
)
),
REPT(
u,
XMATCH(
u,
u
)=SEQUENCE(
ROWS(
u
)
)
)
)
),
H(
l(
I(
c,
,
1
)
),
l(
I(
c,
,
2
)
),
d
))Excel solution 6 for Transpose the given data from, proposed by Sunny Baggu:
=LET(
_c,
SCAN(
"",
IF(
A2:A13 = A2,
B2:B13,
""
),
LAMBDA(
a,
v,
IF(
v = "",
a,
v
)
)
),
_uc,
UNIQUE(
_c
),
IFNA(
REDUCE(
TOROW(
A2:A4
),
_uc,
LAMBDA(
g,
h,
VSTACK(
g,
HSTACK(
h,
LET(
_a,
WRAPROWS(
DROP(
FILTER(
B2:B13,
_c = h
),
1
),
2
),
_b,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
_a
)
),
LAMBDA(
a,
v,
VSTACK(
a,
HSTACK(
INDEX(
_a,
v,
1
),
TEXTSPLIT(
INDEX(
_a,
v,
2
),
,
", ",
,
,
""
)
)
)
)
),
1
),
""
),
_b
)
)
)
)
),
""
)
)Excel solution 7 for Transpose the given data from, proposed by Md. Zohurul Islam:
=LET(
u,
A2:A13,
v,
B2:B13,
w,
TOROW(
UNIQUE(
u
)
),
a,
SCAN(
"",
IF(
u="Country",
v,
""
),
LAMBDA(
x,
y,
IF(
y="",
x,
y
)
)
),
b,
LAMBDA(
z,
DROP(
REDUCE(
"",
z,
LAMBDA(
p,
q,
LET(
r,
TEXTSPLIT(
q,
,
", "
),
VSTACK(
p,
IFNA(
HSTACK(
TAKE(
r,
1
),
DROP(
r,
1
)
),
""
)
)
)
)
),
1
)
),
c,
REDUCE(
w,
UNIQUE(
a
),
LAMBDA(
x,
y,
VSTACK(
x,
IFNA(
HSTACK(
y,
b(
BYROW(
WRAPROWS(
DROP(
FILTER(
v,
a=y
),
1
),
2
),
ARRAYTOTEXT
)
)
),
""
)
)
)
),
c
)Excel solution 8 for Transpose the given data from, proposed by Pieter de B.:
=LET(
a,
A2:A13,
b,
B2:B13,
C,
TOCOL(
TEXTSPLIT(
TEXTAFTER(
", "&FILTER(
b,
a="Cities"
),
", ",
SEQUENCE(
,
85
)
),
", "
),
2
),
L,
LAMBDA(
x,
XLOOKUP(
x&"*",
b,
OFFSET(
b,
-1,
),
"",
2
)
),
S,
L(
C
),
HSTACK(
IF(
XLOOKUP(
L(
S
),
b,
a,
""
)="Country",
L(
S
),
""
),
S,
C
)
)Excel solution 9 for Transpose the given data from, proposed by Asheesh Pahwa:
=LET(
sc,
SCAN(
0,
IF(
A2:A13="Country",
1,
0
),
LAMBDA(
x,
y,
x+y
)
),
u,
UNIQUE(
sc
),
IFNA(
REDUCE(
D1:F1,
u,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
f,
FILTER(
B2:B13,
sc=v
),
d,
DROP(
f,
1
),
s,
SEQUENCE(
ROWS(
d
)
),
o,
ISODD(
s
),
_o,
FILTER(
s,
o
),
I,
INDEX(
d,
FILTER(
s,
NOT(
o
)
)
),
io,
INDEX(
d,
FILTER(
s,
o
)
),
HSTACK(
TAKE(
f,
1
),
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
I
)
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
_i,
@INDEX(
I,
y,
),
IFNA(
HSTACK(
INDEX(
io,
y,
),
TEXTSPLIT(
_i,
,
", "
)
),
""
)
)
)
)
),
1
)
)
)
)
)
),
""
)
)Excel solution 10 for Transpose the given data from, proposed by Imam Hambali:
=LET(
da,
A2:A13,
db,
B2:B13,
f,
LAMBDA(
x,
FILTER(
db,
da=x
)
),
c,
TEXTSPLIT(
TEXTJOIN(
", ",
1,
f(
"Cities"
)
),
,
", "
),
cm,
TEXTBEFORE(
f(
"Cities"
),
", ",
,
,
1
),
s,
f(
"State"
),
i,
XMATCH(
s,
db
)-1,
cl,
IF(
INDEX(
da,
i
)="Country",
INDEX(
db,
i
),
""
),
lk,
LAMBDA(
x,
XLOOKUP(
c,
cm,
x,
""
)
),
VSTACK(
{"Country",
"State",
"Cities"},
HSTACK(
lk(
cl
),
lk(
s
),
c
)
)
)Solving the challenge of Transpose the given data from with Python
Python solution 1 for Transpose the given data from, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "PQ_Challenge_261.xlsx"
input = pd.read_excel(path, usecols="A:B", nrows=13)
test = pd.read_excel(path, usecols="D:F", nrows=11)
result = input.copy()
for col in ["Country","State","Cities"]:
result[col] = np.where(result["Data1"].eq(col), result["Data2"], np.nan)
result[["Country","State"]] = result[["Country","State"]].ffill()
result = result.drop(columns=["Data1","Data2"]).dropna(subset=["Cities"]).reset_index(drop=True)
result = (
result.assign(Cities=result["Cities"].str.split(", "))
.explode("Cities")
.assign(
Country=lambda df: df["Country"].str.strip(),
State=lambda df: df["State"].str.strip(),
Cities=lambda df: df["Cities"].str.strip()
)
.reset_index(drop=True)
)
for col in ["Country","State"]:
result[col] = (
result.groupby(col)[col]
.apply(lambda x: x.where(x.index == x.index[0]))
.droplevel(0)
)
print(result.equals(test)) # True
Solving the challenge of Transpose the given data from with Python in Excel
Python in Excel solution 1 for Transpose the given data from, proposed by Alejandro Campos:
data = xl("A1:B13", headers=True)
result, current_country, current_state = [], "", ""
for key, value in zip(data["Data1"], data["Data2"]):
if key == "Country": current_country, current_state = value, ""
elif key == "State": current_state = value
elif key == "Cities":
for city in value.split(', '):
result.append([current_country, current_state, city])
current_country, current_state = "", ""
df = pd.DataFrame(result, columns=["Country", "State", "Cities"])
Python in Excel solution 2 for Transpose the given data from, proposed by Owen Price:
https://www.linkedin.com/posts/owenhprice_data-analytics-excel-activity-7296580265567535104-R8KN?utm_source=share&utm_medium=member_desktop&rcm=ACoAAAYENJ4BwzD1Qrj8qZ03t5NKQTylKYE3hhMSolving the challenge of Transpose the given data from with R
R solution 1 for Transpose the given data from, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_261.xlsx"
input = read_excel(path, range = "A1:B13")
test = read_excel(path, range = "D1:F12")
result = input %>%
mutate(Country = ifelse(Data1 == "Country", Data2, NA),
State = ifelse(Data1 == "State", Data2, NA),
Cities = ifelse(Data1 == "Cities", Data2, NA)) %>%
fill(Country, State) %>%
select(-Data1, -Data2) %>%
filter(!is.na(Cities)) %>%
separate_rows(Cities, sep = ", ") %>%
mutate(across(c(Country, State, Cities), str_trim)) %>%
mutate(Country = ifelse(row_number() == 1, Country, NA), .by = Country) %>%
mutate(State = ifelse(row_number() == 1, State, NA), .by = State)
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
&