【数据分析】Power BI的使用教程-2 Power Query

2024-10-28 07:08:34

Power BI的数据处理是通过Power Query完成的。

2.1 Power Query编辑器

在Power BI Desktop中，如果还没有任何数据，点击“获取数据”，选择相应的数据格式导入后，就可以进入Power Query编辑器。
在这里插入图片描述

在这里插入图片描述
点击“转换数据”进入Power Query编辑器：
在这里插入图片描述
Power Query编辑器：
在这里插入图片描述
在Power Query编辑器中对数据处理完成后，点击左上角的“关闭并应用”将数据导入Power BI。

2.2 Power Query的优点

  1. 操作简单：无须掌握复杂的函数，仅使用界面上的功能即可完成大部分数据处理工作。
  2. 数据量不限：突破传统Excel数据行的限制。
  3. 自动化：处理过程全记录，每次数据源更新后刷新即可，无须重复操作。

2.3 获取数据

Power BI数据分析的第一步是获取数据，Power BI支持几乎任何来源的任何结构、任何形式的数据。
在这里插入图片描述
Power Query不仅能从本地获取数据，还能从网页抓取数据，比如实时抓取股票涨跌、外汇牌价等交易数据。

以从中国银行网站上抓取外汇牌价信息为例，点击“获取数据”，选择“Web”：
在这里插入图片描述
输入URL：https://www.boc.cn/sourcedb/whpj/
在这里插入图片描述
点击“确定”，等待Power BI获取网页数据，选择需要分析的表格数据并点击“转换数据”进入Power Query编辑器：
在这里插入图片描述

2.4 数据清洗的常用操作

在Power Query中对导入的数据进行整理的过程一般称为“数据清洗”。

2.4.1 提升标题

在Excel中第一行为标题行，从第二行开始才是数据，但在Power Query中，从第一行开始就需要是数据记录，标题在数据之上。一般情况下，Power Query会自动完成提升这个步骤，如果没有，或者需要手动设置时，单击功能栏的“将第一行用作标题”就可以了：
在这里插入图片描述
注：单击“将第一行用作标题”旁边的下拉按钮，还有一个“将标题作为第一行”选项，实际上就是拉低标题，这个功能也特别有用。

2.4.2 更改数据类型

更改数据类型对数据分析也是极其重要的，有两种方式更改数据的数据类型：

方法1：
在这里插入图片描述
方法2：
在这里插入图片描述

2.4.3 删除错误/空值

导入后的数据，有可能出现错误（Error）或者空值（null），根据分析的需要，若要删掉错误和空值，可以通过右键单击该字段选择“删除错误”，或通过单击“筛选”按钮去掉相应勾选来完成。

方式1：

在这里插入图片描述

方式2：

在这里插入图片描述

2.4.4 删除重复项

选中需要删除的列，右键单击后选择“删除重复项”。

在这里插入图片描述

2.4.5 填充

在Excel数据中使用合并单元格的合并的单元格，导入到Power Query后其余合并的单元格会变成空值。

在Power Query中可使用向下填充将数据补全：

在这里插入图片描述

2.4.6 合并列

在Power Query中选择需要合并的列，在菜单栏中单击“合并列”，弹出合并列窗口，可以设置合并列之间的分隔符。

在这里插入图片描述

2.4.7 拆分列

拆分列即合并列的逆操作，可以根据按字符数、数字或字母来分列，如果列中包含多个分隔符，还可以选择按哪个位置的分隔符来拆分。

在这里插入图片描述

2.4.8 分组

分组即对明细数据进行汇总统计。如对所有行进行计数：
在这里插入图片描述

在这里插入图片描述

2.4.9 提取

Power Query可以按照长度、首字符、尾字符、范围等来提取字符。

在这里插入图片描述

2.4.10 行列转置

数据处理中有时需要行列互相转换，直接点击行列转置实现。

在这里插入图片描述
一般直接转置会导致数据丢失，因此需先“将标题作为第一行”，再进行转置，最后记得再“将第一行作为标题”。

在这里插入图片描述

2.4.11 行列操作

Power Query的行列操作十分灵活，非常适合大规模数据操作，包括选择列、删除列、保留行和删除行：

选择列

2.4.12 逆透视列

Power Query中的逆透视功能，能够一键将二维表变为一维表。

可以选中需要逆透视的列，单击“逆透视列”，或者选中不需要逆透视的列，单击“逆透视其他列”。

在这里插入图片描述

2.4.13 透视列

Power Query中的透视功能，能够一键将一维表变为二维表。

