Let say you have the following tables:
- Table “Fruits”, that contains a list of fruits: Apple, Banana, Orange, Kiwi
- Table “Fruit salads”, that contains a list of different fruit salads:
Name: Apple salad, Fruits: Apple
Name: AppleAna, Fruits: Apple, Banana
Name: NotOrange, Fruits: Apple, Banana, Kiwi
The Fruits column in the “Fruit salads” table is a table lookup. The question is now how do I get the unique list of all the fruits used in any salad? I’ve tried using
ListCombine(), but it doesn’t seem like it combines list from Select list or lookup tables.
[Fruit salads].Fruits.ListCombine().Unique() to give the unique list of fruits used, in this case it means all fruits except Orange. Instead I will get the following result: [Apple], [Apple, Banana], [Apple, Banana, Kiwi].
Any ideas how to get the unique list of fruits?