Home » Reverse Cities Columns

Reverse Cities Columns

Reverse all columns from Cities1 to Cities5

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 159
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Reverse Cities Columns with Power Query

Power Query solution 1 for Reverse Cities Columns, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  col = Table.FromColumns(
    List.Transform(Table.ToColumns(Source), each List.RemoveNulls(List.Reverse(_))), 
    Table.ColumnNames(Source)
  )
in
  col
Power Query solution 2 for Reverse Cities Columns, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "InputData"]}[Content], 
  ReverseColumns = List.Accumulate(
    Table.ToColumns(Source), 
    {}, 
    (s, d) => s & {List.Reverse(List.RemoveNulls(d)) & List.Select(d, each _ = null)}
  ), 
  Solution = Table.FromColumns(ReverseColumns, Table.ColumnNames(Source))
in
  Solution
Power Query solution 3 for Reverse Cities Columns, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source    = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Header    = Table.ColumnNames(Source), 
  Reverse   = Table.ReverseRows(Source), 
  ToCol     = Table.ToColumns(Reverse), 
  Transform = List.Transform(ToCol, List.RemoveNulls), 
  Return    = Table.FromColumns(Transform, Header)
in
  Return
Power Query solution 4 for Reverse Cities Columns, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source    = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Header    = Table.ColumnNames(Source), 
  ToCol     = Table.ToColumns(Source), 
  Transform = List.Transform(ToCol, each List.Reverse(List.RemoveNulls(_))), 
  Return    = Table.FromColumns(Transform, Header)
in
  Return
Power Query solution 5 for Reverse Cities Columns, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol = Table.FromColumns(
    List.Transform(Table.ToColumns(Source), each List.Reverse(List.RemoveNulls(_))), 
    Table.ColumnNames(Source)
  )
in
  Sol
Power Query solution 6 for Reverse Cities Columns, proposed by Luan Rodrigues: 
let
  Fonte = Tabela1, 
  res = Table.FromColumns(
    List.Transform(List.Transform(Table.ToColumns(Fonte), List.RemoveNulls), List.Reverse), 
    Table.ColumnNames(Fonte)
  )
in
  res
Power Query solution 7 for Reverse Cities Columns, proposed by Brian Julius: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  ToCols = Table.FromColumns(
    List.Transform(
      List.Transform(Table.ToColumns(Source), each List.RemoveNulls(_)), 
      each List.Reverse(_)
    ), 
    Table.ColumnNames(Source)
  )
in
  ToCols
Power Query solution 8 for Reverse Cities Columns, proposed by Matthias Friedmann: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Cities"]}[Content], 
  Custom1 = Table.FromColumns(
    List.Transform(Table.ToColumns(Source), each List.Reverse(List.RemoveNulls(_))), 
    Table.ColumnNames(Source)
  )
in
  Custom1
Power Query solution 9 for Reverse Cities Columns, proposed by Mahmoud Bani Asadi: 
let
  Source  = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Unpivot = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"), 
  Group   = Table.Group(Unpivot, {"Attribute"}, {{"lst", each List.Reverse([Value])}}), 
  tbl     = Table.FromColumns(Group[lst], Group[Attribute])
in
  tbl
Power Query solution 10 for Reverse Cities Columns, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Custom1 = Table.FromColumns(
    List.Transform(Table.ToColumns(Table.ReverseRows(Source)), List.RemoveNulls), 
    Table.ColumnNames(Source)
  )
in
  Custom1
Power Query solution 11 for Reverse Cities Columns, proposed by Mahmoud Bani Asadi: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Custom1 = Table.FromColumns(
    List.Transform(Table.ToColumns(Source), each List.RemoveNulls(List.Reverse(_))), 
    Table.ColumnNames(Source)
  )
in
  Custom1
Power Query solution 12 for Reverse Cities Columns, proposed by Udit Chatterjee: 
let
  Source = xlProblem159, 
  // get column names and assign text datatype to each column 
  colNames = Table.ColumnNames(Source), 
  colDatatypes = List.Zip({colNames, List.Transform(colNames, each Value.Type("text"))}), 
  // reverse each column and transform table 
  tableToLists = Table.ToColumns(Source), 
  reversedLists = List.Transform(tableToLists, each List.Reverse(List.RemoveItems(_, {"", null}))), 
  listsToTable = Table.FromColumns(reversedLists, colNames), 
  replaceNulls = Table.ReplaceValue(listsToTable, null, "", Replacer.ReplaceValue, colNames), 
  datatypeFix = Table.TransformColumnTypes(replaceNulls, colDatatypes)
in
  datatypeFix

Solving the challenge of Reverse Cities Columns with Excel

