Very nice, it gave me the PQ info that I was looking for to put all the combinations into one result table. This approach needs Refresh, which is not a problem where I expect to use it, but do you have any idea how to build a MAKEARRAY to get a dynamic list from one formula? I've gotten it this far, but can't get the LAMBDA part: =Let(T1R,ROWS(tblProdCat),T2R,ROWS(tblRegion),T3R,ROWS(tblCustSeg),MAKEARRAY(T1R*T2R*T3R,3,LAMBDA( ........ I don't work with LAMBDA enough to get a real feel for it, but once the 544x3 array is made I've got a feeling it should be able to handle filling it out. David
Glad you got what you needed for the query. Like you though, I have very little experience with LAMBDA so not going to be able to help. I tend to avoid anything other people using my spreadsheets can't fix themselves 😉
When I get to the point of saving the combined list, its not letting me "close and load to". I only get the option to "close and load". What am I doing wrong?
You only get close and load to the first time for the first query. After that it defaults to the first option you chose. However, if you click on the queries and connections button on the data ribbon in main Excel you can see all the queries, and then right click on each one in turn and select 'close and load to' to change the settings. In short, no, you didn't do anything wrong 🙂
This is interesting. However, can PQ be used to get unique combinations? For example, if I have three lists of four objects (A,B,C,D) can PQ be used to generate AAA, AAB etc, but not duplicates like ABA or BAA? This would be advantageous because the number of unique combinations is much less than the number of combinations. In this case, the number of combinations is 4^3 = 64 and the number of unique combinations is 20.
Not sure if this is the best method, but one approach in PQ could be this: run the same process as before but also give each item a unique value. Then sum each row and remove the duplicate rows, then remove all the value columns to clean it up. John 👍
@@Up4Excel I've used the functions but haven't tried it with your exact setup but I think they can do it. Actuaĺy pretty straightforward application of these functions unless there is some hidden quirk in your setup that I missed. Imteresting video but I watched it quickly p n my phone while fixing breakfast! That's why the question mark at the end of my comment!!
Any chance we're able to do this with parameters? Like each combination must equal 100%? For instance, five pieces of a pie divvied up into 5% increments? (i.e. combination 1 - 100%, 0%, 0%, 0%, 0%; combination 2 - 95%, 5%, 0%, 0%, 0%; combination 3 - 95%, 0%, 5%, 0%, 0%; etc.)
