龙空技术网

Power Query中从JSON中提取数据变成表格形式的过程

EXCEL数据处理与分析 263

前言:

现在大家对“cjquerypostjson”大约比较关心,朋友们都想要知道一些“cjquerypostjson”的相关知识。那么小编同时在网摘上网罗了一些对于“cjquerypostjson””的相关知识,希望我们能喜欢,你们快快来了解一下吧!

JSON格式数据是非常流行的一种数据存储格式,很多网站都采用JSON格式来传递数据,我们之前讲过用谷歌浏览器就可以抓到JSON格式的数据包,然后就可以放到Power Query中来解析。JSON格式的数据是树形结构,与我们通常的表格结构有所不同,这个转换过程令很多初学者束手无策。今天我们就来说说,如何进行这个转换。

谷歌浏览器中,NetWork标签下的Response里的就是完整的JSON格式数据:

PreView下面就是这个JSON数据的预览:

通过这个预览,我们就能知道:

datanodes:对应的是数据wdnodes:对应的就是数据标签、或者数据对照表,这里面有国家代码对照、年份列表等等

这个数据的结构,即使不在谷歌浏览器中查看,通过Power Query来查看也是一样的:

因为Power Query中的所有操作是可以退回的,所以不用担心做错了,或者损坏数据源。

我们可以一层一层的打开,来观察这个数据的结构,又或者你希望保留其中一步的查看过程,可以这样来做,把数据源命名为JS,然后新建空查询引用JS:

然后我们来点击Record进入下一层:

wdnodes

这时候就出现了两个分支:datanodes、wdnodes,两个列表,我们先看其中的一个列表wdnodes:

列表中又有三个Record,分别对应的是:

指标:地区:时间:

可以点记录所在的行查看预览,记录中又包含有列表,我们再继续向下探索,选地区的记录:

进入这个List之后,我们可以查看到最终的数据了:

应该是每个国家一个记录,中间有国家的代码,对应的中文的国家的名称。

datanodes

我们再建立一个新的查询,查看datanodes数据结构:

这个记录列表有很多行,就代表了数据一共有这么多行,打开其中一个记录:

data对应的就是具体数据。wds其实对应的三个记录看起来是不是有些面熟,就是对应wdnodes的记录表格,只不过wds中只有代码,没有中文名称。

打开以后是这个样子的,地区代码是101:

到这里我们对整个JSON数据的结构有个大概的了解,接下来就是如何提取我们想要的数据:

我们可以分步来做,地区列表、数据

地区列表:

我们回到wdnodes查询,按这个到表的按钮:

然后展开表,我们不要那么多没用的数据,选其中两列就可以了:

这样地区列表就出来了:

数据

其实数据自带时间,不需要单独做时间表:

datanodes查询中我们从头开始,进行到data的记录列表这里,同样转换到表格:

展开记录:

其实大家如果仔细观察就会发现code这一列就包含有地区代码以及年份,但是我们为了演示从wds中获取地区代码以及年份,所以展开时保留了wds列。

展开data就选择data列就可以了

然后我们复制wds列,因为我们要取地区代码与年份:

选中其中一列,按展开的按钮,选提取值,中间会提示分隔符,确定就可以了,我们看到的结果是整列的错误,这个不要紧,我们还要做公式上的调整:

我们为了顺利改写公式要回去看一下时间和地区记录的内容:

记录的名称都是valuecode,

地区在wds列表的第2行的位置,用Power Query的列表引用就是{1}valuecode时间在wds列表的第3行的位置,用Power Query的列表引用就是{2}valuecode

我们根据这个名称来修改公式:

= Table.TransformColumns(复制的列, {"wds", each

Text.Combine(

List.Transform({_{2}[valuecode]}, Text.From)), type text})

粗体字部分就是修改的内容,同样我们再把地区代码提取出来:

最后我们稍作整理,第一列可以不要了,改一下列的名称和数据类型:

合并查询

用地区代码把中文名称查询过来:

展开删除地区代码,这个数据就整理好了。

今天用这么长的篇幅来写这个JSON数据整理的目的,就是为了然大家了解JSON结构到表格结构的转换,当然根据不同的JSON的数据,可以有不同的处理方法,今天这个是一列数据的提取,如果是多列数据的提取,加上多列标题的匹配应该怎么处理呢?

可以使用Table.FromRows函数,把数据、标题分别提取成列表结构,作为Table.FromRows函数的参数就可以处理好。

我们下一回再来处理这个多列数据的提取。

标签: #cjquerypostjson