Power Query数据清洗

本文内容整理自《PowerBI星球」内容合集(2024版)》中的 「B PowerQuery数据清洗」部分。

Power BI官网、学习路径、官方教程

一、数据清洗

  对导入的数据进行数据整理的过程一般称为「数据清洗」，之所以称之为清洗，是因为在数据分析师眼中，杂乱的数据就是脏数据，只有被清洗成干净的数据后才可以进行分析使用，这就要依赖Power Query 功能。

  Excel作为日常办公软件在大数据时代明显有点扛不住，所以从Excel2010开始，推出了一个叫Power Query的插件，可以弥补Excel的不足，处理数据的能力边界大大提升。现在还在用Excel2010和2013的同学可以从微软官网下载power query插件使用。到了Excel2016，微软直接把PQ的功能嵌入进来，放在数据选项卡下。以下是Excel2019的界面：

  PowerBI中的也集成了Power Query，专用于数据处理，所用的也都是M语言，而且其功能也更加丰富。下面是建立数据连接后 Power Query 编辑器的显示方式：

1. 功能区：显示Power Query的所有功能选项卡和功能按钮。
2. 查询窗格：显示活动查询数以及查询的名称，可供你选择、查看和调整。
3. 中央（数据）窗格：显示已选择查询中的数据，可供你调整。
4. 查询设置窗格：列出了查询的属性和已应用的步骤，可以根据你的需要重命名步骤、删除步骤，或对步骤重新排序。
5. 高级编辑器：利用“高级编辑器”可以查看 Power Query 编辑器通过每个步骤创建的代码，后面会详细讲解。
6. 数据保存：数据清洗完毕后，选择Power Query 编辑器主页选项卡下的”关闭并应用“即可保存工作并关闭Power Query 编辑器。
   
   此时选择“文件”>“保存”（或“文件”>“另存为”），即可将工作保存为 .pbix 文件的形式。

1.1 基础操作

1. 数据界面放大/缩小：使用：CTRL+Shift+"+",CTRL+Shift+"-"来放大和缩小窗口。
2. 快速定位到某列：Ctrl+G，在弹出的窗口中选择列名，快速跳转到某列。用HOME,END键直接定位首列和尾列。
3. 查看整行数据：当列很多时，查看某一行的记录不是很方便，可以单击该行的行号，然后在下方的窗口中可看到整行内容的列表。再次按↑↓箭头就可以在这个窗口中快速切换到其他行的记录。
4. 修改加载方式：在Excel中，PowerQuery处理之后有两种加载方式，加载到表（默认）或者加载到连接。如果数据太多，比如超过Excel表格1048576行的限制，是无法完整加载到表格的，可选择后者。右键该查询，选择“加载到”：
   
5. 数据导入重复：如果在导入Excel数据后发现数据重复，很可能是因为数据源Excel表格中存在筛选或定义的名称，导致导入到Power Query时出现缓存的sheet。在Power Query界面中，这些缓存的sheet可能会显示如下：
   
   如果继续直接合并，数据就会重复。只需要在kind列，筛选“sheet”数据行；也可以把含有"_xInm._ "字符或者你没有见过的name筛选出去。
6. 调出编辑栏：编辑栏是显示M公式的窗口，如果发现编辑栏不见了，只需要在视图中，勾选“编辑栏即可”。
   
7. 调出步骤面板：在PowerQuery界面，最右侧是每一步的操作步骤面板（严格来说是查询设置面板），如果发现它消失了，可以通过点击“视图”选项卡中的"查询设置"来显示它。
   
8. 显示列信息：在【视图】中的“数据预览”，勾选“列质量”、"列分发"等功能就可以显示下方这种列标题统计信息。
   
   

1.2 提升/拉低标题

  在Excel中第一行为标题行，从第二行开始才是数据，但在PQ中，从第一行开始就需要是数据记录，标题在数据之上。因此从Excel导入数据的第一步就是要提升标题，点击「转换」，将第一行作为标题：

  将第一行作为标题旁边的下拉按钮，还有个将标题作为第一行，实际上就是拉低标题，这个功能后续也会用到。

1.3 数据类型

