Power Query 中常用的数据清洗、转换操作

操作步骤

选中列——点击列名右侧箭头——进行筛选

可选操作

• 勾选
• 文本筛选器：等于/不等于、开头是/开头不是、结尾是/结尾不是、包含/不包含
• 数字筛选器：等于/不等于、大于/大于等于、小于/小于等于、介于
• 日期筛选器：等于、早于、晚于、介于、接下来N个时间段/之前N个时间段、最早/最晚、非最早/非最晚、年/季/月/周/天/时/分/秒、自定义

图片示例

操作步骤

选择列——勾选要保留的列：留下勾选的，删除未勾选的

选中一列或多列——删除列：删除选中的列

选中一列或多列——删除其他列：删除选中列之外的其他列

图片示例

操作步骤

保留行

删除行

可选操作

• 保留行：保留最前面几行/最后几行/一定范围内的行、保留重复项、保留错误行
• 删除行：删除最前面几行/最后几行/一定范围内的行、删除重复项、删除空行、删除错误行

图片示例

操作步骤

选中一列——主页——排序

可选操作

• 升序?A?to?Z
• 降序?Z?to?A

图片示例

操作步骤

选中一列——右键——移动

可选操作

• 向左移动/向右移动/移到开头/移到末尾

图片示例

操作步骤

选中一或多列——转换——任意列——替换值

可选操作

替换值：将?X?值替换为?Y?值

替换错误：将?错误?替换为?Y?值

图片示例

操作步骤

选中一或多列——转换——任意列——填充

可选操作

向下：用最近的值向下填充空白单元格

向上：用最近的值向上填充空白单元格

图片示例

操作步骤

• 选中一列——转换列——文本列——拆分列

可选操作

• 按分隔符：靠左/靠右拆分一次、每次出现分隔符拆分
• 按字符数：靠左/靠右拆分一次、多次拆分
• 按位置：按索引位置
• 按大小写转换：小写到大写转换/大写到小写转换
• 按数字非数字转换：数字到非数字转换/非数字到数字转换

图片示例

操作步骤

• 选中要合并的多列——添加列——文本列——合并列

可选操作

• 合并列——选择分隔符、合并后新列名

图片示例

操作步骤

• 选中一列——转换/添加列——文本列——提取

可选操作

• 长度：获取文本长度
• 按索引范围：首字符（从左开始N个字符）/结尾字符（从右开始N个字符）/范围（从X位置开始N个字符）
• 按分隔符：分隔符之前/分隔符之后/分隔符之间

图片示例

操作步骤

• 选中一列——转换/添加列——文本列——格式

可选操作

• 处理英文文本：小写（全部字母小写）/大写（全部字母大写）/每个单词首字母大写
• 处理空格和非打印字符：修整（去除字符串首尾空格）/清除（去除所有非打印字符，如换行符等）
• 添加前后缀：添加前缀/添加后缀

图片示例

操作步骤

转换/添加列——日期&时间——日期

可选操作

• 年限：当前日期时间?-?该日期时间列?的差值
• 仅日期：从日期时间列中提取日期
• 分析：将不规范的日期格式转换为统一规范的格式
• 年：年（从日期中提取出年）、年份开始值（提取出这一年的第一天）、年份结束值（提取出这一年的最后一天）
• 月：月（从日期中提取出月）、月份开始值（提取出这一月的第一天）、月份结束值（提取出这一月的最后一天）、一个月的某些日（提取出这一月总共有多少天）、月份名称（月份对应的中文名）
• 季：一年的某一季度（提取出日期是这一年的哪一季度）、季度开始值（提取出这一季度的第一天）、季度结束值（提取出这一季度的最后一天）
• 周：一年的某一周（提取出日期是这一年的第几周）、一个月的某一周（提取出日期是这一月的第几周）、星期开始值（提取出这一周的第一天）、星期结束值（提取出这一周的最后一天）
• 天：天（从日期中提取出天）、每周的某一日（提取出日期是这一周的第几天）、一年的某一日（提取出日期是这一年的第几天）、一天开始值（提取出这一天开始的日期时间）、一天结束值（提取出这一天结束的日期时间）、星期几（提取出这一天对应的星期的中文名）
• 合并日期和时间：选中日期列和时间列，合并为日期时间
• 最早：提取出日期列中最早的日期（只有一个值）
• 最新（最晚）：提取出日期列中最晚的日期（只有一个值）

