— 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)
Post answers in Comment.
(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)
Download Practice File - https://lnkd.in/dJZf_Btr
#excel, #excelchallenge, #powerquerychallenge, #daxchallenge, #m, #dax, #powerbi, #powerquery, #powerpivot, #sheets, #googlesheets, #data, #analytics, #businessintelligence, #finance, #office365, #python, #r
Excel BI LinkedIn Post
Adjunto mi query…
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
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))))))
A solution for #googlesheets
=BYCOL(A2:E8,
LAMBDA(bc,
QUERY({bc,INDEX(COUNTIF(bc,bc))},
"select Col1 order by Col2 desc, Col1 desc")))
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))
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)
