Split Column

[Imrx2]

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.

[ChrisMendoza]

@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:

[Imrx2]

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

[ChrisMendoza]

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

[ichavarria]

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

[Imrx2]

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.