Forum Discussion

Steel Contributor

Mar 05, 2026

Solved

Filter Function or TAKE-DROP Function

Dear Experts, I have a Data like this:- Column A -> Has the File Names, and Column B,C,D have their corresponding data, In Column F I have the unique File Names and from G/H/I...

BI & Data Analysis

excel

Formulas and Functions

power bi

IlirU

Mar 05, 2026

anupambit1797,

Try below formula in cell F2:

=LET(
     colA, A2:.A100,
       tj, TEXTJOIN,
       ts, TEXTSPLIT,
      arr, ARRAYTOTEXT,
    HSTACK(UNIQUE(colA), --ts(tj(";",, BYROW(WRAPROWS(BYROW(TRANSPOSE(ts(tj(";",,
     BYCOL(B2:.D100, LAMBDA(b, arr(INDEX(b, DROP(DROP(GROUPBY(colA, SEQUENCE(ROWS(colA)),
    HSTACK(MIN, MAX),, 0), 1),, 1)))))), ", ", ";")), arr), 2), arr)), ", ", ";"))
)

Hope this helps.

IlirU

m_tarler

Bronze Contributor

Mar 05, 2026

alternatively:

=LET(in,A2:.D1000,
fnames,UNIQUE(TAKE(in,,1)),
fn_out,LAMBDA(fn,LET(a,FILTER(in,TAKE(in,,1)=fn),HSTACK(TAKE(a,1),DROP(TAKE(a,-1),,1)))),
DROP(REDUCE("",fnames,LAMBDA(p,q,VSTACK(p,fn_out(q)))),1))

If only I could get a better grip on Thunks so I could get away from the DROP(REDUCE()) work around for 2d arrays.

another alternative inspired by IlirU use of GROUPBY:

=LET(fnames,A2:.A999,data,B2:.D999,
     DROP(HSTACK(
                GROUPBY(fnames,data,HSTACK(MIN,MIN,MIN),,0),
            DROP(GROUPBY(fnames,data,HSTACK(MAX,MAX,MAX),,0),,1)),1))

PeterBartholomew1

Silver Contributor

Mar 06, 2026

Hi Matt

I do not think we have, as a community, fully embraced the use of either arrays of functions or functions of arrays (thunks in particular). In this instance the use of thunks does not especially simplify things. My worksheet formula was

= LET(
    dataϑ,  BYROW(data, THUNK),
    list,   GROUPBY(file, dataϑ, TOPANDTAILλ, 0, 0),
    rowHdr, TAKE(list,,1),
    bodyϑ,  TAKE(list,,-1),
    return, HSTACK(rowHdr, EVALTHUNKARRλ(bodyϑ)),
    return
  )

This is, turn each row of the data into a single object. Define a function TOPANDTAILλ that stacks the first and last row horizontally. Strip off the file names and convert the list of thunks back to an array using EVALTHUNKARRλ. If one is going to build solutions as arrays of thunks, it would be useful to have a built-in function that stacks the evaluated thunks.

TOPANDTAILλ = LAMBDA(group, THUNK(
    HSTACK(
        (@TAKE(group, 1))(), 
        (@TAKE(group,-1))()
    )
));

The function works by evaluating the first and last thunks in the list before stacking them horizontally and returning the stacked row as a thunk.

It would have been far simpler had the tick counts and slots been returned in pairs,

The functions that form and extract data from arrays of thunks can be found at

https://gist.github.com/pbartxl/a14b250985da31be843ce9ff35d888fc