本帖最后由 一江春水1688 于 2023-11-14 13:06 编辑
研究了Table.FromRecords的容错参数,完美解决出错问题
代码如下:
- let
- url="https://fx.cmbchina.com/api/v1/fx/history-rate/page",
- page = 1,
- headers=[#"Content-Type"="application/json;charset=UTF-8"],
- //2009-01-01至2050-11-12,一次性取1万条数据,这样就不用分页了,实际返回没有这么多。
- content="{""nbr"":""港币"",
- ""startDate"":""2009-01-01"",
- ""endDate"":""2050-11-12"",
- ""pageSize"":""10000"",
- ""pageNum"":" & Text.From(page) & "}",
- web=Text.FromBinary(Web.Contents(url,[Headers=headers,Content=Text.ToBinary(content)])),
- //加了个容错参数 MissingField.UseNull,完美解决出错问题
- data = Table.FromRecords(Json.Document(web)[body][list],{"ratDat", "rthBid", "rtcBid", "rthOfr", "rtcOfr"},MissingField.UseNull),
- 重命名的列 = Table.RenameColumns(data,{{"ratDat", "日期"}, {"rthBid", "汇买价"}, {"rtcBid", "钞买价"}, {"rthOfr", "汇卖价"}, {"rtcOfr", "钞卖价"}})
- in
- 重命名的列
复制代码
|