How to make column iterative Dynamic Array Function?

I’m trying to practice the new M365 Dynamic Array Function and thought of making a simple data profiler that iterates over each column and tells the count of blanks and count of Unique values.

I want to make it dynamic so that it iterates over each column in the range provided to a and gets me the counts.
I tried BYCOL but I got the Nested Array function.
Is there a way to do it?

>Solution :

Well, I usually try to be as compact as I can go with formulas, but to try and tell the story as to what is happening here I have been basically as verbose as possible for educational purposes I guess:

Formula in D1:

=LET(Range,A1:B7,Headers,TAKE(Range,1),Data,DROP(Range,1),REDUCE({"Column Name","Blanks","Unique"},SEQUENCE(COLUMNS(Data)),LAMBDA(Accumulator,Iterator,LET(COL,INDEX(Data,,Iterator),VSTACK(Accumulator,HSTACK(INDEX(Headers,Iterator),COUNTBLANK(COL),ROWS(UNIQUE(TOCOL(COL,1)))))))))

To try and break it down:

• Range – A variable to hold the entire range of data, including your headers, therefor the only piece of formula you’d have to change;
• Headers – Use TAKE() to get the first row from given range;
• Data – Use DROP() to get only the body of data to work with;
• REDUCE() – We open an accumulator/stacker to start VSTACK()‘ing using an accumulator and an current interator;
  • Accumulator – This is the current state at any given time in the iteration. Our starting array is given as the headers in your desired results;
  • Iterator – We have acquired an iterator by counting the amount of columns in our Data variable using SEQUENCE();
  • Within each iteration we can INDEX() the correct header/column and HSTACK() our calculations, thus: COUNTBLANK() and ROWS(UNIQUE(TOCOL())).

I hope this made sense when put in this way. Either way, a small remark; This works on the assumption you’d not have completely empty columns in your Data.