Forum Discussion
Find multiple substring (not defined a priori) in a text
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
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
4 Replies
- SamInogic
Super User
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 [email protected]
Services: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
- barritown
Solution Sage
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
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
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
