Is it possible to find the place value of Numbers using Power Query - M.
Websites
Convert number to words
1.
https://support.microsoft.com/en-us/office/convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98#:~:text=Type%20the%20formula%20%3DSpellNumber(A1,Enter%20to%20confirm%20the%20formula.
w.extendoffice.com/documents/excel/5905-excel-convert-number-to-words-in-rupees.html
Hello @Sairam,
I guess you want to convert numbers to words:
In Japanese you would not need to have a “Translation” table for the number lower than 100 - lower than 10 would be sufficient. But in germanic and romanic languages you need to have a table for the numbers from 0-99, which you can use to replace the numbers with words.
Mine looked like this, so I had to split it first. Note that the first entries are duplicated to cater for numbers of different length:
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
#"Split Column by Delimiter" = Table.SplitColumn(Source, "NumberWord", Splitter.SplitTextByEachDelimiter({"–"}, QuoteStyle.Csv, false), {"Name", "Value"}),
Record = Record.FromTable(#"Split Column by Delimiter")
in
Record
And here is the main code:
let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}}),
#"Added Padding" = Table.TransformColumns(#"Changed Type", {{"Number", each Text.PadStart(_, 15), type text}}),
#"Split Column by Position" = Table.SplitColumn(#"Added Padding", "Number", Splitter.SplitTextByRepeatedLengths(1), {
"HundredTrillions", "TenTrillions", "Trillions",
"HundredBillions", "TenBillions", "Billions",
"HundredMillions", "TenMillions", "Millions",
"HundredThousands", "Tenhousands", "Thousands",
"Hundreds", "Tens", "Ones"}),
#"Replaced space" = Table.ReplaceValue(#"Split Column by Position"," ","",Replacer.ReplaceText,Table.ColumnNames(#"Split Column by Position")),
#"Merged Ones" = Table.CombineColumns(#"Replaced space",{"Tens", "Ones"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Ones"),
#"Merged Thoussands" = Table.CombineColumns(#"Merged Ones",{"Tenhousands", "Thousands"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"thousand"),
#"Merged Millions" = Table.CombineColumns(#"Merged Thoussands",{"TenMillions", "Millions"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"million"),
#"Merged Billions" = Table.CombineColumns(#"Merged Millions",{"TenBillions", "Billions"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"billion"),
#"Merged Trillions" = Table.CombineColumns(#"Merged Billions",{"TenTrillions", "Trillions"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"trillion"),
#"Replaced Numbers" = Table.ReplaceValue(#"Merged Trillions","","",(a,b,c)=>Record.FieldOrDefault(Translation, a, a),Table.ColumnNames(#"Merged Trillions")),
#"Added hundred" = Table.ReplaceValue(#"Replaced Numbers","","",(a,b,c)=>if a <> "" and a <> "zero" then a & " hundred" else "",List.Select(Table.ColumnNames(#"Replaced Numbers"), each Text.Contains(_, "Hundred") )),
#"Added thousand" = Table.ReplaceValue(#"Added hundred",each [thousand],each if [thousand]<>"" then [thousand] &" thousand" else "",Replacer.ReplaceValue,{"thousand"}),
#"Added million" = Table.ReplaceValue(#"Added thousand",each [million],each if [million]<>"" then [million] &" million" else "",Replacer.ReplaceValue,{"million"}),
#"Added billion" = Table.ReplaceValue(#"Added million",each [billion],each if [billion]<>"" then [billion] &" billion" else "",Replacer.ReplaceValue,{"billion"}),
#"Added trillion" = Table.ReplaceValue(#"Added billion",each [trillion],each if [trillion]<>"" then [trillion] &" trillion" else "",Replacer.ReplaceValue,{"trillion"}),
#"Merged Columns" = Table.CombineColumns(#"Added trillion",Table.ColumnNames(#"Added trillion"),Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Word"),
#"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Word", Text.Trim, type text}}),
#"Replaced space2" = Table.ReplaceValue(#"Trimmed Text"," "," ",Replacer.ReplaceText,{"Word"})
in
#"Replaced space2"
Regards,
Matthias
Thanks Matthias, that was super fast. Instead of having a Translation helper table /list and splittling is it possible to declare it a variable based on position value as part of M.
Place Value.xlsm (30.3 KB)
Sairam, I mentioned that I think that a translation list is necessary for romanic and germanic languages. There is no ten one or ten three and there is also no e.g. two ten or three ten etc.
Englisch is in that regard better than German and e.g. French has even more twists in the numbers.
Thank you
And Japanese: They have ten one or ten three and there is also two ten or three ten. 
Wish one of these days your team comes out with Extended currency spell number table spanning across countries in line with the celebrated Extended Date Table.
This will enable readability of business reports across geography in a borderless world.
Hi @Sairam, did the responses provided by the community members help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hello @Sairam, just following up if the response above help you solve your inquiry?
We’ve noticed that no response was received from you on the post above. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Sairam, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
