Forum Discussion
Counting Unique Values Across Multiple Columns
Like you, I to tend to restrict VBA to things that worksheet formulae cannot address, such as turning data into sketches or event handling. Nobody ever trusted me with corporate data, so I had little need to analyse pre-existing data. Hence I have tended to follow the development of dynamic arrays more closely.
The changes being introduced there are just about as profound as PQ, I can write solutions with little attention to legacy techniques such as relative referencing or the use of grid coordinates. If I may, I will continue to follow your venture into PQ (maybe M and DAX) and I hope I cause you no offence if I sometimes offer DA alternatives. At present, only a minority of users are in a position to use array methods and, of those who could, many have been put off by the days of CSE.
By the way, M and modern Excel functionality have many common. For example, if you remember Fibonacci lambda
FIB= LAMBDA(n,
IF(n<=2, SEQUENCE(n,,0),
LET(b, FIB(n-1),
IF( SEQUENCE(n)<n,
b,
INDEX(b,n-1)+INDEX(b,n-2))
))
);same FIB in M looks similar
( n ) =>
[ fn=(j) => let b = @fn(j-1) + @fn(j-2)
in if j < 2 then j else b,
res = List.Transform({0..(n-1)}, fn )
][res]
DAX in particular and tabular modeling in general is another world, at least from my point of view.