Excel BI Challenge Workout 180

Workouts Excel BI Challenge
1

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

Provide a formula to list the maximum number of items (one unit only of each item) which can be bought in $150 from A2:A10. Retain the original sort order of items in answer

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/dGjVpUKx

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

image
image800×496 102 KB

Excel BI LinkedIn Post:

2

LinkedIn Post by:
Bo Rydobon

M-Code

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Acc = let c = Table.ToColumns(Table.Sort(Source,{"Cost ($)"})) in Table.FromColumns(c &{List.Accumulate(c{1},{},(s,l)=> s&{List.Last({0}&s)+l})}),
  Sort = List.Sort(Table.SelectRows(Acc,each [Column3]<=150)[Column1], each List.PositionOf(Source[Items],_))
in
  Sort
3

Linkedin Post by:
Luan Rodrigues

Dax Solution

Although incomplete due to lack of classification, but I decided to train a little DAX

/*-------------------------------------------*/
Soma = SUM(Tabela1[Cost ($)])
/*-------------------------------------------*/
Total Cost =
var sum2 = [Soma]

var tab =
SUMMARIZE(
    ALLSELECTED(Tabela1[Items]),
    Tabela1[Items],
    "cost",[Soma])

var vacc =
SUMX(
    FILTER(
        tab,
        [cost]<=sum2),[Soma])


return
CALCULATE(
[Soma],
FILTER(
    ALLSELECTED(Tabela1[Items]),
    vacc <=150))
4

LinkedIn Post by:
Alejandro Simón

