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 APIWeb.Contents加Json.Document适合接接口后面会专门讲这里的一个大坑。手动粘贴如果只是测试可以用Json.Document配合文本常量但这种做法只适合小数据。这三条路径最后都要落到同一个动作把嵌套结构展开成平表。展开的过程才是真正的重头戏我在下一节把完整动作拆给你看。2. 嵌套 JSON 拆解的完整动作从 Record 到 Table我的经验是绝大多数人卡在 JSON 导入上不是因为不会写函数而是没搞懂展开是有顺序的。你直接在表格右上角点展开按钮Power Query 会帮你自动生成Table.ExpandRecordColumn或Table.ExpandListColumn但遇到三层嵌套自动展开经常把字段搞乱尤其是同名子字段展开完你会看到一堆Data.Data.Data列名根本分不清谁是谁。所以我建议到了嵌套层级比较深的时候手动写展开逻辑脑子里始终有个三步走的框架先定位数组找到哪一列是 List那是纵向拆分的起点。再展开对象对每一行里的 Record 做横向拆分把子字段变成新列。重复直到平表一层层往下直到所有列都是标量值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.TransformTable.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 ToTableRecord.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 JsonContent-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 JsonText.Trim能去掉首尾不可见字符很多 BOM 问题在这一步就解决了。如果还不行把Text变量导出到表格里看一眼前几十个字符基本就能判断对方到底返回了什么。3.3 类别三请求方字段名与服务端不匹配这就是报错里missing field字面意思的真正场景。有些接口对请求体里的字段有强校验比如必须传user_id你传了userId服务端反序列化失败报错就直接是这串。解决办法简单粗暴拿接口文档逐字对照字段名尤其是下划线开头或带版本号的字段一个都不能差。我专门做了一个排查清单每次遇到这个报错就照着走检查项操作说明请求头确认是否有Content-Type: application/json严格接口缺了必报错返回体用文本方式查看返回内容确认不是 HTML 错误页字段名对照接口文档逐一核对注意user_id和userId区别必填字段确认所有必填字段都有值空值要用Json.FromValue正确处理参数编码中文参数需要 URL 编码用Uri.EscapeDataString这套清单我贴在团队内部共享后新人排查这类报错的时间从半天缩到了半小时核心原因是不再瞎猜而是按顺序排除。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(fObject of type {type(obj)} is not JSON serializable) result df.to_dict(orientrecords) with open(rC:\data\export.json, w, encodingutf-8) as f: json.dump(result, f, ensure_asciiFalse, defaultdefault_handler, indent2)注意热词里那个TypeError: object of type set is not json serializable就是典型的 Python 导出问题。pandas 读出来某些列是 set 类型json.dump直接炸必须统一转 listdef default_handler(obj): if isinstance(obj, set): return list(obj) if hasattr(obj, isoformat): return obj.isoformat() raise TypeError(fObject 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 null5.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 SourceApiBaseUrl和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 写爬虫那样灵活但胜在可视化刷新、能对接服务端调度。如果你准备接的第一个接口正好是省市区那种多级嵌套结构建议你按第二节的步骤完整走一遍把展开菜单和手写代码都试一次体会一下两者的差异。后面遇到再复杂的结构心里就有底了。