Forum Discussion
How to Split a specific value from a column
Abbi Still some clean up to do but this should get you started:
Bill number = VAR __Text = [Tags] VAR __1stBill = SEARCH("bill", __Text,,0) VAR __1stSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stBill,0)) VAR __2ndBill = IF(__1stSemi<>0,SEARCH("bill", __Text,__1stSemi,0),0) VAR __2ndSemi = IF(IF(__2ndBill<>0,SEARCH(";", __Text, __2ndBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __2ndBill,0)) VAR __1stBillNum = IF(__1stBill=0,BLANK(),MID(__Text,__1stBill+4,__1stSemi-__1stBill-4)) VAR __2ndBillNum = IF(__2ndBill=0,BLANK(),MID(__Text,__2ndBill+4,__2ndSemi - __2ndBill - 4)) VAR __1stTales = SEARCH("tales:", __Text,,0) VAR __1stTalesSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stTales,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stTales,0)) VAR __1stTalesNum = IF(__1stTales=0,BLANK(),MID(__Text,__1stTales+6,__1stTalesSemi - __1stTales - 6)) RETURN SWITCH(TRUE(), ISBLANK(__1stTalesNum) && ISBLANK(__2ndBillNum),__1stBillNum, ISBLANK(__1stTalesNum) && NOT(ISBLANK(__2ndBillNum)),__1stBillNum & ";" & __2ndBillNum, ISBLANK(__2ndBillNum) && NOT(ISBLANK(__1stTalesNum)),__1stBillNum & ";" & __1stTalesNum, __1stBillNum & ";" & __2ndBillNum & ";" & __1stTalesNum )
Greg_Deckler thanks for looking at my post. Pasting down the data for you to explore. I modified the data. Please help me out.
| ID | Tags | Bill number |
| 456324 | Salt:Bill3334223; shoe;Paper;Note;6428 | 3334223 |
| 43548 | Chain;Keys;Socks:BILL7685;Tomato;Billdetails | 7685 |
| 545666 | Figs;Pops;Goods: Bill37682 | 37682 |
| 32466 | Hats;Yogurt: BiLL 21889; Salt:Bill48205 | 21889;48205 |
| 3350987 | Fanta;Clips;Onti:bill 23487;Billonfine | 23487 |
| 34228 | Sause;Grapes;Pads | |
| 58791 | Napkens;Thread:Bill 396742;Eggs;Trays: BILL 341730 | 396742;3411730 |
| 876537 | Bowl;Jackets: Bill23710;gloves:Bill365901;Tales:610472 | 23710;365901;610472 |
Abbi Still some clean up to do but this should get you started:
Bill number =
VAR __Text = [Tags]
VAR __1stBill = SEARCH("bill", __Text,,0)
VAR __1stSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stBill,0))
VAR __2ndBill = IF(__1stSemi<>0,SEARCH("bill", __Text,__1stSemi,0),0)
VAR __2ndSemi = IF(IF(__2ndBill<>0,SEARCH(";", __Text, __2ndBill,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __2ndBill,0))
VAR __1stBillNum = IF(__1stBill=0,BLANK(),MID(__Text,__1stBill+4,__1stSemi-__1stBill-4))
VAR __2ndBillNum = IF(__2ndBill=0,BLANK(),MID(__Text,__2ndBill+4,__2ndSemi - __2ndBill - 4))
VAR __1stTales = SEARCH("tales:", __Text,,0)
VAR __1stTalesSemi = IF(IF(__1stBill<>0,SEARCH(";", __Text, __1stTales,0),0)=0,LEN(__Text)+1,SEARCH(";", __Text, __1stTales,0))
VAR __1stTalesNum = IF(__1stTales=0,BLANK(),MID(__Text,__1stTales+6,__1stTalesSemi - __1stTales - 6))
RETURN
SWITCH(TRUE(),
ISBLANK(__1stTalesNum) && ISBLANK(__2ndBillNum),__1stBillNum,
ISBLANK(__1stTalesNum) && NOT(ISBLANK(__2ndBillNum)),__1stBillNum & ";" & __2ndBillNum,
ISBLANK(__2ndBillNum) && NOT(ISBLANK(__1stTalesNum)),__1stBillNum & ";" & __1stTalesNum,
__1stBillNum & ";" & __2ndBillNum & ";" & __1stTalesNum
)Greg_Deckler the above DAX formula is working good, but when i have Bill number in Tags for more than twice then we will be missing all the bill number after 2nd occurences. So can we re-model the formula so that any number occurences of Bill number can acheived.
Abbi Well, another approach would be to use Text to Table: Text to Table - Microsoft Power BI Community
Column = VAR __Separator = ";" VAR __SearchText = [Tags] VAR __Text = SUBSTITUTE(__SearchText,__Separator,"|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Word",PATHITEM(__Text,[Value],TEXT) )Basically from there you would add a column that searches for your key words and returns 1 if found and 0 if not. Then you filter to just the 1's. Then you use CONCATENATEX to return all of them.