This problem is contributed by RIJESH THOMAS Align the data as shown.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 686
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Align Custom Data Layout with Power Query
Power Query solution 1 for Align Custom Data Layout, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.Group(
A,
{"Role", "EmpCode"},
{
"All",
each
let
a = (x) => DateTime.ToText(x([Date]), [Format = "MMMyy"]),
b = a(List.Min),
c = a(List.Max)
in
if b = c then b else b & " to " & c
},
0
),
C = Table.Pivot(B, List.Distinct(B[EmpCode]), "EmpCode", "All", each Text.Combine(_, ", ")),
D = Table.Sort(C, each List.PositionOf(A[Role], [Role]))
in
DPower Query solution 2 for Align Custom Data Layout, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
fxDate = (x) => DateTime.ToText(x, "MMMyy"),
Group = Table.Group(
Source,
{"Role", "EmpCode"},
{
"All",
each fxDate(List.Min([Date]))
& (if Table.RowCount(_) = 1 then "" else " to " & fxDate(List.Max([Date])))
},
0
),
Pivot = Table.Pivot(
Group,
List.Distinct(Group[EmpCode]),
"EmpCode",
"All",
each Text.Combine(_, ", ")
),
Return = Table.Sort(Pivot, each List.PositionOf(Group[Role], [Role]))
in
ReturnPower Query solution 3 for Align Custom Data Layout, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Source,
{"Role"},
{
{
"A",
each
let
a = _,
b = List.Transform(a[Date], each Date.ToText(Date.From(_), "MMMyy")),
c = if List.Count(b) = 1 then b{0} else b{0} & " to " & List.Last(b)
in
Table.FromRows({{a[EmpCode]{0}, c}})
}
},
0
),
Exp = Table.ExpandTableColumn(Grp, "A", Table.ColumnNames(Grp[A]{0})),
Pivot = Table.Pivot(
Exp,
List.Distinct(Exp[Column1]),
"Column1",
"Column2",
each Text.Combine(_, ", ")
),
Sol = Table.Sort(Pivot, each List.PositionOf(List.Distinct(Source[Role]), [Role]))
in
SolPower Query solution 4 for Align Custom Data Layout, proposed by Meganathan Elumalai:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Group = Table.Group(
Source,
{"EmpCode", "Role"},
{
"New",
each [
fx = (x) => DateTime.ToText(x([Date]), "MMMyy"),
fin = if Table.RowCount(_) = 1 then fx(List.Min) else fx(List.Min) & " to " & fx(List.Max)
][fin]
},
0
),
Result = Table.Group(
Group,
"Role",
List.Transform(
List.Distinct(Group[EmpCode]),
(f) => {
f,
each Text.Combine(
Table.SelectRows(Group, (x) => x[Role] = [Role]{0} and x[EmpCode] = f)[New],
", "
)
}
)
)
in
ResultPower Query solution 5 for Align Custom Data Layout, proposed by Mihai Radu O:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
tip = Table.TransformColumnTypes(Source, {{"Date", type date}}),
grup = Table.Group(
tip,
{"Role", "EmpCode"},
{
{
"r",
each [
min = Date.ToText(List.Min([Date]), [Format = "MMMyy"]),
max = Date.ToText(List.Max([Date]), [Format = "MMMyy"]),
a = if min = max then min else min & " to " & max
][a]
}
},
GroupKind.Local
),
pivot = Table.Pivot(
grup,
List.Distinct(grup[EmpCode]),
"EmpCode",
"r",
(x) => Text.Combine(x, ", #(lf)")
),
sortRename = Table.RenameColumns(
Table.Sort(pivot, {each List.PositionOf(List.Distinct(Source[Role]), [Role])}),
{"Role", "Title/EmpId"}
)
in
sortRenamePower Query solution 6 for Align Custom Data Layout, proposed by Maciej Kopczyński:
let
source = Excel.CurrentWorkbook(){[Name = "tblStart"]}[Content],
grouping1 = Table.Group(
source,
{"EmpCode", "Role"},
{
{
"Dates",
each Date.ToText(Date.From(List.Min(_[Date])), "MMM", "en-US")
& Date.ToText(Date.From(List.Min(_[Date])), "yy", "en-US")
& " to "
& Date.ToText(Date.From(List.Max(_[Date])), "MMM", "en-US")
& Date.ToText(Date.From(List.Max(_[Date])), "yy", "en-US"),
type text
}
},
GroupKind.Local
),
keepLongerPeriods = Table.SelectRows(
grouping1,
each (Text.Split([Dates], " to "){0} <> Text.Split([Dates], " to "){1})
),
grouping2 = Table.Group(
keepLongerPeriods,
{"EmpCode", "Role"},
{{"Dates", each Text.Combine(_[Dates], ", "), type text}}
),
pivotColumn = Table.Pivot(grouping2, List.Distinct(grouping2[EmpCode]), "EmpCode", "Dates")
in
pivotColumnPower Query solution 7 for Align Custom Data Layout, proposed by Aleksandar Kovacevic:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Grp = Table.Group(
Table.AddColumn(
Source,
"D",
each Date.ToText(Date.From([Date]), [Format = "MMMyy", Culture = "en-US"])
),
{"Role", "EmpCode"},
{"B", each Text.Combine({List.First(_[D]), List.Last(_[D])}, " to ")},
GroupKind.Local
),
Pvt = Table.Pivot(Grp, List.Distinct(Grp[EmpCode]), "EmpCode", "B", each Text.Combine(_, ",")),
Res = Table.Sort(Pvt, each List.PositionOf(Grp[Role], [Role]))
in
ResSolving the challenge of Align Custom Data Layout with Excel
Excel solution 1 for Align Custom Data Layout, proposed by Bo Rydobon 🇹🇭:
=LET(d,TEXT(A2:A23,"mmmy"),b,B2:B23,c,C2:C23,e,b&c,IFNA(IFS({1,0;0,0},"Title/Emp ID"),DROP(PIVOTBY(HSTACK(XMATCH(c,c),c),HSTACK(XMATCH(b,b),b),
IF(e<>DROP(VSTACK(0,e),-1),", "&d,REPT(" to "&d,DROP(e,1)<>e)),LAMBDA(x,MID(CONCAT(x),3,99)),,0,,0),1,1)))Excel solution 2 for Align Custom Data Layout, proposed by John V.:
=LET(
r,
C2:C23,
c,
CHOOSECOLS,
b,
GROUPBY(
HSTACK(
SCAN(
0,
r<>C1:C22,
SUM
),
r,
B2:B23
),
TEXT(
A2:A23,
"mmmy"
),
LAMBDA(
x,
@x&REPT(
" to "&LOOKUP(
"z",
x
),
ROWS(
x
)>1
)
),
,
0
),
PIVOTBY(
c(
b,
2
),
c(
b,
3
),
c(
b,
4
),
ARRAYTOTEXT,
,
0,
,
0
)
)
✅
Data Sorted like answer:
=LET(
r,
C2:C23,
c,
CHOOSECOLS,
h,
HSTACK,
m,
XMATCH,
b,
GROUPBY(
h(
SCAN(
0,
r<>C1:C22,
SUM
),
r,
B2:B23
),
TEXT(
A2:A23,
"mmmy"
),
LAMBDA(
x,
@x&REPT(
" to "&LOOKUP(
"z",
x
),
ROWS(
x
)>1
)
),
,
0
),
o,
c(
b,
2
),
p,
c(
b,
3
),
DROP(
PIVOTBY(
h(
m(
o,
o
),
o
),
h(
m(
p,
p
),
p
),
c(
b,
4
),
ARRAYTOTEXT,
,
0,
,
0
),
1,
1
)
)
✅
Data sorted like answer and title at the corner:
=LET(
r,
C2:C23,
c,
CHOOSECOLS,
h,
HSTACK,
m,
XMATCH,
b,
GROUPBY(
h(
SCAN(
0,
r<>C1:C22,
SUM
),
r,
B2:B23
),
TEXT(
A2:A23,
"mmmy"
),
LAMBDA(
x,
@x&REPT(
" to "&LOOKUP(
"z",
x
),
ROWS(
x
)>1
)
),
,
0
),
o,
c(
b,
2
),
p,
c(
b,
3
),
z,
DROP(
PIVOTBY(
h(
m(
o,
o
),
o
),
h(
m(
p,
p
),
p
),
c(
b,
4
),
ARRAYTOTEXT,
,
0,
,
0
),
1,
1
),
IF(
TAKE(
z,
1
)&TAKE(
z,
,
1
)="",
"Title/Emp ID",
z
)
)Excel solution 3 for Align Custom Data Layout, proposed by Timothée BLIOT:
=LET(
A,
A2:A23,
B,
B1:C23,
C,
B2:B23,
D,
SCAN(
0,
BYROW(
DROP(
B,
1
)<>DROP(
B,
-1
),
LAMBDA(
x,
SUM(
--x
)
)
),
SUM
),
V,
LAMBDA(
n,
m,
GROUPBY(
D,
n,
m,
,
0
)
),
W,
LAMBDA(
k,
TEXTBEFORE(
TAKE(
k,
,
-1
),
",",
,
,
,
TAKE(
k,
,
-1
)
)
),
E,
W(
V(
C2:C23,
ARRAYTOTEXT
)
),
F,
W(
V(
C,
ARRAYTOTEXT
)
),
G,
TEXT(
DROP(
TAKE(
V(
A,
HSTACK(
MIN,
MAX
)
),
,
-2
),
1
),
"MMMYY"
),
H,
BYROW(
G,
LAMBDA(
x,
TEXTJOIN(
" to ",
,
UNIQUE(
TOCOL(
x
)
)
)
)
),
I,
PIVOTBY(
E,
F,
H,
ARRAYTOTEXT,
,
0,
,
0
),
J,
VSTACK(
TAKE(
I,
1
),
SORTBY(
DROP(
I,
1,
),
XMATCH(
DROP(
I,
1,
-3
),
E
)
)
),
HSTACK(
TAKE(
J,
,
1
),
TRANSPOSE(
SORTBY(
TRANSPOSE(
DROP(
J,
,
1
)
),
XMATCH(
TRANSPOSE(
DROP(
J,
-6,
1
)
),
F
)
)
)
)
)Excel solution 4 for Align Custom Data Layout, proposed by Hussein SATOUR:
=LET(
I,
INDEX,
TA,
TEXTAFTER,
e,
B2:B23,
r,
C2:C23,
a,
SCAN(
,
1&"-"&e&r,
LAMBDA(
x,
y,
IF(
TA(
x,
"-"
)=TA(
y,
"-"
),
x,
TEXTBEFORE(
x,
"-"
)+1&"-"&TA(
y,
"-"
)
)
)
),
b,
GROUPBY(
HSTACK(
a,
r,
e
),
A2:A23,
LAMBDA(
z,
TEXT(
MIN(
z
),
"mmmyy"
)&IF(
MIN(
z
)Excel solution 5 for Align Custom Data Layout, proposed by Oscar Mendez Roca Farell:
=LET(g,GROUPBY(HSTACK(SCAN(,N(C1:C22<>C2:C23),SUM),B2:C23), TEXT(A2:A23,"b1mmmy"), LAMBDA(x,IF(ROWS(x)>1,@x&" to "&LOOKUP("z",x),@x)),,0),F,LAMBDA(a,HSTACK(XMATCH(a,a),a)),DROP(PIVOTBY(F(INDEX(g,,3)),F(INDEX(g,,2)),DROP(g,,3),ARRAYTOTEXT,,0,,0),1,1))Excel solution 6 for Align Custom Data Layout, proposed by Duy Tùng:
=LET(K,TAKE,H,HSTACK,b,B2:B23,c,C2:C23,u,DROP(PIVOTBY(H(SCAN(0,c<>C1:C22,SUM),c),H(XMATCH(b,b),b),TEXT(A2:A23,"[$-en]mmmy"),LAMBDA(x,IF(ROWS(x)=1,@x,@x&" to "&@K(x,-1))),,0,,0),1,1),n,IF(K(u,1)&K(u,,1)="","Title/Emp ID",u),DROP(GROUPBY(H(XMATCH(K(n,,1),c),K(n,,1)),DROP(n,,1),LAMBDA(v,TEXTJOIN(", ",,v)),3,0),,1))Excel solution 7 for Align Custom Data Layout, proposed by LEONARD OCHEA 🇷🇴:
=LET(t,A2:C23,C,CHOOSECOLS,r,C(t,3),F,LAMBDA(z,TEXT(z,"mmmy")),PIVOTBY(r,C(t,2),SCAN(,N(r<>VSTACK("",DROP(r,-1))),SUM)&"|"&C(t,1),LAMBDA(x,LET(w,TEXTSPLIT(TEXTJOIN("_",,x),"|","_"),ARRAYTOTEXT(C(GROUPBY(C(w,1),--C(w,2),LAMBDA(x,LET(i,MIN(x),j,MAX(x),IF(i=j,F(i),F(i)&" to "&F(j)))),,0),2)))),,0,,0))Excel solution 8 for Align Custom Data Layout, proposed by Md. Zohurul Islam:
=LET(
dt,
TEXT(
A2:A23,
"mmmyy"
),
emp,
B2:B23,
rol,
C2:C23,
a,
SCAN(
0,
VSTACK(
1,
ABS(
DROP(
rol,
-1
)<>DROP(
rol,
1
)
)
),
SUM
),
b,&
DROP(
PIVOTBY(
HSTACK(
a,
rol
),
emp,
dt,
LAMBDA(
x,
IF(
COUNTA(
UNIQUE(
x
)
)=1,
ARRAYTOTEXT(
x
),
TEXTJOIN(
" to ",
,
TAKE(
x,
1
),
TAKE(
x,
-1
)
)
)
),
0,
0,
,
0
),
,
1
),
p,
DROP(
TAKE(
b,
,
1
),
1
),
q,
DROP(
TAKE(
b,
1
),
,
1
),
s,
DROP(
b,
1,
1
),
d,
PIVOTBY(
TOCOL(
IFNA(
p,
q
)
),
TOCOL(
IFNA(
q,
p
)
),
TOCOL(
s
),
LAMBDA(
x,
TEXTJOIN(
", ",
1,
x
)
),
0,
0,
,
0
),
e,
VSTACK(
IF(
TAKE(
d,
1
)="",
"Title/Emp ID",
TAKE(
d,
1
)
),
SORTBY(
DROP(
d,
1
),
UNIQUE(
rol
)
)
),
e
)Excel solution 9 for Align Custom Data Layout, proposed by Hamidi Hamid:
=LET(ah,
HSTACK,
h,
TAKE,
v,
SORT(
ah(
A2:A23,
B2:C23
),
3,
1
),
x,
SORT(
ah(
MONTH(
A2:A23
),
B2:C23
),
3,
1
),
y,
IFERROR(
VSTACK(
DROP(
x,
1
),
""
),
""
),
m,
(DROP(
VSTACK(
"",
h(
x,
,
1
)
),
-1
)=h(
x,
,
1
)-1)*1,
k,
(CHOOSECOLS(
x,
2
)=VSTACK(
DROP(
CHOOSECOLS(
x,
2
),
1
),
""
))*1,
o,
m+k,
p,
IFERROR(
IF(
o=2,
VSTACK(
DROP(
h(
x,
1
),
1
),
""
),
h(
x,
,
1
)
),
""
),
s,
ah(
IF(
p="",
"",
TEXT(
v,
"mmm yyyy"
)
),
DROP(
x,
,
1
)
),
t,
DROP(
s,
,
-2
),
f,
PROPER(
PIVOTBY(
h(
t,
,
-1
),
CHOOSECOLS(
t,
2
),
h(
t,
,
1
),
LAMBDA(
a,
TEXTJOIN(
"- to ",
,
IF(
a="",
"",
a
)
)
),
0,
0,
,
0
)
),
g,
FILTER(
f,
BYCOL(
f<>"",
OR
)
),
FILTER(
g,
BYROW(
g<>"",
OR
)
))Excel solution 10 for Align Custom Data Layout, proposed by Asheesh Pahwa:
=LET(
e,
B2:B23,
r,
C2:C23,
d,
DROP(
REDUCE(
"",
UNIQUE(
e
),
LAMBDA(
x,
y,
VSTACK(
x,
LET(
f,
FILTER(
HSTACK(
A2:A23,
r
),
e=y
),
t,
TAKE(
f,
,
-1
),
u,
UNIQUE(
t
),
DROP(
REDUCE(
"",
u,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
I,
IF(
t=v,
TAKE(
f,
,
1
),
","
),
j,
TEXTJOIN(
"/",
,
I
),
s,
TEXTSPLIT(
j,
"/",
",",
1,
,
""
),
b,
BYROW(
s,
LAMBDA(
x,
TEXTJOIN(
"to",
1,
x
)
)
),
HSTACK(
v,
y,
ARRAYTOTEXT(
IF(
LEN(
b
)>5,
TEXT(
LEFT(
b,
5
),
"mmmyy"
)&" to "&TEXT(
RIGHT(
b,
5
),
"mmmyy"
),
b
)
)
)
)
)
)
),
1
)
)
)
)
),
1
),
c,
UNIQUE(
r
)&TOROW(
UNIQUE(
e
)
),
x,
XLOOKUP(
c,
TAKE(
d,
,
1
)&INDEX(
d,
,
2
),
TAKE(
d,
,
-1
),
""
),
x
)Excel solution 11 for Align Custom Data Layout, proposed by LUIS FLORENTINO COUTO CORTEGOSO:
=LET(b,B2:B23,c,C2:C23,s,SORTBY,e,CHOOSECOLS,u,UNIQUE,A,HSTACK,v,A(1,TOROW(u(b))),w,VSTACK(1,u(c)),m,GROUPBY(A(SCAN(0,c<>C1:C22,SUM),b,c),A2:A23,LAMBDA(x,TEXT(MIN(x),"mmma")& " to " & TEXT(MAX(x), "mmma")),,0),p,PIVOTBY(e(m,3),e(m,2),e(m,4),ARRAYTOTEXT,,0,,0,),s(s(p,w,),v))Excel solution 12 for Align Custom Data Layout, proposed by red craven:
=LET(a,TEXT(A2:A23,"mmmy"),b,B2:B23,c,C2:C23,j,b&c,p,DROP(SUBSTITUTE(PIVOTBY(HSTACK(XMATCH(c,c),c),HSTACK(XMATCH(b,b),b),IFS(j<>VSTACK(0,DROP(j,-1)),", "&a,j<>VSTACK(DROP(j,1),0)," to "&a,1,""),CONCAT,,0,,0),", ",,1),1,1),IF(TAKE(p,,1)&p="","Title/Emp ID",p))Solving the challenge of Align Custom Data Layout with Python
Python solution 1 for Align Custom Data Layout, proposed by Konrad Gryczan, PhD:
One role is different between input and output
import pandas as pd
from datetime import datetime
import locale
path = "686 Data Alignment.xlsx"
input = pd.read_excel(path, usecols="A:C", nrows=23)
test = pd.read_excel(path, usecols="E:H", skiprows=1, nrows=7)
locale.setlocale(locale.LC_TIME, "English")
input['Role_no'] = input.groupby('EmpCode')['Role'].apply(
lambda x: (x != x.shift().fillna(x.iloc[0])).cumsum()
).reset_index(level=0, drop=True)
result = (
input.groupby(['EmpCode', 'Role_no', 'Role'])
.agg(
max_date=('Date', lambda x: x.max().strftime("%b%y")),
min_date=('Date', lambda x: x.min().strftime("%b%y"))
)
.reset_index()
)
result['period'] = result.apply(
lambda row: row['min_date'] if row['max_date'] == row['min_date'] else f"{row['min_date']} to {row['max_date']}",
axis=1
)
result = result.pivot_table(
index='Role',
columns='EmpCode',
values='period',
aggfunc=lambda x: ', '.join(x)
).reset_index()
print(result)
Python solution 3 for Align Custom Data Layout, proposed by Abdallah Ally:
import pandas as pd
def get_min_max_dates(date_list):
mndate = min(date_list).strftime('%b%y')
mxdate = max(date_list).strftime('%b%y')
return mndate + ('' if mndate == mxdate else ' to ' + mxdate)
file_path = 'Excel_Challenge_686 - Data Alignment.xlsx'
df = pd.read_excel(io=file_path, usecols='A:C')
# Perform data manipulation
rows = df['Role'].unique().tolist()
columns = df['EmpCode'].unique().tolist()
df = (
df
.assign(
Check = df['EmpCode'] + df['Role'],
Group = lambda df: (df['Check'] != df['Check'].shift(1)).cumsum()
)
.groupby(['Group', 'Role']).agg({'Date': lambda x: get_min_max_dates(x), 'EmpCode': 'first'})
.reset_index()
.pivot_table(values='Date', index='Role', columns='EmpCode', aggfunc=', '.join, fill_value='')
.rename_axis('', axis=1)
.reset_index()
.rename(columns={'Role': 'Title/Emp ID'})
)
df = df.sort_values(by='Title/Emp ID', ignore_index=True, key=lambda x: [rows.index(y) for y in x])
df = df[['Title/Emp ID'] + columns]
df
Solving the challenge of Align Custom Data Layout with Python in Excel
Python in Excel solution 1 for Align Custom Data Layout, proposed by Alejandro Campos:
df = xl("A1:C23", headers=True)
df["Date"] = pd.to_datetime(df["Date"], format="%d-%b-%y")
def fmt(d):
d, r, s = sorted(d), [], d[0]
for i in range(1, len(d)):
if (d[i] - d[i-1]).days > 32:
r.append(f"{s.strftime('%b%y')} to {d[i-1].strftime('%b%y')}" if s != d[i-1] else s.strftime('%b%y'))
s = d[i]
r.append(f"{s.strftime('%b%y')} to {d[-1].strftime('%b%y')}" if s != d[-1] else s.strftime('%b%y'))
return ", ".join(r)
g = df.groupby(["Role", "EmpCode"]).agg({"Date": list}).reset_index()
g["Date"] = g["Date"].apply(fmt)
p = g.pivot(index="Role", columns="EmpCode", values="Date").fillna("")[["EMP_1157", "EMP_1078", "EMP_1247"]]
p = p.reindex(["Branch Manager", "Procurement", "Team Supervisor", "Customer Support", "Regional Manager", "Facilities Team"]).reset_index()
p.columns = ["Title/Emp ID", "EMP_1157", "EMP_1078", "EMP_1247"]
p
Python in Excel solution 2 for Align Custom Data Layout, proposed by Aditya Kumar Darak 🇮🇳:
df = xl("A1:C23", True)
df["D"] = pd.to_datetime(df["Date"], format="%d-%b-%y")
df["G"] = df["Role"] != df.groupby("EmpCode")["Role"].shift()
df["G"] = df.groupby("EmpCode")["G"].cumsum()
grp = (
df.groupby(["EmpCode", "Role", "G"])
.agg(S=("D", "min"), E=("D", "max"))
.reset_index()
)
grp["P"] = grp.apply(
lambda x: x["S"].strftime("%b%y")
if x["S"] == x["E"]
else f"{x['S'].strftime('%b%y')} to {x['E'].strftime('%b%y')}",
axis=1,
)
grp = grp.groupby(["EmpCode", "Role"])["P"].apply(", ".join).reset_index()
roles = df["Role"].unique()
emps = df["EmpCode"].unique()
result = {"Role": roles}
for e in emps:
e_data = grp[grp["EmpCode"] == e].set_index("Role")["P"]
result[e] = [e_data.get(r, "") for r in roles]
result = pd.DataFrame(result)
&&