Excel solution 1 for Reverse Cities Columns, proposed by Bo Rydobon 🇹🇭: 
=MAP(A2:E19,LAMBDA(a,IF(a=0,"",INDEX(A2:E19,COUNTA(TAKE(a:E19,,1)),COLUMNS(A2:a)))))
Excel solution 2 for Reverse Cities Columns, proposed by Rick Rothstein: 
=TRANSPOSE(IFNA(TEXTSPLIT(TEXTJOIN("/",,BYCOL(A2:E19,LAMBDA(c,TEXTJOIN(",",,INDEX(c,ROWS(c)-SEQUENCE(ROWS(c),,0)))))),",","/"),""))
Excel solution 3 for Reverse Cities Columns, proposed by John V.: 
=LET(r,A2:E19,IFERROR(INDEX(r,1/(1+BYCOL(r,LAMBDA(y,COUNTA(y)))-SEQUENCE(ROWS(r)))^-1,{1,2,3,4,5}),""))
Excel solution 4 for Reverse Cities Columns, proposed by محمد حلمي: 
=IFNA(DROP(
REDUCE(0,SEQUENCE(5),LAMBDA(a,v,LET(
u,A2:E20,
r,ROWS(u),
c,INDEX(u,SEQUENCE(r,,r,-1),v),
HSTACK(a,FILTER(c,c>0))))),,1),"")
Excel solution 5 for Reverse Cities Columns, proposed by 🇰🇷 Taeyong Shin: 
=LET(m,A2:E19,r,IF(m>0,SEQUENCE(ROWS(m))),T(INDEX(m,BYCOL(r,MAX)-r+1,SEQUENCE(,COLUMNS(m)))))
Excel solution 6 for Reverse Cities Columns, proposed by Kris Jaganah: 
=LET(a,A1:E1,b,A2:E19,c,TOCOL(IF(b="","",b&"-"&a),1,1),d,FILTER(c,c<>""),e,SORTBY(d,RIGHT(d),1,SEQUENCE(ROWS(d)),-1),VSTACK(a,IFNA(DROP(REDUCE("",a,LAMBDA(x,y,HSTACK(x,FILTER(TEXTBEFORE(e,"-"),TEXTAFTER(e,"-")=y)))),,1),"")))
Excel solution 7 for Reverse Cities Columns, proposed by Julian Poeltl: 
=LET(A,A2:E19,B,BYCOL(A,LAMBDA(A,COUNTA(A))),R,DROP(REDUCE("",B,LAMBDA(A,B,HSTACK(A,SEQUENCE(B,,B,-1)))),,1),C,MAKEARRAY(MAX(B),COLUMNS(A),LAMBDA(A,B,B)),IFNA(INDEX(A,R,C),""))
Excel solution 8 for Reverse Cities Columns, proposed by Timothée BLIOT: 
=TRANSPOSE(TEXTSPLIT(TEXTJOIN("/",,BYCOL(A2:E19, LAMBDA(y, TEXTJOIN(":",,SORTBY(y,SEQUENCE(ROWS(y)),-1)) ))),":","/",,,""))
Excel solution 9 for Reverse Cities Columns, proposed by Hussein SATOUR: 
=TRANSPOSE(TEXTSPLIT(CONCAT(BYCOL(A2:E19, LAMBDA(x, TEXTJOIN("/",,INDEX(x, SEQUENCE(COUNTA(x),,COUNTA(x), -1)))))&","), "/", ",",1,,""))
Excel solution 10 for Reverse Cities Columns, proposed by Oscar Mendez Roca Farell: 
=LET(_d, A2:E19,_f, ROWS(_d),_r, REDUCE("", SEQUENCE(COLUMNS(_d)), LAMBDA(i ,x, HSTACK(i, TOCOL( INDEX(_d, 1+_f-SEQUENCE(_f), x), 3)))), IFNA(DROP(_r , ,1),""))
Excel solution 11 for Reverse Cities Columns, proposed by Sunny Baggu: 
=IFNA(DROP(
 REDUCE("",SEQUENCE(COLUMNS(A1:E1)),
 LAMBDA(a,v,
 HSTACK(a,TOCOL(MAKEARRAY(30,1,LAMBDA(r,c,INDEX(A2:E19,30-r+1,v))),3)))),
 ,1),"")
