Forum Discussion

Niels_NL's avatar
Niels_NL
Advocate I
10 years ago
Solved

Remove leading zero's from a string

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

 

 

Need Help
  • sdjensen's avatar
    sdjensen
    10 years ago

    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]), "" )
)

8 Replies

  • sdjensen's avatar
    sdjensen
    Solution Sage
    10 years ago

    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]), "" )
)
    • mo9k8's avatar
      mo9k8
      Frequent Visitor
      9 years ago

      Hi,

       

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

       

      Thanks,

      M

      • sdjensen's avatar
        sdjensen
        Solution Sage
        9 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        sdjensen
        Solution Sage
        9 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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.