选中需要透视的列，单击“透视列”，并选择相应的值列。
在这里插入图片描述

2.4.14 添加列

Power Query中添加列有添加重复列、索引列、条件列、自定义列、示例中的列等形式。

在这里插入图片描述

  1. 添加重复列
    添加重复列即复制选中的某一列，以便对该列的数据进行处理而不破坏原数据。
    在这里插入图片描述
  2. 添加索引列
    索引列即为每行增加一个序号，记录每一行所在的位置，可以选择从0或1开始。
    在这里插入图片描述
  3. 添加条件列
    添加条件列可根据指定的条件，返回特定的值存放在条件列中。这个条件相当于Excel中的IF函数。
    在这里插入图片描述
    在这里插入图片描述
    4.添加自定义列
    自定义列即用M函数生成新的一列。
    在这里插入图片描述5. 添加示例中的列
    添加示例中的列相当于Excel中的智能填充，单击“示例中的列”，可以选择按所选内容或者按所有列来分析，在弹出的窗口中，只要输入前两行数据，系统根据两个示例，自动分析所需要的数据，并填充到所有行。
    在这里插入图片描述

2.4.15 追加查询

追加查询是在现有记录的基础上，在下方添加新的行数据，它是一种纵向合并。

例如有两个表格式相同，需要合并为一个表，单击“追加查询”。

在这里插入图片描述

2.4.16 合并查询

合并查询即横向合并，它相当于Excel的VLOOKUP函数，用于匹配其他表格中的数据。

例如有两张表，一张是每个员工的调动数据表，另外一个表是包含这些员工基础信息表。现在需要在员工基础信息表中添加每个员工对应的调动情况。
在这里插入图片描述
在这里插入图片描述

点击“合并查询”，先选择两个表相互匹配的字段，点击这两个表的“姓名”列，根据需要选择联结种类。

在这里插入图片描述

合并查询匹配过来的是一个表，所以每行都显示为Table，为了得到某一列，可以单击右上角的展开，选择需要的字段以完成合并查询。可以观察到表中增加了每个员工对应的调动情况。
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
通过这种方式可以一次性匹配多列，在展开时，选择需要的列就可以了，而VLOOKUP函数一次只能匹配一列。

2.5 二维表转一维表

二维表更符合人们日常的阅读习惯，信息更浓缩，展现效果更好，但作为源数据进行数据分析时，一维表更合适。

以下是一个二维表的示例：
在这里插入图片描述

而一维表每一列是一个维度，列名就是该列值的共同属性；每一行是一条独立的记录。

以下是一个一维表的示例：

在这里插入图片描述

根据二维表结构的不同，转换成一维表的方式也有所不同。

2.5.1 行列标题均为单层的二维表

直接使用Power Query中的逆透视功能。

选中“季度”列，点击“逆透视列”下的“逆透视其他列”。

在这里插入图片描述

注：透视/逆透视等操作，生成的结果表的列名，需自己手动更改。

2.5.2 行标题有多层的二维表

行标题带有层级结构的二维表，有两层行标题，如下表所示：

在这里插入图片描述
将该表导入Power Query编辑器后，合并单元格的内容会显示为null。

在这里插入图片描述

首先把年度列向下填充，将年度数据补齐：
在这里插入图片描述

接着选中“年度”和“季度”列，点击“逆透视其他列”，完成一维表的转换。

在这里插入图片描述

2.5.3 列标题有多层的二维表

列标题带有层级结构的二维表，有两层列标题，如下表所示：

在这里插入图片描述

将该表导入Power Query编辑器后，点击“转置”，即可将这种类型的二维表转换为行标题有多层的二维表类型，接着“将第一行用作标题”，选中第一列，“逆透视其他列”即可。

在这里插入图片描述

在这里插入图片描述

2.5.4 行、列标题均有多层的二维表

行标题和列标题均带有层级结构，如下表所示：

在这里插入图片描述
将该表导入Power Query编辑器：

在这里插入图片描述

  1. 将“年度”列向下填充：
    在这里插入图片描述
  2. 将“年度”列和“季度”列合并，生成年度季度列：
    在这里插入图片描述
    “将标题作为第一行”：
    在这里插入图片描述

此时，该二维表已转换为列标题有多层的二维表。

  1. “转置”表，并“将第一行用作标题”，“替换值”，就转换成了行标题有多层的二维表：
    在这里插入图片描述

  2. 选中前两列，逆透视其他列，就转换成了一维表：

在这里插入图片描述
5. 为了和源数据维度一致，将年度季度列进行分列，得到最终的一维表：
在这里插入图片描述