图片示例

操作步骤

转换/添加列——日期&时间——时间

可选操作

• 仅时间：从日期时间列中提取时间
• 本地时间：
• 分析：将不规范的时间格式转换为统一规范的格式
• 小时：小时（从时间列中提取出小时）、小时开头（提取出这一小时开始的时间）、小时结尾（提取出这一小时结束的时间）
• 分钟：从时间列中提取出分
• 秒：从时间列中提取出秒
• 合并日期和时间：选中日期列和时间列，合并为日期时间
• 最早：提取出时间列中最早的时间（只有一个值）
• 最新（最晚）：提取出时间列中最晚的时间（只有一个值）

图片示例

操作步骤

转换/添加列——日期&时间——持续时间

可选操作

图片示例

操作步骤

• 数据类型

可选操作

• 数值：小数、定点小数、整数、百分比
• 日期时间：日期/时间、日期、时间、日期/时间/时区、持续时间
• 文本
• 布尔值
• 二进制

图片示例

操作步骤

主页——组合——合并查询

可选操作

• 合并查询：在当前选中查询上合并新查询
• 将查询合并为新查询：新建一个查询，进行合并查询

图片示例

操作步骤

主页——组合——追加查询

可选操作

• 追加查询：在当前选中查询上追加新查询
• 将查询追加为新查询：新建一个查询，进行追加查询

图片示例

操作步骤

转换——表格——分组依据

图片示例

（1）选择用来分组的字段，如日期、产品

（2）选择用来汇总的数据字段、汇总方式，设置新列名

（3）获得汇总之后新的数据表

操作步骤

转换——任意列——透视列

可选操作

透视列：

图片示例

操作步骤

转换——任意列——逆透视列

可选操作

• 逆透视列：
• 逆透视其他列：
• 仅逆透视选定列：

图片示例

操作步骤

选中列——添加列——常规——重复列

可选操作

将选中列复制一份

图片示例

操作步骤

添加列——常规——条件列

可选操作

IF?...?THEN?...?ELSE?

图片示例

操作步骤

添加列——常规——索引列

可选操作

• 索引从0开始
• 索引从1开始
• 自定义：自定义起始索引和每一步的增量（需要将几份数据交叉合并的时候很有用）

图片示例

操作步骤

添加列——常规——自定义列

可选操作

使用?M?函数和公式自定义列

图片示例

操作步骤

Power?Query?——?视图?——?数据预览?——?列分发（列分布）

图片示例

说明

勾选后，在每一列上方显示此列数据的分布图，以及非重复值（Distinct???value，包含重复值和NULL）和唯一值（Unique?value，不包含重复值和NULL，只出现一次的值）的数量

操作步骤

Power?Query?——?视图?——?数据预览?——?列质量

图片示例

说明

勾选后，在每一列上方显示此列数据的质量数据：有效、错误和空数据的百分比

操作步骤

Power?Query?——?视图?——?数据预览?——?列配置文件（列属性）

可选操作

图片示例

勾选后，选中某一列时，就在下方显示这一列的统计信息和取值分布。包括计数：计数、错误值计数、空值计数、非重复值计数、唯一值计数、NaN值计数、零值计数；统计信息：最大值、最小值、平均值、标准差

操作步骤

转换/添加列——数字

可选操作

图片示例

操作步骤

转换——表格——转置

转换——表格——反转行

可选操作

• 转置：行列转置
• 反转行：所有行前后调换顺序

图片示例