Power BI数据清洗实战

Power BI数据分析与可视化实践【行情 报价 价格 评测】-京东

Power BI Desktop的查询编辑器功能非常强大，我们继续通过一个实战案例来演示这些常用功能。我们手上有一个Excel文件，其中包含门店销售记录，文件内有两个表格，分别是一店和二店的销售数据，如图3-31所示。

图3-31

我们发现表格中有3行无意义的数据，同时需要将一店和二店的数据合并在一起。打开Power BI Desktop工具，从功能区的“开始”选项卡中单击“获取数据”下拉按钮，在下拉列表中选择“Excel工作簿”选项。随后，在弹出的“打开”对话框中选择第3章的案例源文件“门店销售记录.xlsx”。Power BI Desktop会在“导航器”对话框中展示一店和二店的数据表信息。在左侧窗格中选中一个表时，右侧窗格中会显示该数据表的数据预览，如图3-32所示。

图3-32

在将数据加载到Power BI Desktop中之前，我们先选中“一店”和“二店”，然后单击“编辑”按钮来调整数据。

首先，删除表格中的前3行无用数据。在“开始”功能区选项卡中，单击“删除行”按钮，选择“删除最前面几行”选项，将会打开如图3-33所示的对话框。在该对话框中，设置行数为3。对“一店”和“二店”表进行相同的操作。

图3-33

在“转换”功能区选项卡中，单击“将第一行用作标题”命令，如图3-34所示。“一店”和“二店”表都需如此操作。

图3-34

现在我们需要将“一店”和“二店”这两个相同结构的工作表进行合并。我们选中“一店”，单击“开始”功能区选项卡中的“追加查询”，追加“二店”表，如图3-35所示。需要注意的是，追加查询只能对结构相同、字段标题相同的表格进行合并，若表格结构不同，则可能导致错误发生。现在“一店”表已经包含两张表的内容，把“一店”表重命名为“销售记录”表。

图3-35

业务需求是了解产品在下单月份的销售金额汇总情况，不需要查看详细数据，只需要到产品分类这一级别即可。因此，我们还需要引入一张“产品分类”表。在“开始”功能区选项卡中，单击“新建源”，选择“Excel工作簿”数据源选项，如图3-36所示。

图3-36

选择案例Excel文件“产品分类.xlsx.”，在“导航器”对话框中，选择“产品分类”表，如图3-37所示，单击“确定”按钮。

图3-37

我们需要将多张表进行横向汇总（类似于Excel中VLookup函数的功能），即向“销售记录”表中添加“产品分类”这一字段。这可以通过“合并查询”功能实现。“合并查询”是指将一张表的新字段信息添加到另一张表中，前提是这两张表具有相同的字段属性。具体操作如下：选择“销售记录”表，然后在“开始”功能区选项卡中单击“合并查询”，在“要合并的表”下拉列表中选择“产品分类表”，并选择这两张表都具有的相同字段“产品名称”，如图3-38所示。这一“合并查询”功能在工作中非常实用，以往在Excel中通常通过VLookup函数来完成类似操作。

图3-38

在完成操作后，你会看到“销售记录”表的右侧新增了一个可扩展列。单击右上角的图标，会显示可扩展的列选项，此时只需勾选“产品分类”字段，如图3-39所示。勾选后，“产品分类”字段将被成功添加到“销售记录”表中。这一合并结果与在Excel中使用VLookup函数得到的结果一致，但无须手动编写公式，操作更为便捷。

图3-39

我们要了解每个产品分类在每个下单月份的销售金额情况。首先，我们需要从“销售记录”表中提取“下单日期”字段的月份信息。在“转换”功能区选项卡中，单击“日期”，选择“月份”，如图3-40所示。

图3-40

完成上述操作后，“下单日期”字段中的月份信息已被提取。接下来，我们将“下单日期”字段重命名为“月份”，如图3-41所示。

图3-41

