23、Power Query-XML与JSON数据获取

首先扫盲:

JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。它基于JavaScript的一个子集;

XML(Extensible Markup Language)即可扩展标记语言,Xml是Internet环境中跨平台的,依赖于内容的技术,是当前处理结构化文档信息的有力工具。

两者的共同优点是都是文本表示的数据格式,可以跨平台、跨系统交换数据。

要是还没有看懂这段文字,我们就直接来看代码。

下面是一段简单的Json代码:

{
    "name": "中国",
    "province": [{
        "name": "黑龙江",
        "cities": {
            "city": ["哈尔滨", "大庆"]
        }
    }, {
        "name": "广东",
        "cities": {
            "city": ["广州", "深圳", "珠海"]
        }
    }, {
        "name": "台湾",
        "cities": {
            "city": ["台北", "高雄"]
        }
    }, {
        "name": "新疆",
        "cities": {
            "city": ["乌鲁木齐"]
        }
    }]
}

从代码的角度很明显它是”国家——省份——城市“的表,我们要怎么获取它的数据呢?

下面我们就用Power Query去获取它,依旧是选中——”从表“进行操作。


这个时候我们需要用到的菜单是”转换“——”分析“。

发现它底下就有两个选项:

1、XML

2、JSON

这次我们选择"Json"。


接着我们依次按照”国家——省份——城市“进行展开。


这样我们就获取成功了,我们可以发现,这个JSON代码的结构是相当有规律的,有兴趣的胖友可以去了解学习一下。

说完Json,我们接着聊一下XML代码,这个我们见的就比较多了,我们的Excel的文件就是这样构成的。

比如我们把刚刚做的这个excel改一个扩展名为”rar“,进行解压操作。



我们可以发现,其实每一层,都是由XML文件组成的。

下面我们给出这样一段XML代码。

<!--  Edited by XMLSpy??  -->
<breakfast_menu>
<style type="text/css"/>
<food>
<name>Belgian Waffles</name>
<price>$5.95</price>
<description>
Two of our famous Belgian Waffles with plenty of real maple syrup
</description>
<calories>650</calories>
</food>
<food>
<name>Strawberry Belgian Waffles</name>
<price>$7.95</price>
<description>
Light Belgian waffles covered with strawberries and whipped cream
</description>
<calories>900</calories>
</food>
<food>
<name>Berry-Berry Belgian Waffles</name>
<price>$8.95</price>
<description>
Light Belgian waffles covered with an assortment of fresh berries and whipped cream
</description>
<calories>900</calories>
</food>
<food>
<name>French Toast</name>
<price>$4.50</price>
<description>
Thick slices made from our homemade sourdough bread
</description>
<calories>600</calories>
</food>
<food>
<name>Homestyle Breakfast</name>
<price>$6.95</price>
<description>
Two eggs, bacon or sausage, toast, and our ever-popular hash browns
</description>
<calories>950</calories>
</food>
</breakfast_menu>

下面我们同样直接拉到PQ里面整一下。



分析发现其实我们只要扩展”food“这个表,直接点击”Table“。


下面我们还是通过一个实例来巩固。

从外部XML文件获取数据,进行处理。

”要求:从demo.xml文件中统计上世纪年各年代、国家歌手的数量?“

这个时候我们是通过一个新的方法进行打开:Power Query——从文件——从XML。




很高级,直接全部获取了。

下面我们直接处理我们的需求,根据国家以及年代汇总歌手的数量。

我们最辣手的是处理一下年代,其实很简单,从“YEAR”左边第2个位置开始取一位即可,加上后缀“0年代”。



效果如下:


最后进行“分组依据",根据”国家“——”年代“进行计数,对”年代“进行”透视列“操作。



得到最终结果:



相关内容推荐