1.3.1 自动识别并转换数据类型

  设置正确的数据类型非常重要，后期数据建模和可视化过程中，很有可能会出现一些意想不到的错误，最后发现是数据类型设置的不对，所以一开始就要养成设置数据类型的好习惯。设置数据类型有两种方式：功能区的数据类型选项和点击列名左侧的ABC/123图标。

  另外，Power BI 在导入时会自动识别并转换数据类型，比如将数字文本自动更改为整数类型，有时候，这并不是我们想要的结果，比如下图左侧的excel数据，导入之后因为自动改为整数类型，导致没有了前缀的0：

  解决的办法也非常简单，直接把“更改的类型”这个步骤删掉，数据就恢复为与Excel一样的格式了：

  如果不想自动检测数据类型的话，可以禁用此功能：在文件>选项>数据加载中，勾选“从不检测未结构化源的列类型和标题”。

1.3.2 四舍五入问题

当我们在 Power Query 中进行保留 2 位小数的操作，可以通过界面功能实现：

  仔细检查会发现，部分结果并非是我们所预期的“四舍五入”，比如3.145 被舍入成了 3.14，而非 3.15。这是因为Power Query中的默认舍入方法是一种被称为“Banker‘s rounding”（银行家舍入）的舍入方法，也被称为 “四舍六入五成双”。

  当遇到数值舍入位数的后一位是 5 时，它距离舍入位数两端的数值都相同，那它会被舍入到距离它最近的偶数。如果要进行四舍五入，可以使用Number.Round函数，他有两个参数：

• digits：舍入位数
• roundingMode：舍入模式。

舍入模式	值	舍入的最后一位为5时
RoundingMode.Up	0	向上舍入。
RoundingMode.Down	1	向下舍入。
RoundingMode.AwayFromZero	2	向远离零的方向舍入。
RoundingMode.TowardZero	3	向零舍入。
RoundingMode.ToEven，默认值	4	舍入到最接近的偶数。

  RoundingMode.Up 和 RoundingMode.AwayFromZero 都可以做到对正数进行“四舍五入”，如果是负数，则应使用后者。另外还有其它几个函数：

函数名	描述	示例	示例结果
Number.RoundDown	向下取整，忽略小数部分	Number.RoundDown(3.14159)	3
Number.RoundUp	向上取整，忽略小数部分	Number.RoundUp(3.14159)	4
Number.Floor	向下取整到最接近的整数	Number.Floor(3.999)	3
Number.Ceiling	向上取整到最接近的整数	Number.Ceiling(3.001)	4
Number.Int	截取整数部分，忽略小数部分	Number.Int(3.999)	3

1.4 列运算

1.4.1 删除空值/错误值/重复项

  数据导入后，有可能出现错误（Error）或者空值（null），数据分析之前，就需要删除它们。选中需要清洗的列，右键菜单中进行操作就行：

对于重复项，选中需要删重的列，右键选择「删除重复项」即可。

1.4.2 空值运算

  平时接触到的源数据常常有空值，比如Excel数据中的空白单元格，power query中会显示为null，大多数时候，我们并不能简单粗暴的删除其中的空值，而是需要在PQ中对数据进一步运算整理。但是在在Power Query运算中：null+数字=null，例如以下数据：

  求两列相加，可以通过添加自定义列的方式（见本文2.1章节），直接用运算符"+"；或者是选择一列，点击标准->添加进行快捷列运算：

  null参与运算的所有结果都成了null。如果将null用0来替换，加法结果正常，但是乘法会出错，所以不能简单的将其替换为0。可行的办法是使用List类函数。依然以两列相加为例，相加用List.Sum函数：

在List类函数的运算中，null无论与数字怎么运算，最终的结果都是数字。例如：

List.Max(null,数字) = 数字
List.Min(null,数字) = 数字
List.Product(null,数字) = 数字

1.4.3 快捷列运算

• 选中两列时，除了加法，还可以进行乘、除、减、百分比、取模等各种运算
• 选中三列及以上时，只有加法和乘法两种运算。

  仔细查看计算时的M代码，前者使用的是运算符，这样当数据中含有null时，结果也是null；后者使用的是List类函数，结果会无视null的存在。

  选择一列，选择“添加”，会弹出一个窗口，在数据框中输入一个数值后，生成的新列，就是这个数值和原列相加的结果：

  比如输入100，结果如下：

总结：利用功能区的"标准"运算功能，可以实现快捷列计算。以加法为例：

1. 选中一列时，以运算符的形式为该列加上同一个数；

2. 选中两列时，以运算符的形式两列相加；

3. 选中三列及以上时，以List.Sum函数的逻辑多列相加。

1.4.4 添加/删除前缀

使用添加自定义列的方式，通过函数可进行前缀的添加和删除。

1. 添加前缀字符，补齐位数：使用公式Text.PadStart([编码],8,"0")可将不定长的数字前面补0，统一为8位数字。
   
