Align the data as given in answer expected.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 520
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Align Rowwise Data with Power Query
Power Query solution 1 for Align Rowwise Data, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromColumns(
List.Zip(
List.TransformMany(
Table.ToRows(Source),
each
let
r = List.RemoveLastN(_, each _ = null)
in
List.Combine(
List.Transform(
List.Split(
List.Skip(List.Zip({List.FirstN(Table.ColumnNames(Source), List.Count(r)), r})),
4
),
List.Zip
)
),
(i, _) => {i{0}} & _
)
)
)
in
SPower Query solution 2 for Align Rowwise Data, proposed by Kris Jaganah:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Calc = Table.AddColumn(
Source,
"Custom",
each
let
a = List.Skip(Record.FieldNames(_)),
b = List.Skip(Record.FieldValues(_)),
c = (Table.ColumnCount(Source) - 1) / 2,
d = List.FirstN(a, c),
e = List.FirstN(b, c),
f = List.LastN(a, c),
g = List.LastN(b, c),
h = Table.FromRows({d, e}) & Table.FromRows({f, g})
in
h
),
Keep = Table.SelectColumns(Calc, {"Group", "Custom"}),
Xpand = Table.ExpandTableColumn(Keep, "Custom", {"Column1", "Column2", "Column3", "Column4"}),
Rmov =
if List.NonNullCount(Record.FieldValues(Xpand{Table.RowCount(Xpand) - 1})) <= 1 then
Table.RemoveRows(Xpand, Table.RowCount(Xpand) - 2, 2)
else
Xpand
in
RmovPower Query solution 3 for Align Rowwise Data, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Split = Table.Split(Source, 1),
Transform = List.TransformMany(
Split,
(x) =>
[
D = Table.DemoteHeaders(x),
Tp = Table.Transpose(D),
Sk = Table.Skip(Tp),
R = Table.Split(Sk, 4)
][R],
(x, y) =>
[
H = Table.FromRows({List.Repeat({x[Group]{0}}, 2)}),
S = Table.RemoveLastN(y, each [Column2] = null),
C = if Table.IsEmpty(S) then S else H & S,
R = Table.Transpose(C)
][R]
),
Return = Table.Combine(Transform)
in
ReturnPower Query solution 4 for Align Rowwise Data, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"Group"},
{
{
"A",
each
let
a = Table.RemoveColumns(_, "Group"),
b = Table.DemoteHeaders(a),
c = Table.ToColumns(b),
d = List.RemoveLastN(c, each _{1} = null),
e = List.Split(d, 4),
f = List.Transform(e, each Table.FromColumns(_)),
g = Table.Combine(f)
in
g
}
}
),
Sol = Table.ExpandTableColumn(
Group,
"A",
List.Distinct(List.Combine(List.Transform(Group[A], each Table.ColumnNames(_))))
)
in
SolPower Query solution 5 for Align Rowwise Data, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table3"]}[Content],
Headers = List.Skip(Table.ColumnNames(Source)),
Headers1 = List.FirstN(Headers, 4),
Headers2 = List.LastN(Headers, 4),
Records = List.Transform(
Table.ToRows(Source),
each [
f = {_{0}},
a = List.FirstN(_, 5),
b = f & List.LastN(_, 4),
c = if List.NonNullCount(a) > 1 then {f & Headers1, a} else {{}},
d = if List.NonNullCount(b) > 1 then {f & Headers2, b} else {{}},
e = List.Select(c & d, each not List.IsEmpty(_))
][e]
),
Result = Table.FromRows(List.Combine(Records))
in
ResultSolving the challenge of Align Rowwise Data with Excel
Excel solution 1 for Align Rowwise Data, proposed by Bo Rydobon 🇹🇭:
=LET(z,A1:Z4,DROP(REDUCE(,SEQUENCE(ROWS(z)),LAMBDA(a,i,LET(v,INDEX(+z,i,),d,DROP(v,,1),b,IF(d="","",d),c,SEQUENCE(,COLUMNS(b))<=MATCH(9^9,b),w,LAMBDA(x,WRAPROWS(FILTER(x,c),4,"")),
u,VSTACK(w(TAKE(DROP(z,,1),1)),w(b)),r,ROWS(u),s,SEQUENCE(r),IFNA(VSTACK(a,HSTACK(IF(s,@v),SORTBY(u,MOD(s-1,r/2)))),"")))),1))Excel solution 2 for Align Rowwise Data, proposed by Rick Rothstein:
=LET(
f,
LAMBDA(
g,
LET(
h,
WRAPROWS(
B1:I1,
4
),
b,
SUM(
OFFSET(
g,
0,
5,
4
)
)>0,
v,
0+WRAPROWS(
OFFSET(
g,
0,
1,
1,
4+4*b
)&"",
4
),
HSTACK(
SEQUENCE(
2+2*b,
,
g,
0
),
IFERROR(
WRAPROWS(
TOROW(
HSTACK(
h,
v
)
),
4
),
""
)
)
)
),
d,
DROP(
REDUCE(
"",
A2:A4,
LAMBDA(
a,
x,
VSTACK(
a,
f(
x
)
)
)
),
1
),
FILTER(
d,
ISNUMBER(
TAKE(
d,
,
1
)
)
)
)Excel solution 3 for Align Rowwise Data, proposed by John V.:
=LET(b,REDUCE(0,I2:I4,LAMBDA(a,v,LET(f,LAMBDA(t,r,IF(SUM(r),IFNA(HSTACK(@+A4:v,IF({1,2,3,4}>IFNA(MATCH(1,0/r),),"",VSTACK(t,IF(r="","",r)))),@+A4:v),0)),VSTACK(a,f(B1:E1,TAKE(B4:v,1,4)),f(F1:I1,TAKE(F4:v,1)))))),FILTER(b,TAKE(b,,1)))Excel solution 4 for Align Rowwise Data, proposed by محمد حلمي:
=LET(
i,
DROP(
REDUCE(
0,
B2:B4,
LAMBDA(
a,
v,
LET(
b,
A4,
s,
@+v:A4,
u,
OFFSET(
v,
,
,
,
4
),
k,
OFFSET(
v,
,
4,
,
4
),
r,
B1:E1,
e,
F1:I1,
VSTACK(
a,
IFNA(
HSTACK(
s,
VSTACK(
IF(
s=b,
IF(
u,
r,
""
),
r
),
u,
IF(
s=b,
IF(
k,
e,
""
),
e
),
k
)
),
s
)
)
)
)
),
1
),
e,
IF(
OR(
TAKE(
i,
-1,
-4
)
),
i,
DROP(
i,
-2
)
),
IF(
e=0,
"",
e
)
)Excel solution 5 for Align Rowwise Data, proposed by محمد حلمي:
=LET(d,DROP(REDUCE("",A2:A4,LAMBDA(a,g,VSTACK(a,LET(h,WRAPROWS(B1:I1,4),b,SUM(OFFSET(g,0,5,4))>0,
v,0+WRAPROWS(OFFSET(g,0,1,1,4+4*b)&"",4),
HSTACK(SEQUENCE(2+2*b,,g,0),IFERROR(
WRAPROWS(TOROW(HSTACK(h,v)),4),"")))))),1),
FILTER(d,ISNUMBER(TAKE(d,,1))))Excel solution 6 for Align Rowwise Data, proposed by محمد حلمي:
=LET(i,DROP(REDUCE(0,B2:B4,LAMBDA(a,v,LET(
b,B4,s,@+v:A4,u,OFFSET(v,,,,4),k,OFFSET(v,,4,,4),
r,B1:E1,e,F1:I1,VSTACK(a,IFNA(HSTACK(s,
VSTACK(IF(v=b,IF(u,r,""),r),u,IF(v=b,IF(k,e,""),e),k)),s))))),1),
IF(OR(TAKE(i,-1,-4)),i,DROP(i,-2)))Excel solution 7 for Align Rowwise Data, proposed by Kris Jaganah:
=LET(
a,
A2:A4,
b,
B1:I1,
c,
B2:I4,
d,
ROWS(
c
),
e,
COLUMNS(
c
),
f,
e/2,
g,
DROP(
TOCOL(
IF(
SCAN(
0,
c,
LAMBDA(
x,
y,
IF(
y="",
1+x,
)
)
)<2,
IF(
c="",
"",
c
),
z
),
3
),
-1
),
h,
TAKE(
TOCOL(
INDEX(
b,
,
SEQUENCE(
e,
d,
,
1/d
)
),
,
1
),
ROWS(
g
)
),
i,
VSTACK(
WRAPROWS(
h,
f,
""
),
WRAPROWS(
g,
f,
""
)
),
j,
ROWS(
i
),
k,
TOCOL(
WRAPCOLS(
SEQUENCE(
j
),
j/2
)
),
HSTACK(
ROUNDUP(
SORT(
k
)/f,
0
),
DROP(
REDUCE(
"",
k,
LAMBDA(
x,
y,
VSTACK(
x,
CHOOSEROWS(
i,
y
)
)
)
),
1
)
)
)Excel solution 8 for Align Rowwise Data, proposed by Julian Poeltl:
=LET(T,DROP(REDUCE(1,A2:A4,LAMBDA(A,B,VSTACK(A,HSTACK(B,LET(S,SEQUENCE(2,4),F,HSTACK(INDEX(B1:I1,1,S),INDEX(CHOOSEROWS(B2:I4,B),1,S)),C,IF(SUM(CHOOSEROWS(F,2))=0,DROP(F,-1),F),W,WRAPROWS(TOCOL(C),4),D,IF(ROWS(W)>2,W,DROP(W,,-(COLUMNS(TAKE(W,1))-XMATCH(1,IFNA((DROP(W,1)=0)*(DROP(DROP(W,1),,1)=0),0))+1))),D))))),1),C,IFNA(IF(T=0,"",T),""),HSTACK(SCAN(,TAKE(C,,1),LAMBDA(A,B,IF(B="",A,B))),DROP(C,,1)))Excel solution 9 for Align Rowwise Data, proposed by Julian Poeltl:
=LET(A,DROP(REDUCE(0,A2:A4,LAMBDA(A,B,VSTACK(A,LET(S,SEQUENCE(2,4),F,HSTACK(INDEX(B1:I1,1,S),INDEX(CHOOSEROWS(B2:I4,B),1,S)),C,IF(SUM(CHOOSEROWS(F,2))=0,DROP(F,-1),F),HSTACK(B,WRAPROWS(TOROW(C),4)))))),1),H,HSTACK(SCAN(,TAKE(A,,1),LAMBDA(A,B,IFERROR(B,A))),DROP(A,,1)),IF(H=0,"",H))Excel solution 10 for Align Rowwise Data, proposed by Timothée BLIOT:
=LET(V,VSTACK,H,HSTACK,A,B1:I1,B,B2:I4,F,LAMBDA(l,m,n,TAKE(INDEX(l,m,),,n)),DROP(DROP(REDUCE(0,ROW(1:3),LAMBDA(y,x,V(y,H(x,V(F(A,1,4))),H(x,F(B,x,4)),H(x,F(A,1,-4)),H(x,F(B,x,-4)))) ),1),-2))Excel solution 11 for Align Rowwise Data, proposed by Sunny Baggu:
=LET(
_r,
IF(
B2:I4 = "",
"",
B2:I4
),
_a,
TOCOL(
B2:I4
),
_b,
MAX(
SEQUENCE(
ROWS(
_a
)
) * N(
_a <> 0
)
),
_c,
TAKE(
TOCOL(
_r
),
_b
),
_d,
TAKE(
TOCOL(
IF(
A2:A4,
B1:I1
)
),
_b
),
_e,
WRAPROWS(
TOCOL(
HSTACK(
WRAPROWS(
_d,
4,
""
),
WRAPROWS(
_c,
4,
""
)
)
),
4
),
_f,
TOCOL(
TAKE(
WRAPROWS(
TAKE(
TOCOL(
IF(
B1:I1 <> "",
A2:A4
)
),
_b
),
4
),
,
2
)
),
HSTACK(
_f,
_e
)
)Excel solution 12 for Align Rowwise Data, proposed by Abdallah Ally:
=LET(a,B1:E1,b,F1:I1,v,""&REDUCE("",A2:A4,LAMBDA(x,y,LET(c, OFFSET(y,,1,,4),d,OFFSET(y,,5,,4),VSTACK(x,IF(COUNT(c)>0,VSTACK(HSTACK(y,a),HSTACK(y,c))),IF(COUNT(d)>0,VSTACK(HSTACK(y,b),HSTACK(y,d))))))),FILTER(v,BYROW(v,LAMBDA(x,NOT(OR(ISNA(x)))))))Excel solution 13 for Align Rowwise Data, proposed by Hamidi Hamid:
=LET(w,A2:A4,bd,B2:I4,r,TOCOL(IFNA(w,B1:I1)),er,r-MAX(r)+TOCOL(bd)+SEQUENCE(COUNTA(r),,0,0),x,FILTER(TOCOL(bd),er<>0),y,FILTER(TOCOL(IFNA(B1:I1,w)),er<>0),n,FILTER(TOCOL(IFNA(w,B1:I1)),er<>0),c,WRAPROWS(x,4,""),v,WRAPROWS(y,4,""),lv,SEQUENCE(ROWS(v)),llv,VSTACK(lv,lv),aq,HSTACK(llv,VSTACK(v,c)),sg,SORT(TOCOL(IF(SEQUENCE(4),TRANSPOSE(w),""))),sq,DROP(SORTBY(aq,TAKE(aq,,1)),,1),dt,HSTACK(sg,IF(sq=0,"",sq)),tu,BYROW(ISERROR(dt)*1,LAMBDA(a,SUM(a))),qq,FILTER(dt,tu=0),qq)Excel solution 14 for Align Rowwise Data, proposed by And&y Heybruch:
=DROP(
TEXTSPLIT(
TEXTJOIN(
";",
,
BYROW(
A2:I4,
LAMBDA(
a,
HSTACK(
TEXTJOIN(
"|",
FALSE,
TAKE(
a,
,
1
),
INDEX(
B1:I1,
,
SEQUENCE(
,
4,
1
)
)
)
)
&";"&
HSTACK(
TEXTJOIN(
"|",
FALSE,
TAKE(
a,
,
1
),
INDEX(
a,
,
SEQUENCE(
,
4,
2
)
)
)
)
&";"&
HSTACK(
TEXTJOIN(
"|",
FALSE,
TAKE(
a,
,
1
),
INDEX(
B1:I1,
,
SEQUENCE(
,
4,
5
)
)
)
)
&";"&
HSTACK(
TEXTJOIN(
"|",
FALSE,
TAKE(
a,
,
1
),
INDEX(
a,
,
SEQUENCE(
,
4,
6
)
)
)
)
)
)
),
"|",
";"
),
-2
)Excel solution 15 for Align Rowwise Data, proposed by Eddy Wijaya:
=LET(
raw,B2:I4,
group,A2:A4,
h_1,BYROW(raw,LAMBDA(r,TEXTJOIN(",",FALSE,r))),
h_2,DROP(REDUCE(0, group & "," & h_1, LAMBDA(a,v,
LET(
data, TEXTSPLIT(TEXTAFTER(v, ","), , ","),
lastRow, LOOKUP(2, 1/(data<>""), SEQUENCE(ROWS(data))),
seq, "Value_" & SEQUENCE(lastRow),
adjData, IF(lastRow<5, DROP(data, -4), data),
mergeDS, HSTACK(seq, adjData),
wrapL, LAMBDA(d, TOCOL(d, , TRUE)),
wrapped, IF(lastRow<5, wrapL(mergeDS), VSTACK(wrapL(TAKE(mergeDS, 4)), wrapL(DROP(mergeDS, 4)))),
result, HSTACK(TEXTBEFORE(v, ","), IFNA(WRAPROWS(wrapped, 4), "")),
VSTACK(a, IFNA(result, TEXTBEFORE(v, ",")))
))), 1),IFERROR(--h_2,h_2))Excel solution 16 for Align Rowwise Data, proposed by Edwin Tisnado:
=LET(
s,
SEQUENCE(
2,
4
),
a,
DROP(
REDUCE(
0,
A2:A4,
LAMBDA(
x,
y,
VSTACK(
x,
HSTACK(
{1;1;1;1}*y,
SORTBY(
VSTACK(
INDEX(
B1:I1,
s
),
INDEX(
INDEX(
B2:I4,
y,
),
s
)
),
{1;2;1;2}
)
)
)
)
),
1
),
DROP(
IF(
a=0,
"",
a
),
-2
)
)Excel solution 17 for Align Rowwise Data, proposed by El Badlis Mohd Marzudin:
=LET(t,DROP(DROP(REDUCE("",A2:A4,LAMBDA(x,y,VSTACK(x,LET(k,VSTACK($B$1:$E$1,FILTER(B2:E4,A2:A4=y),F1:I1,FILTER(F2:I4,A2:A4=y)),HSTACK(EXPAND(y,ROWS(k),,y),k))))),1),-2),IFNA(HSTACK(TAKE(t,,3),DROP(TAKE(t,,-2),-2)),""))Solving the challenge of Align Rowwise Data with Python
Python solution 1 for Align Rowwise Data, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "520 Alignment of Data.xlsx"
input = pd.read_excel(path, usecols="A:I", nrows= 3, dtype = str)
test = pd.read_excel(path, usecols="A:E", skiprows = 7, header = None, dtype = str)
def correct_transform_dataframe(df):
result = pd.DataFrame()
for _, row in df.iterrows():
group = row['Group']
first_header = pd.DataFrame([[group, 'Value_1', 'Value_2', 'Value_3', 'Value_4']], columns=[0, 1, 2, 3, 4])
first_half = pd.DataFrame([[group] + list(row[['Value_1', 'Value_2', 'Value_3', 'Value_4']])], columns=[0, 1, 2, 3, 4])
second_header = pd.DataFrame([[group, 'Value_5', 'Value_6', 'Value_7', 'Value_8']], columns=[0, 1, 2, 3, 4])
second_half = pd.DataFrame([[group] + list(row[['Value_5', 'Value_6', 'Value_7', 'Value_8']])], columns=[0, 1, 2, 3, 4])
result = pd.concat([result, first_header, first_half, second_header, second_half], ignore_index=True)
return result
result = correct_transform_dataframe(input)
result = result[:-2]
result.loc[8, 3:4] = np.nan
print(result.equals(test)) # True
Solving the challenge of Align Rowwise Data with Python in Excel
Python in Excel solution 1 for Align Rowwise Data, proposed by Abdallah Ally:
df = xl("A1:I4", headers=True)
# Perform data munging
cols = df.columns.tolist()
half_cols = len(cols) // 2
headers1 = cols[1: half_cols + 1]
headers2 = cols[half_cols + 1: ]
values = []
for i in df.index:
f = [i + 1]
a = df.iloc[i, 1: half_cols + 1].tolist()
b = df.iloc[i, half_cols + 1:].tolist()
if len([x for x in a if pd.notna(x)]) > 1:
values.extend([f + headers1, f + a])
if len([x for x in b if pd.notna(x)]) > 1:
values.extend([f + headers2, f + b])
df = pd.DataFrame(values).fillna('')
df
Solving the challenge of Align Rowwise Data with R
R solution 1 for Align Rowwise Data, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/520 Alignment of Data.xlsx"
input = read_excel(path, range = "A1:I4")
test = read_excel(path, range = "A8:E17", col_names = FALSE) %>% janitor::clean_names()
result = input %>%
pivot_longer(-c(1), names_to = "value_no", values_to = "value") %>%
mutate(v_no = as.numeric(str_extract(value_no, "\d+")),
mod = (v_no - 1) %/% 4) %>%
select(-v_no) %>%
nest_by(Group, mod) %>%
filter(!all(is.na(data$value))) %>%
mutate(data = list(list(t(data)) %>% as.data.frame())) %>%
unnest(data) %>%
ungroup() %>%
select(-mod) %>%
mutate(X4 = ifelse(row_number() == 9, NA, X4),
X3 = ifelse(row_number() == 9, NA, X3))
colnames(result) = colnames(test)
identical(result, test)
# [1] TRUE
&&