Using Conditional column to return a string and the numbers following it

[Anonymous]

Hello,

Apologies if I don't explain this the best, still a little new to Power BI desktop. So essentially I have a column that has a couple million values, and some of them contain a medcode (i.e. med001, med002...med313) inside a long string of text. So the conditional column I have I set the parameters as if it contains med, return med, if not it returns another column. The issue I am having is I am trying to get it to return the numbers following it as well. I figure I can just add in the 300 rows in the conditional column and filter out each med code specifically, but I just wanted to see if there was a way I could automate it? I've tried having it return "med###" and "med***" hoping that would return the next three numbers but it just returns those characters.

Thank you!

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

You can achieve your goal by using Conditional column directly.

I build a sample to have a test.

You can set the parameters as if column1 contains med, return column1, if not it returns another column. 

Result is as below.

Or you can build a calculated column.

C.Custom = 
IF(CONTAINSSTRING('Table'[Column1],"med"),'Table'[Column1],'Table'[Column2])

Result:

You can download the pbix file from this link: Using Conditional column to return a string and the numbers following it

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[DataInsights]

@Anonymous,

Try this calculated column:

MedCode = 
VAR vMedCodeStartPos =
    SEARCH ( "med", TestMed[Column1], 1, 0 )
VAR vResult =
    IF (
        vMedCodeStartPos = 0,
        TestMed[Column2],
        MID ( TestMed[Column1], vMedCodeStartPos, 6 )
    )
RETURN
    vResult