PowerQuery与PowerPivot将引爆你的桌面级数据分析：能量巨大，超出你想象！

PowerQuery介绍(逻辑封装，能量封装)

PowerQuery（简称PQ）可以将你所有的数据处理逻辑封装进去，包括选取和读取多个文件、读取文件夹内全部文件、筛选（删除）数据、去重、聚合、透视、设置标题等等。就像录制的宏一样，当然，比宏的功能更强大、更专业，它背后有专门的数据整理语言（M语言）做支撑。这样在做数据处理时，你就不必再重复劳动，所有的逻辑都会被保留，下次数据更新你只需要点击刷新即可，当然你也可以方便的对原有的处理逻辑进行迭代修改。

PowerQuery对数据处理逻辑惊人的封装能力，背后依靠的是M语言的近600个函数，Table、Record、List数据结构，灵活的异常处理，60多种数据源支持。

PowerQuery的所有运算均在电脑内存中进行，是全内存运算，其运算效率远超Excel Sheet函数（Sheet函数达到万行级别时就很慢了），仅仅在运算结束后才可将数据导出到Excel或者PowerPivot中。如果不加载只保持数据连接，PQ编辑界面关闭后内存变量将会全部清空。因此PQ就像只被*的神兽，如果不考虑加载数据的话，其实际可处理数据量是无上限的，仅与电脑的内存大小有关。如果考虑加载到存储对象（如Excel Sheet表、Power Pivot），其行数限制也只是其存储对象的行数有限制，与PQ无关。注意，假如你用PQ选择最终加载到Excel Sheet表的话，你可以处理的数据量仍然是无上限，只是最终选择导出的结果要低于100万行（Excel Sheet表限制）。当然，你也可以选择不加载到任意对象，只保持数据连接，在PQ里直接复制结果，不过要注意的是，PQ界面可复制的行数最高为1000行。（期待PQ未来能有更多的数据加载选项，或者直接有写出到txt的函数。当然，如果PQ能成为独立软件，那就更牛了！绝对是小型ETL工具的绝佳选择！）

PowerQuery是对传统Excel数据处理流程的再造，更专业化、更自动化，其函数覆盖了传统Excel函数的绝大多数（除了统计、财务、工程等专业函数）。非专业化的通用数据处理操作，全部可以用PQ解决。因此，现在你拿到数据的第一步就要考虑用PQ导入，然后在PQ里进行数据变换。

PowerPivot介绍（模型建立，数据分析，能量释放）

由于PQ的超大数据量处理能力，传统Excel的100万行Sheet已不足以与其匹配。微软推出了全新的“Sheet”，就是Power Pivot（简称PP）里的“Sheet”，这里面的表支持的行数无上限，与PQ能力完全匹配。并且PP也有其自有的超强能力，除了同样拥有PQ的内存运算特性，还引入了列压缩技术。列压缩技术，通俗来讲，就是假如有一列数据，有1000万行，但是只有2个不相同的值，这样在存储时，就只存2个值，再存一个索引，用于标记第几行是哪个值。这种存储会给运算带来极大的效率提升，比如要对所有行运算，等价于仅仅对不重复值的运算。这种技术让百万、千万行数据运算秒级响应成为可能。当然，这只是最简单的一个例子，具体列压缩运算会加入更多优化算法。

PP的特点不只是运算效率高、存储量大，PP也有自己的DAX语言，用于专业的数据分析。利用DAX语言可以建立更为灵活、强大的度量，用于支持高级透视分析。PP同时支持建立多表之间的关系，形成复杂业务数据模型。

有了PQ和PP，Excel的数据处理形成了一个全新的数据处理途径，数据源->PQ->PP。这也形成了我们所谓的新一代Excel(Modern Excel)（注：此叫法非官方，是国外Power BI大牛、前微软Power BI项目组员工Robb Collie提出，现致力于Power BI咨询推广）。

Excel的Sheet表在新一代Excel里也不是毫无用处，PP里的“Sheet”是只读的，不允许修改，只能作为数据存储。对于制作报表来说，Excel的传统Sheet仍是必选。从PP到Excel有两种途径，一是透视表，二是链接表。从PQ到Excel有直接的表加载项。

“PQ+传统Sheet”与“PQ+PP”如何选择？

对于如何选择，没有绝对的规则，只能将自己经验总结如下：

1、其实PQ本身就可以完成数据分析，甚至有的数据处理在PQ里更为便捷。因此，建议如果能用PQ直接解决的，就不用PP；

2、PQ不能做的有：多表关系模型、度量、超100万行数据存储、百万行以上聚合运算（PQ运算明显吃力）；

3、如果要用PP，建议用PQ+PP。因为PP自带的数据获取能力太弱，无法在导入PP前对数据进行灵活预处理。并且PP导入Excel数据时会存在数据遗漏（见下图）。

PowerPivot数据导入异常问题

个人看法：在新一代Excel里，传统的非专业函数显得有些多余。Excel的传统Sheet将更多的用于制作报表，作为报表展现端，而不是数据处理。

PowerQuery和PowerPivot组件的引入，是Excel二十年以来最令人振奋的升级-也是变革！

关注PowerBI之家头条号，尽情欣赏新一代Excel数据分析之美！

让PQ与PP引爆你数据分析的×××