Remove leading zero's from a string

[Niels_NL]

Hey guys,

the situation is as follows: I have a set of values (string-type) that can either contain a number or the value 'AFW' (i.e.: unknown data). However, some numbers have leading zero's at the front (example: 0043526). I'd like to have those leading zero's removed, but still want the data-type to be a string considering I also still have the 'AFW'-values.

Test-table, original situation:

Test-table, desired situation:

Does anyone know any methods on how to get this done without modifying the original dataset (I only got this data exported into PowerBI Desktop, so I cannot edit this in Excel e.g.)?

Thanks,

~ Niels

[Anonymous]

In the powerQuery Editor

Add Column -> Custom Column

= try Number.From([NumberX]) otherwise [NumberX]

Note : NumberX is name of the text type column which has leading zeros. 

[sdjensen · Accepted solution]

Hi,

You could try something like this. This code will first check if the string can be converted into a number - if it can't then it will just return the string else it will convert the string to a number (removing leading zero's) and then turn the number back into a string.

NumberXNoLeadingZero = 
IF(
	ISERROR( VALUE( 'Table'[NumberX] ) ),
	'Table'[NumberX],
	CONCATENATE( VALUE('Table'[NumberX]), "" )
)

[Anonymous]

Hello Sdjensen,

I have a similar issue to the one posted, but it has to do with removing leading zeros from each octet of an IP address. For example, I am getting IP address information int the following format:

062.045.162.150

and I need to convert it to:

62.45.162.150

How can I modify your example to get the result desired?

Thank you,

[sdjensen]

Hi @Anonymous,

I am sorry, but I haven't seen your question until now - did you solve it?

I the Query Editor I would use the Split by delimiter '.' into 4 columns and then for each column remote the leeding 0's and then Concatenate the 4 columns back into one column.

[mo9k8]

Hi,

@sdjensen HELP PLEASE... Do you just add this to the Query Editor? I can't get it to work.

Thanks,

M

[sdjensen]

@mo9k8 - It is a DAX formula, so you have to add it as a calculation.

[mo9k8]

@sdjensen Perfect!

[Niels_NL]

@sdjensen Thanks, this worked!