Forum Discussion
Counting Unique Values Across Multiple Columns
Thanks for the link to "Off the Grid" material. It looks well presented with good material. I am reasonably familiar with the PQ user interface and can modify the M-code. Despite that, I have the feeling that I am merely paddling in the shallows. There seem to be whole raft of list and table methods available through the use of the Advanced Editor that I have only the sketchiest idea of how they may be exploited.
PeterBartholomew1 Hi Peter, it's one of many sites I found just over a year ago. And it helped me to get started with PQ. I moved to an iMac over 10 years ago, because I was frustrated by the many performance issues I experienced on PC's. Start-up a Mac and it works. ALL the time.
Though, I did miss PQ's introduction in 2013, but now run W10 and Excel on a 9 years old MacBook Pro via Parallels. Just great.
Am amazed that PQ isn't more widespread after 8 years. So many out (t)here think that VBA is the only way out of "complex" problems. True, VBA can do much, but in most cases one can do without. And VBA is difficult for most to become really good at, whereas PQ is easy to become reasonably good at without really scratching the surface of M. And I understood that it can do magic once you really know what you are doing. Am not there yet myself, I'm afraid.
At the moment, when I face a somewhat complex problem, my initial thought is "can I solve it with PQ?". If not, I try harder to find a PQ / M solution anyway. If I still can't solve it, only then I will revert to traditional or the more modern Excel functions and perhaps VBA.
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.
