Return Missing or Extra Values of a Text String from a Non-Related Table

Hi o-johnralphp  ,
Thanks for reaching out to the Microsoft fabric community forum.


I tried implementing your requirement using LOOKUPVALUE, but it doesn’t work with your data because LOOKUPVALUE can only handle exact text matches, not concatenated text strings like the ones in your column. It would only work if you split the text into separate rows or values, which would likely disrupt your data structure.

Instead you can consider using PySpark in Fabric Notebook’s for this type of transformation, as it provides much greater flexibility when working with string operations. Instead of relying on complex DAX calculated columns, you can easily join Table1 and Table2 on Package Type using join(), bringing in the mandatory contents for each package. Once the tables are joined, you can use PySpark’s string and array functions such as split(), array_except(), and array_intersect() to compare the dispensed and mandatory items efficiently. This allows you to identify which items are missing or extra for each record.

I hope this information helps. Please do let us know if you have any further queries.
Thank you