Excel solution 12 for Reverse Cities Columns, proposed by Sunny Baggu: 
=IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(A1:E1)),LAMBDA(a,v,HSTACK(a,TOCOL(CHOOSEROWS(CHOOSECOLS(A2:E19,v),SEQUENCE(30,,30,-1)),3)))),,1),"")
Excel solution 13 for Reverse Cities Columns, proposed by Md. Zohurul Islam: 
=LET(u,A2:E19,v,A1:E1,sq,SEQUENCE(,COLUMNS(u)),
z,VSTACK(v,IFNA(DROP(REDUCE("",sq,LAMBDA(x,y,LET(
a,CHOOSECOLS(u,y),
b,FILTER(a,a<>0),
n,SEQUENCE(COUNTA(b)),
c,SORTBY(b,n,-1),
d,HSTACK(x,c),d))),,1),"")),
z)
Excel solution 14 for Reverse Cities Columns, proposed by Charles Roldan: 
=LET(x, A2:E19, n, ROWS(x), m, COLUMNS(x), 
f, LAMBDA(x, SUM(--(LEN(x)>0))), 
INDEX(TEXT(x, ),
 1+MOD(BYCOL(x, f)-SEQUENCE(n), n),
 SEQUENCE(, m)))
Excel solution 15 for Reverse Cities Columns, proposed by Stefan Olsson: 
=BYCOL(A1:E20, LAMBDA(bc, QUERY({bc, SEQUENCE(ROWS(bc))},"Select Col1 Where Col1 is not null Order by Col2 desc",1)))
Excel solution 16 for Reverse Cities Columns, proposed by Abhishek Kumar Jain: 
=MAP(A2:E19,LAMBDA(x,LET(a,INDEX(x,SEQUENCE(ROWS(x),,ROWS(x),-1),),IF(a=0,"",a))))
Excel solution 17 for Reverse Cities Columns, proposed by Guillermo Arroyo: 
=IFNA(DROP(REDUCE("";BYCOL(A1:E19;LAMBDA(a;TEXTJOIN("|";1;a)));LAMBDA(i;j;HSTACK(i;LET(p;TEXTSPLIT(j;;"|");q;COUNTA(p);VSTACK(INDEX(p;1;1);INDEX(p;SEQUENCE(q-1;;q;-1);1))))));;1);"")
Excel solution 18 for Reverse Cities Columns, proposed by Quadri Olayinka Atharu: 
=LET(a,A2:E19,b,A1:E1,
VSTACK(b,IFNA(DROP(
REDUCE("",b,LAMBDA(x,y,HSTACK(x,
TOCOL(INDEX(FILTER(a,A1:E1=y),
SEQUENCE(ROWS(a),,ROWS(a),-1)),1)))),,1),"")))
Excel solution 19 for Reverse Cities Columns, proposed by Anup Kumar: 
=SORTBY(FILTER(A2:A30,A2:A30<>""),SEQUENCE(COUNTA(A2:A30),,,-1))
Excel solution 20 for Reverse Cities Columns, proposed by Gabriel Raigosa: 
=LET(mat,A2:E19,SI.ERROR(INDICE(mat,EXCLUIR(REDUCE(1,BYCOL(mat,LAMBDA(x,CONTARA(x))),LAMBDA(m,f,APILARH(m,SECUENCIA(f,,f,-1)))),,1),{1,2,3,4,5}),"")) 
 
=LET(mat,A2:E19,SI.ERROR(INDICE(mat,EXCLUIR(REDUCE(1,BYCOL(mat,LAMBDA(x,CONTARA(x))),LAMBDA(m,f,APILARH(m,SECUENCIA(f,,f,-1)))),,1),SECUENCIA(,5)),"")) 

▶️EN: 
 =LET(mat,A2:E19,IFERROR(INDEX(mat,DROP(REDUCE(1,BYCOL(mat,LAMBDA(x,COUNTA(x))),LAMBDA(m,f,HSTACK(m,SEQUENCE(f,,f,-1)))),,1),{1,2,3,4,5}),"")) 

 =LET(mat,A2:E19,IFERROR(INDEX(mat,DROP(REDUCE(1,BYCOL(mat,LAMBDA(x,COUNTA(x))),LAMBDA(m,f,HSTACK(m,SEQUENCE(f,,f,-1)))),,1),SEQUENCE(,5)),""))
Excel solution 21 for Reverse Cities Columns, proposed by Enrico Giorgi: 
=INDEX(A2:INDEX(A:A,COUNTA(A:A)),SEQUENCE(COUNTA(A2:INDEX(A:A,COUNTA(A:A))),1,COUNTA(A2:INDEX(A:A,COUNTA(A:A))),-1))

➡ ITALIAN VERSION
=INDICE(A2:INDICE(A:A;CONTA.VALORI(A:A));SEQUENZA(CONTA.VALORI(A2:INDICE(A:A;CONTA.VALORI(A:A)));1;CONTA.VALORI(A2:INDICE(A:A;CONTA.VALORI(A:A)));-1))
Excel solution 22 for Reverse Cities Columns, proposed by Pawan Keswani: 
=IF(COUNTBLANK($L$3:$L3)>COUNTA(A$3:A$20)," ",
HLOOKUP(M$2,$A$2:$E$20,COUNTA(A$2:A$20)+ROW(M$3)-ROW(M3),0))

&&&

Leave a Reply