2. 删除前缀字符：使用公式Text.TrimStart([8位编码],"0")，可删除前缀字符0
   

1.5 缺失值处理

1.5.1 填充缺失值

在Excel数据中经常会见到合并单元格的情况，导入后就变成了空值：

在PQ中直接向下填充即可，相当于pandas中的df.fillna(method='ffill')语句。

1.5.2 获取第一列非空数据

  假设有以下产品销量数据，有些产品是从第一年就有数据，有些产品是从后面的某一年才产生业务，如何添加一列，来显示每个产品首年业务数据呢？

  这是一种很常见的需求，从上面的表格直观来看，就是如何找出第一个非空的数据。普通的做法可以通过IF嵌套判断，比如在PowerQuery中可以这样添加自定义列：

第一个非空数据=
if [2020]=null and [2021]=null and [2022]=null 
then [2023]
    else if [2020]=null and [2021]=null
    then [2022]
        else if [2020]=null
        then [2021]
            else [2020]

还有一种更简单的写法：

=[2020]??[2021]??[2022]??[2023]

  双问号??是一种非空运算符，它会尝试返回符号前面的数据，如果前面的是空值，则返回后面的数据。

如果要同时获得首年的年份（非空列名），可以添加以下自定义列：

1.6 合并/拆分列

1.6.1 合并列

  在PQ中选择需要合并的列，比如把[区域]和[城市]合并。只需要在「转换」中找到”合并列“，弹出合并列窗口进行设置即可：

1.6.2 模糊匹配

  假设有两个表，分别是各省市2018和2019年的数据，两个年度的省份名称规则不一致，2019年全称，而2018年是简称。

  这个问题用Excel公式或者M函数也能找到解决方案，更简单的方法则是直接使用Power Query的模糊匹配功能。将这两个表格导入到PowerBI中，进入Power Query编辑器，点击合并查询，以省份为关联列，联结种类选择为左外部，最重要的是在联结种类下面，勾选 “使用模糊匹配执行合并” 。

点击确定，并展开合并列，竟然都是空值：

在上面勾选模糊匹配时，你应该能注意到，下面还有个模糊匹配选项，打开后发现还有这些参数可以设置：

1. 相似性阈值：默认0.8，但是上面的数据，相似度最高才0.67（比如"北京"和"北京市")，所以默认匹配时全部没有匹配成功。而"新疆"和"新疆维吾尔自治区"的相似度更低，只有0.25，所以为了都能匹配成功，我们把这个阈值调到0.25，就OK了。
   
2. 最大匹配数：如果不填，会把所有匹配行找出来。可以根据需要，想匹配出来几行就填写数字几。
3. 转换表：可以通过查询中的另外一个表作为转换表进行匹配，在转换表中，可以提前定义好，不规范值和规范值的对应关系，相当于同义词表，特定场景下非常有用。

  另外，不要因为有模糊匹配功能，就可以对源数据不加约束，依然应该尽量规范你的数据源，能保持一致最好。模糊匹配一方面是计算量特别大，另外既然是模糊查找，就很可能会有误差，当数据量比较大时，这种数据误差还很难被识别出来。

1.6.3 拆分列

  拆分相当于是合并列的反动作，不过功能更丰富。点击转换->拆分列，可以按字符数分隔，也可以按分隔符分隔。如果列中包含多个分隔符，还可以选择按哪个位置的分隔符来分隔。比如把刚才的合并列再拆分一下，又变成合并前的格式了：

  如果没有分割符怎么办？在Power query中还可以按从数字到非数字的转换来分列，或者按大小写字母的转换来进行分列：

1.6.4 分列到行&多种分隔符进行分列

  有时候数据都挤在一个单元格里，直接拆分到不同的列使用起来很不方便。在PQ中，还可以直接将某一列的内容拆分到不同行中（左下图）。

  如果有多种分割符，无论选择哪个都没法直接分开（右上图）。这时候，仅靠界面功能就难以正确分列了，这里我们就需要用M函数来完成。直接添加步骤，编辑框中输入：

=Table.SplitColumn(
      提升的标题, "区号",                 
      Splitter.SplitTextByAnyDelimiter(
           {
   "，","；","-","+","。"}, 
            QuoteStyle.Csv
        )
)

其中提升的标题是上一个步骤的名称，使用时要更改为实际的步骤名，字符替换为实际数据的分隔符

  看起来有点长，其实主要是使用了Splitter.SplitTextByAnyDelimiter函数，并把所有的分隔符做成一个列表，并将其作为该函数的第一个参数:

  关于分列，主要是找出数据排列的规律，是有固定的分隔符、有固定的字符数，还是有规律的从数字到文本等等，找到规律以后，就按规律进行拆分就可以了。

