Power Query - find split and remove

2
votes

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.

enter image description here

2
powerbipowerquerym
I've posted an answer, but have you tried using 'column from examples'? It can accommodate various delimiters, an might be better that a 'code' solution – Jon
Wow I just tried it. Worked fine ! Simple and easy . Never knew Power Bi had this feature. Thanks for your response – saran p

2 Answers

2
votes

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.

0
votes

It is possible, using the Text.PositionOfAny, Text.Range and variables. You need to create a new calculated column, with the following code:

So len, position_special, position_n are variables

len - gets the length of the text for getting the later substring

position_special = Text.PositionOfAny([Column1], {"-", "_"}, Occurrence.Last)

Looks for the "-" or "_" in the column, if it isn't there, then it will return a -1. You can test this in its own custom column.

position_n = position_special + 1

Just adds 1 to the string for the start position

return_value = if position_special <> -1 then Text.Range([Column1], position_n , (len - position_n)) else Text.TrimStart([Column1], "0")

This section is the IF/THEN/ELSE section, so it checks if it has a special character, not equal to -1, then will get the text in the range, if not, it should find trim the leading "0". The full code for the calculated column is:

let
len = Text.Length([Column1]),
position_special = Text.PositionOfAny([Column1], {"-", "_"}, Occurrence.Last),
position_n = position_special + 1,
return_value = if position_special <> -1 then Text.Range([Column1], position_n , (len - position_n)) else Text.TrimStart([Column1], "0")  
in  
return_value

enter image description here

Hope that helps. It may need tiding up, but it should get you what you want