原始数据如下:
转换后结果为:
DAX代码:
EVALUATE
VAR tSta = UNION(
ROW("TestCode","PPRE"),
ROW("TestCode","TAST"),
ROW("TestCode","TAST,OCCP"),
ROW("TestCode","OCCP,WAKC")
)
//统计每行中的Station数量
VAR a = ADDCOLUMNS(
tSta,"StationCount",
VAR aa = LEN([TestCode]) - LEN(SUBSTITUTE([TestCode],",","")) + 1 RETURN aa
)
//取得最大数量的Station
VAR vMaxStation = MAXX(a,[StationCount])
//根据最大数量创建辅助表
VAR b = GENERATESERIES(1,vMaxStation,1)
//生成叉积表
VAR c = CROSSJOIN(a,b)
//得到确切的行数
VAR d = FILTER(c,[StationCount]>=[Value])
//使用PATHITEM函数,通过[value]获取Station所处位置
VAR e = ADDCOLUMNS(
d,"Location",
PATHITEM(SUBSTITUTE([TestCode],",","|"),[Value])
)
//去掉无用的列
VAR z = SUMMARIZE(e,[Location])
RETURN z
如果字符串都是用逗号串接的,需要去掉其中的重复值,也可以用这种方法。