Find multiple substring (not defined a priori) in a text

[LordAssalt00]

Hello everyone, I'm new to this forum and also to using Power BI Desktop. I was trying to solve a small issue I'm encountering.

In practice, I need to be able to find words within a text contained in a row, let me explain it better. In a cell in column A, I have the string "A,B,C". In the cell in column B, I have the string "A,B,C,D,E,F,G,H,I".

So, I need to check if "A", "B", and "C" are in "A,B,C,D,E,F,G,H,I".

Similarly, if I have the string "C,B", I need to be able to check if "C" and "B" are in "A,B,C,D,E,F,G,H,I".

The strings in the cells of columns A and B do not have a fixed number of elements; they can vary.

The results is better explained here:

Column A	Column B	Results
A,B,C	A,B,C,D,E,F,G,H,I	True
C,B	A,B,C,D,E,F,G,I,N,P,Z	True
D,Z	A,B,C,I,Z	False

Thanks to everyone who will help me

[barritown]

Hi @LordAssalt00,

In addition to the Power Query solution proposed by @SamInogic, here is a DAX one:

And in plain text for convenience:

Calculated Column = 
VAR qty = LEN ( [Column A] ) - LEN ( SUBSTITUTE ( [Column A], ",", "" ) ) + 1
VAR tbl1 = GENERATESERIES ( 1, qty, 1 )
VAR tbl2 = ADDCOLUMNS ( tbl1, "Item", PATHITEM ( SUBSTITUTE ( [Column A], ",", "|" ), [Value] ) )
VAR tbl3 = ADDCOLUMNS ( tbl2, "Flag", INT ( NOT CONTAINSSTRING ( [Column B], [Item] ) ) )
RETURN IF ( SUMX ( tbl3, [Flag] ) > 0, FALSE (), TRUE () )

 Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[LordAssalt00]

And in case i have to perform the control of each row of A to all the rows of B?
Like check A,B,C if it is contained into  A,B,C,D,E,F,G,H,I into A,B,C,D,E,F,G,I,N,P,Z and into A,B,C,I,Z returning the id associated?

1	A,B,C	A,B,C,D,E,F,G,H,I	1,2,3
2	C,B	A,B,C,D,E,F,G,I,N,P,Z	1,2
3	D,Z	A,B,C,I,Z

Sorry for the replay that complicate the argument =(

[barritown · Accepted solution]

@LordAssalt00,

In this case, the calculated column should look like this:

And in plain text:

Calculated Column = 
VAR tbl1 = SELECTCOLUMNS ( Data2, "ID", [ID], "Col B", [Column B] )
VAR qty = LEN ( [Column A] ) - LEN ( SUBSTITUTE ( [Column A], ",", "" ) ) + 1
VAR tbl2 = GENERATESERIES ( 1, qty, 1 )
VAR tbl3 = ADDCOLUMNS ( tbl2, "Item", PATHITEM ( SUBSTITUTE ( [Column A], ",", "|" ), [Value] ) )
VAR tbl4 = CROSSJOIN ( tbl3, tbl1 )
VAR tbl5 = ADDCOLUMNS ( tbl4, "Flag", INT ( CONTAINSSTRING ( [Col B], [Item] ) ) )
VAR tbl6 = SUMMARIZE ( tbl5, [ID], "Cnt",
                       VAR CurrentID = [ID]
                       RETURN SUMX ( FILTER ( tbl5, [ID] = CurrentID ), [Flag] ) )
VAR tbl7 = FILTER ( tbl6, [Cnt] = qty )
RETURN CONCATENATEX ( tbl7, [ID], "," ) 

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[SamInogic]

Hi @LordAssalt00 ,

You can follow steps in Power Query Editor,

1. Create a custom column to create list for Column A by separating text using delimitor.

Text.Split([#"Column A"], ",")

2. Create a custom column to create a list for Column B by separating text using a delimiter.

Text.Split([#"Column B"], ",")

3. Now to compare values from both list create another custom column with below expression,

List.Accumulate(List.Positions([ColumnASplit]), true, (a,b) => a and ([ColumnASplit]{b} = [ColumnBSplit]{b}))

This will result as follows:

Thanks!

Inogic Professional Services Division

Power Platform and Microsoft Dynamics 365 CRM Development – All under one roof!

Drop an email at crm@inogic.com

Services:  http://www.inogic.com/services/

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/