Reverse data given in rows.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 291
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Reverse data given in rows with Power Query
Power Query solution 1 for Reverse data given in rows, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Ans = Table.FromRows(
List.Transform(
Table.ToRows(Source),
each List.Sort(_, {{each _ is null}, (r) => - List.PositionOf(_, r)})
),
Table.ColumnNames(Source)
)
in
AnsPower Query solution 2 for Reverse data given in rows, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromRows(
List.Transform(
Table.ToRows(Source),
(r) => List.Sort(r, {{each _ = null}, {each List.PositionOf(r, _), 1}})
),
Table.ColumnNames(Source)
)
in
SPower Query solution 3 for Reverse data given in rows, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
S = Table.FromColumns(
List.Zip(List.Transform(Table.ToRows(Source), each List.Reverse(List.RemoveNulls(_)))),
Table.ColumnNames(Source)
)
in
SPower Query solution 4 for Reverse data given in rows, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
ToRow = Table.ToRows(Source),
Reverse = List.Transform(
ToRow,
each List.Sort(_, {(f) => List.PositionOf(_, f ?? Number.PositiveInfinity), 1})
),
Return = Table.FromRows(Reverse, Table.ColumnNames(Source))
in
ReturnPower Query solution 5 for Reverse data given in rows, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.Combine(
List.Transform(
Table.ToRows(Source),
each
let
a = List.RemoveNulls(_),
b = List.Reverse(a),
c = Table.FromRows({b}, List.FirstN(Table.ColumnNames(Source), List.Count(b)))
in
c
)
)
in
SolPower Query solution 6 for Reverse data given in rows, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.Transpose(
Table.FromColumns(List.Transform(Table.ToRows(Fonte), each List.RemoveNulls(List.Reverse(_)))),
Table.ColumnNames(Fonte)
)
in
resPower Query solution 7 for Reverse data given in rows, proposed by Ramiro Ayala Chávez:
let
Origen = Excel.CurrentWorkbook(){0}[Content],
a = List.Transform(Table.ToRows(Origen), each List.Reverse(_)),
b = Table.Transpose(Table.FromColumns(List.Transform(a, each List.Select(_, each _ <> null)))),
c = Table.RenameColumns(
b,
{
{"Column1", "Cities1"},
{"Column2", "Cities2"},
{"Column3", "Cities3"},
{"Column4", "Cities4"},
{"Column5", "Cities5"}
}
)
in
cPower Query solution 8 for Reverse data given in rows, proposed by Luke Jarych:
let
Source = Table1,
Table0 = Table.ToRows(Source),
Table2 = List.Transform(Table0, each List.RemoveNulls(List.Reverse(_))),
Table3 = Table.FromColumns(Table2),
Table4 = Table.Transpose(Table3, Table.ColumnNames(Source))
in
Table4Power Query solution 9 for Reverse data given in rows, proposed by Szabolcs Phraner:
let
Source = Excel.CurrentWorkbook(){[Name = "table"]}[Content],
ColNames = Table.ColumnNames(Source),
//Custom function for row reversal
ReverseRows = (Row) =>
let
ReversedRow = List.RemoveNulls(List.Reverse(Row)),
FieldCount = List.Count(ReversedRow),
ColumnCount = List.Count(ColNames),
//Prevents error by supplementing each row in order to match that total number of columns
Supplement = List.Repeat({null}, ColumnCount - FieldCount)
in
List.InsertRange(ReversedRow, FieldCount, Supplement),
ReversedRows = List.Transform(Table.ToRows(Source), ReverseRows),
TableFromRows = Table.FromRows(ReversedRows, ColNames)
in
TableFromRowsSolving the challenge of Reverse data given in rows with Excel
Excel solution 1 for Reverse data given in rows, proposed by Bo Rydobon 🇹🇭:
=VSTACK(
A1:E1,
TEXTSPLIT(
TEXTJOIN(
0,
,
EXPAND(
SORTBY(
A2:E19,
{5,
4,
3,
2,
1}
),
,
6,
1
)
),
0,
1,
1,
,
""
)
)
=LET(
z,
A2:E19,
REDUCE(
A1:E1,
SEQUENCE(
ROWS(
z
)
),
LAMBDA(
a,
n,
LET(
x,
INDEX(
z,
n,
),
VSTACK(
a,
""&SORTBY(
x,
x=0,
,
-COLUMN(
z
),
)
)
)
)
)
)Excel solution 2 for Reverse data given in rows, proposed by Rick Rothstein:
=TOROW(INDEX(A2:E2,,SEQUENCE(,5,5,-1)),3)Excel solution 3 for Reverse data given in rows, proposed by Rick Rothstein:
=IFNA(
TEXTSPLIT(
TEXTJOIN(
"*",
,
BYROW(
A2:E19,
LAMBDA(
r,
TEXTJOIN(
"/",
,
INDEX(
r,
,
SEQUENCE(
,
5,
5,
-1
)
)
)
)
)
),
"/",
"*"
),
""
)Excel solution 4 for Reverse data given in rows, proposed by John V.:
=REDUCE(A1:E1,SEQUENCE(ROWS(A2:E19)),LAMBDA(a,v,LET(r,INDEX(A2:E19,v,{5,4,3,2,1}),VSTACK(a,SORTBY(r,r=0)&""))))
Inspired by محمد حلمي :
✅=REDUCE(A1:E1,A2:A19,LAMBDA(a,v,LET(r,INDEX(v:E19,1,{5,4,3,2,1}),VSTACK(a,SORTBY(r,r=0)&""))))Excel solution 5 for Reverse data given in rows, proposed by محمد حلمي:
=REDUCE(
A1:E1,
A2:A19,
LAMBDA(
a,
d,
LET(
x,
INDEX(
d:E19,
1,
),
VSTACK(
a,
SORTBY(
x,
x=0,
,
{5,
4,
3,
2,
1},
)
)&""
)
)
)Excel solution 6 for Reverse data given in rows, proposed by Kris Jaganah:
=LET(
a,
A2:E19,
b,
A1:E1,
VSTACK(
b,
TEXTSPLIT(
TEXTJOIN(
"#",
1,
BYROW(
a,
LAMBDA(
x,
TEXTJOIN(
", ",
,
SORTBY(
x,
b,
-1
)
)
)
)
),
", ",
"#",
,
,
""
)
)
)Excel solution 7 for Reverse data given in rows, proposed by Timothée BLIOT:
=IFERROR(
REDUCE(
G1:K1,
SEQUENCE(
18
),
LAMBDA(
a,
v,
LET(
r,
INDEX(
A2:E19,
v,
),
s,
FILTER(
r,
r<>""
),
VSTACK(
a,
SORTBY(
s,
SEQUENCE(
,
COLUMNS(
s
)
),
-1
)
)
)
)
),
""
)Excel solution 8 for Reverse data given in rows, proposed by Hussein SATOUR:
=TEXTSPLIT(
CONCAT(
BYROW(
A2:E19,
LAMBDA(
x,
TEXTJOIN(
"/",
,
INDEX(
x,
SEQUENCE(
5,
,
5,
-1
)
)
)&"|"
)
)
),
"/",
"|",
1,
,
""
)Excel solution 9 for Reverse data given in rows, proposed by Sunny Baggu:
=LET(
_tbl, IF(A2:E19 = "", NA(), A2:E19),
IFNA(
DROP(
REDUCE(
"😊",
SEQUENCE(ROWS(_tbl)),
LAMBDA(a, v,
VSTACK(a, LET(_seq, SEQUENCE(, 5), TOROW(SORTBY(INDEX(_tbl, v, ), _seq, -1), 3)))
)
),
1
),
""
)
)Excel solution 10 for Reverse data given in rows, proposed by Abdallah Ally:
=LET(
a,
A2:E19,
b,
SEQUENCE(
ROWS(
a
)
),
IFNA(
DROP(
REDUCE(
"",
b,
LAMBDA(
x,
y,
VSTACK(
x,
LET(
u,
TOCOL(
CHOOSEROWS(
a,
y
),
1
),
v,
SEQUENCE(
COUNTA(
u
)
),
TOROW(
SORTBY(
u,
v,
-1
)
)
)
)
)
),
1
),
""
)
)Excel solution 11 for Reverse data given in rows, proposed by Abdallah Ally:
=LET(a,A2:E19,DROP(REDUCE("",SEQUENCE(ROWS(a)),LAMBDA(x,y,VSTACK(x,LET(b,TOCOL(CHOOSEROWS(a,y),1),EXPAND(TOROW(SORTBY(b,SEQUENCE(COUNTA(b)),-1)),,5,""))))),1))Excel solution 12 for Reverse data given in rows, proposed by Asheesh Pahwa:
=LET(
a,
A2:E19,
IFNA(
DROP(
REDUCE(
"'",
SEQUENCE(
ROWS(
a
)
),
LAMBDA(
acc,
v,
VSTACK(
acc,
LET(
I,
INDEX(
a,
v,
),
g,
FILTER(
I,
I<>0
),
h,
COUNTA(
FILTER(
g,
NOT(
ISNA(
g
)
)
)
),
INDEX(
g,
,
SEQUENCE(
,
h,
h,
-1
)
)
)
)
)
),
1
),
""
)
)Excel solution 13 for Reverse data given in rows, proposed by JvdV –:
=REDUCE(A1:E1,A2:A19,LAMBDA(x,y,LET(z,OFFSET(y,,,,5),VSTACK(x,SORTBY(z,z="",,{5,4,3,2,1},)))))Excel solution 14 for Reverse data given in rows, proposed by Tolga Demirci, PMP, PMI-ACP, MOS-Expert:
=TEXTSPLIT(CONCAT(MAP(BYROW(A2:E19;LAMBDA(y;TEXTJOIN(";";;TOROW(MAP(SUM(LEN(SEQUENCE(COUNTA(TOCOL(HSTACK(y)));1;1)))+1-SEQUENCE(COUNTA(TOCOL(HSTACK(y)));1;1);LAMBDA(x;XLOOKUP(x;SEQUENCE(COUNTA(TOCOL(HSTACK(y)));1;1);TOCOL(HSTACK(y)))))))));LAMBDA(q;TEXTJOIN(",";;TEXTSPLIT(q;";"))))&"/");",";"/";1;;"")Excel solution 15 for Reverse data given in rows, proposed by Pieter de Bruijn:
=LET(a,A2:E19,MAKEARRAY(ROWS(a),COLUMNS(a),LAMBDA(r,c,LET(d,1+MAX(IF(INDEX(a,r,0)<>"",SEQUENCE(,5),0))-c,e,IF(d,INDEX(a,r,d),""),IF(e="","",e)))))Excel solution 16 for Reverse data given in rows, proposed by Ziad A.:
=BYROW(A2:E19,LAMBDA(r,TOROW(CHOOSECOLS(r,5,4,3,2,1),1)))Excel solution 17 for Reverse data given in rows, proposed by Giorgi Goderdzishvili:
=LET(
arr,
A2:E19,
srt,
BYROW(
arr,
LAMBDA(
x,
TEXTJOIN(
",",
TRUE,
INDEX(
x,
1,
SEQUENCE(
,
COLUMNS(
x
),
COLUMNS(
x
),
-1
)
)
)
)
),
spl,
TEXTSPLIT(
TEXTJOIN(
";",
,
srt
),
",",
";",
,
,
""
),
spl
)Excel solution 18 for Reverse data given in rows, proposed by Daniel Garzia:
=IFNA(
REDUCE(
A1:E1,
ROW(
1:18
),
LAMBDA(
a,
b,
VSTACK(
a,
TEXTSPLIT(
TEXTJOIN(
1,
,
INDEX(
INDEX(
A2:E19,
b,
),
,
SEQUENCE(
,
5,
5,
-1
)
)
),
1
)
)
)
),
""
)Excel solution 19 for Reverse data given in rows, proposed by Anup Kumar:
=LET(
row_line, TOROW(A2:E2,1),
SORTBY(row_line,SEQUENCE(,COUNTA(row_line),,-1)))Excel solution 20 for Reverse data given in rows, proposed by samir tobeil:
=VSTACK(A1:E1,TEXTSPLIT(TEXTJOIN("-",,BYROW(CHOOSECOLS(A2:E19,6-ROW(1:5)),LAMBDA(x,TEXTJOIN(",",,x)))),",","-",,,""))Excel solution 21 for Reverse data given in rows, proposed by Miguel Angel Franco García:
=LET(a;INDICE(A:E;SECUENCIA(18;;2;1);SECUENCIA(;CONTARA(A2:E2);CONTARA(A2:E2);-1));SI(a=0;"";a))Solving the challenge of Reverse data given in rows with Python in Excel
Python in Excel solution 1 for Reverse data given in rows, proposed by Bo Rydobon 🇹🇭:
[sorted(a[::-1],key=lambda x:x=='' ) for a in xl("A2:E19").fillna('').values]Python in Excel solution 2 for Reverse data given in rows, proposed by John V.:
Hi everyone!
xl("A2:E19").iloc[:,::-1].fillna('').apply(lambda r: sorted(r, key=lambda x: x == ''), axis=1)
Blessings!
Solving the challenge of Reverse data given in rows with R
R solution 1 for Reverse data given in rows, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
input = read_excel("Reverse Rows.xlsx", range = "A1:E19")
test = read_excel("Reverse Rows.xlsx", range = "G1:K19")
mat <- as.matrix(input)
reverse_align_values <- function(row) {
row_no_na <- row[!is.na(row)]
row_reversed <- rev(row_no_na)
result <- c(row_reversed, rep(NA, length(row) - length(row_no_na)))
return(result)
}
mat <- t(apply(mat, 1, reverse_align_values))
result <- as.tibble(mat)
colnames(result) <- colnames(test)
&&