How to convert number to words

[Anonymous]

Hi All,

I have a requirement wherein I need to convert the total cost in one column to words in another column dynamically. For example, if the total cost is 1180.08 RS, I need to display 'Rupees One Thousand One Hundred And Eighty & Eight Paise Only'. Please help.

[rajendraongole1 · Accepted solution]

Hi @Anonymous - In Power Query Editor,from "New Source," create a  "Blank Query", new query to "NumberToWords"

Open advanced query editor of blank query and replace the below code

let
NumberToWords = (num as number) as text =>
let
units = {"", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine"},
teens = {"Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen"},
tens = {"", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety"},

toWords = (n as number) as text =>
let
nInt = Number.RoundDown(n),
nDec = Number.RoundDown((n - nInt) * 100),

crore = Number.RoundDown(nInt / 10000000),
lakh = Number.RoundDown((nInt - crore * 10000000) / 100000),
thousand = Number.RoundDown((nInt - crore * 10000000 - lakh * 100000) / 1000),
hundred = Number.RoundDown((nInt - crore * 10000000 - lakh * 100000 - thousand * 1000) / 100),
remainder = nInt - crore * 10000000 - lakh * 100000 - thousand * 1000 - hundred * 100,

partToWords = (part as number, scale as text) as text =>
if part = 0 then "" else
if part < 10 then units{part}
else if part < 20 then teens{part - 10}
else tens{Number.RoundDown(part / 10)} & " " & units{Number.Mod(part, 10)},

croreText = if crore = 0 then "" else partToWords(crore, "Crore") & " Crore ",
lakhText = if lakh = 0 then "" else partToWords(lakh, "Lakh") & " Lakh ",
thousandText = if thousand = 0 then "" else partToWords(thousand, "Thousand") & " Thousand ",
hundredText = if hundred = 0 then "" else units{hundred} & " Hundred ",
remainderText = if remainder = 0 then "" else partToWords(remainder, ""),
paiseText = if nDec = 0 then "" else " & " & partToWords(nDec, "") & " Paise",

result = Text.Trim(croreText & lakhText & thousandText & hundredText & remainderText) & paiseText & " Only"
in
if nInt = 0 then "Zero" & paiseText & " Only" else result
in
toWords(num)
in
NumberToWords

• Go to your original query that contains the sample data.
• Add a new column by clicking on "Add Column" and then "Invoke Custom Function".
• Select your NumberToWords function and pass the TotalCost column as the parameter.

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[Anonymous]

Thank you. I'll check and update you.