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。

选择”CD"表点编辑进入处理。（下面的style是样式表，我们不需要关注。）

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

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

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

效果如下：

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

得到最终结果：