2.39 PowerBI数据建模-将数字转换为简体和繁体中文大写

本文链接：https://blog.youkuaiyun.com/pbi_zijixue/article/details/145466109

加入 PowerBI自己学知识星球：下载源文件，边学边练；遇到问题，提问交流，有问必答。

为满足某些财务场景，需要将数字转换为简体和繁体中文大写。

解决方案

在PowerBI中，DAX中没有现成的函数，需要用复杂的手工公式将数字替换为中文，这个公式要把数字的各种情况考虑进去，加到公式的逻辑中去。

主要考虑以下因素：

1 空值

2 正负

3 小数

4 数字长度

5 个位是0

6 连续0

7 简体、繁体

用到的函数有：

1 VAR变量

2 INT取整

3 ABS取绝对值

4 LEN计算文本长度

5 FIND指定字符在第几个字符

6 MID取中间字符

7 RIGHT 取右侧字符

8 IF条件判断和嵌套

9 SWITCH条件判断

10 CONTAINSSTRING文本包含

11 SUBSTITUE文本替换

逻辑梳理清楚，书写成代码，后面直接复制粘贴就能使用，只要将代码中的m等于要转换的度量值或列即可。

简体中文大写的代码，如下：

Measure 简体中文大写 =

VAR m = [Measure]

VAR a = INT(ABS(m))

VAR lenth = LEN(a)

VAR b1 = IF(MID(a,lenth,1)="0",IF(lenth>=2,BLANK(),0),MID(a,lenth,1))

VAR b2 = IF(lenth>=2,IF(MID(a,lenth-1,1)="0",IF(MID(a,lenth,1)="0",BLANK(),0),MID(a,lenth-1,1)&"十"))

VAR b3 = IF(lenth>=3,IF(MID(a,lenth-2,1)="0",IF(MID(a,lenth-1,1)="0",BLANK(),0),MID(a,lenth-2,1)&"百"))

VAR b4 = IF(lenth>=4,IF(MID(a,lenth-3,1)="0",IF(MID(a,lenth-2,1)="0",BLANK(),0),MID(a,lenth-3,1)&"千"))

VAR b5 = IF(lenth>=5,IF(MID(a,lenth-4,1)="0",BLANK(),MID(a,lenth-4,1)&"万"))

VAR b6 = IF(lenth>=6,IF(MID(a,lenth-5,1)="0",IF(MID(a,lenth-4,1)="0",BLANK(),0),IF(MID(a,lenth-4,1)="0",MID(a,lenth-5,1)&"十万",MID(a,lenth-5,1)&"十")))

VAR b7 = IF(lenth>=7,IF(MID(a,lenth-6,1)="0",IF(MID(a,lenth-5,1)="0",BLANK(),0),IF(MID(a,lenth-5,1)="0"&&MID(a,lenth-4,1)="0",MID(a,lenth-6,1)&"百万",MID(a,lenth-6,1)&"百")))

VAR b8 = IF(lenth>=8,IF(MID(a,lenth-7,1)="0",IF(MID(a,lenth-6,1)="0",BLANK(),0),IF(MID(a,lenth-6,1)="0"&&MID(a,lenth-5,1)="0"&&MID(a,lenth-4,1)="0",MID(a,lenth-7,1)&"千万",MID(a,lenth-7,1)&"千")))

VAR b9 = IF(lenth>=9,IF(MID(a,lenth-8,1)="0",BLANK(),MID(a,lenth-8,1)&"亿"))

VAR b10 = IF(lenth>=10,IF(MID(a,lenth-9,1)="0",IF(MID(a,lenth-8,1)="0",BLANK(),0),IF(MID(a,lenth-8,1)="0",MID(a,lenth-9,1)&"十亿",MID(a,lenth-9,1)&"十")))

VAR b11 = IF(lenth>=11,IF(MID(a,lenth-10,1)="0",IF(MID(a,lenth-9,1)="0",BLANK(),0),IF(MID(a,lenth-9,1)="0"&&MID(a,lenth-8,1)="0",MID(a,lenth-10,1)&"百亿",MID(a,lenth-10,1)&"百")))

VAR b12 = IF(lenth>=12,IF(MID(a,lenth-11,1)="0",IF(MID(a,lenth-10,1)="0",BLANK(),0),IF(MID(a,lenth-10,1)="0"&&MID(a,lenth-9,1)="0"&&MID(a,lenth-8,1)="0",MID(a,lenth-11,1)&"千亿",MID(a,lenth-11,1)&"千")))

VAR b13 = IF(lenth>=13,IF(MID(a,lenth-12,1)="0",BLANK(),IF(MID(a,lenth-11,1)="0"&&MID(a,lenth-10,1)="0"&&MID(a,lenth-9,1)="0"&&MID(a,lenth-8,1)="0",MID(a,lenth-12,1)&"万亿",MID(a,lenth-12,1)&"万")))

VAR b =

SWITCH(lenth,

1, b1,

2, b2&b1,

3, b3&b2&b1,

4, b4&b3&b2&b1,

5, b5&b4&b3&b2&b1,

6, b6&b5&b4&b3&b2&b1,

7, b7&b6&b5&b4&b3&b2&b1,

8, b8&b7&b6&b5&b4&b3&b2&b1,

9, b9&b8&b7&b6&b5&b4&b3&b2&b1,

10, b10&b9&b8&b7&b6&b5&b4&b3&b2&b1,

11, b11&b10&b9&b8&b7&b6&b5&b4&b3&b2&b1,

12, b12&b11&b10&b9&b8&b7&b6&b5&b4&b3&b2&b1,

13, b13&b12&b11&b10&b9&b8&b7&b6&b5&b4&b3&b2&b1,

"整数部分数字过长,不能显示"

)

VAR c = IF(CONTAINSSTRING(m, "."), "点"&RIGHT(m,LEN(m)-FIND(".",m)))

VAR d = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(b&c,"1","一"),"2","二"),"3","三"),"4","四"),"5","五"),"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")