寻求兼容Power BI的SQL拆分多分隔字段为独立列的解决方案

嘿，我明白你遇到的问题了——Power BI对某些SQL的CROSS APPLY语法支持确实有局限，尤其是当你想把拆分后的行转成列的时候。针对你这个固定有13个值的分隔字段，我有两个实用的方案，都能完美适配Power BI：

方案一：SQL层面直接拆分出固定列 #

既然你的Data字段拆分后固定是13个值，我们可以用字符串定位函数直接提取每一个位置的内容，这样查询返回的就是现成的列，不用依赖CROSS APPLY或者动态Pivot，Power BI可以直接识别使用。

修改后的SQL查询如下：

SELECT
    DataMessage.MessageDate
    , DataMessage.Data
    , Sensor.SensorID
    , CSNet.CSNetID
    , Sensor.SensorName
    , Application.ApplicationName
    -- 提取第1个值（第一个|之前的内容）
    , SUBSTRING(DataMessage.Data, 1, CHARINDEX('|', DataMessage.Data) - 1) AS Col1
    -- 提取第2个值（第一个和第二个|之间的内容）
    , SUBSTRING(DataMessage.Data, CHARINDEX('|', DataMessage.Data) + 1, CHARINDEX('|', DataMessage.Data, CHARINDEX('|', DataMessage.Data) + 1) - CHARINDEX('|', DataMessage.Data) - 1) AS Col2
    -- 提取第3个值
    , SUBSTRING(DataMessage.Data, CHARINDEX('|', DataMessage.Data, CHARINDEX('|', DataMessage.Data) + 1) + 1, CHARINDEX('|', DataMessage.Data, CHARINDEX('|', DataMessage.Data, CHARINDEX('|', DataMessage.Data) + 1) + 1) - CHARINDEX('|', DataMessage.Data, CHARINDEX('|', DataMessage.Data) + 1) - 1) AS Col3
    -- 第4到第12个值可以按照上面的逻辑依次类推，这里省略重复代码，你可以复制修改CHARINDEX的嵌套层级
    -- 提取第13个值（最后一个|之后的内容）
    , RIGHT(DataMessage.Data, LEN(DataMessage.Data) - CHARINDEX('|', REVERSE(DataMessage.Data), 1) + 1) AS Col13
FROM
    [corp--monnit1\monnitsql].[Enterprise1].[dbo].[DataMessage] 
LEFT OUTER JOIN
    [corp--monnit1\monnitsql].[Enterprise1].[dbo].[Sensor] ON DataMessage.SensorID = Sensor.SensorID 
RIGHT OUTER JOIN
    [corp--monnit1\monnitsql].[Enterprise1].[dbo].[CSNet] ON Sensor.CSNetID = CSNet.CSNetID 
LEFT OUTER JOIN
    [corp--monnit1\monnitsql].[Enterprise1].[dbo].[Application] ON Sensor.ApplicationID = Application.ApplicationID
WHERE
    CHARINDEX('|',DataMessage.Data)>0
    AND Application.ApplicationName ='ThreePhaseCurrentMeter500'
    AND Csnet.CSNetID = 10
    AND DataMessage.MessageDate > '03/29/2023'
    AND DataMessage.Data != '0'

提示：第4到第12列的提取逻辑和前3列一致，只需要逐层嵌套CHARINDEX来定位对应的分隔符位置，你可以根据示例快速复制修改。这种方法的好处是直接在数据源返回结构化的列，Power BI导入后无需额外处理。

方案二：在Power BI的Power Query中处理 #

如果你不想修改SQL，也可以在Power BI内部用Power Query完成拆分和转列的操作，步骤如下：

• 把你的原始SQL查询作为数据源导入Power BI
• 进入Power Query编辑器
• 找到Data列，右键选择拆分列 -> 按分隔符
• 在弹出的窗口中，选择分隔符为|，然后选择拆分为行，点击确定
• 给当前表添加一个索引列：点击添加列 -> 索引列 -> 从1开始
• 选中原始表中除了Data和索引列之外的所有列（比如MessageDate、SensorID等），然后点击转换 -> 透视列
• 在透视列设置中，将值列选择为拆分后的Data列，高级选项选择不要聚合，点击确定

这样操作后，Power Query会自动把每个原始行的13个拆分值转成对应的13列，完全避开SQL语法兼容问题，而且后续如果拆分的数量有变化，调整起来也更灵活。

备注：内容来源于stack exchange，提问作者Justin Hill