数据分析之用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中三组数据合并,计算出各客户不同产品的折扣金额
合并查询里的联接种类
案例之中国式排名
案例之分组中国式排名
案例之生成笛卡尔积表
案例之多行属性合并
from https://blog.csdn.net/su_2018/article/details/83444953