2.6 M函数

Power Query中的函数称为M函数，可实现更复杂的操作。

数据清洗的每一个步骤，都是使用M函数实现的，只是Power Query将一些常用操作简化成了键鼠操作。

打开菜单栏视图→高级编辑器，可查看M函数实现的数据清洗的每一步操作：

在这里插入图片描述

2.6.1 M函数的基本规范

  1. 严格区分大小写，每一个字母必须按函数规范书写，第一个字母都是大写的。
  2. 表被称为Table，每行的内容是一个Record，每列的内容是一个List。
  3. 行标是大括号{}，比如取第一行的内容：=表{0}；列标用中括号[]，比如提取自定义列的内容：=表[自定义]。取第一行自定义列的内容：=表{0}[自定义]。

注：Power Query的第一行从0开始。

2.6.2 常用的M函数

  1. 聚合函数
    • 求和：List.Sum()
    • 求最小值：List.Min()
    • 求最大值：List.Max()
    • 求平均值：List.Average()
  2. 文本函数
    • 求文本长度：Text.Length()
    • 去文本空格：Text.Trim()
    • 取前n个字符：Text.Start()
    • 取后n个字符：Text.End()
    • 移除文本：Text.Remove()
    • 提取文本：Text.Select()
提取字符类型 M函数
提取数字 Text.Select([文本数据],{“0”…“9”})
提取大写英文字符 Text.Select([文本数据],{“A”…“Z”})
提取小写英文字符 Text.Select([文本数据],{“a”…“z”})
提取全部英文字符 Text.Select([文本数据],{“A”…“z”})
提取全部中文字符 Text.Select([文本数据],{“一”…“龟”})

Text.Remove()函数同样可以使用上表，不同的是结果变为移除这些字符。

  1. 提取数据函数
    • 从Excel表中提取数据：Excel.Workbook()
    • 从Csv/Txt中提取数据：Csv.Document()
  2. 条件函数
    • If then else（相当于Excel中的IF函数)

2.7 分列技巧

2.7.1 常规分列

最常见的是有固定分隔符的规范数据，这种数据按照分隔符拆分即可。

在这里插入图片描述
若没有分隔符，还可以考虑“按字符数”、“按位置”、“按照从小写到大写的转换”、“按照从大写到小写的转换”、“按照从数字到非数字的转换”、“按照从非数字到数字的转换”。

高级选项中还能将一个单元格中的内容拆分为多行。

2.7.2 多种分隔符

如果分隔符有多种，那么可以使用M函数进行分列：

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

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

2.8 批量汇总

批量汇总即将多个表的内容汇总到一个表中。

2.8.1 批量汇总多表数据

一个工作薄有多张工作表：

在这里插入图片描述

  1. 将该工作薄导入Power BI，选择任意一个工作表，单击“转换数据”：
    在这里插入图片描述

  2. 在Power Query编辑器窗口中，在右侧的步骤栏删掉“源”之后的所有步骤：
    在这里插入图片描述

  3. 筛选Name列，选择需要合并的工作表，如果不选择，就是合并所有的工作表数据。
    在这里插入图片描述

  4. 展开Data列的数据，得到合并所有工作表的效果：
    在这里插入图片描述
    在这里插入图片描述

  5. 对数据表进行相应的数据处理，得到最终的表格：
    加粗样式

2.8.2 批量合并多工作簿数据

  1. 将需要合并的多个工作簿放在同一个文件夹中：
    在这里插入图片描述

  2. 在Power BI中选择“获取数据”→“全部”→“文件夹”，选择文件夹所在位置：
    在这里插入图片描述

  3. 在预览界面中点击“转换数据”：
    在这里插入图片描述

  4. 进入Power Query编辑器，添加自定义列=Excel.Workbook([Content])：
    在这里插入图片描述
    Excel.Workbook是一个M函数，用于将Power Query导入数据自动生成的二进制工作表转换成可读的table文件。

  5. 展开自定义列，所有Excel工作簿的每个工作表都会显示出来：
    在这里插入图片描述
    展开之后，会有一列是所有工作簿中的工作表列表，如果只想合并部分工作表，可以在这里通过筛选实现，如果不做任何筛选，就是合并每一个工作簿中的所有数据。

  6. 展开Data列，把合并的数据展示出来：
    在这里插入图片描述

  7. 经过数据整理，得到最终的数据表：
    在这里插入图片描述

上一篇：webpack4 - 动态导入文件 dynamic-import 报错的解决方法-介绍


下一篇：Kubernetes 组件、工作流程