# Excel BI Challenge Workout 188

— CAN YOU SOLVE THIS - EXCEL CHALLENGE 188 —
(Solutions in any language are also welcome for Excel Challenges)

Sort Col 1 through 5 column wise on the basis of frequency.
Higher frequency will be sorted first.
In case of equal frequency, larger number will be sorted first.

Frequency = Occurrence
Taking first column as example
1 appears 3 times, 8 appears 2 times, 4 appears once and 5 appears once.
Sorting has to be done on the basis of this frequency.
Hence sorting would be 1 (freq = 3), 8 (freq=2), 5 and 4 (in case of tie, larger number comes first)

(Your formula need not be a single formula. You can write multiple formulas to arrive at a solution. Also your formula need not be different from others as long as you have worked out your formula independently)

#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r

M-Code

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Sort = Table.FromColumns(List.Transform(Table.ToColumns(Source),(l)=> List.Sort( List.RemoveNulls(l), {{each List.Count([List.Select](http://list.select/)(l,(l)=> l=_)),1},{each _,-1}})),Table.ColumnNames(Source))
in
Sort
``````

## LinkedIn Post by: Alejandro Simón

Gracias Bo, le di vueltas al problema y logré solucionarlo de una forma menos sofisticada utilizando el articulo de Rick de Groot

``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Col = Table.ToColumns(Source),
Calc = List.Transform(Col, each
let
a = List.RemoveNulls(_),
b = Table.FromColumns({a}),
c = [Table.Group](http://table.group/)(b, {"Column1"}, {"Count", each List.Count(_)}),
d = Table.Sort(c,{{"Count", Order.Descending}, {"Column1", Order.Descending}})[Column1],
e = List.Sort( a, ( x , y ) => [Value.Compare](http://value.compare/)(List.PositionOf( d, x ), List.PositionOf( d, y ) ) )
in e),
Sol = Table.FromColumns(Calc, Table.ColumnNames(Source))
in
Sol
``````

## LinkedIn post by:Felipe Perez Arevalo

``````let
Source = Table.ToColumns(Table1),
Custom1 =
Table.FromColumns(
List.Transform(
Source,
each [
step0=List.RemoveMatchingItems(_,{null}),
step1=Table.FromList(step0, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
step2=Table.Group(step1, {"Column1"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"Details", each _, type table [Column1=nullable number]}}),
step3 = Table.Sort(step2,{{"Count", Order.Descending},{"Column1", Order.Descending}}),
step4 = Table.RemoveColumns(step3,{"Column1", "Count"}),
step5 = Table.ExpandTableColumn(step4, "Details", {"Column1"}, {"Column1"})
][step5][Column1]
),
Table.ColumnNames(Table1)
)
in
Custom1
``````

Sorting Columns w/ #powerquery. #bitanbit #powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.FromColumns(List.Transform(Table.ToColumns(Source), (c) =>
List.RemoveNulls(List.Sort(c, {{(a, b) =>
[Value.Compare](http://value.compare/)(
List.Count([List.Select](http://list.select/)(c, each _ = b)),
List.Count([List.Select](http://list.select/)(c, each _ = a))
)}, {each _, 1}}
))
), Table.ColumnNames(Source)
)
in
Solution
``````

I like my formula too much, It was fun. :))

`= Table.FromColumns(List.Transform(Table.ToColumns(Source),(x)=>List.Sort(x,(a,b)=>Value.Compare(if a=null then 10 else 1/List.Count(List.Select(x,each _=a)),if b=null then 10 else 1/List.Count(List.Select(x,each _=b))))))`

`=DROP(REDUCE(0,SEQUENCE(COLUMNS(A2:E8)),LAMBDA(a,n,LET(b,INDEX(A2:E8,,n),HSTACK(a,IF(b,SORTBY(b,-COUNTIF(b,b),,-b,),""))))),,1)`

``````=DROP(REDUCE(0,SEQUENCE(5),LAMBDA(a,d,LET(
r,INDEX(A2:E8,,d),
HSTACK(a,
IFERROR((SORTBY(r,COUNTIF(r,r),-1,r,-1)&"")+0,""))))),,1)
``````

``````=BYCOL(A2:E8,
LAMBDA(bc,
QUERY({bc,INDEX(COUNTIF(bc,bc))},
"select Col1 order by Col2 desc, Col1 desc")))
``````

``````=LET(
Fx, LAMBDA(c, SORTBY(c, -COUNTIF(c, c), , -c, )),
v, REDUCE(Fx(A2:A8), SEQUENCE(COLUMNS(B2:E8)),
LAMBDA(a,n, HSTACK(a, Fx(INDEX(B2:E8, , n)))
)),
IF(v, v, "")
)
``````

``````=LET(
_input, A2:E8,
IFNA(
DROP(
REDUCE(
"",
SEQUENCE(COLUMNS(_input)),
LAMBDA(a, v,
HSTACK(
a,
LET(
_col, INDEX(_input, , v),
_u, UNIQUE(_col),
_cnt, MAP(_u, LAMBDA(a, SUM(N(_col = a)))),
_freq, XLOOKUP(_col, _u, _cnt),
TOCOL(SORTBY(_col, _freq, -1, _col, -1), 1)
)
)
)
),
,
1
),
""
)
)
``````

`=LET(w;UNIQUE(A2:A8);r;COUNTIF(A2:A8;UNIQUE(A2:A8));i;SORT(COUNTIF(A2:A8;UNIQUE(A2:A8));;-1);c;TEXTJOIN(;;REPT(IFERROR(MAP(i;r;w;LAMBDA(a;b;c;XLOOKUP(a;b;c)));MAP(i;LAMBDA(a;XLOOKUP(a;r;w))));i));MID(c;SEQUENCE(LEN(c));1))`

``````=LET(_d,A2:E8,
fx,LAMBDA(Col,LET(c,FILTER(Col,Col<>""),u,UNIQUE(c),f,DROP(FREQUENCY(c,u),-1),x,XLOOKUP(c,u,f),SORTBY(c,x,-1,c,-1))),
IFNA(REDUCE(fx(CHOOSECOLS(_d,1)),DROP(SEQUENCE(COLUMNS(_d)),1),LAMBDA(a,b,HSTACK(a,fx(INDEX(_d,SEQUENCE(ROWS(_d)),b))))),""))
``````

``````let
Fonte = [
a = List.Transform(List.Transform(Table.ToColumns(Tabela1), (x)=> List.RemoveNulls(x)), (x)=> Table.FromColumns({x})),
a1 = List.Transform(a, each _[Column1]),
b = List.Transform(a, (y)=>
Table.Sort(Table.Group(y, {"Column1"}, {"Count", each List.Count(_)}),{{"Count",Order.Descending},{"Column1",Order.Descending}})[Column1]),
c = Table.FromRows(List.Zip({a1,b}))
][c],
Ind = Table.AddIndexColumn(Fonte, "Índice", 1, 1, Int64.Type),
exp = Table.ExpandListColumn(Ind, "Column1"),
res = Table.FromColumns(
Table.Group(exp, {"Índice"}, {{"Contagem", each [
a = [[Column1]],
b = _[Column2]{0},
c = Table.Sort(_,each List.PositionOf(b,[Column1]))[Column1]
][c]}})[Contagem], Table.ColumnNames(Tabela1))
in
res
``````

Alternative Sorting Columns w/ hashtag#powerquery. hashtag#bitanbit hashtag#powerbi

``````let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Solution = Table.FromColumns(List.Transform(Table.ToColumns(Source), (t) =>
List.Accumulate(
List.Distinct(List.RemoveNulls(t)),
{},
(s, c) => let d = List.Difference(List.RemoveNulls(t), s) in s & [List.Select](http://list.select/)(d, each List.Contains({List.Max(List.Modes(d))}, _))
)
), Table.ColumnNames(Source))
in
Solution
``````

I cant seem to get BYCOL to work, so fudged it…if anyone is able to offer advice I would be grateful!

``````=LET(a,LAMBDA(a,SORTBY(a,COUNTIF(a,a),-1,a,-1)),
b,HSTACK(a(A2:A8),a(B2:B8),a(C2:C8),a(D2:D8),a(E2:E8)),
IF(b=0,"",b))
``````

## LinkedIn Post by:Oscar Mendez Roca Farell

hi everyone, my proposal solution:

`=DROP( IFNA( REDUCE("", SEQUENCE(5), LAMBDA(i, x, LET(_c, ORDER(TOCOL(INDEX(A2:E8, ,x), 1)),_s, SEQUENCE(ROWS(_c)), _m, MMULT(--(_c=TOROW(_c)),_c^0), _n, MMULT((_m=TOROW(_m))*(_s>=TOROW(_s)), _c^0), HSTACK(i, ORDRBY(_c, _m&_n, -1))))), ""), ,1)`

`=LET(z,A2:E8,MAKEARRAY(ROWS(z),COLUMNS(z),LAMBDA(r,c,LET(b,INDEX(z,,c),IF(INDEX(b,r),MOD(LARGE(COUNTIF(b,b)*100+b,r),100),"")))))`

Shorter with hard code

`=MAKEARRAY(7,5,LAMBDA(r,c,LET(b,INDEX(A2:E8,,c),IF(INDEX(b,r),MOD(LARGE(COUNTIF(b,b)*10+b,r),10),""))))`

## LinkedIn Post by:John Jairo V.

Hi to all!

One option could be: `=DROP(REDUCE(0,ROW(1:5),LAMBDA(i,y,LET(c,INDEX(A2:E8,,y),HSTACK(i,IF(c,SORTBY(c,-COUNTIF(c,c),,-c,),""))))),,1)`

Blessings!

``````=+LET(