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
unclejemima
Post Patron
Post Patron

Pull 2 words separated by dashes to make new Column

I've got some items in Inventry[part] where I need to make a new column based off the part number.

 

Example of part numbers...

55-D-Kaffee-German-L-Hot
55-D-Kaffee-German-M-Hot
55-D-Kaffee-Italian-L-Hot
55-D-Kaffee-Italian-L-Ice
55-D-Kaffee-Italian-M-Hot
55-D-Kaffee-Italian-M-Ice
55-D-MilchK-7Layer-L-Gel
55-D-MilchK-7Layer-L-Hot
55-D-MilchK-7Layer-L-Ice
55-D-MilchK-7Layer-M-Gel
55-D-MilchK-7Layer-M-Hot
55-D-MilchK-7Layer-M-Ice
55-D-MilchK-Cappuc-L-Hot
55-D-MilchK-Cappuc-M-Hot

 

I'm trying to make a new column where it will extract the last 2 words between the "-"

 

Example, if the code is 55-D-MilchK-Cappuc-M-Hot, I want to extract MilchK-Cappuc

 

Any creative way I could do that in a new column???  The number of characters is not always the same, but there is always dashes separating the words.

 

Please and thank you!

1 ACCEPTED SOLUTION

@unclejemima,

 

You could try otherwise:

 

try Text.BetweenDelimiters([PartNumber],"-","-",1,1) otherwise Text.BetweenDelimiters([PartNumber],"-","-",1,1)

It should allow you to find the errors, then you can analyse the reason why it failed and modify your Add Column.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



View solution in original post

13 REPLIES 13
ChrisMendoza
Resident Rockstar
Resident Rockstar

@unclejemima,

 

Does this work for your entire dataset?

=Text.BetweenDelimiters([PartNumber],"-","-",1,1)

It seems to work for your small sample:

3.PNG

 






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



When trying...

=Text.BetweenDelimiters([PartNumber],"-","-",1,1)

 

...I get an error.  My dataset has a bunch of values where the Part is 9 digits.  Perhaps its throwing an error because there is parts that don't have a delim.

 

Is there a way to mod the code to it will not error on parts that don't have a - as a delim?

 

 

@unclejemima,

 

You could try otherwise:

 

try Text.BetweenDelimiters([PartNumber],"-","-",1,1) otherwise Text.BetweenDelimiters([PartNumber],"-","-",1,1)

It should allow you to find the errors, then you can analyse the reason why it failed and modify your Add Column.






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



Is that in the query editor or just a calculated column formula?

Query Editor Text.BetweenDelimiters( )






Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!



zapps
Frequent Visitor

Hi @unclejemima,

 

In your Edit Query follow these steps:

 

1) Highlight the column you want to separate.

2) Click on "Add Column" tab from your ribbon.

3) Click on the Drop Down for "Column from examples" and select "From Selection"

4) In your first line, type Kaffee-German and press enter. 

Capture.PNG

This should auto fill the rest of your cells to pull the information you want.

 

Hope this helps!

 

zapps

 

This is cool...let me try!

I must be doing something wrong...not working.  See screenshot...

 

power bi query1.PNG

I'm assuming its because there are other (many tens of thousands of codes) that don't start with "55-D"


Perhaps I need to make a column that shows them only shows the parts if they start with "55-D"...and then I can apply the "Add Column>from Example> from Selection?"

Thoughts? 

Anonymous
Not applicable

You can right clikc your column header in your Query Editor, select Split column -> by delimiter, Enter the details to split ( Delimiter : -, split at "Each occurrance of the delimter" ). This will create new columns based on your data, keep the required column, delete other columns, you can filter based on this column as needed.

 

Thanks
Raj

Am I doing this correct?  Is my deliminator :-,

 

deliminator.PNG

@unclejemima

 

Hi, try with this Extract -Text Between Delimiters

 

Sin título.png

 

Regards

 

Victor




Lima - Peru

Got this to work by selecting custom deliminator, putting simply - as the deliminator and leaving all as default.

 

But what happend to my Part column...i was hoping it would leave Part alone and give me as well Part.1, Part.2, etc.  I can't find the original Part column.  

 

Is there a way to split the column, but leave the original column alone?

 

deliminator3.PNG

 

 

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.