Trying to use a string in a column to come up with a name

[Anonymous]

Hello, Sorry if I am not the best at explaining, I am fairly new to Power BI, but I will try to do my best. Basically I have a columnn [apppreffer] where about a third of the rows have some sort of variation of med001, med002...and so on. They located in the middle of a string such as DonnaApp_med001_CDL. Each of the med codes have a certain name that corresponds with them. I tried doing something with the containtstring fucntion and then the search function but I couldn't get the formatting correct. Any help is greatly appreciated.

Thank you,

Kieran L.

[HotChilli]

It looks like you want to extract any text that begins "med" . Is that right?  Do you want a new column?

Post a small data sample (not a picture) please and the desired output.  Someone will help.

[Anonymous]

Yes I want to use a column, because I tried using a measure but it wouldnt accept the column [apprefer] in the measure.

The column with the data goes something like this (on the left) with the desired output on the right

Group1201_med003_cdl-a-truck-driver-branding	Google - PPC -SF
Group1201_med241_Entry-Level-CDL-A-Truck-Driver-Earn-Top-Pay-FAST_Statesboro	ClickCast - programmatic -SF
Blank
AllTruckJobs - General Lead (Experienced)
1201App_med001_CDL-A-TRUCK-DRIVER-_10_000-IN-LONGEVITY-REWARDS_Atlanta_Campaign-South_LP_	Indeed - PPC -SF

[Daviejoe]

Have you tried doing this in Power Query with Add a Column > Column From Examples?

 Start typing in the med003 text and do so in a few more columns and it will pick up the text between the "_"

Does this achieve what you are looking for?

[Anonymous]

Maybe? So i go and add a new column from examples, and in that I put all the different med cods in that column? theres a lot more columns in the table than I showed. There is about 7 million rows of data so would that then automatically go through all of them and trnasform the codes into the company names?

[Daviejoe]

If you type in several of the rows that have _med007_ then it will work out what you want to do and it will extract the med codes into one column automatically.  You will then be able to link the new med code column to the relevant company name.  If I've followed you correctly.  Columns from Examples is really powerful.

[Anonymous]

Is column from examples available on Power BI Desktop? or just the regular version? Would one option be to create a column that pulls out any med codes, then make a table with all the med codes and corresponding names and then connect the two? Would that be easier? Sorry if I don't understand everything.

[Daviejoe · Accepted solution]

Hi @Anonymous 

This is available in Power BI Desktop.

Yes, you could pull out the Med codes in this table and connect it to a table that has the Med codes and their corresponding names.

That would be the easiest way.

[Anonymous]

Awesome! Would I use the filter function to do that? Or would it be a different one?

[Daviejoe]

How many Med codes/company names do you have?

[Anonymous]

Just over 300 different codes/names

[Daviejoe]

You will need to have a seperate table that has all the Med codes and the Company name.

Are you able to get one of these generated by your IT Team? Does your data source have a distinct list of Med Codes & the corresponding Company names?

Or you may have to create it in Excel yourself and import it as a seperate table.

You can then match the Med code on each table in a relationship and then pull in the Company Name once that relationship is established correctly.

[Anonymous]

I have the list in an email, I was going to create the table today. Once I connect the two how will I pull out the med codes from the string of text that it is in? not all the med codes have the same string, for example some might be abcdmed001 and some might be efmed001gh or something similar?

[Daviejoe]

Without seeing all the variations of the med codes it's difficult for me to say exactly how you should wrangle the data.

If each med code is 6 characters then  you can use column from examples, once you've typed in several cells/rows Power Query will pick up what you are trying to do.  If you type in med001 for abcdmed001 and med 001 for efmed001gh then it will work on the basis that it needs to extract 'med' and the 3 characters after it.

Does this help?  I think you will need to try it and see how you get on as I've only seen a few of the Med codes.

[Anonymous]

Got it. Just finished up the excel table with the med codes and names. Will keep you updated. Thank you for all your help!

[Daviejoe]

No worries, hope you nail it!