<Excel> 数据分析之用Power Query实现数据转换和清洗（功能操作篇）

一个案例快速了解Power Query

凭借 Power Query，可以搜索数据源，创建连接，然后按照可满足你需求的方式调整数据（例如删除列、更改数据类型或合并表格）以创建报表。
例：将门店一&门店二两个工作簿的数据整合到一张工作表，并统计出各销售代表每个月的业绩

数据的行列管理及删除重复项

如下例所示：

数据格式的转换及删除错误

如下例所示，更改表格中标注黄色的异常单元格

*注意事项
1.大小写：Power Query对英文字母的大小写十分敏感，不能用大写字母去找小写字母，反之一样
2.修整：在所选列的每个单元格中删除前导空格和尾随空格
使用“修整”功能时需注意，尽量不要对整张表格进行一次性修整，由于数据格式不同，容易出现报错（文本格式下要）
3.清除：清除所选列中的非输入字符（例如：回车）
4.添加前缀/后缀：在所选列单元格的前/后增加指定的字符，类似于合并

数据格式的拆分、合并和提取操作演示

转置和反转

转置：行列数据互转

反转行：第一行数据和最后一行数据的互换

透视和逆透视（二维和一维的转换）

一维指仅有列标题的数据，二维指既有行标题又有列标题的数据
透视：一维转二维
选中的列透视后会以行数据的形式出现

逆透视：二维转一维

分组依据和添加列

如下例所示：
求出每个班组的人数，平均年龄，居住外地人数，大学生人数，平均满意度

“添加列”功能下“自定义列”说明：

日期和时间的整理功能说明

如下图所示：

持续时间：通过年限来提取

数学运算功能说明

如下图所示：

追加查询

用途：追加多份数据到一份数据中查询，对表格的纵向追加
要求多份数据的列标题名称一致（导出的数据以第一份数据的格式为准）

1.追加同一工作表的两份数据到一份数据
如下例所示：

若列标题不一致，追加查询后导出的数据会新增一列不同名称的列数据
如下例所示：

或者修改数据后重新导入两份数据

2.追加一个工作簿中多工作表的数据到一个工作表
如下例所示：

3.追加一个文件夹内多工作簿的数据到一个工作表
如下例所示：

*需要运用Power Query的M函数之一
=Excel.Workbook(目标)
作用：将Binary列内容转换为Table列

4.追加一个文件夹内多CSV文件到一个工作表

*也可用以下简便方法追加多工作簿/多CSV文件
如下例所示：

合并查询

通过多张数据共有的字段，将其他源数据的内容添加到一张工作表
是对表格的横向合并，类似于Excel的Vlookup函数
如下例所示：
将sheet1中三组数据合并，计算出各客户不同产品的折扣金额

合并查询里的联接种类

案例之中国式排名

案例之分组中国式排名

案例之生成笛卡尔积表

案例之多行属性合并