进一步综合介绍 Power Query 使用方法

张文洲

前面介绍了 Power Query的基本用法，并列示了五个具体应用的例子。具体参见 快来学习 Power Query 吧，屌爆了的Excel加载项！ 今天结合一个具体的例子，综合的给大家介绍 Power Query 的功能

首先，我有一张数据源表，三张同样结构的数据表。这是我从网上下载的

我先新建一个工作簿，通过 EXCEL文件导入数据，首先导入A公司的数据

输入数据源所在路径以后，稍等片刻，excel窗口右边会弹出找到的数据列表，点击下面的 编辑查询按钮

这样就进入查询编辑器的界面了，主要分为五个区域，
最上面是功能区，里面包含所有的操作命令工具。从后面的操作可以看出，这些功能基本上都可以在下面的列表编辑区域和参数设置区域右键或直接设置。
接下来是公式编辑栏，里面会自动生成一些列公式，不懂不要紧，你完全可以把它隐藏起来，直接点右边的小三角就可以了。因为后面可以看出，我们暂时基本上用不上这个。
下面从左至右依次是 数据源树形列表区域、数据列示编辑区域、参数设置区域（我自己起的名字，暂且先这么叫着吧）
树形列表区域，我们可以看见数据源工作簿中所有的工作表（包括xlsx隐藏的工作表、宏表），点击表格，数据列示区域会出现相应的数据列表。
中间的数据列表，我们可以进行多种操作，下文会提到。
右边是查询名称、说明、操作历史记录和数据加载选项的设置区域。

下面开始介绍数据列示区域各种快捷功能入口，首先点击列表行列标题交界处的小方格，看看
在这里我们可以
使用第一行作为标题（替换掉系统设置的默认标题）
移出或保留唯一值
删除错误值
插入序号列、自定义列
移动单列或多列的位置
筛选前多少项  或 序号范围
追加记录或合并记录

接下来看看在某一列上右键的功能
删除列
分列 或者 将多列合并为一列
移出重复值、错误值
替换数据内容
改变数据类型
插入自定义咧或序号咧
重命名
分类汇总
逆透视（将二维数据转换为一维数据的便捷功能）

接下来看看筛选功能
首先看看日期型数据的筛选
可以按年月日、季度、周等多项条件筛选

文本型数据筛选

数值型数据筛选

因为最后我想将三个表合并到一起，所以这里我先插入一个自定义字段 公司名称

在自定义对话框里面，我直接录入公式 ="A公司"
注意，这里的公式和工作表里面的公式不是一回事，两者有相通的地方，但是主要是区别。具体语法请看我红线标示，点击可以看到有关帮助。右边字段列表，类似于透视表的计算字段可选字段。

看到新增的这一列，我将其列名称改为 公司

到此第一个表查询设置完毕了，但是这不是我最终想要的数据，我暂时不想将其加载到我某个工作表，所以请看下图
先给这个查询命个名吧，比如 A公司  说明信息可以不用填写
然后就是刚才我一系列操作的历史记录了，我可以随时修改这些步骤
最关键的在最下面，有两个选项，至少应选择一个
如果想加载到工作表，那么点击完成后，会新建一个工作表，并将查询结果返回到这个工作表。
如果想加载到数据模型，就可以在后面使用powerpovit进一步分析了。这里只是加载到缓存中，不会新建工作表。
因为这不是我要的最终数据，所以我只加载到数据模型。
当然这些设置可以随时更改的，没有关系。

点击左上角应用并保存，返回工作表。

这时excel窗口右侧会出现一个新的面板，里面列示了我设置的查询，包括记录行数，鼠标移动到上面，还可以预览数据。并且可以随时编辑、加载到工作表。

按照同样的步骤，我将其他两个公司的数据都加载到数据模型中

接下来要做的是将三个表合为一张表
power query提供了合并和追加两项功能
合并：将两个表连接在一起，两个表直接应该有一个关键连接字段。这两个表结构可以不同，但必须至少有一个相同的关键字段。相当于 SQl中join语句（关于合并功能使用，请参照快来学习 Power Query 吧，屌爆了的Excel加载项！示例5）追加：将两个结构相同的表合到一块，后面一个表追加到前面一个表（主表）的下面。
因为这三张表相同，所以我要用的是追加功能。
有多重方式访问追加功能

excel功能区访问


进入查询编辑器，在查询编辑器功能区访问

点击追加，会出现追加对话框，上面一个表是主表，后面一个是要追加的数据表。也就是把后面一个表的数据追加到前面一个表。

点击确定，会看见这时公司列已经包含A公司和B公司的数据了

张文洲

照此，再把C公司的数据追加进来
需要注意的是，我这里所说的追加和合并，都是针对之前设置的三个查询来说的，追加和合并的对象都是查询。

