Forum Discussion
Solved
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. ...
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.