将JSON数组导入到Excel中
我正在尝试将带有以下格式的JSON文件导入Excel:
[
[1,2,3,4],
[5,6,7,8]
]
我想获得一个带有2行和4列的电子表格,其中每一行包含内部数组的内容作为单独的列值,例如
A | 列B列 | B列C | 列D |
---|---|---|---|
1 | 2 | 3 | 4 |
5 | 6 | 7 | 8 |
尽管这似乎是一个容易解决的问题,但我似乎找不到正确的PowerQuery语法,或者找到涵盖此情况的现有答案。我可以轻松地作为具有8个值的单列导入,但似乎无法将内部数组分为单独的列。
I'm trying to import a JSON file with the following format into Excel:
[
[1,2,3,4],
[5,6,7,8]
]
I want to get a spreadsheet with 2 rows and 4 columns, where each row contains the contents of the inner array as separate column values, e.g.
Column A | Column B | Column C | Column D |
---|---|---|---|
1 | 2 | 3 | 4 |
5 | 6 | 7 | 8 |
Although this would seem to be an easy problem to solve, I can't seem to find the right PowerQuery syntax, or locate an existing answer that covers this scenario. I can easily import as a single column with 8 values, but can't seem to split the inner array into separate columns.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
假设JSON
在PowerQuery中看起来像是此代码
生成
它将JSON转换为列表列表,转换为列表表,将列表扩展为使用逗号的文本,然后将其扩展到列中通过计算逗号的最大数量,在动态创建新列的列名之后
Assuming the JSON looks like
then this code in Powerquery
generates
It converts the JSON to a list of lists, converts to a table of lists, expands the list to be text with commas, then expands that into columns after dynamically creating column names for the new columns by counting the max number of commas