在选择一个查询以后点击追加，被选择的查询被默认为主表，这时候只需要选择一个附表，下面应该选择C公司


追加完成以后，返回工作表，这时会看见原来建立的三个子查询，还有最后一个合并查询。合并查询的记录数就是前面三个子查询记录数的和。

接下来围绕最后的合并查询做一些调整，
首先介绍一下合并列，我们可以选择多列，将其合并为一列，并且可以选择连接字符


自定义连接字符，这里我录入一个短连接符 -

完成以后看见两列合为一列了，我可以修改这一列的名称。PS：结合前面说到的插入自定义列，如果想保留原来两列，可以插入一个自定义咧，公式 = A列&“-”&B列 即可
这里要合并多列是一样的操作。

看见没有，我数据里面日期、数量、单价都是左对齐的，这是因为他们是文本格式的数据，现在我转换一下这三列的数据格式
日期可以直接转换

单价列因为有货币符号，直接转换会出错，于是我先替换掉人民币符号


替换以后，再进行转换，转换为数值

转换数据以后，我就可以对数值进行计算了，我插入一个自定义字段，录入公式
看见没有，和数据透视表的计算字段是一回事吧

数量*单价=金额

接下来，看看如何对这个表格进行分类汇总计算
在任意列右键-分组

在弹出的分组统计对话框里面
分组依据：选择要分组的字段，可以是一列或多列，如果是多列，点击加减号就可以了
新列名称：分组统计后会生成一个统计列，需要给他一个名称
分组计算方式：这里面有几种计算方式  如 sum min max rows之类的
计算列：如果计算方式是min max sum的话，这一列就必须选择数值型列，如果是其他计算方式，这一参数不可选。



这里我按照地区和城市分组，统计销售收入和平均销量


点击完成，看看效果

这是一个二维表格，我现在想把“销售收入”和"平均销量"放在一个单独的列，把数据放在一列，转换为一维数据。
类似于把数据透视表转换为数据源（所以这项功能叫逆透视）
请看操作，一键完成，多么方便
选择要反转的两列，右键，逆透视

看看效果

如果这是最终结果的话，那么我可以把它加载到工作表了

张文洲

说明：这里进行了这么多操作，主要是为了给大家展示 power query 的各项功能，实际的数据并没有太大意义。因为真要得出最后这个结果的话，用几句SQL查询语句就轻松解决了。可是这里的操作都是可视化的，即便在SQL方面你是一个完全的小白，依然能快捷的将各种不同数据源的数据快速组合到一块，并进行各种条件查询。
最后，所谓查询，都是可以随着数据源的更新而随时刷新数据的，尤其适合经常就特定数据源进行常规统计分析工作。利用power query可以极大地提高工作效率，结合powerpivot等其他组件，更能做出依据数据源的动态报表。

Excel德宝

不顶不行啊{:soso_e179:}

superboyli520

必须顶啊，感谢分享！！！EXCEL达人。

penp

谢谢,谢谢!学习了!

function2046

谢谢学习了

hiliqi

好像不支持手写SQL

540223905

张文洲 发表于 2013-11-9 02:12
说明：这里进行了这么多操作，主要是为了给大家展示 power query 的各项功能，实际的数据并没有太大意义。因 ...

仔细拜读了您的两次关于power query的帖子,感觉到这个插件的功能真是很强大。但关于表格追加查询的功能还有点小疑问，您的例子里，用于追加的表一共有三张，所以操作了3次追加功能，那如果我有三十张表需要连接，那是不是说我必须操作三十次追加功能才能把这三十张表连接在一块儿呢？在该插件中有没有更快捷一点的方法呢？另外，期待您能介绍更多的关于该插件的高级综合运用方面知识。

540223905

张文洲 发表于 2013-11-9 02:12
说明：这里进行了这么多操作，主要是为了给大家展示 power query 的各项功能，实际的数据并没有太大意义。因 ...

我目前使用的是excel2013,原生自带power pivot ,因为网上有关pivot 的资料很少，所以自己摸索着使用，只用到一些看粗浅的功能，即使如此，已经觉得很好用了，看了您关于PowerQuery的介绍后，又在官网上下载了中文版的PowerQuery_2.17.3850.242 (32-bit) [zh-CN].msi安装使用，但界面与您所介绍的英语版略有不同，不知道是不是升级了。试着摸索了一下，感觉到它与power pivot 真是完美的结合。目前关于这两个插件的进阶性使用的介绍真是相当的少，目前我也就只看到您这儿对于基本功能有较为详细的介绍，不知道您能不能多发点帖子详细介绍一下它们更加高级一些的使用功能能和相互配合使用的综合性案例。或者有没有相关的网站或书箱推荐给我进行更加系统地学习，非常感谢。