什么是PowerQuery – 简单得不像超级查询实力派 | Excel120

闲读古书 2018-05-11

展开全文

概述

本文将全面而详细的介绍一种微软为了【自助大数据分析】而推出的工具，它并不是刚刚推出，只是可能很多人还不知道。

首先说明一点：PowerQuery是以查询为主题而存在的。任何非以此主题为前提，用其他技术与之的对比都不合适。

如果你已经是轻松玩转VBA的”EXCEL高手”，这个工具将是一个极好的补充，可以替代大量编码时间，另外如果投资时间学习M语言（PowerQuery内置编程语言）将推开一个新的大门。
如果你是Excel初学者，需要向Excel导入数据而苦思怎么办，那PowerQuery正是你的最佳选择，没有之一。
如果你经常使用Excel做数据统计汇总，你已经一只脚跨入了【自助大数据分析】的大门，用对工具，学好理论，你也能分析百万甚至过亿的数据，PowerQuery是必备工具。

帮助微软打广告对我一点也没有好处，甚至恨微软没有在中国部署更符合中国国情的服务器，但看到几十个QQ群中伙伴的讨论都在绕弯路，还是有必要分享一个值得大家了解的工具：PowerQuery。

本文不是一份PowerQuery的教程，可能不会给出关于如何合并N个部门数据的详细教程，也不会详细描述PowerQuery的语法。

文本将说明PowerQuery可以干什么，善于干什么，在做这些事情上，它最大的优势在于什么，如果精通PowerQuery可以带来什么，并附有一些案例。

另外，PowerQuery是微软推出的一整套Power工具-系列的一个组成成员。这里有必要再次强调一下整个系列，包括：

PowerQuery – 定位：查询。记住它的中文翻译即可：超级查询。凡是要查询数据（各个地方各种格式），只要此工具即可。

PowerPivot – 定位：透视。对PowerPivot有所了解的人，可能不一定赞同这个描述，那是因为您到了见山不是山的阶段。记住它的中文翻译即可：超级透视。透视，是一个比较复杂的概念，需要单开文章详细说明。通俗地说，要了解一件复杂事情的本质，需要拨开迷雾，从多个角度来看它，所谓穿越迷雾，多维度观察。PowerPivot及相关技术相对复杂，见相关文章深入。

PowerBIDestop – 定位：洞察。PowerBIDestop/PowerBI 是为了能通过快速的图形拖拽，从图表的可视角度，观察出事物的规律，进而才知道要定制什么样的报表来监测什么样的变化。（如果你用过PowerView，这里并不做介绍，是因为PowerView的一切功能已经内含在PowerBIDestop中了，换句话说PowerView被微软淘汰了，当然还能用。）

PowerMap – 定位：地图。如果你以为地图就是中国各地区”热力图”（销售额区域分布），那你就太小看这件事了。凡是以二维空间作为基础面的，都可以用地图来做展示，比如：一个公司员工的场地工位，标注一个员工的绩效、成本、性格等，一个几千人的企业，形成一个3D版的员工价值地图，你想象出了吗。

PowerPoint – 定位：演说。你可能会觉得这个工具不用做介绍，太普遍了。的确如此。关于如何用好PowerPiont有大量牛人和牛逼教程。我还是要告诉你95%的教程包括牛人的教程都讲了用PowerPiont制作作品的方法和绝妙技巧，而关于这背后的本质（懂了本质，就能灵活应变，技巧是短暂的，本质是真理的，是不变的），甚少有介绍。举个例子：如何以基本不变的套路对一个给定时间给定主题的演说用最小的时间制作出从可用级到极致级的PPT。

欢迎加入QQ群Excel120进一步探讨。

成为Power – X大师：

整观Power系列，给定一个主题，用PowerQuery系统地获取百万到亿级别的数据；使用PowerBIDestop建模并洞察其中奥秘，记录这些规律；并使用PowerPivot按诸多方面定期透视它们的变化以跟踪监测；并利用Excel作为呈现区域，定制仪表盘或KPI，呈现透视结果。如有必要，对它们有选择地使用VBA做自动化处理（如定期发邮件）；对于适当的地方，可以用PowerMap做3D可视化展示。当有必要的时候，基于这些事实，利用PowerPiont制作一份极致的PPT，做一场震撼全场的大数据演讲。

为了说明一件事是合理的，往往必须在更大的集合中来描述这件事，您已经看到这里啰嗦地说了一大堆和PowerQuery无关的东西，但作为一个大的整体，也许你已经意识到了这个整体做到了1+1+1+1 > 100 的感觉是什么样的。

一些说明

