如何在Google Data Studio中提取分号分隔字符串的指定位置数值并生成报表

问题背景 #

我有一份每日导入Google Sheet的CSV报告，包含「Item ID」（每行唯一）和「Location results」列，后者以分号分隔位置与对应数值（例：Location A :1;Location B:3）。需要在Google Data Studio中生成报表，按精确匹配的位置展示对应Item ID的数值，且尽量避免手动修改Google Sheet，完全通过Data Studio公式/功能实现。

原始数据示例 #

期望报表视图 #

实现步骤 #

1. 连接并预处理数据源 #

将Google Sheet作为数据源连接到Data Studio后，先对「Location results」字段执行拆分多行操作：

• 编辑数据源，找到「Location results」字段，点击右侧菜单选择「拆分多行」
• 分隔符输入;，确认后每行的位置-数值对会拆分为独立行，对应原Item ID

2. 提取位置名称与数值 #

创建两个自定义字段，拆分位置和数值并处理格式：

• Location（位置名称）：

  TRIM(REGEXP_EXTRACT(Location results, "^(.*?):"))
  

  作用：提取冒号前的位置文本，用TRIM去除前后多余空格（比如示例中Location A 的尾部空格），保证精确匹配。
• Value（原始数值）：

  CAST(TRIM(REGEXP_EXTRACT(Location results, ":(.*)$")) AS NUMBER)
  

  作用：提取冒号后的数值文本，去除空格后转换为数字类型。

3. 处理缺失值（转为0） #

创建自定义字段Value (填充0)，将空值（无对应数值的位置-Item组合）转为0：

IFNULL(Value, 0)

4. 构建透视表实现目标视图 #

添加透视表组件，配置如下：

• 行维度：选择Location字段
• 列维度：选择Item ID字段
• 指标：选择Value (填充0)，聚合方式设置为求和（由于每个Location-Item ID组合仅对应一行数据，求和结果即为实际数值）

完成配置后，透视表会自动生成「位置为行、Item ID为列」的报表，无对应数值的单元格显示0，且位置名称完全精确匹配。

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