Order the cities columns on the basis of number of cells populated in descending order. In case of same number of cells populated, higher number suffix in Cities will take precedence like in case of Cities1 and Cities2, Cities2 will be listed first.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 169
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Sort Cities by Populated with Power Query
Power Query solution 1 for Sort Cities by Populated, proposed by Bo Rydobon 🇹🇭:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sort = Table.PromoteHeaders(
Table.FromColumns(
List.Reverse(List.Sort(Table.ToColumns(Table.DemoteHeaders(Source)), List.NonNullCount))
)
)
in
SortPower Query solution 2 for Sort Cities by Populated, proposed by Zoran Milokanović:
let
Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content],
DemotedHeaders = Table.DemoteHeaders(Source),
Sorting = List.Sort(
Table.ToColumns(DemotedHeaders),
{{each List.Count(List.RemoveNulls(_)), 1}, {each Number.From(Text.RemoveRange(_{0}, 0, 6)), 1}}
),
PromotedHeaders = Table.PromoteHeaders(Table.FromColumns(Sorting))
in
PromotedHeadersPower Query solution 3 for Sort Cities by Populated, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
DH = Table.DemoteHeaders(Source),
Sol = Table.PromoteHeaders(
Table.FromColumns(
List.Reverse(
List.Sort(List.Transform(Table.ToColumns(DH), each List.RemoveNulls(_)), List.Count)
)
)
)
in
SolPower Query solution 4 for Sort Cities by Populated, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
cab = Table.DemoteHeaders(Fonte),
tab = Table.ToColumns(cab),
con = Table.FromList(tab, Splitter.SplitByNothing(), null, null),
rec = Table.AddColumn(
con,
"Personalizar",
each [a = [Column1]{0}, b = List.Count(List.RemoveNulls(List.RemoveFirstN([Column1], 1)))]
),
exp = Table.ExpandRecordColumn(rec, "Personalizar", {"a", "b"}, {"a", "b"}),
cls = Table.PromoteHeaders(
Table.FromColumns(Table.Sort(exp, {{"b", Order.Descending}, {"a", Order.Descending}})[Column1])
)
in
clsPower Query solution 5 for Sort Cities by Populated, proposed by Victor Wang:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Result = Table.SelectColumns(
Source,
List.Sort(
Table.ColumnNames(Source),
{{each List.NonNullCount(Table.Column(Source, _)), 1}, {each Text.End(_, 1), 1}}
)
)
in
ResultPower Query solution 6 for Sort Cities by Populated, proposed by Krzysztof Kominiak:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
TabHeaders = Table.FromList(Table.ColumnNames(Source), null, {"Cities"}),
AddCountLists = Table.AddColumn(
TabHeaders,
"Count",
each List.NonNullCount(Table.Column(Source, [Cities]))
),
ListHeaders = Table.Sort(
AddCountLists,
{{"Count", Order.Descending}, {"Cities", Order.Descending}}
)[Cities],
Result = Table.SelectColumns(Source, ListHeaders)
in
ResultPower Query solution 7 for Sort Cities by Populated, proposed by Guillermo Arroyo:
let
Origen = Excel.CurrentWorkbook(){[Name = "Tabla1"]}[Content],
a = Table.DemoteHeaders(Origen),
b = Table.Transpose(a),
c = Table.AddColumn(b, "Aux", each List.RemoveNulls(Record.ToList(_))),
d = Table.SelectColumns(c, {"Aux"}),
e = Table.AddIndexColumn(d, "Index", 1, 1, Int64.Type),
f = Table.AddColumn(e, "Count", each List.Count([Aux])),
g = Table.Sort(f, {{"Count", Order.Descending}, {"Index", Order.Descending}}),
h = Table.RemoveColumns(g, {"Index", "Count"}),
i = Table.TransformColumns(h, {"Aux", each Text.Combine(List.Transform(_, Text.From), "|")}),
j = Table.SplitColumn(i, "Aux", Splitter.SplitTextByDelimiter("|")),
k = Table.Transpose(j),
l = Table.PromoteHeaders(k, [PromoteAllScalars = true])
in
lSolving the challenge of Sort Cities by Populated with Excel
Excel solution 1 for Sort Cities by Populated, proposed by Bo Rydobon 🇹🇭:
=LET(z,A1:E19,SORTBY(z&"",-BYCOL(z,LAMBDA(a,COUNTA(a))),,TAKE(z,1),-1))Excel solution 2 for Sort Cities by Populated, proposed by Rick Rothstein:
=SUBSTITUTE(SORTBY(A1:E19,BYCOL(A:E,LAMBDA(c,COUNTA(c))),-1),0,"")Excel solution 3 for Sort Cities by Populated, proposed by John V.:
=SORTBY(A1:E19,-BYCOL(A1:E19,LAMBDA(y,COUNTA(y)))-RIGHT(A1:E1)%)Excel solution 4 for Sort Cities by Populated, proposed by محمد حلمي:
=SORTBY(A2:E19,
BYCOL(A2:E19,LAMBDA(a,COUNTA(a))),-1)Excel solution 5 for Sort Cities by Populated, proposed by 🇰🇷 Taeyong Shin:
=T(SORTBY(A1:E19, BYCOL(A1:E19, LAMBDA(c, COUNTA(c))), -1, A1:E1, -1))
or
T function by Reference
=SORTBY(T(+A1:E19), BYCOL(A1:E19, LAMBDA(c, COUNTA(c) )), -1, A1:E1, -1 )Excel solution 6 for Sort Cities by Populated, proposed by Kris Jaganah:
=LET(a,A1:E19,b,A1:E1,c,BYCOL(a,LAMBDA(x,COUNTA(x))),f,SORTBY(a,c,-1,b,-1),IF(f=0,"",f))Excel solution 7 for Sort Cities by Populated, proposed by Julian Poeltl:
=LET(A,A1:E19,S,SORTBY(A,BYCOL(A,LAMBDA(A,COUNTA(A))),-1,TAKE(A,1),-1),IF(S<>0,S,""))Excel solution 8 for Sort Cities by Populated, proposed by Timothée BLIOT:
=LET(A, A2:E19, B,A1:E1, C, SORTBY(VSTACK(B,A), BYCOL(A, LAMBDA(a, COUNTA(a))),-1,B,-1), IF(C=0,"",C))Excel solution 9 for Sort Cities by Populated, proposed by Hussein SATOUR:
=LET(a, A1:E19, b, BYCOL(a, LAMBDA(x, COUNTA(x))), c, BYCOL(a, LAMBDA(x, LEN(CONCAT(x)))), d, SORTBY(a,b,-1,c,-1), IF(d<>0, d, ""))Excel solution 10 for Sort Cities by Populated, proposed by Sunny Baggu:
=LET(_r,SORTBY(A1:E19,BYCOL(A1:E19,LAMBDA(a,COUNTA(a))),-1,SEQUENCE(,COLUMNS(A1:E1)),-1),IF(_r="","",_r))
=LET(_a,SORTBY(A1:E19,DROP(REDUCE("",SEQUENCE(COLUMNS(A1:E1)),LAMBDA(a,v,HSTACK(a,COUNTA(INDEX(A1:E19,,v))))),,1),-1,SEQUENCE(,COLUMNS(A1:E1)),-1),IF(_a="","",_a))Excel solution 11 for Sort Cities by Populated, proposed by Sunny Baggu:
=LET(_celebrate,"Happy Birthday Kris Jaganah sir for contributing in Excel BI",
_srt1,SCAN("",SEQUENCE(,COUNTA(A2:E2)),LAMBDA(a,v,COUNTA(INDEX(A2:E20,,v)))),
_srt2,SEQUENCE(,COLUMNS(A2:E2)),
_r,SORTBY(A2:E20,_srt1,-1,_srt2,-1),
IF(_r="","",_r))Excel solution 12 for Sort Cities by Populated, proposed by Md. Zohurul Islam:
=LET(z,A1:E19,u,SORTBY(z,BYCOL(z,COUNTA),-1,TAKE(z,1),-1),IF(u=0,"",u))Excel solution 13 for Sort Cities by Populated, proposed by Julien Lacaze:
=T(SORTBY(A1:E19,BYCOL(A1:E19,LAMBDA(arr,COUNTA(arr))),-1,TAKE(A1:E19,1),-1))Excel solution 14 for Sort Cities by Populated, proposed by Guillermo Arroyo:
=LET(m,A1:E19,p,BYCOL(m,LAMBDA(u,COUNTA(u))),q,SEQUENCE(,COLUMNS(m)),r,CHOOSECOLS(m,SORTBY(q,p,-1,q,-1)),IF(r=0,"",r))Excel solution 15 for Sort Cities by Populated, proposed by Quadri Olayinka Atharu:
=LET(array,A1:E19,
cnt,BYCOL(array,LAMBDA(x,COUNTA(x))),
SUBSTITUTE(SORTBY(array,cnt,-1,CHOOSEROWS(array,1),-1),"",""))Excel solution 16 for Sort Cities by Populated, proposed by Ricardo Alexis Domínguez Hernández:
=LET(Array,
TAKE(SORTBY(A:E,BYCOL(A:E,LAMBDA(x,COUNTA(x))),-1,RIGHT(A1:E1,1),-1),MAX(BYCOL(A:E,LAMBDA(x,COUNTA(x))))),
IF(Array=0,"",Array))Excel solution 17 for Sort Cities by Populated, proposed by Britt Deaton, FSA:
=INDEX($A$2:$E$19,SEQUENCE(18),RIGHT(CHOOSEROWS(SORT(SORT(VSTACK(A1:E1,BYCOL(A2:E19,LAMBDA(a,COUNTA(a)))),1,-1,TRUE),2,-1,TRUE),1),1))Excel solution 18 for Sort Cities by Populated, proposed by Ben Gutscher:
=LET(result,SORTBY($A$1:$E$19,BYCOL(A1:E19,LAMBDA(col,COUNTA(col))),-1,RIGHT(A1:E1),-1),IF(result="","",result))Excel solution 19 for Sort Cities by Populated, proposed by Yarden Rozen:
Excel solution 20 for Sort Cities by Populated, proposed by Hedi GHRIBI:
=sort(sort(A1:E19,1,-1,1),BYCOL(A1:E19,LAMBDA(a,counta(a))),-1,1)Solving the challenge of Sort Cities by Populated with Python
Python solution 1 for Sort Cities by Populated, proposed by Md Ismail Hosen:
import pandas as pd
file_path = "/Users/mac/Downloads/Order Cities.xlsx"
df = pd.read_excel(file_path, sheet_name="Sheet1")
df = df.iloc[:,0:5]
non_nan_count = df.count().to_frame(name="Non NaN count").reset_index()
correct_column_order = non_nan_count.sort_values(["Non NaN count","index"],ascending=[False,False])["index"].tolist()
final_df = df[correct_column_order]
print(final_df)
&&&