![Excel 2016 中的 OData Feed/Power Query 未检索全部/重复某些记录](https://linux22.com/image/1582823/Excel%202016%20%E4%B8%AD%E7%9A%84%20OData%20Feed%2FPower%20Query%20%E6%9C%AA%E6%A3%80%E7%B4%A2%E5%85%A8%E9%83%A8%2F%E9%87%8D%E5%A4%8D%E6%9F%90%E4%BA%9B%E8%AE%B0%E5%BD%95.png)
在 Excel 中,我成功连接到来自 Data.Medicare.gov 的 OData 源(该网站是https://data.medicare.gov/Hospital-Compare/Healthcare-Associated-Infections-Hospital/77hc-ibv8/data终点是https://data.medicare.gov/api/odata/v4/77hc-ibv8)。
然而,现在我仔细审查和分析数据,我发现网站上的一些数据行/记录(https://data.medicare.gov/Hospital-Compare/Patient-survey-HCAHPS-Hospital/dgck-syfz/data) 从我的 Excel 数据中缺失。刷新 Excel 中的数据后,一些之前缺失的行会出现,而其他行会消失。每次刷新时出现或消失的行似乎是随机的。
例如,医院名称 =“Trinitas Regional Medical Center”且措施 ID =“HAI-1-SIR”的记录在网站上,但有时会出现,然后在每次刷新时从 Excel 数据表 (__id =“row-6s6r~jx5f.wuje”) 中重新出现。
不确定这是否是由于数据集中的行数过多(>170k)造成的。我找到的唯一相关讨论是https://blog.crossjoin.co.uk/2018/05/03/troubleshooting-data-refresh-performance-issues-with-odata-data-sources-in-power-bi-and-excel-using-fiddler/但不要认为这解决了我的具体问题。
更新 1:
为该网站提供 OData Feed 服务的 Socrata 做出如下回应:
[我们] 能够在 Excel 中重现此行为,但我不确定是什么原因造成的。不过,这似乎不是 OData 源本身的问题,因为我可以通过浏览器持续访问该行(例如,https://data.medicare.gov/api/odata/v4/77hc-ibv8('row-6s6r~jx5f.wuje')),因此这似乎与 Excel 处理数据的方式有关。不幸的是,我在网上找不到太多资料来解释为什么会发生这种情况,因此最好联系 Microsoft 支持部门,看看他们是否能够进一步提供帮助。
更新2:
在与 Microsoft 专业技术支持进行大量故障排除和讨论后,他们(错误地)得出结论,OData 源中存在重复记录。我再次联系了 Socrata 支持,他们考虑到了我的观察,即这种情况只发生在大型数据集中,并提出了解决问题的建议(请参阅下面发布的答案)。
答案1
Socrata 支持发现了这个问题并建议在 OData feed URL 中附加一个 $top 参数,这为我解决了这个问题:
在 Excel 中加载 OData 源时,Excel 会在加载大型数据集时自动在后台对结果进行分页,而此加载过程会导致重复记录。您可以通过向 OData 源 URL 添加一个 $top 参数来解决此问题,该参数的值大于或等于数据集中的总行数,这将强制 Excel 在单个请求中加载所有数据,而不是对结果进行分页。例如,如果您输入https://data.medicare.gov/api/odata/v4/77hc-ibv8?$top=10000000 作为 URL,这将加载所有记录,并且不会有任何重复。
Microsoft Office 365 支持已确认”添加 $top 命令确实可以停止重复。“
更新:
虽然上述 $top 参数最初解决了该问题,但我开始在 Excel 中收到以下错误消息:
无法连接
我们在尝试连接时遇到错误。
详细信息:“Microsoft.Mashup.Engine1.Library.Resources.HttpResource:请求失败:OData 版本:3 和 4,错误:远程服务器返回错误:(500)内部服务器错误。 (发生内部错误。 请联系 Socrata 支持,参考诊断代码 epsfgirt9lxlekyt89oq25x54)OData 版本:4,错误:远程服务器返回错误:(500)内部服务器错误。 (发生内部错误。 请联系 Socrata 支持,参考诊断代码 58cf025wln5br4qzw6lcrxh3a)
因此我联系了 Socrata 支持部门,他们给出了以下回复:
[我们] 遇到了同样的错误。我咨询了我们的工程团队,他们说他们最近确实做了一些更新,这可能导致了这个问题,但也应该修复了潜在的问题。因此,如果您从 Excel 中的 OData URL 中删除“?$top=100000”,然后使用https://data.medicare.gov/api/odata/v4/yv7e-xc69,这应该可以工作,并且不会像您之前看到的那样返回重复记录。我今天已经在几个不同的资产上测试了这个问题,它确实似乎已经修复了……。
我使用了常规 OData 端点(https://data.medicare.gov/api/odata/v4/yv7e-xc69) 并且它加载时没有重复。