BigQuery数据导入Power BI时聚合列显示为嵌套JSON问题

从Google Analytics 4（GA4）导出数据到BigQuery，通过指定SQL查询生成表后，BigQuery内的Pageviews列是正常整数类型，但导入Power BI后，该列被识别为包含整行数据的JSON字符串，触发类型不匹配错误。此前使用相同流程生成报表已稳定运行一年多，近期新建查询/表时才出现该问题，源数据未做改动。

原查询语句：

SELECT
  Date,
  Time,
  IF (ENDS_WITH(uri,'/'), 
    LEFT(uri,LENGTH(uri)-1), 
    uri) as Page,
  COUNT(distinct unique) as Pageviews,
  COUNT(distinct SessionID) as UniquePageviews,
  SessionID

FROM (
  SELECT
    REGEXP_EXTRACT(replace(replace((select value.string_value from unnest(event_params) where key = 'page_location'),'https://',''),'http://',''),r'[^?]*') as uri,
    date(TIMESTAMP_MICROS(event_timestamp),'America/Edmonton') as Date,
    FORMAT_TIME('%R', time(TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), MINUTE),'America/Edmonton')) as Time,
    CAST((select value.int_value from unnest(event_params) where key = 'ga_session_id') as string) as SessionID,
    concat(event_bundle_sequence_id,event_timestamp) as unique

  FROM
    `lateral-store-###`.`analytics_###`.`events_*`
  WHERE 
    event_name = 'page_view'
)

GROUP BY
  Date,
  Time,
  Page,
  SessionID

ORDER BY Date DESC, Page ASC

1. 子查询中使用了unique这一SQL保留字作为字段名，尽管BigQuery允许该写法，但Power BI的连接器解析时可能出现异常，误将整行数据绑定到聚合列上。
2. 分组逻辑包含SessionID，导致每行对应一个Session，聚合列Pageviews的语义与分组维度不匹配，加剧了Power BI的解析混乱。

1. 替换保留字字段名 #

将子查询中的unique字段名改为非保留字（如unique_id），同时同步修改外层聚合语句：

-- 子查询内修改
concat(event_bundle_sequence_id,event_timestamp) as `unique_id`
-- 外层聚合修改
COUNT(distinct `unique_id`) as Pageviews

2. 调整分组逻辑（按需选择） #

如果业务需求是按日期、时间、页面聚合数据，移除SessionID的分组，避免每行对应单个Session：

GROUP BY
  Date,
  Time,
  Page -- 移除SessionID分组

若需保留Session维度，改用ARRAY_AGG(SessionID)收集对应页面的Session列表，避免聚合列解析异常。

3. 强制指定字段类型 #

在BigQuery查询中显式转换聚合结果为整数类型，确保Power BI能正确识别：

CAST(COUNT(distinct `unique_id`) AS INT64) as Pageviews,
CAST(COUNT(distinct SessionID) AS INT64) as UniquePageviews

4. Power BI端重置数据连接 #

修改BigQuery查询并重新生成表后，在Power BI中：

• 删除原有数据源连接，重新导入新生成的表
• 进入Power Query编辑器，手动将Pageviews列类型设置为整数
• 关闭自动类型检测，手动指定各列数据类型

SELECT
  Date,
  Time,
  IF (ENDS_WITH(uri,'/'), 
    LEFT(uri,LENGTH(uri)-1), 
    uri) as Page,
  CAST(COUNT(distinct `unique_id`) AS INT64) as Pageviews,
  CAST(COUNT(distinct SessionID) AS INT64) as UniquePageviews
FROM (
  SELECT
    REGEXP_EXTRACT(replace(replace((select value.string_value from unnest(event_params) where key = 'page_location'),'https://',''),'http://',''),r'[^?]*') as uri,
    date(TIMESTAMP_MICROS(event_timestamp),'America/Edmonton') as Date,
    FORMAT_TIME('%R', time(TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), MINUTE),'America/Edmonton')) as Time,
    CAST((select value.int_value from unnest(event_params) where key = 'ga_session_id') as string) as SessionID,
    concat(event_bundle_sequence_id,event_timestamp) as `unique_id`
  FROM
    `lateral-store-###`.`analytics_###`.`events_*`
  WHERE 
    event_name = 'page_view'
)
GROUP BY
  Date,
  Time,
  Page
ORDER BY Date DESC, Page ASC

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