数据分析之用Power Query实现数据转换和清洗(功能操作篇)

一个案例快速了解Power Query

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

如下例所示:

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

转置和反转

转置:行列数据互转

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

透视和逆透视(二维和一维的转换)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

逆透视:二维转一维

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

分组依据和添加列

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

如下图所示:

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

数学运算功能说明

如下图所示:

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

追加查询

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

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)


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

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

合并查询

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

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

合并查询里的联接种类

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

案例之中国式排名

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

案例之分组中国式排名

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

案例之生成笛卡尔积表

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

案例之多行属性合并

数据分析之用Power Query实现数据转换和清洗(功能操作篇)

from  https://blog.csdn.net/su_2018/article/details/83444953

 

相关推荐