Excel BI Challenge Workout 188

Workouts Excel BI Challenge
21

LinkedIn Post by:
Miguel Angel Franco García

Envío mi soluccion

=LET(a;INDICE(ORDENAR(ORDENAR(APILARH(A2:A8; [CONTAR.SI](http://contar.si/)(A2:A8; A2:A8));1;-1);2;-1);; 1);SI(a=0;""; a))

22

LinkedIn post by:
Hussein SATOUR

Thank you Excel BI

=IFERROR(TRANSPOSE(--TEXTSPLIT(CONCAT(BYCOL(A2:E8, LAMBDA(x, TEXTJOIN(",",,SORTBY(x, COUNTIF(x,x), -1))&"/"))), ",", "/",1)),"")

23

Power Query solution that I post in LinkedIn:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    a = Table.Transpose(Source),
    b=Table.Split(a,1),
    c=(x)=> 
        let
            step1=Table.Transpose(x),
            step2=Table.SelectRows(step1,each [Column1]<>null),
            step3=Table.Group(step2,"Column1",{{"tbl",each [Column1]},{"Custom",each Table.RowCount(_)}}),
            step4=Table.Sort(step3,{{"Custom", 1},{"Column1",1}}),
            step5=Table.ExpandListColumn(step4,"tbl")[Column1]

        in step5,
    d = List.Transform(b,c),
    e=Table.FromColumns(d,Table.ColumnNames(Source))
in
    e