认识Power Query并了解基本功能

Power Query（以下简称PQ）作为Power BI组件的起始端，承担着数据的加载和清洗职能。但单就PQ而言，独立使用依然非常强大，今天就开始我们BI组件的学习之路，本篇文章先让大家对PQ有个初步的认识和基本功能的讲解，后续随着学习的不断深入会讲解更多实用的功能。由于我在M语言上不是非常擅长，所以更多的会侧重于分享一些通过工具栏按钮和简单的M语句进行操作。仅仅这些已经足够我们在工作上当中使用了。

什么是Power Query

可以查看我之前的文章，Excel、Power BI、Power Query、Power Pivot、M语言、DAX语言之间的关系，有详细的介绍。

那么PQ能做什么？当然是做数据的加载和清洗，但是可能大家对所谓的加载和数据清洗完全没概念，我举几个应用场景的例子。

Power Query的常用功能

多表格合并

使用PQ可以批量合并同一格式的多个文件，这些以前都需要VBA才能完成的工作，现在通过PQ就可以轻松完成。

多表格合并.png

合并查询

效果类似Excel中的VLOOKUP，但是在PQ中可以很简单的实现多条件查找且计算速度非常快。

合并查询.png

逆透视

透视的表格多为结构可以通过PQ被逆透视为二维数据，而通过常规的方法其实不太容易办到。

将左边的效果转换为右侧.png

......

当然，除了我列举的几个之外还有更多好玩的功能，后续会慢慢分享给各位。

Power Query的安装

win7系统

如果是win7系统安装Excel2013是需要下载插件的，Excel本身并没有内置，安装后会增加一个菜单。如果安装2016则Excel内置。无论安装13还是16，如果使用PQ还需要安装IE9以上版本才能正常使用。
下载IE9，简书私信回复“4”获取密码
下载Power Query

win10系统

win10系统安装Excel2013+插件和Excel2016均不需要再安装IE9，可直接打开使用。

Power BI Desktop

如果你是直接使用的桌面版，只需要在微软官网下载即可使用，内部组件是完整的。关于PQ后续的讲解我更多的会侧重于在Excel中的使用，桌面版的主要功能和在Excel中基本一致，一个学会另一个也能轻松上手，不用太过纠结。如果是一般用户以处理数据为主，建议从Excel开始学习，桌面版更侧重于数据的可视化展示。
下载Power BI Desktop

如何打开Power Query

对于PQ对数据的加载方式，常用的有3种

• 从表格

从表格是数据已经在Excel中，通过点击数据菜单-获取和转换-从表格，将数据加载到PQ中。如果你的源数据不是表结构，则会提示你创建表，包含标题并点击确定。如果没提示创建表且表格不是表结构直接进入了PQ，则会报错，这时需要提前将源数据创建表结构以后再从表格加载。这种方式主要用于，数据较少，变动较小的数据。

从表格

• 从文件夹/从文件

从文件是指从本地指定的路径中找一个文件，加载到PQ中，从文件是指从本地指定的路径中找一个文件夹，将其下面的文件全部加载到PQ中。

打开位置.png
从文件.png
从文件夹.png

• 从数据库/其他

如果是从数据库加载数据，暂时没试过从SQL Server加载，目前仅使用过从MySQL加载，如果是MySQL加载则需要安装插件。首次使用数据库需要输入账号密码及权限等设置，而在数据库的加载页面还可以通过SQL语句对需要加载的数据进行预处理，直接加载你想要的数据。

由于PQ中计算数据不如在SQL中效率高，如果是从SQL加载数据到PQ，建议通过SQL代码将数据处理成你想要的结构后再加载到PQ，或只在PQ中做简单的基本的操作，一旦遇到大量数据，且操作了多条件合并查询、分组等操作，则会造成明显的效率降低，如果数据量较小则影响并不明显，几十万的数据跑起来速度还是可以的。一般我们在Excel中，使用10万对10万的匹配，则可能需要5分钟，而在PQ中也就是几秒钟的事。如果你的数据少完全不用考虑这个因素。
PQ使用MySQL加载数据需下载插件

图片.png
图片.png

Powe Query界面

界面中工具栏按钮并没有Excel中那么多，其功能相对也比较好理解，可分别点击看看其对应的功能，后续会花点时间介绍下各按钮的作用。

图片.png

菜单栏

菜单栏一共5个，分别对应了不同的工具栏组

文件.png 转换.png
添加列.png
视图.png

区域功能

• 下面一张图显示当前页面有多个查询表格

  
  表.png

• 右侧是我们全部的编辑步骤，我们所有的操作都会被记录下来，我们可以查看历史的操作步骤并编辑。也就是我之前所说的类似一个宏，但是是一个可以编辑步骤的宏。

  
  步骤.png

• 中间上方是编辑栏，我们所有的操作都是通过PQ重点 M语句实现的。中间最大的区域是数据区域，用以显示当前数据的内容。

  
  编辑栏及数据区域.png

Power Query操作数据和Excel中的不同之处

在Excel中，数据是以单元格为单位，对每一个单元格进行操作，有上下行的概念。但是在PQ中，实际上是数据库思维，是基于列对数据进行操作，虽然也有上下文的概念但是和在Excel中完全不一样，因为你无法直接对单个数据进行编辑。包括在Power Pivot中使用DAX语言对数据继续操作都是基于列，均无法直接编辑单个数据。

这个操作方式和我们原有的数据操作的习惯完全不同，如果你了解数据库语言，那么你理解起来会非常快，但如果只操作过Excel，那么你需要一个适应时间。由于数据是基于列操作的，所以效率比在Excel中高。

其实在Excel中也有类似的功能，就是通过创建表，我们可以得到类似的效果，当我们对创建的表进行操作的时候，你会发现所有行的公式都是一样的，但是其结果却是不同的，而在PQ和PP中对数据操作都是一样的，系统会自动对行上下文进行转换。

表面上来看似乎降低了我们操作数据的自由程度，而当你熟悉之后，你会发现其效率是比在Excel中更加的方便和快捷，PQ在大量数据、重复劳动方便比Excel有优势，但是对于个性需求还是使用Excel更加方便。

表结构.png

总结

关于PQ操作的东西较多，且对于很多人而言是新事物，写文章不如视频演示的效果好，目前正在申请网易云课堂，申请下来之后会录制一些简单的教程放送给大家。