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
Imrx2
New Member

Split Column

Hi,

I have following column in which the characters before "/" need to be removed (deleted). If split column is used, the values after "/" goes to a new column. but I need those values to remain in column A along with the existing values without "/".

 

Column A
Ellite/trf1234u
Elite/czc0987xy
Pro/trfwx89io
trfgh789re
czcyr6780i
Pro/czc89piu
Elitepro/ui876tr

 

Thanks in advance.

5 REPLIES 5
ChrisMendoza
Resident Rockstar
Resident Rockstar

@Imrx2 - use:

 Table.TransformColumns(#"Changed Type", {{"Column A", each Text.AfterDelimiter(_, "/"), type text}})

which can also be used in the GUI in the Transform tab  > Text Column > Extract > Text After Delimiter

returns:

image.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!



Hi Chris,

when Extract > Text After Delimiter is done, the values which do not contain the delimiter "/" become blank.

please note, I have over 6000 rows in this sheet along with other columns.

Thanks

 

@Imrx2 - well, I suppose you could add a column as :

= Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column A],"/") then Text.AfterDelimiter([Column A], "/") else [Column A])

rather than using the GUI. 






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!



ichavarria
Solution Specialist
Solution Specialist

Hi @Imrx2,

 

You can split the "Column A" into two separate columns by using the "Split Column" transformation in Power Query. Here's how you can do it:

 

  1. Go to the Power Query Editor.
  2. Select the "Column A" by clicking on its header.
  3. Go to the "Transform" tab in the ribbon and click on "Split Column".
  4. In the drop-down menu, select "By Delimiter".
  5. In the "Split Column by Delimiter" dialog box, enter "/" as the delimiter.
  6. Choose "At the right-most delimiter" from the "Split at" options.
  7. Enter a name for the new column that will contain the characters after "/".
  8. Click on "OK" to split the column.

 

This will split the "Column A" into two separate columns: the original column with the characters before "/", and a new column with the characters after "/". If you want to keep the values after "/" in the original column, you can follow these additional steps:

 

  1. Click on the header of the new column that contains the characters after "/" to select it.
  2. Go to the "Transform" tab in the ribbon and click on "Replace Values".
  3. In the "Replace Values" dialog box, enter "/" as the value to replace, and leave the "With" field blank.
  4. Click on "OK" to replace the "/" with blank values.
  5. Rename the new column to "Column A" by right-clicking on its header and selecting "Rename".
  6. Click on "Close & Apply" to apply the changes and load the data into Power BI.

 

This will keep the values after "/" in the original "Column A" column, along with the existing values without "/".

 

 

Best regards, 

Isaac Chavarria

If this post helps, then please consider Accepting it as the solution and giving Kudos to help the other members find it more quickly

Thanks Isaac for the quick response. but the new column which is getting created after splitting does not contain "/" character. moreover for the values without the delimiter in the original column its showing null in the new column getting created.

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.