如果你正在学习Excel如何获取并分析数据，那不管你在社区看到技术方案，都将不再是主流方式。这些非主流方式包括但不限于：

用SQL从其他数据源获取数据，因此告诫你需要学习SQL。
用VBA从其他文件读取数据，因此告诫你VBA是Excel中的高级修炼阶段。
用ODBC方式连接远程数据库的。
用称为Microsoft Query的工具来做查询的。

这么说的原因是微软早也意识到这个问题，所以在这方面早有规划，推出了PowerQuery。至于为什么这么做，你可以再阅读一遍本文概述，作为一个Power系列，必须有一种工具能够和其他Power工具在一个量级来提供查询支持。

PowerQuery到底干什么

回到”PowerQuery是什么？”的正题。回答是：PowerQuery是在Excel平台控制及转换数据的最佳工具。（没有之一）不论你是何种Excel用户，你可能经常做如下的事务：

数据获取：从不同来源，不同结构，不同形式获取数据并按统一格式进行横向合并，纵向（追加）合并，条件合并等。

数据转换：将原始数据转换成期望的结构或格式。

数据处理：为了后续分析的需要进行数据预处理，例如：加入新列、新行、处理某些单元格值。

使用场景

为了形象的说明使用PowerQuery的场景，如下所示：

作为一个Excel用户，你是如何完成”将数据转换到”的任务的呢。回想一下，你会不会如此陷入如此困境，如下所示：

揭开庐山面目

PowerQuery到底长啥样，有图有真相。

PowerQuery是一个微软创建的基于Excel的免费插件，可以在Excel2010（及更高版本）使用。在Excel2016及PowerBIDestop中已经内置。

如果你对数据建模有一定理论基础，那你应该已经发现PowerQuery实际上完成了ETL的工作。也就是它可以从几乎任何数据源获取数据并通过内置的运算做数据转换最终加载进入Excel或数据模型。

它在不同的软件中如下所示：

使用PowerQuery的样子

使用PowerQuery可以从这些地方获取数据，如下所示：

获取了数据以后，只需要点击鼠标就能完成的功能包括但不限于以下这些：

不用举例，你便已经知道，你的需求通过PowerQuery都能满足。只是可能你还不会用而已。

学习PowerQuery有多难

自然会到这个问题。让我们用一幅图来说明PowerQuery的学习难度曲线。如下所示：

不必太惊讶，正如你看到的，学习PowerQuery所获得的收益比学习Excel函数或VBA要更有性价比。也就是说你并不需要花费学习诸如VLOOKUP, INDEX, MATCH, OFFSET, INDIRECT之类的函数或是VBA，而仅仅使用PowerQuery就能实现同样甚至更复杂的应用。事实上，你只是需要点点鼠标就可以完成大部分操作，而不必触碰任何代码。

在哪里学习PowerQuery

如果你至此已经被”洗脑”，决定体验和开始学习PowerQuery之旅，那你一定会问在哪里学PowerQuery，教程，视频。

很简单，这些问题，搜索引擎是最好不过的回答者。

要提醒您的是：目前（截止2015.10）在国内还没发现任何系统化学习PowerQuery的教程，但有些爱好者有些文章做了介绍，这些也完全可以作为入门教程参考。

好了，不要犹豫，直接去搜索引擎吧。

另外，笔者所在的QQ群Excel120也共享了一些文件，可供学习，欢迎加入交流。

PowerQuery水平分级

如果你已经开始使用了PowerQuery，可能你会像好奇Excel一样好奇，究竟PowerQuery如何划分水平等级的，那可以参考一下如下：

可以看出，使用PowerQuery图形界面就足以完成80%的日常任务，这些甚至不需要太多的学习时间。因此PowerQuery是一种上手极快的工具。

但你仍然会发现，在高级阶段是需要自己编写公式和代码的，一种称之为M代码的语言，这也说明PowerQuery不是一个无价值的小玩具，它兼顾了入门和功能强大。

这正如同使用Windows，你完全可以通过使用鼠标操作大部分Windows功能，但会编程的话你可以使用无限的操作系统功能。这充分做到了在入门期间，它将让你感受到它的容易和简单并帮助你立刻解决你的问题，你会立即意识到这是个好东西，这是一开始PowerQuery对你的馈赠。它同时为你打开了一扇门，接着如果你想完全驾驭它，并用它在数据界驰骋，那你就必须投入时间去了解它的本质原理，语言规范以及功能库，这是一个你对它投入的阶段，一旦过了这个坎，它便成了她，将陪你遨游数据界，畅所无阻，你也才能真正欣赏到她的独特魅力。