Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Datanaut!
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"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.