使用电源查询使 Json 出类拔萃
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42060625/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Json to excel using power query
提问by kemis
I have some jsonon a website that i want to convert to excelusing the power queryoption from web. But I ran into a small problem. My jsonlooks like this:
我json在网站上有一些我想excel使用power query选项转换为from web。但是我遇到了一个小问题。我的json看起来像这样：
[
{
"id" : 1,
"visitors" : 26,
"some_number" : 1,
"value" : 3500
},
{
"id" : 2,
"visitors" : 21,
"some_number" : 5,
"value" : 2000
}
]
but when i use from webi get this:
但是当我使用时，from web我得到了这个：
I can drill down into a record,convert it to a table, transpose and use first row as header but then i get just one row. How can i get all of my data to the table and not just one row?
我可以深入到记录中，将其转换为表格，转置并使用第一行作为标题，但随后我只得到一行。我怎样才能把我的所有数据都放到表中，而不仅仅是一行？
回答by Mike Honey
First I would use the List Tools / Transform menu (it should be automatically selected) and click the To Tablebutton. This will give you a single-column table with 2 rows. Then I would click the small Expand button - it will appear in the column headings, just to the right of "Column1". Uncheck the Use original column name ...option and you will get a table of 4 columns and 2 rows.
首先，我将使用 List Tools / Transform 菜单（它应该被自动选中）并单击To Table按钮。这将为您提供一个包含 2 行的单列表。然后我会单击小的展开按钮 - 它会出现在列标题中，就在“Column1”的右侧。取消选中Use original column name ...选项，您将获得一个包含 4 列和 2 行的表格。
Here's the full script I generated:
这是我生成的完整脚本：
let
Source = Json.Document(File.Contents("C:\Users\Mike.Honey\Downloads\json2.json")),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "visitors", "some_number", "value"}, {"id", "visitors", "some_number", "value"})
in
#"Expanded Column2"
回答by Robert K. Bell
The Table.FromRecords()functionis suitable for that sample data:
该Table.FromRecords()函数适用于该样本数据：
let
Source = Json.Document("[{""id"": 1, ""visitors"": 26, ""some_number"": 1, ""value"": 3500}, {""id"": 2, ""visitors"": 21, ""some_number"": 5, ""value"": 2000}]"),
AsTable = Table.FromRecords(Source)
in
AsTable
回答by MarcelBeug
You need to convert the list to a table first, then you can expand the record column and proceed from there. If no luck, then you can take a look at this videoI created recently for a similar question.
您需要先将列表转换为表格，然后您可以展开记录列并从那里继续。如果不走运，那么您可以看一下我最近为类似问题创建的这个视频。
