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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mal_Sondh
Helper II
Helper II

Extracting data from a column after a certain character

Hi,

 

What is the best way to extract data such as this into another column:

If i have an original field called Col name and i wanted to normailse this as shown in the Normalised column, what formula would i need to write?

In basic format, the logic would be as follows:

Read the characters of the Col Name until you get to the first '-',  Ignore all chars before the '-', if another '-' exists take all chars between this and the orginal '-' and store in the new column(Normailsed Name), otherwise just take the rest of the chars and store in the new column (Normailsed Name) - see expected output below:

 

Col Name Normalised Name
abc - abc123 abc123
xyz - xyz123 - 456 xyz123

 

Any ideas?

2 ACCEPTED SOLUTIONS
mahoneypat
Employee
Employee

You can just add a new column with this formula

= Text.BetweenDelimiters([Col Name], "-", "-")

 

Regards,

Pat

 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

PhilipTreacy
Super User
Super User

Hi @Mal_Sondh 

Further to that, wrap it in Text.Trim to remove leading and trailing space characters

 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Trim(Text.BetweenDelimiters([Col Name], "-", "-")))

 

Phil 


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

2 REPLIES 2
PhilipTreacy
Super User
Super User

Hi @Mal_Sondh 

Further to that, wrap it in Text.Trim to remove leading and trailing space characters

 

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Trim(Text.BetweenDelimiters([Col Name], "-", "-")))

 

Phil 


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


mahoneypat
Employee
Employee

You can just add a new column with this formula

= Text.BetweenDelimiters([Col Name], "-", "-")

 

Regards,

Pat

 

 

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.