Check for match when one of the tables has multiple keys concatenated

[Iwanicki]

I need to check if there is a match between two tables. One of the tables has some records that have multiple keys concatenated. Example:

Table1

ServerName	ServerIP
A	11.11.11
B	5.5.5;6.6.6;7.7.7
C	8.8.8

Table2

ServerIP

11.11.11

5.5.5

6.6.6

8.8.8

What I need to do:
For each ServerName, check if all its ServerIPs from Table1 have a match in Table2. In the case presented above, there is a full match for ServerNames A and C but not for B.

How I currently do that:
I split the ServerIP column of Table1 by delimiter ( ; ) into rows. I merge Table1 and Table2, then add a column where true is returned for matching records and false is returned for records with no match. Then I group by ServerName and perform a MIN aggregation on the true/false column. The output of the query is a table that for each ServerName contains information if there is a match for all ServerIPs or not. The problem is that I create an additional query, and I already have several such queries in the data model and the number is growing, which is undesired.

Is there any way I can perform the check without adding additional queries? 

[Iwanicki]

Thank you all. Unfortunately, none of the ideas presented here achieve the expected result. However, the approach with custom function that @ERD seems to be the most promising, I'll try to go that way.

[v-kelly-msft]

Hi  @Iwanicki ,

Sorry for the late reply.

Use below M codes to get the result you need:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTUAyOlWJ1oJVM9IASzzPSAEMyy0ANCpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ServerIP = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let 
 serverip=[ServerIP] in Table.SelectRows(Table, each [New]=serverip){0}[ServerName]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Table.RowCount(Table.SelectRows(#"Added Custom",(x)=>x[Custom]=[Custom]))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each let 
 serverip=[ServerIP] in Table.SelectRows(Table, each [New]=serverip){0}[Custom]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Custom.1]=[Custom.2] then [Custom] else null),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom3",{{"Custom.3", "Final result"}})
in
    #"Renamed Columns"

And you will see:

For the updated .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ERD · Accepted solution]

Hi @Iwanicki ,

You can lookup the values using custom M function and return result for found/not found values in first table.

1. Add a new blank query
2. Create custom function fxLookup:

(lookupValue as any, lookupTable as table, lookupColumnName as text, returnColumnValue as text) =>
let
 colLookup = Table.Column(lookupTable, lookupColumnName),
 colToReturn = Table.Column(lookupTable, returnColumnValue),
 lookup = List.PositionOf(colLookup, lookupValue, 0),
 Result = if lookup >=0 then "Found" else "Not found"
in
 Result

3. In your Table 1 click Add Column - Invoke Custom Function, fill in the fields and click Ok. Et voilà:

In code:

#"Invoked Custom Function" = Table.AddColumn(Source, "CheckMatching", each #"fxLookup"([ServerIP], S2, "ServerIP", "ServerIP"))

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

[Iwanicki]

Thank you guys for your replies. Unfortunately, none of the suggested ideas solve the issue that I presented. I was seeking a non-DAX solution; I do know DAX, but in my use case a neat M solution would be better. M's ...Contain... functions are not sufficient: firstly, I need to check if all ServerIPs match, not just some; secondly, they might give me false matches, for example, 1.1.11 does contain 1.1.1, but in fact such a match would not make sense.
I came across an example of a user-defined M function this week, something I wasn't familiar with, and while it had a completely different purpose, it gave me an idea. If I have a solution, I'll share it here.

[StefanoGrimaldi]

check out this topic that may have the solution for this problem you looking for

https://community.powerbi.com/t5/Desktop/any-function-can-do-exact-match-text-in-power-query/m-p/698... 

[v-kelly-msft]

Hi  @Iwanicki ，

If you wanna reduce the queries,I would suggest you do some calculations using dax:

First duplicate the column "ServerIP" from table1 then split it into rows by semicolon:

Then back to data view and create 3 columns as below:

Column = LOOKUPVALUE('Table'[ServerName ],'Table'[ServerIP - Copy],'Table (2)'[ServerIP],blank())

Column 2 = CONCATENATEX(FILTER('Table (2)','Table (2)'[Column]=EARLIER('Table (2)'[Column])),'Table (2)'[ServerIP],";")

Column 3 = LOOKUPVALUE('Table'[ServerName ],'Table'[ServerIP],'Table (2)'[Column 2],blank())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[StefanoGrimaldi]

hey, 

if they are on different tables you can explore using the contains function inside M code, as a new column, here a example of it (M code on advance editor, can be added as new column), them exapand the resulting column:

 Table.AddColumn(#"Added Custom1", " new column name ", each let currenttext =[ServerIP] in Table.SelectRows(#"table2",each Text.Contains(currenttext,[ServerIP])))

[Jakinta]

Maybe this?

let
    Source = Table1,
    FromTable2 = Table.AddColumn(Source, "FullMatch", each List.ContainsAny(Table2[ServerIP], {[ServerIP]}))
in
    FromTable2