1.6.5 连续分隔符分割

  在复现《PowerQuery常见的5个问题》配图时，我将上图发给kimi，没有其它指示，结果识别成了下面这种格式，复制到excel中只有一列。

Content    Name                Data    Item                        Kind                Hidden
Binary     sheet1              Table   sheet1                      Sheet              FALSE
Binary     _xlnm._FilterDatabase Table   sheet1_xlnm._FilterDatabase DefinedName      TRUE
Binary     sheet1              Table   sheet1                      Sheet              FALSE
Binary     sheet1              Table   sheet1                      Sheet              FALSE

  加载到powerbi后，显示如下。如果直接选择按空格分割，因为存在连续空格，会分割成十几列（包含空格列）。

  powerbi中并没有将多个分隔符视为单个的选项，所以只能使用M公式，添加自定义列的方式将多个空格转为1个。

= Table.AddColumn(更改的类型, "自定义", each Text.Combine(
       	   List.RemoveItems(
           Text.Split([Column1], " "),//文本按空格拆分为列表
           {
   ""}						  //使用RemoveItems函数移除列表中的空项
       ),
       " "							  //Combine函数以单个空格分隔符合并列表，其实这时候你可以改成任意分隔符
   ))

后面删除原列，正常拆分自定义列，提升标题就行。

1.7 分组统计

1.7.1 数值汇总

相当于Excel中的分类汇总功能，比如刚才的数据，我们要计算各区域1月份的合计金额，点击转换->分组依据：

1.7.2 文本汇总

1. 分组：对名称列，点击转换>分组依据，在弹出的窗口中，选择按类型分组，操作是“所有行”
   
2. 修改M公式：分组后先不展开，将原公式

Table.Group(源,{
   "类型"},{
   {
   "名称", each _, type table [类型=nullable text, 名称=nullable text]}})

改为：

Table.Group(源, {
   "类型"}, {
   {
   "名称", each Text.Combine([名称],","), type table [类型=nullable text, 名称=nullable text]}})

  如果不想在合并后的文本中出现重复值，只需要名称字段外面套一个List.Distinct就可以了，上面的Text.Combine表达式改成：

Text.Combine( List.Distinct( [名称] ),"," )

  另外，使用CONCATENATEX函数，只需要写一个度量值就可以直接实现这个需求，详见《用一个度量值返回列表》

名称列表 =
CONCATENATEX(
    '示例数据',
    [名称],
    ","
)

1.8 提取

  PQ的提取功能可以按照长度、首字符、尾字符、范围等来提取，比如下面这个例子，提取前2个字符：

1.9 行列转置（日期不见了）

比如下面这个表，直接行列转置会发现标题“月份”不见了。

  这是因为转置的时候，只转数据的部分，月份并不在数据区，我们要想保留月份，先要把月份标题降下来，即”将标题作为第一行“，然后再转置。

1.10 行列操作

1.11 逆透视（二维表->一维表）

  由于数据分析的需要，常常需要将二维表变成一维表，在Excel中需要很多操作步骤才能完成，而通过逆透视功能，可以一键降为一维表。

1.11.1 一维表和二维表

在Excel中常见的是二维表，你可能天天都在用：

而一维表是长这样的：

简单来说：

• 一维表的每一列就是一个维度，列名就是该列值的共同属性

• 一维表的每一行就是一条独立的记录

  二维表更符合我们日常的阅读习惯，信息更浓缩，适合展示分析结果，但作为源数据进行数据分析时，就需要一维表。一维表的每一列是一个独立的维度，列名或者字段名就是数据分析的基础，比如利用列名与其他表建立关系；编写DAX时直接使用列名；数据可视化时直接把字段拖入到某个属性框中，等等。

1.11.2 简单情形

  比如，需要把下表中三个产业结构的数据转化为一个字段，只需要选中地区和年度列（按住“Shift”键可选择多个相邻的列，按住“Ctrl”键可选择非相邻列 ），右键单击，选择逆透视其他列即可（如果列名不是第一行数据，而是类似column1,column2...，需要先提升标题）：

1.11.3 行标题带有层级结构

  将上表导入到PowerQuery编辑器后，先把年度列向下填充，将年度数据补齐，然后再进行逆透视：

1.11.4 列标题带有层级结构的

这种表格可以先转置，转置以后，就是第二种情形，然后再进行逆透视就可以了。