Power Query - find split and remove

Question

I have a column that contains text data. I want to perform some operations on it.

1. Check if it has special characters, if so, then split and remove the leading zeros from the second part of the text and return the second part (without leading zeros).

2. If no special characters, remove leading zeros and return the text

I did this in Excel using search and substitute functions but I want to perform the same operation in Power BI Power Query Editor.

Answer 1

To remove leading zeros in text you can use the following custom column expression:

=Text.TrimStart([Column1], "0")

In the query editor under Transform > Split Column > By Delimiter you can split your column by a delimiter and a few extra settings. In your case you can do this a few times with the different delimiters. You can also choose which part you need and which you delte.

You also can combine both of the actions a few times.