快速入门EXCEL插件-Power Query

南山饮水

一、Power Query介绍

1、什么是Power Query

Power Query（以下简称PQ）是Excel的一个插件程序，主要存在于微软Excel功能中。目前已经内置在Excel2016及以后的版本中，而之前的版本需要单独安装此插件。
PQ可以导入来自不同数据源的数据，将清洗、整理好的数据传递给数据透视表、Power Pivot、Power View、Power Map等工具进行数据分析和展示。简言之，PQ就是一个智能的数据处理工具。
2、为什么要学习Power Query

作为Excel中的一个数据处理插件，PQ突破并实现了Excel处理百万级数据的限制，同时作为微软系列软件中一个比较常用的功能，PQ功能存在于微软多个软件中，比如Excel和Power BI。因此我们可以总结到，学习PQ的原因有如下：

• 有操作记录功能，能在下次自动执行如上步骤
  
• 能从不同来源直接获取数据，实现智能的数据处理，减少数据操作步骤和时间
  
• 增加数据处理方式
  
• 能同时掌握多个软件的操作，比如Power BI
  

二、Power Query界面介绍



区域1：功能选项卡区域，主要有：主页+转换+添加列+视图
区域2：展示连接的数据源
区域3：展示选中的数据源对应的明细
区域4：展示选中单元格后，对应的数值
区域5：数据表的查询属性设置，一般只用来修改数据表表名
区域6：记录数据处理步骤
三、Power Query数据获取

进入PQ界面有两种主要的方式：直接启动+导入数据。
1、直接启动



点击步骤：【数据】选项卡 → 【获取数据】→ 【启动Power Query编辑器】
备注：Excel各版本启动方式不同，若出现差异，可通过上面各选项挨个查看。
2、导入数据



常用的数据导入方式有：

• 本地文件导入：格式有txt、csv、xlsx等
  
• 网站数据导入：通过链接网站地址，获取对应的数据内容
  
• 数据库导入：通过链接各种数据库，直接获取对应的数据内容
  

四、Power Query常用操作

1、主页



（1）关闭并上载

• 关闭并上载：关闭PQ编辑器，并将处理后的数据加载到默认的Excelsheet页中
  
• 关闭并上载至：关闭PQ编辑器，并将处理后的数据以“表格/数据透视表/数据透视图/连接”的形式显示。
  

（2）刷新预览

• 刷新预览：只刷新选中的数据源
  
• 全部刷新：刷新PQ连接的全部数据源
  

（3）选择列&删除列

• 选择列：点击后，可以选择要保留的列，剩余列将被删除
  
• 转到列：主要针对数据字段很多的情况，点击后，可以快速选择对应的列
  
• 删除列：点击后，删除选中的数据列
  
• 删除其他列：点击后，除了选中列，其余数据列均被删除
  

（4）保留行&删除行

• 保留/删除最前面几行：保留或删除最前面的指定数量的行
  
• 保留/删除最后几行：保留或删除最后的指定数量的行
  
• 保留/删除重复项：保留重复值，剩余非重复值将被删除；删除重复值，将删除重复且多余的行
  
• 保留/删除错误：保留错误，不含错误值的行将被删除；删除错误值，含有错误值的行将被删除
  
• 保留行的范围：保留指定行范围之间的数据
  
• 删除间隔行：删除指定范围的数据行
  

（5）拆分列       

• 按分隔符：可以自行设置分隔符，来拆分数据
  
• 按字符数：可以按字符个数来拆分数据
  
• 按位置：直接指定要拆分的数据位置
  

（6）数据类型


对数据列进行格式修改，可以通过选中列，在此处进行类型修改，也可以直接在数据明细中，在字段标题的左边选择类型进行修改。
（7）将第一行作为标题

• 将第一行用做标题：如果导入时，PQ没有将第一行识别为标题行，则可以点击此处进行设置
  
• 将标题作为第一行：如果导入时，PQ错误识别将第一行识别为标题行，点击此处可以修改
  

（8）替换值
类似于Excel中CTRL+H的替换功能，将A值替换为B值。
（9）合并查询


合并查询，等同于Excel中的多表连接，类似于VLOOKUP的函数功能。此处是直接帮助我们完成多表连接的操作。
（10）追加查询


追加查询，等同在原数据表的最后新增数据行。这种查询，必须要保证两个表的字段数量、名称和格式完全一致。
2、转换



点开"转换"选项卡，我们会发现此处的很多功能我们在"主页"选项卡中已经见过了。没错，主页其实是汇聚了我们常用的PQ功能，而在这个选项卡中，我们剩余常用的内容还有：
（1）合并列
合并列，类似于Excel中的CONCAT函数，将多个字段进行文本连接，生成一个新的字段。
（2）日期&时间




此功能，类似于Excel中的几个日期和时间函数，可以直接获取年月日时分秒等数据。
3、添加列



点击"添加列"选项卡，大家又会发现：这里有很多功能在上一个选项卡也存在，那它们到底有什么区别呢？
其实，它们的主要区别在于：之前的功能都是在原来的字段上直接处理并覆盖，而"添加列"选项卡，得到的数据结果不会直接覆盖原字段，并且会生成新的字段。
该选项卡中，剩余几个重要的常用功能：
（1）条件列


条件列，类似于Excel中的IF函数，也类似于SQL中的CASE WHEN函数。此处只需要点击选择，不需要额外处理代码。
（2）索引列
索引列，对选中的表添加索引，可以从0开始，也可以从1开始，当然也可以自定义。
（3）重复列
重复列，对选中的列进行复制。