Forum Discussion
Search of multiple values connecting multiple tables
ema_rz Now, I didn't recreate your tables, 6 levels deep, but did a mock-up at a smaller scale in Power Query. Although you mentioned not to be "practical" with PQ, you might be able to follow the steps in the attached (not so elegant) example. With a number of consecutive merge and expand steps, starting with the last table, you will end up with the sequence of items from the first column in the first table that correspond to the item selected from the second column of the last table.
What a revolting problem. Are you sure you need to solve it?
To iterate through a sequence of Tables you would need to be able to address each Table by index, say = TblColλ(1,2), where the Lambda function is defined by
= LAMBDA(tbleNum,fieldNum,
CHOOSECOLS(CHOOSE(tbleNum,Table1,Table2),fieldNum)
)(note the presence of a beta release function).
Then you would need a further Lambda function = NextTargetλ(target,k), which, given the list of valid target values for Table k would produce a list of valid output values
= LAMBDA(tgt,tblNum,
DROP(REDUCE("", tgt,
LAMBDA(list,t,
VSTACK(list, FILTER(TblColλ(tblNum,1),TblColλ(tblNum,2)=t)))
),
1)
)Even then, you would need to provide a further REDUCE formula to step through the Tables and cater for the fact that some of your tables have the columns in reverse order. Concatenating the result, if you ever got there, would be the easy part!
Unless someone is paying you a large sum of money for this, I would recommend finding some other way of spending you life. I attach a file with outline ideas.
