Excel Power Query按字符拆分列后避免重复Items completed值

Excel & Power Query 实操步骤 #

• 把表1导入Power Query：点击「数据」选项卡→「从表格/区域」，勾选“我的表格有标题”
• 拆分Request列：选中Request列，转到「转换」选项卡→「拆分列」→「按字符数」，输入499，选择「拆分为行」
• 加索引列：点击「添加」选项卡→「索引列」→「从1开始」
• 标记每个Ref ID的首行：
  1. 「转换」→「分组依据」，分组列选Ref ID，新列名设为“组内数据”，操作选「所有行」
  2. 添加自定义列，公式写：=Table.AddColumn([组内数据], "是否首行", each [索引] = List.Min([组内数据][索引]))
  3. 展开“组内数据”列，删掉多余的中间列
• 调整Items completed列：再添加自定义列，公式：=if [是否首行] then [Items completed] else null，之后删掉原Items completed列，把新列重命名成Items completed
• 点击「关闭并上载」，就能得到符合要求的表3

SQL 实现方案（以SQL Server为例） #

假设原表结构是：Table1 (RefID INT, Request VARCHAR(MAX), ItemsCompleted INT)

用递归CTE拆分文本，同时标记每行是否为该RefID的首行，最终只保留首行的计数：

WITH SplitRequests AS (
    SELECT 
        RefID,
        Request,
        ItemsCompleted,
        1 AS RowNum,
        LEFT(Request, 499) AS SplitRequest,
        STUFF(Request, 1, 499, '') AS RemainingRequest
    FROM Table1
    WHERE LEN(Request) > 0
    UNION ALL
    SELECT 
        RefID,
        Request,
        ItemsCompleted,
        RowNum + 1,
        LEFT(RemainingRequest, 499),
        STUFF(RemainingRequest, 1, 499, '')
    FROM SplitRequests
    WHERE LEN(RemainingRequest) > 0
)
SELECT 
    RefID,
    SplitRequest AS Request,
    CASE WHEN RowNum = 1 THEN ItemsCompleted ELSE NULL END AS ItemsCompleted
FROM SplitRequests
ORDER BY RefID, RowNum;

不管用哪种方法，核心都是给每个RefID下的拆分标记首行，只保留首行的Items completed值，后续行设为null/0，这样统计总计的时候就不会因为拆分重复计数。

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