Adjunto mi query…

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Sort = Table.Sort(Source,{{"Cost ($)", Order.Ascending}}),
  Add = Table.AddIndexColumn(Sort, "Index", 1, 1, Int64.Type),
  RT = Table.SelectRows(Table.AddColumn(Add, "Sum", each List.Sum(List.FirstN(Add[#"Cost ($)"], [Index]))), each [Sum]<= 150)[Items],
  Sol = List.Sort(RT, each List.PositionOf(Source[Items], _))
in
  Sol
5

LinkedIn Post by:
Gabriel Raigosa

:arrow_forward: EN:

=FILTER(A2:A10,ISNUMBER(XMATCH(A2:A10,LET(m,SORT(A2:B10,2,1,),IF(SCAN(,TAKE(m,,-1),LAMBDA(up,dn,up+dn))<=150,TAKE(m,,1),)))))

=LET(it,A2:A10,co,B2:B10,az,SORTBY(it,co),FILTER(it,ISNUMBER(XMATCH(it,IF(SCAN(,XLOOKUP(az,it,co),LAMBDA(up,dn,up+dn))<=150,az,)))))

:arrow_forward: ES:

=FILTRAR(A2:A10,ESNUMERO(COINCIDIRX(A2:A10,LET(m,ORDENAR(A2:B10,2,1,),SI(SCAN(,TOMAR(m,,-1),LAMBDA(up,dn,up+dn))<=150,TOMAR(m,,1),)))))

=LET(it,A2:A10,co,B2:B10,az,ORDENARPOR(it,co),FILTRAR(it,ESNUMERO(COINCIDIRX(it,SI(SCAN(,BUSCARX(az,it,co),LAMBDA(up,dn,up+dn))<=150,az,)))))

6

LinkedIn Post by:
Zoran Milokanović

The maximum number of items w/ #powerquery. #bitanbit #powerbi

let
Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],
Max = 150,
AddSort = let r = Table.ToRecords(Source) in List.Accumulate(List.Positions(r), {}, (s, d) => s & {Record.AddField(r{d}, "Sort", d)}),
SortingRecords = List.Sort(AddSort, {{each _[#"Cost ($)"], Order.Ascending}, {each _[Sort], Order.Ascending}}),
Calculate = List.Accumulate(SortingRecords, {}, (s, d) =>
let
c = d[#"Cost ($)"],
rc = List.Sum(List.Transform(s, each _[#"Cost ($)"]))
in
if s = {} and c > Max then s
else if s = {} and c < Max then s & {d}
else if rc + c > Max then s
else s & {d}
),
Solution = List.Transform(List.Sort(Calculate, {each _[Sort], Order.Ascending}), each _[Items])
in
Solution
7

LinkedIn Post by:
Brian Julius

My #powerquery solution:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddOrigSort = Table.AddIndexColumn(Source, "OrigSort", 1, 1, Int64.Type),
Sort = Table.Sort(AddOrigSort,{{"Cost ($)", Order.Ascending}}),
AddIndex = Table.AddIndexColumn(Sort, "Index", 1, 1, Int64.Type),
AddCumulSum = Table.AddColumn(AddIndex, "CumulSum", each [
a = List.FirstN( AddIndex[#"Cost ($)"], [Index] ),
b = List.Sum( a )
][b]),
Filter = Table.SelectRows(AddCumulSum, each [CumulSum] <= 150),
Sort2 = Table.Sort(Filter,{{"OrigSort", Order.Ascending}}),
Clean = Table.SelectColumns(Sort2,{"Items"})
in
Clean
8

LinkedIn Post by:
Muhammad Rayan S.
 

=LET(
    t, SORT(A2:B10, 2, 1),
    n, TAKE(t, , -1),
    s, SCAN(0, n, LAMBDA(a, b, a + b)),
    c, COUNT(FILTER(s, s < 151)),
    TAKE(t, c, 1)
)
9

LinkedIn Post by:
Nicolas Micot

French:

=LET(_tableau; ASSEMB. H(SEQUENCE(NBVAL(A2:A10)); A2:B10);_tri; TRIER(_tableau;3;1);_couts;INDEX(_tri;0;3);_coutCumule; SCAN(0;_couts; LAMBDA(a;b;a+b));_filtre; FILTRE(_tri;_coutCumule<=150);INDEX(TRIER(_filtre;1;1);0;2))

10

LinkedIn Post by:
Victor Wang
 

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 sortByCost = Table.Sort(Source,{{"Cost ($)", Order.Ascending}}),
 getPos = List.Accumulate(sortByCost[#"Cost ($)"], [pos = 0, sum = 0], (state, current) => if state[sum] + current > 150 then state else [pos = state[pos]+1, sum = List.Sum({state[sum], current})]),
 getList = List.Sort(List.FirstN(sortByCost[Items], getPos[pos]), each List.PositionOf(Source[Items], _))
in
 getList
11

LinkedIn Post by:
Guillermo Arroyo

Power Query

let
 Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
 a = Table.AddIndexColumn(Origen, "Aux", 1),
 b = Table.Sort(a,{{"Cost ($)", Order.Ascending}}),
 c = Table.AddIndexColumn(b, "Aux1", 1),
 d = Table.AddColumn(c, "Accumulate", each List.Sum(List.FirstN(c[#"Cost ($)"], [Aux1]))),
 e = Table.SelectRows(d, each [Accumulate] <= 150),
 f = Table.Sort(e,{{"Aux", Order.Ascending}}),
 g = Table.SelectColumns(f,{"Items"})
in
 g
12

LinkedIn Post by:
Tolga Demirci

not what it is cracked up to be…but a try.

=MAP(LET(w;SORT(B2:B10;;1);q;150-SCAN(0;w;LAMBDA(x;y;x+y));UNIQUE(XLOOKUP(FILTER(q;MIN(q)*(q>0));q;w)));LAMBDA(z;TEXTJOIN(", ";;IFERROR(MAP(B2:B10;A2:A10;LAMBDA(a;b;XLOOKUP(z;a;b)));""))))

13

LinkedIn post by:
Stevenson Yu
 

=LET(A, A2:B10, LIMIT,150,
B, SORT(A,2),
C, DROP(VLOOKUP(A,FILTER(B,SCAN(0,DROP(B,,1),LAMBDA(X,Y,X+Y))<=LIMIT),1,0),,-1),
D, FILTER(C,ISTEXT(C)),
IFERROR(D,"None"))

Because after all this time, I still love VLOOKUP :eyes:
Formula length: 164 characters.

14

LinkedIn Post by:
Tulio Marcos
 

let
 Fonte = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
 #"1" = Table.TransformColumnTypes(Fonte,{{"Cost ($)", Int64.Type}}),
 #"2" = Table.AddIndexColumn(#"1", "Class", 0, 1, Int64.Type),
 #"3" = Table.Sort(#"2",{{"Cost ($)", Order.Ascending}}),
 #"4" = Table.AddIndexColumn(#"3", "Índice", 1, 1, Int64.Type),
 #"5" = Table.AddColumn(#"4", "Personalizar", each List.Sum(List.FirstN(#"4"[#"Cost ($)"], [Índice]))),
 #"6" = Table.SelectRows(#"5", each [Personalizar] < 150),
 #"7" = Table.Sort(#"6",{{"Class", Order.Ascending}})
in
 #"7"
15

LinkedIn post by:
Luan Rodrigues
 

let
 Fonte = Tabela1,
 class = Table.Sort(Fonte,{{"Cost ($)", Order.Ascending}}),
 Ind = Table.AddIndexColumn(class, "Índice", 1, 1, Int64.Type),
 acc = Table.AddColumn(Ind, "Personalizar", each List.Sum( List.FirstN(Ind[#"Cost ($)"],[Índice]))),
 fil = Table.SelectRows(acc, each [Personalizar] <= 150),
 res = Table.Sort(fil,each List.PositionOf(Fonte[Items],[Items]))[[Items]]
in
 res
16

LinkedIn Post by:
Quadri Olayinka Atharu
 

=LET(s,SORT(A2:B10,2),rt,SCAN(0,TAKE(s,,-1),LAMBDA(x,y,x+y))<=150,
a,FILTER(s,rt),b,HSTACK(a,XMATCH(a,A2:A10)),r,TAKE(SORT(b,3),,1),
r)
17

LinkedIn post by:
Al Jun Esguerra

Tried solving by using excel functions only

Step 1: created Column I and J
=SORT(A2:B10,2,1)

Step 2: Column K is running sum
=IF(I2<>"",SUM($J$2:J2),"")

Step 3: Input on cell L2 = 150

Step 4: Column F
=SORT(FILTER(H2:H10,K2:K10<$L$2))

Step 5: Column D based on rownumber on Column F
=IF(F2<>"",INDEX(A:A,F2),"")

image
image1091×422 94.3 KB

18

LinkedIn Post by:
Kris Jaganah

=LET(a,A2:A10,c,SEQUENCE(ROWS(a))&"-"&a,d,SORT(HSTACK(c,B2:B10),2,1),TEXTAFTER(SORT(FILTER(TAKE(d,,1),SCAN(0,TAKE(d,,-1),LAMBDA(x,y,x+y))<150)),"-"))

19

LinkedIn post by:
Sunny Baggu
 

=LET(_input,A2:B10,_tbl,HSTACK(SEQUENCE(ROWS(_input)),_input),
_tbls,SORT(_tbl,3),
_cond,SCAN(0,TAKE(_tbls,,-1),LAMBDA(a,v,a+v))<=150,DROP(SORT(FILTER(DROP(_tbls,,-1),_cond)),,1))
20

LinkedIn Post by:
Oscar Mendez Roca Farell

hi everyone, my proposal solution:

=LET(_a, A2:A10,_m, ORDER(A2:B10,2), INDEX(_a, ORDER( MATCH( DROP( TAKE(_m, COUNT( SCAN(0, INDEX(_m, ,2), LAMBDA(i, x, i+x/((i+x)<150))))), ,-1),_a, ))))