如果要对表格进行分类汇总计算，可以使用“分组依据”功能。这一功能类似于创建数据透视表。在“转换”功能区选项卡中单击“分组依据”按钮，弹出“分组依据”对话框。在该对话框中选择要分组的字段，可以是一列或多列。如果是多列，可以通过单击加减号进行调整。这里我们以“产品分类”和“月份”为分组依据。分组统计后会生成一个新的统计列，我们将该列命名为“销售金额”，并选择“求和”操作，对“金额”列进行求和，如图3-42所示。

图3-42

如果需要统计每个产品类别在每个月的销售金额，可以使用“透视列”功能。在Power BI中，透视列功能可以将一维表转换为二维表，实现行转列的操作。具体操作为：选中“月份”这一列，单击“转换”功能区选项卡中的“透视列”，将“销售金额”设置为值列，并选择“求和”作为聚合值函数，如图3-43所示。

图3-43

透视列后的二维表结果如图3-44所示。

图3-44

如果我们要把二维表转换成一维表，就需要用到“逆透视”功能。在Power BI Desktop的查询编辑器中如何进行逆透视呢？如图3-45所示，选中“产品分类”列，然后单击“转换”功能区选项卡中的“逆透视其他列”命令。

图3-45

现在已经把二维表转换成一维表了，我们重新命名一下，如图3-46所示。你只需要记住逆透视就是把表中的列转换成值，而透视列则是把值转换成列。

图3-46

查询编辑器会记录我们对每个查询（表）的所有数据调整操作（这些操作称为“应用的步骤”），并将其保存为可查看或修改的文本，如图3-47所示。通过查询编辑器的应用步骤功能，我们可以修改之前的操作。例如，可以删除某个步骤，只需选择该步骤旁边的X按钮即可；还可以调整步骤的顺序，重新排列它们。当我们更新数据时，无须重复之前的调整操作，只需单击“刷新”按钮，查询编辑器就会自动按照保存的步骤顺序依次执行，完成数据更新。

图3-47

另外，使用高级编辑器可以查看或修改任何查询的文本。在“视图”功能区选项卡中选择“高级编辑器”时，高级编辑器会立即显示。这些查询代码是使用M语言编写的，如图3-48所示。

图3-48

如果你是高级用户，对于那些难以通过界面操作完成的数据清洗任务，完全可以摆脱界面的束缚，直接在高级编辑器中编写M语言代码。M语言的公式和函数库非常庞大且相对复杂，关于其函数语法的详细介绍，请参考随书赠送的资源。对于初学者而言，Power BI Desktop的查询编辑器与其他工具相比，大部分数据清洗任务仅需通过鼠标操作即可完成，整个清洗流程不仅可视化，还具备可复用性。

在完成数据清洗后，关闭Power Query编辑器并应用更改。由于我们只想将“销售记录”表加载到模型中，可以在“二店”表和“产品分类”表的属性中取消勾选“启用加载到报表”复选框，这样只有“销售记录表”会被加载到模型中，如图3-49所示。

最终，我们将清洗好的数据加载到Power BI Desktop中（如图3-50所示），为后续的数据建模和数据可视化操作奠定了基础。

  

图3-49  

    

图3-50

综上所述，Power BI数据整理具备以下能力：从各类数据源中提取和加载数据，以及利用“Power Query编辑器”对加载的数据进行清洗。Power BI的数据清洗功能，其原理是通过Power Query的M语言脚本对数据加载过程进行额外的预处理操作。

微软Power BI的Power Query编辑器是一个强大的数据整理和清洗工具，它通过友好的用户界面（UI）自动生成M语言脚本，能够将不规范甚至杂乱无章的数据表整理得井井有条，无须手动编写复杂代码。其核心优势在于帮助用户高效完成那些耗时且附加值较低的数据准备工作。当数据完成标准化和规范化后，用户可以更专注于使用Power BI进行建模分析和数据可视化。