Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This M snippet is a pattern for converting currency stored as text to a proper numeric value e.g. $18B becomes 18000000000
#"Converted currency text" = Table.AddColumn(#"Previous query step","new_column_name",each
let
//convert the original text to lower case
lower = Text.Lower([currency_as_text]),,
//add as many Text.Replace as you need to remove unwanted words
//in case of many words to remove, could iterate a list of words
words_removed = Text.Replace(lower,"unknown",""),
//for text $180B, following split creates a list {"$180","b"}
//use this splitter instead of Text.End in case suffix is multiple characters
split = Splitter.SplitTextByCharacterTransition(
{"0".."9"}, // split when one of these
{"a".."z"} // changes to one of these
)(words_removed), // use the splitter function on the words_removed variable
//get the second list item created above
//e.g. "b"
//if the original value doesn't have a suffix, there's an error here, so put "nope" instead
suffix = try split{1} otherwise "nope",
//now define a record to use as a lookup
//we'll use these numbers with the Number.Power function
lookup = [b = 9, bn = 9, bns = 9,
m = 6, mn = 6, mns = 6, mm = 6,
k = 3, nope = 0],
//get the first list item (the amount)
//e.g. $180
numtext = split{0},
//convert amount to numeric (should handle currency symbol automatically)
//e.g. 180
num = Number.FromText(amt),
//multiply the number by 10 raised to the power from the lookup record created above
//e.g. 180 * 10^9 = 180000000000
new_num = num * Number.Power(10,Record.Field(lookup,suffix))
//ignore errors
in try new_num otherwise null
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment