Power BI处理JSON全攻略：从嵌套拆解到API对接

1. 先搞清楚：Power BI 眼里的 JSON 到底是什么样

做 Power BI 的人，十有八九迟早会撞上 JSON。我最早接触这个组合，是帮一个客户接第三方接口的订单数据，对方甩过来一个几兆的 JSON 文件，里面嵌套了三层，我当时第一反应是"这玩意在 Excel 里不是挺好处理的吗"，结果发现 Power BI 的导入逻辑跟 Excel 完全不是一个路子。

先说一个很多人容易绕晕的点：JSON 在 Power Query 里并不是"一张表"，它天生是层级结构。顶层可能是数组，数组里套对象，对象里再套数组，这就是为什么你直接导入 JSON 文件，看到的往往是一个孤零零的 Record（记录）或 List（列表），而不是像 CSV 那样直接出现行列。

以 Json.Document 函数为例，它是 Power Query 解析 JSON 的核心入口。你可以用高级编辑器运行一句：

let Source = Json.Document(File.Contents("C:\data\orders.json")) in Source

返回的结果不是 Table，而是一个 native 结构。如果 JSON 顶层是[{...},{...}]，你会得到一个 List；如果顶层是{"data": {...}}，你会得到一个 Record。理解这个差异，是后面所有拆解动作的前提，因为List 要用Table.FromList或List.Transform处理，Record 要用Record.Field或Record.ToTable处理，用错函数就是一堆报错。

再补一个基础概念，方便刚入门的朋友：JSON 有且只有六种值——对象（Record）、数组（List）、字符串、数字、布尔、null。在 Power Query 里，前两种对应 Record 和 List，后四种对应 text、number、logical、null。所以"Power BI 导入 JSON"这件事，本质上是把 Record 和 List 摊平到 Table的一个过程，理解了这一点，你就不会在导入环节卡太久。

导入路径一般是三选一：

• 本地文件：File.Contents加上Json.Document，适合一次性分析离线数据。
• Web API：Web.Contents加Json.Document，适合接接口，后面会专门讲这里的一个大坑。
• 手动粘贴：如果只是测试，可以用Json.Document配合文本常量，但这种做法只适合小数据。

这三条路径最后都要落到同一个动作：把嵌套结构展开成平表。展开的过程才是真正的重头戏，我在下一节把完整动作拆给你看。

2. 嵌套 JSON 拆解的完整动作：从 Record 到 Table

我的经验是，绝大多数人卡在 JSON 导入上，不是因为不会写函数，而是没搞懂"展开"是有顺序的。你直接在表格右上角点"展开"按钮，Power Query 会帮你自动生成Table.ExpandRecordColumn或Table.ExpandListColumn，但遇到三层嵌套，自动展开经常把字段搞乱，尤其是同名子字段，展开完你会看到一堆Data.Data.Data列名，根本分不清谁是谁。

所以我建议，到了嵌套层级比较深的时候，手动写展开逻辑，脑子里始终有个三步走的框架：

1. 先定位数组：找到哪一列是 List，那是"纵向"拆分的起点。
2. 再展开对象：对每一行里的 Record 做横向拆分，把子字段变成新列。
3. 重复直到平表：一层层往下，直到所有列都是标量值（text/number/date）。

2.1 一个真实的三级联动案例：从省市区数据说起

热词里有个"省市区三级联动json数据"，这个例子特别适合讲清楚多级嵌套。假设你拿到这样一份 JSON，标准的省市区结构：

[ { "code": "110000", "name": "北京市", "children": [ { "code": "110100", "name": "市辖区", "children": [ {"code": "110101", "name": "东城区"}, {"code": "110102", "name": "西城区"} ] } ] }, { "code": "310000", "name": "上海市", "children": [] } ]

导入 Power Query 后，初始步骤你应该这样做：

let Source = Json.Document(File.Contents("C:\data\region.json")), ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandProvince = Table.ExpandRecordColumn(ToTable, "Column1", {"code", "name", "children"}, {"province_code", "province_name", "children"}) in ExpandProvince

注意这步很关键：Table.ExpandRecordColumn最后一个参数是新列名列表，如果你不写，它会自动用原名，但三个字段里有两个叫code和name，两层一起展开后必然冲突，所以我从一开始就改名为province_code、province_name。这是很多人踩过坑的地方，展开的时候必须顺手重命名，否则后面全乱套。

接着处理 children 列。你点展开按钮，Power Query 会生成Table.ExpandListColumn，它的作用是"把列表里的每个元素变成新的一行"。这一步有个细节：Table.ExpandListColumn展开后，对应行的其他字段会自动复制填充，所以市辖区那层的 province 信息不会丢。

city 层和 district 层的展开逻辑完全一样，我就不重复贴代码了，但给你一个可以直接用的通用思路：每一层展开后立刻检查行数。省这一层有 34 行，市这层可能就几百行，区这层几千行，行数是逐层递增的，如果某次展开后行数没变，说明你的字段名拼错了或者这一层本来就只有一个元素，及早发现能省大量排查时间。

2.2 防御式展开：字段缺失时怎么办

热词里有一个非常扎眼的报错：failed to deserialize the json body into the target type: input: missing fie，它在这里出现，几乎可以肯定是有人在调接口、做解析的时候遇到了JSON 字段缺失。

这种问题的根源在于，JSON 不像数据库表结构那么规整，有的行有children，有的行直接没有这个键，比如上面上海的children是个空数组还好说，但更常见的是有的对象干脆不写这个字段。用Table.ExpandListColumn拆一个不存在的字段，或者 Record 里取一个不存在的 key，就会触发错误。

防御式写法推荐用Record.FieldOrDefault，取不到就返回默认值：

Table.TransformColumns( ExpandProvince, {"children", each try Record.Field(_, "children") otherwise null} )

如果是手动取列，我更习惯这样写：

try Table.ExpandListColumn(PrevStep, "children") otherwise PrevStep

一行代码，取不到 children 就保留原表，后面继续处理。这种写法在接第三方接口时几乎必备，因为你永远猜不到对方的脏数据长什么样。

2.3 List.Transform 处理更深层的结构

三层之内的嵌套，用Table.ExpandListColumn加Table.ExpandRecordColumn就够了。但超过三层，或者你需要在展开前做筛选、排序，建议直接上List.Transform+Table.FromRecords的组合。

举个例子，如果你只想保留有 children 的省份，并且把每个省份的 children 转成独立表，可以这样：

let Source = Json.Document(File.Contents("C:\data\region.json")), Filtered = List.Select(Source, each Record.FieldCount(_) > 1), ToRecords = List.Transform(Filtered, each [ code = Record.Field(_, "code"), name = Record.Field(_, "name"), children = try Record.Field(_, "children") otherwise {} ]), ToTable = Table.FromRecords(ToRecords) in ToTable

Record.Field和Record.FieldOrDefault的区别要搞清楚：前者取不到直接报错，后者返回你给定的默认值。在数据清洗场景里，默认值永远是更安全的选择，宁可后面发现多了空行，也比一遇到脏数据就整表失败强。

3. 高频报错 "failed to deserialize the json body" 的根因排查

这个报错是热词里的重头戏，也是我在实际接 API 时被折磨最久的一个问题。它不是你 Power Query 写错了，而是你在用Web.Contents请求接口时，对方服务端返回的不是合法 JSON，或者返回的 JSON 缺少你请求中期望的字段，服务端反序列化失败后把错误信息原样返回，Power BI 再尝试解析这坨错误信息，于是报出这串英文。

我复盘了手头遇到的所有案例，基本可以归成三类：

3.1 类别一：请求头缺少 Content-Type

很多接口要求请求必须带Content-Type: application/json，你把 Web.Contents 的请求头发过去，默认可能不带或带的是表单格式。如果你调用的是 POST 接口，传 body 的时候务必显式声明：

let Body = Json.FromValue([name = "张三", age = 18]), Source = Web.Contents( "https://api.example.com/user", [ Headers = [ #"Content-Type" = "application/json" ], Content = Text.FromBinary(Body) ] ), Json = Json.Document(Source) in Json

Content-Type这个坑最隐蔽的地方在于：有的接口不校验请求头，能正常返回；有的接口严格校验，缺了就直接报错。同样的代码换个接口就挂，很多新手会以为是 Power BI 的问题，其实对方网关直接就给你驳回了。

3.2 类别二：接口返回的不是纯 JSON

有些接口在 JSON 前面或后面带着 BOM 头、注释字符，或者干脆返回的是 HTML 错误页。你用浏览器打开接口地址，看到的内容漂漂亮亮，但Json.Document一解析就报错。这种情况我建议先做一步"净化"：

let Raw = Web.Contents("https://api.example.com/data"), Text = Text.FromBinary(Raw), Trimmed = Text.Trim(Text), Json = try Json.Document(Trimmed) otherwise error "接口返回不是纯 JSON，请检查是否有BOM或HTML包裹" in Json

Text.Trim能去掉首尾不可见字符，很多 BOM 问题在这一步就解决了。如果还不行，把Text变量导出到表格里看一眼前几十个字符，基本就能判断对方到底返回了什么。

3.3 类别三：请求方字段名与服务端不匹配

这就是报错里missing field字面意思的真正场景。有些接口对请求体里的字段有强校验，比如必须传user_id，你传了userId，服务端反序列化失败，报错就直接是这串。解决办法简单粗暴：拿接口文档逐字对照字段名，尤其是下划线开头或带版本号的字段，一个都不能差。

我专门做了一个排查清单，每次遇到这个报错就照着走：

这套清单我贴在团队内部共享后，新人排查这类报错的时间从半天缩到了半小时，核心原因是不再瞎猜，而是按顺序排除。

4. 从 Power BI 导回 JSON：反向操作的三条路径

前面讲的都是"JSON 进 Power BI"，但实际工作中经常需要反向操作：把 Power BI 里的表导出成 JSON，供下游系统使用。Power BI Desktop 本身没有一键导 JSON 的按钮，但有三条成熟路径，我按推荐程度排序讲。

4.1 路径一：Power Query 里用 M 函数拼 JSON

如果你只是想把某张查询表转成 JSON 字符串，直接在 Power Query 里就能搞定。核心思路是：表 → 记录列表 →Json.FromValue→ 文本。

let Source = YourTable, Records = Table.ToRecords(Source), JsonBinary = Json.FromValue(Records), JsonText = Text.FromBinary(JsonBinary) in JsonText

然后你可以把这个单格值加载到 Excel 工作表，也可以直接作为查询输出。缺点是数据量大的时候性能一般，几万行以内的数据没问题，再大就会卡。优点是零成本，不依赖任何外部工具，适合临时用。

4.2 路径二：Python/R 脚本做复杂导出

数据量大或者结构复杂的时候，我建议走 Python 脚本。Power BI Desktop 里启用 Python 脚本数据源，可以直接读取当前模型数据，做任意加工后导出 JSON 文件。

import pandas as pd import json # 假设 df 是当前数据 # 处理日期等不可序列化类型 def default_handler(obj): if hasattr(obj, 'isoformat'): return obj.isoformat() raise TypeError(f'Object of type {type(obj)} is not JSON serializable') result = df.to_dict(orient='records') with open(r'C:\data\export.json', 'w', encoding='utf-8') as f: json.dump(result, f, ensure_ascii=False, default=default_handler, indent=2)

注意热词里那个TypeError: object of type set is not json serializable就是典型的 Python 导出问题。pandas 读出来某些列是 set 类型，json.dump直接炸，必须统一转 list：

def default_handler(obj): if isinstance(obj, set): return list(obj) if hasattr(obj, 'isoformat'): return obj.isoformat() raise TypeError(f'Object of type {type(obj)} is not JSON serializable')

4.3 路径三：XMLA 端点加 PowerShell，走正经的工程化路子

Power BI Premium 或 Shared 容量支持 XMLA 端点，你可以用 PowerShell 执行 DAX 查询，拿到结果后转 JSON。这是最接近"自动化导出"的方案，适合定时任务或集成到 CI/CD。

# 简化示例：通过 XMLA 查询导出 $query = 'EVALUATE YourTable' # 使用 ADOMD.NET 执行查询 # 结果 DataTable 转 JSON

这条路配置起来有一定门槛，需要服务端开启 XMLA 只读或读写端点，但一旦跑通，就能脱离 Desktop 做定时导出了。我自己的项目里，最后就是用 Azure Automation 定时跑这个脚本，每天把报表数据导出成 JSON 供业务系统拉取。

三条路径的选择标准，我的经验是这样：临时手工导出选路径一，数据量大且有 Python 环境选路径二，需要稳定定时导出选路径三。别一上来就搞 XMLA，先把简单方案跑通，再按需升级。

5. 实战复盘：一次完整的外部 API 到 Power BI 的 JSON 对接

讲完基础，我完整复盘一次真实项目，让你看看整条链路是怎么串起来的。项目背景很简单：客户有一个会员系统，提供 JSON 接口返回消费记录，需要每天导入 Power BI 做分析。

5.1 第一步：先看接口返回结构，再动手写代码

我拿到接口文档后，第一件事不是在 Power BI 里写查询，而是用 Postman 先调一次接口，把返回的 JSON 存成文件，用格式化工具看结构。这个习惯帮我避了至少一半的坑，因为文档写的字段名和实际返回经常对不上。

我当时看到的返回结构大致是：

{ "code": 200, "message": "success", "data": { "total": 3, "list": [ { "order_id": "A001", "user": {"id": 1001, "name": "张三"}, "items": [{"sku": "P01", "qty": 2}, {"sku": "P02", "qty": 1}], "pay_time": "2025-06-01 12:30:00" } ] } }

这就是典型的三层嵌套：最外层是响应包装，中间是列表，列表里的对象又嵌套对象和数组。我在纸上画了一下层级关系，确认了拆解路径：data→list→ 展开user和items。

5.2 第二步：用分步查询把拆解逻辑拆成可读的模块

这里我要说一个非常有用的习惯：别把整个处理流程写在一个 let 里，而是拆成多个查询，每个查询负责一个层级的清洗，这样后续排查问题只需要定位到具体查询，不用在一坨代码里找。

我的做法是在查询列表里建三个查询：

• Raw_API：负责请求和解析，输出data字段。
• Orders：从list转表，展开user字段，保留order_id和pay_time。
• OrderItems：在Orders基础上展开items，形成明细行。

Raw_API的核心代码：

let Source = Web.Contents( "https://api.example.com/member/orders", [ Headers = [ #"Authorization" = "Bearer " & Credential, #"Content-Type" = "application/json" ] ] ), Json = Json.Document(Source), Data = try Record.Field(Json, "data") otherwise error "接口返回异常: " & Text.FromBinary(Source) in Data

注意这里的try ... otherwise error，如果对方接口挂掉返回错误信息，我会把原始报文连同报错一起抛出来，后面在 Power Query 的"数据源设置"里就能直接看到原因，排查效率极高。

Orders查询里最值得讲的是pay_time的处理。JSON 里它是字符串，直接DateTime.FromText转类型，但有时候对方会返回带时区的格式2025-06-01T12:30:00Z，直接转会报错。稳妥写法是：

try DateTime.FromText([pay_time]) otherwise null

5.3 第三步：应对"日期字段被拆成两列"的杂音

处理 JSON 展开时，Power Query 有时候会把日期字段自动识别成两个列（日期和时间各一列），这是因为展开的时候它按你的默认类型设置做了拆分。我遇到过好多次，每次都要手动合并回去。原因其实很简单：Power Query 的Table.ExpandRecordColumn展开的字段如果是 datetime 类型，它会保留成一个字段；但如果原始 JSON 里字符串带空格和冒号，它可能被识别成text，就不会拆。真正拆的场景是出现在"自动生成的列类型转换"环节。

解决方案是在展开后统一加一步类型转换：

Table.TransformColumnTypes(PrevStep, {{"pay_time", type datetime}})

这一步放在所有展开完成之后做，一次搞定。如果你的源数据里有的行是空字符串、有的是标准时间，建议先做清洗，把空字符串替换成 null，再做类型转换，否则整列转换会报错。

5.4 第四步：刷新参数的动态化

客户要我做成每天自动刷新的报表，所以接口地址不能写死。我把接口地址、Token 都做到参数里，连接器用"参数化 Web.Contents"的方式引用。Power BI 服务里配置数据集刷新时，只需要更新数据源凭据就能生效，改地址也不用重新发布。

let Source = Web.Contents( ApiBaseUrl & "/member/orders", [Headers = [ #"Authorization" = "Bearer " & ApiToken ]] ) in Source

ApiBaseUrl和ApiToken在"管理参数"里配置，类型分别为文本和凭据。用凭据类型的好处是 Power BI 服务会以安全方式存储，不会明文暴露在报表里。这个细节，做企业级对接的时候非常重要。

6. 工程化避坑要点：从字段缺失到性能失控

最后这部分，我想把实战中反复踩到、且常规文档很少提的坑一次性讲透。这些点不解决，你前面写再多正确的代码都会被一个脏数据打回原形。

6.1 字段缺失的"隐性连锁反应"

第一节提过missing field，这里展开讲它带来的连锁反应。假设你的items数组在 1000 行数据里只出现了 990 次，有 10 行没有这个字段。你用Table.ExpandListColumn展开时，Power Query 的行为是：缺失的行会变成 null，但展开后这些行会整体消失吗？不会，它们会保留，只是对应的展开列为 null。问题是，你后续如果对这列做求和、转类型，就会有新报错，或者更隐蔽——生成图表时这几行直接不显示，导致数字对不上。

我的建议是展开后立刻加一步统计检查：

Table.TransformColumns( ExpandedTable, {"items", each try List.Count(_) otherwise 0} )

先用List.Count或者说Table.RowCount这类方式确认缺失比例，再决定是补默认值还是过滤。大数据分析里，脏数据不可怕，可怕的是脏数据悄悄改变你的统计口径。

6.2 性能问题：小心 Web.Contents 的重复调用

Power Query 有个特点：每一步都可能是惰性求值，但某些操作会触发重复的数据源调用。你写了一个Web.Contents，然后在后续步骤里筛选、排序、分组，如果某些筛选需要先加载全部数据，Power Query 可能反复请求接口。对方接口如果有频率限制，你就会被封 IP。

解决思路是：在导入阶段把数据完整落盘到本地缓存，然后基于缓存做后续处理。具体做法是先建一个"原始数据"查询，这个查询不做过多的转换，只做Json.Document和Table.FromRecords，然后在后续查询里引用它，而不是每次从 API 拉。这样刷新时，API 只被调用一次，后续所有逻辑都在内存里跑。

另一个性能大坑是Json.Document处理超大文件。几 MB 的 JSON 没问题，但如果你遇到几十 MB 甚至上百 MB 的接口返回，Power Query 会卡到怀疑人生。这时候建议分页拉取，或者在上游服务端先做字段裁剪，只保留分析需要的字段，别把整个 payload 都拉进来。

6.3 关于 JSON 格式化工具的使用建议

热词里很多关于"json格式化工具"的搜索，我也顺便说几句。解析 JSON 之前，找一个顺手的格式化工具离线版备用是值得的，因为接口返回的网络数据经常是一行压缩的，肉眼看不出结构。我的习惯是先把原始文本存成 .json 文件，用带语法高亮的编辑器打开，折叠层级看清结构，再回到 Power Query 里动手。这个"看清再动手"的动作，能省下大把试错时间。

至于在线格式化工具，我不太建议把敏感的接口数据贴到网页上，尤其是有 Token 或用户信息的返回值，泄露风险远大于便利性。本地离线工具或 VS Code 自带格式化就够用了。

6.4 日期、时区、空值三个细节

• 日期格式：尽量在 M 查询里统一转成datetime，不要在 DAX 里反复转字符串。JSON 解析时时间字段经常是字符串，早转早省心。
• 时区：接口返回带Z结尾的 UTC 时间，要明确你的报表时区基准，最好在 M 查询里用DateTime.AddZone和DateTimeZone.ToLocal统一校准，别留着在 DAX 里处理。
• 空值：JSON 里null和缺失字段在 Power Query 里都显示为 null，但语义不同。用Record.HasFields判断字段是否存在，可以区分这两种情况，这在数据质量分析里很有用。

7. 一个真正能救命的 M 函数封装

我知道看到这里，你可能觉得内容多、记不住。所以我最后分享一个我项目里一直在用的函数封装，它把"一个 JSON 数组转成 Power Query 表"的动作简化成了一行调用。你把它放到 Power Query 的"新建查询 → 空白查询"里，命名为fn.JsonArrayToTable：

(JsonText as text, optional ColumnNames as list) => let Parsed = try Json.Document(JsonText) otherwise error "JSON 解析失败，请检查原始文本", Records = if Type.Is(Value.Type(Parsed), List.Type) then Parsed else {Parsed}, Table = Table.FromRecords(Records), Typed = if ColumnNames = null then Table else Table.TransformColumnTypes(Table, List.Transform(ColumnNames, each {_, type any})) in Typed

这个函数做的事情很简单：输入 JSON 文本，自动判断是数组还是单个对象，统一转成表，可选地对指定列做类型转换。你可以在任意查询里这样调用：

fn.JsonArrayToTable(JsonText, {{"order_id", type text}, {"amount", type number}})

别小看这个封装，它至少帮我省了上百行重复代码。每次接到新接口，我只需要构建参数列表，剩下的脏活累活统一走这个函数，遇到解析失败还能直接看到错误提示，不用一层层去翻 Query 的步骤。

顺手再说一个编码问题：如果你从 Web 接口读到的 JSON 里有中文乱码，大概率是响应没按 UTF-8 解码。你可以用Text.FromBinary(Raw, Encoding.UTF8)强制指定编码，或者直接在Web.Contents里加一个[Encoding = Encoding.UTF8]参数，这个细节在对接国内接口时特别常见。

我在实际项目里发现，只要把上面这套"导入-拆解-排查-导出"的流程想清楚，Power BI 处理 JSON 真的没那么玄乎。它不像 Python 写爬虫那样灵活，但胜在可视化刷新、能对接服务端调度。如果你准备接的第一个接口正好是省市区那种多级嵌套结构，建议你按第二节的步骤完整走一遍，把展开菜单和手写代码都试一次，体会一下两者的差异。后面遇到再复杂的结构，心里就有底了。