how to convert "K" to thousands?

[Anonymous]

I have a column with numbers and some of the rows contain "K" value next to the number. for example: 23.5K.

I want to convert the number to be 235000 instead. please help.

[CNENFRNL]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrE0tjDRMzJWitWJVjI21bNU8AYzDc31DIyylWJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Exp = _t]),
    #"To Num" = Table.AddColumn(Source, "Num", each if Text.Contains([Exp], "k", Comparer.OrdinalIgnoreCase) then Expression.Evaluate(Text.Select([Exp], {"0".."9","."}) & "*1000") else Number.From([Exp]))
in
    #"To Num"

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Try this in a new custom column:

if Text.EndsWith(Text.From([Expenses]), "K")
then Number.From(Text.Start([Expenses], Text.Length([Expenses]) - 1)) * 1000
else [Expenses]

If you want to do this transformation in-column, rather than creating a new one, you could add a replace values custom step instead, something like this:

Table.ReplaceValue(
  previousStep,
  each [Expenses],
  each if Text.EndsWith(Text.From([Expenses]), "K")
  then Number.From(Text.Start([Expenses], Text.Length([Expenses]) - 1)) * 1000
  else [Expenses],
  Replacer.ReplaceValue,
  {"Expenses"}
)

Pete