Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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.

1 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

15 REPLIES 15
HotChilli
Super User
Super User

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
Not applicable

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-brandingGoogle - PPC -SF
Group1201_med241_Entry-Level-CDL-A-Truck-Driver-Earn-Top-Pay-FAST_StatesboroClickCast - 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

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

 

Column from examples.png

 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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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?

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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

How many Med codes/company names do you have?

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Just over 300 different codes/names

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.

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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?

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

No worries, hope you nail it!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.