杰森使用电源查询来excel [英] Json to excel using power query
问题描述
我在网站上有一些json
,我想使用power query
选项from web
转换为excel
.但是我遇到了一个小问题.我的json
看起来像这样:
I have some json
on a website that i want to convert to excel
using the power query
option from web
. But I ran into a small problem. My json
looks like this:
[
{
"id" : 1,
"visitors" : 26,
"some_number" : 1,
"value" : 3500
},
{
"id" : 2,
"visitors" : 21,
"some_number" : 5,
"value" : 2000
}
]
但是当我使用from web
时,我得到了:
but when i use from web
i get this:
我可以向下钻取一条记录,将其转换为表,转置并使用第一行作为标题,但随后我只得到一行.我如何才能将我的所有数据而不只是一行数据获取到表中?
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?
推荐答案
首先,我将使用列表工具/转换"菜单(应自动选择它),然后单击到表格按钮.这将为您提供一个包含2行的单列表.然后,我将单击小的扩展"按钮-它会出现在"Column1"右侧的列标题中.取消选中使用原始列名... 选项,您将获得一个包含4列2行的表格.
First I would use the List Tools / Transform menu (it should be automatically selected) and click the To Table button. 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.
这是我生成的完整脚本:
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"
这篇关于杰森使用电源查询来excel的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!