Convert text to number

[AlineCl]

Hello,

In power query, i want to insert a conversion from text to number (to delete zero in the beginning) in this formula

= Table.AddColumn(#"Lignes filtrées1", "idbat-HH", each if Text.Contains([RS], "GRAND DELTA HABITAT") then "VL"&[EXTRACT_IDENT_INT] else null)

Il have tried :

= Table.AddColumn(#"Lignes filtrées1", "idbat-HH", each if Text.Contains([RS], "GRAND DELTA HABITAT") then "VL"&NumberFromText([EXTRACT_IDENT_INT]) else null)

But it doesn't work !

Thanks

[AlineCl]

Ok, it's good ! thanks 

= Table.ReplaceValue(#"idbat-ER",each [EXTRACT_IDENT_INT],each if Text.Contains([RS], "GRAND DELTA HABITAT") or Text.Contains([RS],"AXEDIA") then Text.TrimStart([EXTRACT_IDENT_INT],"0") else [EXTRACT_IDENT_INT],Replacer.ReplaceText,{"EXTRACT_IDENT_INT"})

[ronrsnfld]

What do you mean by "doesn't work"? Error? Computer crash? Wrong result?

If an error, what is the error message?

What is the content of [EXTRACT_IDENT_INT]?

What do you expect for a result? (Note: You cannot concatenate a string and a number)

I don't know if this is a typo, but:

NumberFromText is not valid M Code.  Try Number.FromText

[Peter_Beck · Accepted solution]

Hi -

So really, you want to just trim leading zeros from a text value, is that correct?

If so, take a look at this solution:

https://community.powerbi.com/t5/Desktop/Remove-leading-Zero-s-in-Query/m-p/247410

There is a Text.TrimStart function that might do what you want. You didn't post the error you are getting, but I don't believe using "&" to attempt to concatenate a number to a text will work.

Here is an example that seems to do what you want:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgtSS1S0lFySk3OBlIGhkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [FirstName = _t, LastName = _t, Hours = _t]),
MyTable = Table.AddColumn(Source,"idbat-HH",each if Text.Contains([Hours],"01") then "VL" & Text.TrimStart([Hours],"0") else null)
in
MyTable

Cheers,

Peter