Forum Discussion
Convert text to number
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
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
MyTableCheers,
Peter
3 Replies
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
MyTableCheers,
Peter
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
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"})
