Forum Discussion
Extract data from a column if the string matches multiple criterion
Hello,
I need help in extracting data from 'Data Column' as shown below. The string match condition that I'm looking for are "FM-" as shown in ID#1, "FM x" (FM with a space followed by a number x) as shown in ID#9 & "FMx" where x is a number as shown in ID#6. Any other FM's not meeting the above 3 criterias should be ignored. Is it possible to extract the sample data in another column?
| ID | Data Column | Output |
| 1 | Hello from FM-223, Random FM FM FM | FM-223 |
| 2 | Sample Data XXXXXX | Null |
| 3 | Sample Data XXXXXX | Null |
| 4 | Sample Data XXXXXX | Null |
| 5 | Sample Data XXXXXX | Null |
| 6 | Hi from FM212…..XXXXX sample FM to be ignored | FM212 |
| 7 | Lorem Ipsum | Null |
| 8 | placeholder text | Null |
| 9 | Bye FM 444 FM488 yes, more FMto be ignored | FM 444 FM488 |
2 Replies
rb160488 ,
Personally, I would spend some time to try to cleanse the data at source to make it easier to work with.
A bit of a challenge but I believe I have a solution for you. Needed to use a combination of the various TEXT functions available in DAX.
My first step was to find the start digit if your [Data Column] contains "FM":
FIND FM = FIND( "FM",FM[Data Column ], 1, 0 )ID
Data Column
Output
FIND FM
1
Hello from FM-223, Random FM FM FM
FM-223
12
2
Sample Data XXXXXX
Null
0
3
Sample Data XXXXXX
Null
0
4
Sample Data XXXXXX
Null
0
5
Sample Data XXXXXX
Null
0
6
Hi from FM212…..XXXXX sample FM to be ignored
FM212
9
7
Lorem Ipsum
Null
0
8
placeholder text
Null
0
9
Bye FM 444 FM488 yes, more FMto be ignored
FM 444
5
Next, I used the SWITCH Statement to create your 3 conditions given above:
Output1 = SWITCH( TRUE(), CONTAINSSTRING( [Data Column ], "FM-" ), MID( [Data Column ], [FIND FM], 6 ), // Satisfies the FM- scenario, but assumes only 3 digits after. CONTAINSSTRING( [Data Column ], "FM" ) && MID( [Data Column ], [FIND FM]+2,1) IN {"1", "2", "3","4","5","6","7","8","9"}, MID([Data Column ], [FIND FM], 5 ), // Satisfies "FM" & assumes 3 digits after CONTAINSSTRING( [Data Column ], "FM ") && MID( [Data Column ], [FIND FM]+3,1) IN {"1", "2", "3","4","5","6","7","8","9"}, MID([Data Column ], [FIND FM], 6 ))Output1
FM-223
FM212
FM 444
The only condition I wasn’t able to satisfy is if there are multiple FM’s in the same record. I would need to give this one some extra thought. There are probably alternative ways to play with the various text functions, but given all of the scenarios you provided, this was my best shot. Hope you can make any adjustments necessary with your real world data.
https://learn.microsoft.com/en-us/dax/containsstring-function-dax
Regards,
Thank you! Let me know if are able to figure out the Multiple FM's case.
