Transform the problem table into result table as shown.
📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 242
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reformat Item Entries with Power Query
Power Query solution 1 for Reformat Item Entries, proposed by Zoran Milokanović:
let
Source = Table.UnpivotOtherColumns(
Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name = "Input"]}[Content], "I"),
{"I"},
"Column1",
"Column2"
),
S = Table.RemoveColumns(
Table.SelectRows(
Source,
each (
[Column1]
= "Hall" and Table.PositionOf(Source, _)
= Table.PositionOf(Source[[Column2]], [Column2 = [Column2]]) or [Column1]
<> "Hall"
)
),
{"I"}
)
in
SPower Query solution 2 for Reformat Item Entries, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.UnpivotOtherColumns(A, {"Hall"}, "Column1", "Column2"),
C = Table.Combine(
Table.Group(
B,
{"Hall"},
{
{
"All",
each Table.InsertRows(_, 0, {[Hall = null, Column1 = "Hall", Column2 = [Hall]{0}]})[
[Column1],
[Column2]
]
}
}
)[All]
)
in
CPower Query solution 3 for Reformat Item Entries, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Group = Table.Group(
Source,
"Hall",
{
"A",
each [U = Table.UnpivotOtherColumns(_, {}, "Column1", "Column2"), R = Table.Distinct(U)][R]
}
),
Return = Table.Combine(Group[A])
in
ReturnPower Query solution 4 for Reformat Item Entries, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unp = Table.UnpivotOtherColumns(Source, {"Hall"}, "Col1", "Col2"),
Sol = Table.Combine(
Table.Group(
Unp,
{"Hall"},
{{"A", each Table.FromColumns({{"Hall"} & [Col1]} & {{[Hall]{0}} & [Col2]})}}
)[A]
)
in
SolPower Query solution 5 for Reformat Item Entries, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unp = Table.UnpivotOtherColumns(Source, {}, "Col1", "Col2"),
Add = Table.AddColumn(
Unp,
"A",
each try if Text.Contains([Col2], "Hall") then [Col2] else null otherwise null
),
FD = Table.FillDown(Add, {"A"}),
Sol = Table.Distinct(FD, Table.ColumnNames(FD))[[Col1], [Col2]]
in
SolPower Query solution 6 for Reformat Item Entries, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
Table.Group(
Source,
{"Hall"},
{
{
"A",
each
let
a = _,
b = Table.ColumnNames(a),
c = {a{0}[Hall]} & List.Combine(Table.ToRows(Table.RemoveColumns(a, "Hall"))),
d = {b{0}} & List.Repeat(List.Skip(b), Table.RowCount(a)),
e = Table.SelectRows(Table.FromColumns({d} & {c}), each [Column2] <> null)
in
e
}
}
)[A]
)
in
SolPower Query solution 7 for Reformat Item Entries, proposed by Luan Rodrigues:
let
grp = Table.Group(
Tabela1,
{"Hall"},
{{"tab", each Table.Distinct(Table.UnpivotOtherColumns(_, {}, "Atributo", "Valor"))}}
)[tab],
rst = Table.Combine(grp)
in
rstPower Query solution 8 for Reformat Item Entries, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Hall", "Date"}, "Attrib", "Value"),
Transform = List.Transform(
List.Distinct(Unpivot[Hall]),
each [
a = Table.SelectRows(Unpivot, (x) => x[Hall] = _),
b = List.Transform(
List.Distinct(a[Date]),
(x) =>
[
u = Table.SelectRows(a, (y) => y[Date] = x),
v = {{"Date", Date.From(x)}} & Table.ToRows(u[[Attrib], [Value]])
][v]
),
c = {{"Hall", _}} & List.Combine(b)
][c]
),
Result = Table.FromRows(List.Combine(Transform))
in
ResultPower Query solution 9 for Reformat Item Entries, proposed by Abdallah Ally:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivot = Table.UnpivotOtherColumns(Source, {"Hall"}, "A", "V"),
Group = Table.Group(Unpivot, {"Hall"}, {{"Data", each _}}),
Transform = Table.TransformRows(Group, each {{"Hall", [Hall]}} & Table.ToRows([Data][[A], [V]])),
Result = Table.FromRows(List.Combine(Transform))
in
ResultPower Query solution 10 for Reformat Item Entries, proposed by Eric Laforce:
let
Source = Excel.CurrentWorkbook(){[Name = "tData242"]}[Content],
Group = Table.Group(
Source,
"Hall",
{
"G",
(t) =>
let
_Unpivot = Table.UnpivotOtherColumns(Table.RemoveColumns(t, {"Hall"}), {}, "A", "V")
in
{{"Hall", t[Hall]{0}}} & Table.ToRows(_Unpivot)
}
),
Result = Table.FromRows(List.Combine(Group[G]))
in
ResultPower Query solution 11 for Reformat Item Entries, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Unpivoted = Table.UnpivotOtherColumns(Source, {"Hall"}, "Column 1", "Column 2"),
Grouped = Table.Group(
Unpivoted,
{"Hall"},
{{"Rows", each {{"Hall", [Hall]{0}}} & List.Zip({[Column 1], [Column 2]})}}
)[Rows],
Res = Table.FromRows(List.Combine(Grouped))
in
ResPower Query solution 12 for Reformat Item Entries, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
A = Table.Group(Source, {"Hall"}, {{"T", each _}}),
F=(x)=>
let
a = Table.TransformColumnTypes(x,{{"Date", type date}}),
b = Table.FromColumns({Table.ToRows(Table.RemoveColumns(a,{"Hall"}))},{"T"}),
c = Table.AddColumn(b, "T2", each Table.SelectRows(Table.FromColumns({List.Skip(Table.ColumnNames(a),1),[T]}),each [Column2]<>null)),
d = Table.Combine({hashtag#table({"Column1","Column2"},{{"Hall",a[Hall]{0}}}),Table.Combine(c[T2])})
in
d,
B = Table.AddColumn(A, "F", each F([T])),
C = Table.Combine(B[F])
in
C
Power Query solution 13 for Reformat Item Entries, proposed by Peter Krkos:
PowerQuery solution:
= Table.Combine(Table.Group(Source, "Hall", {{"T", each
[ Unpivoted = Table.UnpivotOtherColumns(_, {"Hall"}, "Column1", "Column2"),
InsertedRow = Table.InsertRows(
Unpivoted,
0,
{Unpivoted{0} & [Column1 = Text.Select(Unpivoted{0}[Hall], {"a".."z", "A".."Z"}), Column2 = Unpivoted{0}[Hall]]})[[Column1], [Column2]]
][InsertedRow], type table}}, 0,
(x,y)=> Value.Compare(x, y))[T])
Power Query solution 14 for Reformat Item Entries, proposed by CA Raghunath Gundi:
let
Source = Excel.CurrentWorkbook(){[Name = "Question"]}[Content],
A = Table.TransformColumnTypes(
Source,
{
{"Hall", type text},
{"Date", type date},
{"Guest1", type text},
{"Guest2", type text},
{"Guest3", type text},
{"Guest4", type text}
}
),
B = Table.AddIndexColumn(A, "Index", 0, 1, Int64.Type),
C = Table.UnpivotOtherColumns(B, {"Index"}, "Attribute", "Value"),
D = Table.RemoveColumns(C, {"Index"}),
E = Table.AddColumn(D, "Custom", each [Attribute] & [Value]),
F = Table.AddIndexColumn(E, "Index", 0, 1, Int64.Type),
G = Table.SelectRowsWithErrors(F, {"Custom"}),
H = F,
I = Table.RemoveRowsWithErrors(H, {"Custom"}),
J = Table.Distinct(I, {"Custom"}) & G,
K = Table.Sort(J, {{"Index", Order.Ascending}}),
L = Table.RemoveColumns(K, {"Custom", "Index"})
in
LPower Query solution 15 for Reformat Item Entries, proposed by Alexandre Garcia:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = {"Column1","Column2"},
C = "Hall",
D = Table.Combine(Table.Group(Table.UnpivotOtherColumns(A, {C}, B{0}, B{1}), C, {"x", each hashtag#table(B,{{C,Table.FirstValue(_)}}) & Table.SelectColumns(_,B)})[x])
in D
Power Query solution 16 for Reformat Item Entries, proposed by Francesco Bianchi 🇮🇹:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
sol = Table.SelectRows(
Table.Combine(
Table.Group(
Source,
{"Hall"},
{
{
"All",
each [
r = Table.ToRows(_),
c = List.Combine(r),
n = List.Count(r),
i = Table.ColumnNames(Source),
a = List.Repeat(i, n),
z = List.Zip({a, c}),
d = List.Distinct(z),
t = Table.FromRows(d)
][t]
}
}
)[All]
),
each [Column2] <> null
)
in
solPower Query solution 17 for Reformat Item Entries, proposed by Joevan Bedico:
let
Source = Excel.CurrentWorkbook(){[Name = "Table2"]}[Content],
Answer = Table.Combine(
Table.Group(
Source,
"Hall",
{
"Grp",
each
let
x = Table.RemoveColumns(_, "Hall")
in
Table.FromRows(
{{"Hall", [Hall]{0}}}
& List.Combine(
List.Transform(
Table.ToRows(x),
each List.Select(List.Zip({Table.ColumnNames(x), _}), each _{1} <> null)
)
)
)
}
)[Grp]
)
in
AnswerPower Query solution 18 for Reformat Item Entries, proposed by Khanh Lam chi:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Date", type date}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
#"Changed Type",
{"Hall"},
"Column1",
"Column2"
),
#"Grouped Rows" = Table.Combine(
Table.Group(
#"Unpivoted Other Columns",
{"Hall"},
{
"Count",
(g) =>
let
tbl = Table.RemoveColumns(g, "Hall")
in
Table.InsertRows(
tbl,
0,
{[Column1 = Table.ColumnNames(Source){0}, Column2 = Table.FirstValue(g)]}
)
}
)[Count]
)
in
#"Grouped Rows"Solving the challenge of Reformat Item Entries with Excel
Excel solution 1 for Reformat Item Entries, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:F5,
l,
LAMBDA(
a,
TOCOL(
IFS(
z>0,
a
),
3
)
),
DROP(
UNIQUE(
HSTACK(
l(
A2:A5
),
l(
A1:F1
),
l(
z
)
)
),
,
1
)
)Excel solution 2 for Reformat Item Entries, proposed by Bo Rydobon 🇹🇭:
=LET(
z,
A2:F5,
h,
A2:A5,
d,
TEXTSPLIT(
TEXTAFTER(
UNIQUE(
TOCOL(
IFS(
z>0,
h&A1:F1&"_"&z
),
3
)
),
VSTACK(
h,
"_"
),
{1,
2}
),
"_"
),
IFERROR(
--d,
d
)
)Excel solution 3 for Reformat Item Entries, proposed by Julian Poeltl:
=LET(
H,
A1:F1,
T,
A2:F5,
C,
HSTACK(
IF(
VSTACK(
0,
DROP(
TAKE(
T,
,
1
),
-1
)
)=TAKE(
T,
,
1
),
"",
TAKE(
T,
,
1
)
),
DROP(
T,
,
1
)
),
S,
TOCOL(
C,
3
),
HSTACK(
TOCOL(
IF(
C<>"",
H,
N
),
3
),
FILTER(
S,
S<>""
)
)
)Excel solution 4 for Reformat Item Entries, proposed by Duy Tùng:
=LET(
f,
LAMBDA(
x,
TOCOL(
IFS(
A2:F5>0,
x
),
3
)
),
DROP(
UNIQUE(
HSTACK(
f(
A2:A5
),
f(
A1:F1
),
f(
A2:F5
)
)
),
,
1
)
)Excel solution 5 for Reformat Item Entries, proposed by Sunny Baggu:
=LET(
_f,
DROP(
REDUCE(
"",
SEQUENCE(
ROWS(
A2:A5
)
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
_a,
CHOOSEROWS(
& A2:F5,
v
),
TRANSPOSE(
VSTACK(
FILTER(
A1:F1,
_a <> ""
),
FILTER(
_a,
_a <> ""
)
)
)
)
)
)
),
1
),
_c,
N(
TAKE(
_f,
,
-1
) = TOROW(
UNIQUE(
A2:A5
)
)
),
_r,
SEQUENCE(
ROWS(
_f
)
),
_k,
BYCOL(
IF(
_c,
1,
x
) * _r,
LAMBDA(
a,
MIN(
TOCOL(
a,
3
)
)
)
),
_m,
BYROW(
N(
_r = _k
),
LAMBDA(
a,
SUM(
a
)
)
),
_n,
BYROW(
_c,
LAMBDA(
a,
SUM(
a
)
)
),
FILTER(_f,
1 - (_n - _m))
)Excel solution 6 for Reformat Item Entries, proposed by LEONARD OCHEA 🇷🇴:
=LET(
d,
HSTACK(
SCAN(
,
A2:A5,
LAMBDA(
a,
b,
IF(
b=a,
"",
b
)
)
),
B2:F5
),
F,
LAMBDA(
x,
TOCOL(
IF(
d<>"",
x,
z
),
3
)
),
HSTACK(
F(
A1:F1
),
F(
d
)
)
)Excel solution 7 for Reformat Item Entries, proposed by Md. Zohurul Islam:
=LET(
p,
A2:A5,
q,
B1:F1,
r,
B2:F5,
unq,
UNIQUE(
p
),
s,
DROP(
REDUCE(
"",
unq,
LAMBDA(
x,
y,
LET(
a,
TOCOL(
FILTER(
r,
p=y
)
),
b,
TOCOL(
IFNA(
q,
FILTER(
p,
p=y
)
)
),
d,
HSTACK(
b,
a
),
e,
FILTER(
d,
a<>0
),
f,
HSTACK(
"Hall",
y
),
g,
VSTACK(
f,
e
),
h,
VSTACK(
x,
g
),
h
)
)
),
1
),
s
)Excel solution 8 for Reformat Item Entries, proposed by Pieter de B.:
=HSTACK(TOCOL(IFS(A2:F5>0,A1:F1),2),TOCOL(A2:F5,1))Excel solution 9 for Reformat Item Entries, proposed by Hamidi Hamid:
=LET(m,
B2:F5,
n,
A2:A5,
k,
A1:F1,
u,
IF(
n=VSTACK(
DROP(
n,
1
),
0
),
n,
""
),
x,
HSTACK(
TOCOL(
IFNA(
k,
m
)
),
TOCOL(
IFNA(
HSTACK(
u,
m
),
k
)
)
),
p,
TAKE(
x,
,
-1
),
t,
VSTACK(h1:i1,
FILTER(x,
(p<>"")*(p>0))),
t)Excel solution 10 for Reformat Item Entries, proposed by ferhat CK:
=LET(
a,
LAMBDA(
x,
y,
z,
LET(
q,
TOCOL(
x&"-"&y
),
w,
HSTACK(
TEXTBEFORE(
q,
"-"
),
TEXTAFTER(
q,
"-"
)
),
e,
FILTER(
w,
TAKE(
w,
,
-1
)<>""
),
VSTACK(
z,
MAP(
e,
LAMBDA(
j,
IFERROR(
TEXT(
j,
"gg.aa.yyyy"
),
y
)
)
)
)
)
),
VSTACK(
a(
B1:F1,
B2:F3,
HSTACK(
A1,
A2
)
),
a(
B1:F1,
B4:F5,
HSTACK(
A1,
A4
)
)
)
)Excel solution 11 for Reformat Item Entries, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
j,
LET(
y,
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
BYROW(
HSTACK(
TEXTSPLIT(
TEXTJOIN(
",",
,
MAP(
UNIQUE(
A2:A5
),
LAMBDA(
f,
LET(
e,
IF(
f=A2:A5,
1,
0
),
LET(
d,
SCAN(
0,
e,
LAMBDA(
a,
b,
SUM(
a,
b
)
)
),
TEXTJOIN(
",",
,
LET(
c,
IF(
e>0,
IF(
d>0,
d,
""
),
""
),
FILTER(
c,
c<>""
)
)
)
)
)
)
)
),
,
","
),
B2:F5
),
LAMBDA(
x,
TEXTJOIN(
",",
FALSE,
TOCOL(
x
)
)
)
)&","
),
,
",",
FALSE
),
-1
),
IFERROR(
VALUE(
y
),
y
)
),
HSTACK(
FILTER(
TEXTSPLIT(
REPT(
TEXTJOIN(
",",
,
TOCOL(
A1:F1
)
)&",",
COUNTA(
A2:A5
)
),
,
",",
TRUE
),
IF(
j=2,
"",
j
)<>""
),
LET(
i,
IF(
j=2,
"",
LET(
z,
DROP(
TEXTSPLIT(
TEXTJOIN(
,
,
BYROW(
A2:F5,
LAMBDA(
x,
TEXTJOIN(
",",
FALSE,
TOCOL(
x
)
)
)
)&","
),
,
",",
FALSE
),
-1
),
IFERROR(
VALUE(
z
),
z
)
)
),
FILTER(
i,
i<>""
)
)
)
)Excel solution 12 for Reformat Item Entries, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=LET(
i,
LET(
c,
TEXTSPLIT(
TEXTJOIN(
",",
FALSE,
BYROW(
A2:F5,
LAMBDA(
x,
TEXTJOIN(
",",
FALSE,
TOCOL(
x
)
)
)
)
),
,
",",
FALSE
),
FILTER(
c,
c<>""
)
),
IF(
BYROW(
IF(
TRANSPOSE(
TEXTSPLIT(
TEXTJOIN(
,
,
MAP(
UNIQUE(
A2:A5
),
LAMBDA(
a,
TEXTJOIN(
",",
,
a=i
)&"/"
)
)
),
",",
"/",
TRUE
)
),
1,
0
),
LAMBDA(
a,
SUM(
a
)
)
)=1,
i,
""
)
)
=LET(
v,
MAP(
J2#,
LAMBDA(
y,
IF(
AND(
y<>"",
COUNTIF(
J2:y,
y
)>1
),
1,
0
)
)
),
a,
TEXTSPLIT(
TEXTJOIN(
",",
FALSE,
BYROW(
A2:F5,
LAMBDA(
x,
TEXTJOIN(
",",
FALSE,
TOCOL(
x
)
)
)
)
),
,
",",
FALSE
),
HSTACK(
LET(
j,
IF(
v=0,
FILTER(
TEXTSPLIT(
REPT(
TEXTJOIN(
",",
,
TOCOL(
A1:F1
)
)&",",
COUNTA(
A2:A5
)
),
,
",",
TRUE
),
a<>""
),
""
),
FILTER(
j,
j<>""
)
),
LET(
b,
LET(
j,
IF(
v=0,
LET(
c,
a,
FILTER(
c,
c<>""
)
),
""
),
FILTER(
j,
j<>""
)
),
IFERROR(
VALUE(
b
),
b
)
)
)
)Excel solution 13 for Reformat Item Entries, proposed by Imam Hambali:
=LET(
h,
A2:A5,
ha,
IF(
MOD(
SEQUENCE(
ROWS(
h
)
),
2
)=0,
NA(),
h
),
hc,
HSTACK(
ha,
B2:F5
),
co,
TOCOL(
hc,
3
),
d,
HSTACK(TOCOL(IF((NOT(
ISNA(
hc
)
))*(hc>0)=1,
A1:F1,
NA()),
3),
co),
VSTACK(
{"Column1",
"Column2"},
d
)
)Excel solution 14 for Reformat Item Entries, proposed by Eddy Wijaya:
=REDUCE(
H1:I1,
UNIQUE(
A2:A5
),
LAMBDA(
a,
v,
VSTACK(
a,
LET(
I,
UNIQUE(
TOCOL(
FILTER(
A2:F5,
A2:A5=v
),
3
)
),
c,
SCAN(
0,
I,
LAMBDA(
a,
v,
IF(
LEN(
v
)=1,
a+1,
0
)
)
),
&
HSTACK(
IFERROR(
IFS(
c>0,
"Guest"&c,
--I,
"Date"
),
"Hall"
),
I
)
)
)
)
)Excel solution 15 for Reformat Item Entries, proposed by red craven:
=LET(
a,
A2:A5,
b,
B2:F5,
v,
HSTACK(
IFS(
a<>A1:A4,
a
),
IFS(
b>0,
b
)
),
HSTACK(
TOCOL(
IFS(
v>0,
A1:F1
),
3
),
TOCOL(
v,
3
)
)
)Solving the challenge of Reformat Item Entries with Python
Python solution 1 for Reformat Item Entries, proposed by Luan Rodrigues:
import pandas as pd
file = "PQ_Challenge_242.xlsx"
df = pd.read_excel(file,usecols="A:F")
grp = df.groupby(['Hall','Date']).apply(lambda x: pd.melt(x,id_vars=[],var_name="Atributo",value_name="Valor").dropna() ).reset_index(drop=True)
rst = pd.DataFrame(grp).drop_duplicates()
print(rst)
Python solution 2 for Reformat Item Entries, proposed by Abdallah Ally:
import pandas as pd
file_path = 'PQ_Challenge_242.xlsx'
df = pd.read_excel(file_path, usecols='A:F', nrows=4)
# Perform data manipulation
df = (df
.assign(Index = df.index)
.melt(
id_vars=['Hall', 'Index'],
var_name='Column1',
value_name='Column2'
)
.dropna(subset='Column2')
.sort_values(by=['Hall', 'Index'])
)
dfs = []
columns = df.columns[2:]
for hall in df['Hall'].unique():
top_row = pd.DataFrame(data=[['Hall', hall]], columns=columns)
hall_values = df[columns][df['Hall'] == hall]
dfs.extend([top_row, hall_values])
df = pd.concat(dfs, ignore_index=True)
df
Solving the challenge of Reformat Item Entries with Python in Excel
Python in Excel solution 1 for Reformat Item Entries, proposed by Alejandro Campos:
df = xl("A1:F5", headers=True)
result = [[key, val] for _, row in df.iterrows() for key, val in
[['Hall', row['Hall']], ['Date', row['Date']]] + [[guest, row[guest]] for guest in
['Guest1', 'Guest2', 'Guest3', 'Guest4'] if pd.notna(row[guest])]]
result_df = pd.DataFrame(result, columns=['Column1', 'Column2'])
.drop([4, 13]).reset_index(drop=True)
Python in Excel solution 2 for Reformat Item Entries, proposed by Aditya Kumar Darak 🇮🇳:
data = xl("A1:F5", headers=True)
def Chlng(group):
Melt = (
group.melt(var_name="Column1", value_name="Column2", ignore_index=False)
.dropna()
.drop_duplicates()
)
Index = Melt.sort_index().reset_index(drop=True)
return Index
data = data.groupby("Hall").apply(Chlng).reset_index(drop=True)
data
Solving the challenge of Reformat Item Entries with R
R solution 1 for Reformat Item Entries, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_242.xlsx"
input = read_excel(path, range = "A1:F5")
test = read_excel(path, range = "H1:I16")
result = input %>%
mutate(across(everything(), as.character)) %>%
mutate(no = row_number() %>% as.character(), .by = Hall) %>%
unite(Hall, Hall, no, sep = "_") %>%
pivot_longer(everything(), names_to = "name", values_to = "value") %>%
filter(!is.na(value), !str_ends(value, "_2")) %>%
mutate(value = str_remove(value, "_1"))
colnames(result) = colnames(test)
all.equal(result, test, check.attributes = FALSE)
# not equal because of wrong formating of dates in equipment.
R solution 2 for Reformat Item Entries, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Power Query/PQ_Challenge_242.xlsx"
input = read_excel(path, range = "A1:F5")
test = read_excel(path, range = "H1:I16")
result = input %>%
mutate(across(everything(), as.character)) %>%
mutate(no = row_number() %>% as.character(), .by = Hall) %>%
unite(Hall, Hall, no, sep = "_") %>%
pivot_longer(everything(), names_to = "name", values_to = "value") %>%
filter(!is.na(value), !str_ends(value, "_2")) %>%
mutate(value = str_remove(value, "_1"))
colnames(result) = colnames(test)
all.equal(result, test, check.attributes = FALSE)
# not equal because of wrong formating of dates in equipment.
&