topic Find multiple substring (not defined a priori) in a text in DAX Commands and Tips

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

Anonymous — Thu, 28 Sep 2023 08:06:12 GMT

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

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

SamInogic — Thu, 28 Sep 2023 09:51:35 GMT

Hi Anonymous ,

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/

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

barritown — Thu, 28 Sep 2023 10:00:10 GMT

Hi Anonymous,

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

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

Anonymous — Thu, 28 Sep 2023 10:11:28 GMT

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 =(

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

barritown — Thu, 28 Sep 2023 12:20:51 GMT

Anonymous,

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