What’s the best way you can check if one list is a subset of another list?
I.e. that all the elements of List_1 are in List_2
For example:
Bonus points if you can display which element is missing:
I’d love to see better implementations than what I have. Good luck!
(Note, if you’re going to do this don’t forget that an empty cell is not really empty, it actually contains a “”, which counts as an element. That can mess you up if you’re not aware.)
Requested access.
I had a hard time figuring out a way to do this in my last game and I’m already curious about your solution since mine is really ugly, but somehow working 
I think my solution is also ugly. It would be nice if they added a more native way to check for subsets, because the only solution I have so far is an indecipherable formula. Plus it breaks if there are any empty rows, which is a questionable feature that I don’t want to have to fix with another line to the formula…
Access shared
That is a cool solution! Mine is actually very close to that but not that elegant (yes. I made it even more ugly :D)
My needle-list is a lookup from a multi-select-table-lookup-column, which gives me a weird list-object back (including linked references to the original table, that wont match with the other list…). My solution was to flatten this monster to a string with totext and trim methods, than split it up again to a normal list, than go through it with “formulaMap” and “in” (a little bit like you did) and than checked if there is a false in it.
I think you could move this section to the “suggestion” thread. Since we got “contains” and “in”, there could also be a subset formula.
How would I move it there? And when I do, do you think it would be better to rewrite as an answer rather than making people click the link and request access?
Would this work ?
FormulaMap(Table.List2,Table.List1.Find(CurrentValue) ).Contains(-1)
Hmm… Unless I’m doing something wrong - which is always the first possibility - I don’t think it is working.
@mallika appears to have2 different lists referenced in his code (List2 and List1). Your formula has the same List (List1) twice. Does that have any impact on the result?
It should
What I am doing here is using FormulaMap -
So, so for each item in Table.List2
Find it in Table.List1
Find returns the position of the item and -1 if none is found.
So, FormulaMap(Table.List2,Table.List1.Find(CurrentValue) ) will return something like (1,2,-1,2)
And I use contains to see if there is a -1 in there.
Well, a set is always a subset of itself. So it should always return true for checking if List1 is subset of List1.
Share your doc with me and lets see if this works 
Take a look - fixed the first formula and added Not() to flip my true and false
FormulaMap(Table.List1,Table.List2.Find(CurrentValue) ).Contains(-1) to
Not(FormulaMap(Table.List1,Table.List2.Find(CurrentValue) ).Contains(-1))
I think I shared it! Try again!
Brillant @mallika, just for that you win the grand prize: 
<-- one star. (that’s the highest honor you can get)
It’s also much cleaner than mine is, so you also win the best solution award. Coincidentally, it’s the same as the star above, and we’ve run out of our emoji budget, so you’re just going to have to treasure that one star.
While I value the immensely I’ll be overjoyed to know that you have understood FormulaMap() and how I implemented this solution.
I think I got it. It’s basically: for item in list1, find the same item in list2. Is that correct?
Another question for you, is there any sort of change in algorithmic time complexity between using the FormulaMap(Table.List1,Table.List2.Find(CurrentValue) ) approach vs the In(false,Table.List1.Unique().FormulaMap(CurrentValue.In(Table.List2.Unique()))).Not() approach? In other words, would one be faster? And is that something I should think about when writing my formulas in Coda?
