如何在Dataverse表的计算列中将数字转换为英文单词？

Dataverse计算列没有内置的数字转英文单词函数，你可以用下面的Power Fx自定义逻辑来实现，支持0到999万亿范围内的整数转换：

首先，创建一个计算列（类型设为文本），粘贴以下公式，记得把公式里的[YourNumberColumn]替换成你实际的数字列名称：

With(
    {
        // 基础数字单词映射
        Ones: Table(
            {Num: 0, Word: "zero"},
            {Num: 1, Word: "one"},
            {Num: 2, Word: "two"},
            {Num: 3, Word: "three"},
            {Num: 4, Word: "four"},
            {Num: 5, Word: "five"},
            {Num: 6, Word: "six"},
            {Num: 7, Word: "seven"},
            {Num: 8, Word: "eight"},
            {Num: 9, Word: "nine"},
            {Num: 10, Word: "ten"},
            {Num: 11, Word: "eleven"},
            {Num: 12, Word: "twelve"},
            {Num: 13, Word: "thirteen"},
            {Num: 14, Word: "fourteen"},
            {Num: 15, Word: "fifteen"},
            {Num: 16, Word: "sixteen"},
            {Num: 17, Word: "seventeen"},
            {Num: 18, Word: "eighteen"},
            {Num: 19, Word: "nineteen"}
        ),
        Tens: Table(
            {Num: 2, Word: "twenty"},
            {Num: 3, Word: "thirty"},
            {Num: 4, Word: "forty"},
            {Num: 5, Word: "fifty"},
            {Num: 6, Word: "sixty"},
            {Num: 7, Word: "seventy"},
            {Num: 8, Word: "eighty"},
            {Num: 9, Word: "ninety"}
        ),
        // 量级单词
        Scales: Table(
            {Factor: 1000000000000, Word: "trillion"},
            {Factor: 1000000000, Word: "billion"},
            {Factor: 1000000, Word: "million"},
            {Factor: 1000, Word: "thousand"},
            {Factor: 100, Word: "hundred"}
        )
    },
    // 定义转换小于1000数字的函数
    With(
        {
            ConvertLessThan1000: Num -> 
                If(
                    Num < 20,
                    LookUp(Ones, Num = ThisRecord.Num).Word,
                    If(
                        Num < 100,
                        Concatenate(
                            LookUp(Tens, Floor(Num/10) = ThisRecord.Num).Word,
                            If(Mod(Num,10) > 0, " " & LookUp(Ones, Mod(Num,10) = ThisRecord.Num).Word, "")
                        ),
                        Concatenate(
                            LookUp(Ones, Floor(Num/100) = ThisRecord.Num).Word & " hundred",
                            If(Mod(Num,100) > 0, " " & ConvertLessThan1000(Mod(Num,100)), "")
                        )
                    )
                )
        },
        // 处理大数的主逻辑
        If(
            [YourNumberColumn] = 0,
            "zero",
            With(
                {
                    ProcessScale: (RemainingNum, ScaleIndex) -> 
                        If(
                            ScaleIndex > CountRows(Scales),
                            "",
                            With(
                                {
                                    CurrentScale: Index(Scales, ScaleIndex),
                                    ScaleValue: Floor(RemainingNum / CurrentScale.Factor)
                                },
                                Concatenate(
                                    If(
                                        ScaleValue > 0,
                                        ConvertLessThan1000(ScaleValue) & " " & CurrentScale.Word & " ",
                                        ""
                                    ),
                                    ProcessScale(Mod(RemainingNum, CurrentScale.Factor), ScaleIndex + 1)
                                )
                            )
                        )
                },
                // 调用处理函数并去除末尾空格
                TrimEnds(ProcessScale(Abs([YourNumberColumn]), 1) & If([YourNumberColumn] < 0, " negative", ""))
            )
        )
    )
)

使用说明 #

• 替换公式中的[YourNumberColumn]为你Dataverse表中实际的数字列名称（比如[Amount]）
• 该公式支持整数范围：-999,999,999,999,999 到 999,999,999,999,999
• 对于小数列，可以扩展逻辑处理小数点后的部分，比如提取小数位后单独转换为"point XXXX"格式

示例转换 #

• 100 → "one hundred"
• 2345 → "two thousand three hundred forty five"
• 1234567 → "one million two hundred thirty four thousand five hundred sixty seven"
• -456 → "four hundred fifty six negative"（注：如果需要把negative放前面，可以调整最后拼接的逻辑）

内容的提问来源于stack exchange，提问作者Justin Vella