Forum Discussion
How to Split a specific value from a column
Hi All,
We have a column named Tags against each ID which contains the bill numbers. Have to pull out the bill numbers against each ID from tags column. Bill number doesn't have standard format. Few Syntax from below screens hot -- Itemname:BillBillnumber , Itemname:BILLBillnumber , Itemname: BillBillnumber. Each tag is seperated by a ";" . Each ID can have many bill numbers or dont have it. There may be some other number in the tags list other than Bill number.
Tried using Power Query and also dax(new column), unable to figure it out. Please help.
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 )
8 Replies
Abbi You didn't post sample data as text so you may get a few syntax errors because I can't test this but try:
Bill number column = VAR __Text = [Tags] VAR __1stBill = SEARCH("bill", __Text,,BLANK()) VAR __1stSemi = SEARCH(";", __Text, __1stBill,BLANK()) VAR __2ndBill = SEARCH("bill", __Text,__1stSemi,BLANK()) VAR __2ndSemi = SEARCH(";", __Text, __2ndBill,BLANK()) VAR __1stBillNum = IF(ISBLANK(__1stBill),BLANK(),MID(__Text,__1stBill+4,__1stSemi - 1stBill - 4)) VAR __2ndBillNum = IF(ISBLANK(__2ndBill),BLANK(),MID(__Text,__2ndBill+4,__2ndSemi - 2ndBill - 4)) RETURN IF(ISBLANK(__2ndBillNum),__1stBillNum, __1stBillNum & ";" & __2ndBillNum)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 )
