Find text string in another table

[marcel97]

Hello all,

Our customer is creating tickets in their ticket system, which we mirror in our own ticket system.

The data of our customer looks as follows:

SummaryCustomer	DescriptionCustomer
Ticket 1	Description to Ticket 1
Ticket 2	Description to Ticket 2
Ticket 3	Description to Ticket 3
Ticket 4	Description to Ticket 4
Ticket 5	Description to Ticket 5

No when we mirror those tickets in our ticket system, our employees put a prefix in front of the Summary of the Customer (e.g. Customer123 Ticket 1) => see below

Summary

Customer Ticket 1

12345

45678

Internal OP 1

34567

Cust. Ticket 2

Custom. Ticket 3

C. Ticket 4

Customer123 Ticket 5

While there is no clear convention for the prefix, our summary will always contain the Summary of the Customer that comes from the first table.

In our table, I now want to find out for each item, whether it comes from the customer table, i.e. something like this:

Summary	Source
Customer Ticket 1	Customer
12345	Internal
45678	Internal
Internal OP 1	Internal
34567	Internal
Cust. Ticket 2	Customer
Custom. Ticket 3	Customer
C. Ticket 4	Customer
Customer123 Ticket 5	Customer

It has to work via comparing the text strings of the Summary fields.

Is there a way to do it? I tried using Containsstring() but failed miserably.

Thanks and kind regards

Marcel

[DataInsights · Accepted solution]

@marcel97,

Try this calculated column in InternalTable:

Source = 
VAR vTable =
    FILTER (
        CustomerTable,
        VAR vCustomerValue = CustomerTable[SummaryCustomer]
        RETURN
            CONTAINSSTRING ( InternalTable[Summary], vCustomerValue )
    )
VAR vResult =
    IF ( COUNTROWS ( vTable ), "Customer", "Internal" )
RETURN
    vResult