Power BI 傻瓜入门 7. 清理、转换和加载数据

傻瓜入门 7. 清理、转换和加载数据"/>

Power BI 傻瓜入门 7. 清理、转换和加载数据

本章内容包括

• 根据异常、属性和数据质量问题确定清理需求
• 解决数据类型、值、键、结构和查询之间的不一致
• 在数据加载之前根据查询和命名约定对数据进行流化处理

要进行任何数据清理和转换，您的组织需要分析师、工程师和侦探。这里的想法是，在进入系统之前或在数据存在于其预期的数据存储中之后，必须首先分析数据。仅仅掩盖数据并不能解决问题。你需要遵循一个严格的过程，在数据的大海捞针。如果没有严格的流程，就无法确保所有列、值和键的数据一致性。通过遵循细致的分析过程，您可以设计优化的查询，帮助将数据加载到系统中而不会出现问题。本章通过评估整个生命周期以及Power BI专业人员必须进行的支持活动来帮助您开发该流程，以使他们的数据在可视化消费中大放异彩。

运用你的侦探技能来寻找异常和不一致

异常数据有多种形式。使用Power Query，您可以发现您可能正在关注的异常数据趋势——即使是那些您自己很难捕捉到的轻微模糊性。例如，您可以看到如何将断章取义的美元金额或错误追溯到扭曲数据结果的缺失值。这些都是您可以使用Power BI解决的真实场景。

发现错误最简单、最明显的方法是查看Power Query Editor中的表。可以使用“数据预览”功能来评估每列的质量。您可以在每列中查看标题值下的数据，以便验证数据、捕捉错误和发现空值。
您只需选择“查看”⇒ 数据预览⇒ Power Query主菜单中的列质量。在图7-1中，您立即注意到Agency列缺少数据，如报告为空的<1%数字所示。这种行为与数据异常一致。

在网上，在本章中，您可以使用FiscalAwards.xlsx文件进行练习，该文件可以加载到Power BI Desktop中。要访问该文件，请访问第五章的下载文件Power BI 傻瓜入门 5. 准备数据源-CSDN博客。

请注意，除Agency列外的所有列都具有100%的有效性。在这种情况下，<1%意味着您有一个空值或错误的数据。使用Power Query调查数据质量问题的目的最好通过这次采样来说明，因为所有其他列显示的错误百分比都为0。您将在本章的后面学习如何纠正此类歧义。

检查这些数据结构和列属性

评估数据超出了列质量。另一个可以用来更好地识别数据结构问题的度量涉及列值分布，它是对所选列中所有不同值的度量，以及每个值所代表的表中行的百分比。通过选择“视图”，可以在Power Query Editor中启用此测量 视图⇒ 数据预览⇒ 列分发。在图7-2中，请注意“总值”列具有大量不同和唯一的值。

以下是与众不同之处：

• Distinct number：当忽略重复项时，列中不同值的数目
• 唯一数字：精确出现一次的值的数量

通过使用“列分发”命令，可以确定每列中不同值和唯一值的数量。如前所述，值列的分发在列标题下可见。无论分析目标如何，列分析都可用于所有数据类型。

每一列显示数据的形状——比如值的分布，或者特定数据类型出现的频率。例如，2021年的价值最为明显，而2011年至2020年的价值在图表中按比例分布，如图7-2所示，在授予年份标题下。

提示：如果要评估Power BI和Power Query Editor之外的数据，请右键单击所选列，然后从显示的菜单中选择“复制值分布”。您将获得一个不同值的列表，以及数据在列中显示的次数。

记住，列分布是一个很有价值的实用程序：如果某些列提供的业务价值很小，则可以省略这些列。一个例子可能是，您有数量有限的不同值。在进行分析时删除列可以构建更强大的查询，因为您可以通过删除不需要的数据集来消除混乱。

从数据统计中找到一些帮助

统计数据有时可能是你最好的朋友，这就是为什么你想考虑使用它们来分析和了解数据的性质。
要启用统计信息的数据预览，请转到Power Query Editor，选择View⇒ 从主菜单中选择Data Preview，然后选中Column Quality和Column Profile复选框，如图7-3所示。

启用功能后，选择需要进一步统计分析的列标题。在图7-4中，您可以从标有“财政年度奖励”的Excel电子表格中找到“总价值”和“年度奖励”列的概况。请注意底部的“常规统计信息”面板，然后是各个列的统计信息。您的选择也不限于列配置文件和列质量。您还可以查看数据中的空白、单空格和列分布。

以下是可以评估的关键列统计信息：

• 总价值计数
• 错误数
• 空列
• 非重复列
• 唯一值
• 最小值、最大值和平均值
• 零值、奇数或偶数的数目

请记住，如果列有文本，则统计信息与数字列相比会有所不同。对于文本列，将突出显示空字符串和值的数量。相反，在数字列中，只能使用空值。

步入数据生命周期

数据很少是完美的。除非您连接到一个准备好的数据集，在那里您对为您创建的内容控制有限，否则在加载任何内容进行分析之前，您很有可能需要进行一些数据清理和数据转换。

Power BI提供了一个功能强大的工具，可以帮助您完成整个数据生命周期，强调数据清理和转换。这个工具就是Power Query。在Power Query中，用户可以使用Get命令和transform data命令提取、转换和加载（ETL）数据。在本书中，您将使用Power Query在连接到数据源时连接、转换、获取和评估可用数据。Power Query是Power BI中Power Query Editor背后的基础架构。

请记住，Power Query对Power BI来说并不是什么新鲜事。事实上，该产品也是Excel不可或缺的一部分。除了Power BI和Excel之外，其他产品还包括Power Query，它是使用低代码方法实现查询开发现代化的一种手段。

PowerQuery没有使用SQL，而是使用了另一种编程语言，称为M.M（混搭的缩写），这是一种函数式、区分大小写的查询公式语言。作为Power BI查询编辑器的一部分，该语言有助于在将数据加载到Power BI模型之前准备数据。M专门用于查询，DAX是数据分析表达式语言，是数据科学和分析专业人员用于深入分析的分析数据计算语言。与主要用于数据转换阶段的M不同，DAX用于数据视图阶段。后续的章节描述了DAX。

解决不一致

数据越多，就越需要注意不一致、意外值、空值和其他数据质量问题。在Power Query的帮助下，Power BI为用户提供了多种处理不一致的方法。其中包括替换值、删除行和完成根本原因分析。

替代值

用户可以直接在Power Query Editor界面中用所需结果替换错误的值。只要您创建或导入到Power BI的数据源中出现错误，就可以使用这种方法。这种行为的一个示例是用更新的唯一值替换null值。不过，使用这种技术时有一个问题：用户必须修复源中的错误，否则刷新期间的值可能会被重写。您可以通过右键单击一列，然后选择一个选项来访问您的选项，如图7-5所示。

要替换错误，请执行以下步骤：

1. 右键单击列标题，然后从Power Query Editor中显示的菜单中选择“替换错误”。
2. 在“值”框中输入要替换的值。
3. 单击“确定”。

替换列中的值遵循类似的过程，如图7-6所示。请执行以下步骤：

1. 右键单击列标题，然后从Power Query Editor中显示的菜单中选择“替换值”。
2. 在出现的新屏幕上，填写要查找的值和替换为字段。
3. 完成后，单击“确定”按钮。

提示：选择“替换值”菜单后，系统会提示您进行多次更新。在“高级”下，可以看到两个选项：“匹配整个单元格内容”和“使用特殊字符代码替换”。如果试图替换列中的文本，则需要匹配整个单元格的内容。如果启用“匹配整个单元格内容”选项，则“增强查询编辑器”不会替换“替换为”值限制为“要查找的值”值的值。此外，假设您希望替换一个独特的角色。在这种情况下，需要选中“使用特殊字符代码替换”复选框。否则，该值不会输入到框中。

技术: 如果要同时替换多列中的数据，必须按Control键（Ctrl），然后选择每个具有要替换的值的列。如果要按特定顺序选择一系列列，请按Shift键，然后按首选项的时间顺序选择每一列。
请记住，在替换字段中输入的数据类型必须在所有列中匹配，否则将出现错误。

使用Power Query删除行

有时，您会发现必须删除整行数据，因为这些行中的某些内容正在创建大量错误。要删除一行，您可以假设更正错误应该很简单，只需右键单击列并从上下文菜单中选择“删除错误”即可。使用此方法只删除存在已知错误的行。假设您希望删除表中符合可能导致错误的特定条件的所有行。在这种情况下，您可以单击列标题左侧的“表”图标，选择受影响的行，然后从显示的菜单中选择“删除错误”。

挖掘根本原因

每次列中出现错误时，您都可以查看错误背后的消息。要查看错误，请选择有问题的单元格。错误消息显示在页面底部的“预览”面板中。使用此方法，用户可以查看表、记录、列表中的各种内容类型，当然还有嵌入的错误。

图7-7显示了在将新的自定义列添加到数据集中后引入的错误。出现的问题是数据类型不匹配。文本字段和数字字段都不能创建典型的列值。事实证明，类型转换通常是触发错误消息的根本原因之一。

技术有时，您可能需要将列从一种类型转换为另一种类型（例如，从Text转换为Number）。在Power BI中，这被称为类型转换。大多数情况下，使用Power Query转换数据后，会立即进行类型转换更改。

评估和转换列数据类型

很少有数据源可以在黄金时段使用。您需要对它们进行整形，以便Power BI和Power Query Editor可以使用它们。（诚然，与结构化数据库系统相比，这种行为通常适用于文件，但这与重点无关。）在处理数据集时，需要在添加或减少行和列数据时添加查询步骤。即使在尝试转换列数据时，评估和转换数据可能是一个复杂的过程也不足为奇。在本节中，我将重点介绍有助于将数据转换为最纯粹可能状态的主题。

为联接查找和创建适当的键

Power BI通过多种不同的方式组合表中的数据来支持用户，但无论您选择哪种方式，都必须在查询中使用联接。（联接是组合多个表中数据的一种方式；它使用两个或多个表的公共键将这些表组合在一起。）使用Power Query Editor，可以使用“合并”功能完成此操作。如果要使用Power Query之外的模型创建关系，可以创建隐式联接。联接的使用取决于业务需求。

请记住，在众多的联接类型中，您最常听到的两种是隐式联接和显式联接。隐式联接执行与表字段的左外部联接，从另一个表中提取。显式联接指定两个表的集成。使用隐式联接有很多好处。语法是一个关键优势，因为它是显式联接语法的有用替代品。事实上，隐式联接可以出现在维护显式联接语法的同一查询中。

联接表需要设置一个条件。一个明确的标准是识别每个表中的键。如果您查看示例FiscalAwards.xlsx文件中的Primary和Sub Awards，您会发现扮演此角色的一个明显选择是每个表的Agency Key列。（尽管如果密钥的命名方式相同，数据通常会显得更干净，但这不是必须的。）

技术表可以由一个或多个联接语句表示。如果表表示在联接的一侧，则表中的键在每一行中都是唯一的。如果表由联接的Many面表示，则并非所有键都是唯一的，这会产生一些重复。正如您可能已经猜到的，“一侧”表示为主键，而“多侧”可以是外键。一对一（1:1）和多对多（M:M）关系有时确实存在；但是，Power Query中生成的结果可能不合适；一对一的关系可能会产生狭窄的结果集，而多对多往往会产生太多的结果。

记住数据建模时需要记住以下两个关键术语：

• 关系是数据模型中实体之间的连接，反过来又反映了业务规则。实体之间的关系可以是一对一、一对多或多对多。
• 联接有点不同，因为您要在两个或多个表之间建立关系来提取数据。数据通常使用主键、外键或组合映射在一起，称为组合键。

考虑以下与联接和关系相关的信息：

• 联接键：您可以一次基于一个或多个列执行联接。使用Power Query合并表不需要创建复合键。在Power Query中创建联接时，请特别注意列类型。必须将数据类型彼此匹配，否则联接将不起作用。
• 键的关系：Power BI将尽最大努力解决不同的数据类型，包括在可能的情况下转换数据类型。不过，理想情况下，在创建联接时，应该确保关系中的数据类型相同。

记住，Power BI只允许单列对上两个表之间的物理关系。这句话意味着，如果表中有一个复合键，则必须将键列组合成一列，才能创建必要的物理关系。您可以使用Power Query或在DAX的帮助下设置计算列来执行此任务。

可以通过两种不同的方式组合列：创建新列或在位合并列。若要添加新的合并列，请首先选择要合并的列，然后选择“添加”⇒ 列⇒来自文本⇒ 从Power Query Editor功能区合并列。如果希望就地合并列，则替换原始列。选择要合并的列，然后选择“转换”⇒ 文本列⇒ 从同一菜单中合并列。

无论你选择两个选项中的一个，最终的结果都是一样的。图7-8显示了Merge界面。您可以组合Prime Awards和Sub Awards中的一个或多个专栏。然后，选择联接的类型。结果是一个合并了这两列的新列。

该过程的最后一步是从“合并”界面上的“分隔符”下拉菜单中定义分隔符。您可以选择预定义的分隔符，也可以通过从菜单中选择“自定义”来创建自己的分隔符。如果选择后一种方法，则可以选择输入新列名。完成后，单击“确定”。

在本例中，冒号被用作分隔符。最后，新列称为Agency Sub-Agency，如图7-9所示。

调整列数据以满足Power Query要求

并非您摄取的每个数据源都可能具有正确的数据类型。Power Query会根据可用数据集中的特征尽最大努力检测数据类型。例如，您可能正在使用基于美国的邮政编码作为数据集的一部分。Power Query可能（错误地）将以零开头的邮政编码视为整数。结果，这些起始值被截断了。为什么？因为一个整数截断了零。在本例中，邮政编码应该是Text数据类型，而不是整数。

当您开始在列中评估数据时，请记住PowerQuery会尝试将它接收到的任何数据转换为图7-10中所示的数据类型之一。通过确保首先使用正确的数据类型，可以使Power Query保持在正确的轨道上。

记住，您经常会看到复杂的数据类型，如函数、列表、记录和表。请记住，并非所有数据类型在加载数据后都可用。

如果要更改数据类型，可以右键单击列标题，然后从菜单中选择“更改类型”。然后选择所需的类型，如图7-11所示。在列中更改数据类型一次后，您会看到一个提示，询问您是否同意更改列类型并插入步骤。图7-12显示了插入步骤的示例。

组合查询

在Power BI中，您可以通过以下两种方式之一使用Power Query组合查询。首先，您可以附加查询。这意味着将其他查询添加到现有的一组查询中，就好像在堆叠数据一样。在创建附加查询时，通常使用SQL的UNIONALL运算符等模式。另一方面，使用合并结构组合查询是基于提供的主键和外键。您需要使用Merge查询设置JOIN语句。

追加查询

你总是可以把桌子做得更高或更宽。追加时，表会更高。原因是您的查询包含相同数量的列。
在某些情况下，生成的表具有来自所有查询的列；在其他情况下，原始查询中不存在的列可能会填充到数据集中。在这种情况下，每一行都保留空值。

Power BI用户可以在其“原样”语句中附加查询，也可以创建一个新的查询来容纳聚合数据。若要附加查询，请在有一个或多个查询要选择时进行此选择。不需要构建新的查询——只需重用现有的查询即可。追加查询而不创建新查询是Power BI中的默认选择。

当您获取许多新的数据行并使用原始查询将它们串在一起时，您应该选择“将查询附加为新查询”。

要访问“附加查询为新查询”，请转到“增强查询编辑器”主功能区。然后选择“将查询附加为新查询”。然后要求您将两个或多个表中的行连接起来。选择表和行后，请按“确定”。

合并查询

合并查询时，将它们组合在一起，从而生成更宽的表。因为您继承了更多的列，所以水平增长是很自然的。关键的考虑因素是你使用哪一套钥匙。
这两个表中的列必须具有匹配的值，以确保一个表可以与第二个表的行组合。

就像附加查询一样，您有两个合并选项——创建一个新查询或合并两个查询并将其称为new。合并查询涉及使用Power Query创建六种联接类型中的一种，

Power Query合并查询的6大联接种类_51CTO博客_power query 多表合并查询

当您尝试使用其中一个查询时，您可能会意识到您的数据并不完美。为了减轻一些质量问题，Power Query在执行合并时支持模糊匹配。当您可以比较单独列表中的项目时，就会出现模糊匹配。如果有接近的匹配，则形成联接。您可以在建立模糊匹配时设置匹配容差和相似性阈值。下面的链接显示了模糊匹配选项包括描述的选项。

模糊合并 - Power Query | Microsoft Learn

要合并查询，请执行以下步骤：

1. 在“查询编辑器”功能区的“常用”选项卡上，找到“合并查询”。
2. 选择“合并查询”，而不是“将查询合并为新查询”。
3. 在“合并查询”界面中选择要合并的表和列。
4. 选择两个表共用的键。请注意，相应的键列高亮显示。
5. 从“联接类型”下拉列表中选择所需的联接类型。
6. 单击“确定”。

在图7-13中，您可以看到您正在合并Prime和Sub Awards查询。所选的公用键为“Obligated”。所选联接种类的类型为“完全外部”。

请注意，图7-14中出现了一个新列Sub Awards。每一行都高亮显示并显示“表”。要查看子奖项数据，您可以单击“表格”链接进行深入查看。合并两个表时，您可以

• 添加新表
• 用超链接表示表格
• 将双箭头按钮而不是“过滤器”按钮作为列的一部分

双箭头是另一种过滤器类型，允许用户从两个或多个表数据集中搜索数据。

在新列中选择任何单元格时，将显示合并表中包含的内容的预览。

展开表时，可以展开或聚合：

• 展开：在这里，您可以从合并表中选择要添加到当前表中的列。如果合并的表有多个匹配行，则当前表的行是重复的行。
• 聚合：如果您想在当前表中合并行而不重复，这是您的最佳选择。使用DAX，提供最适合每列的函数是确保数据正确组合的一种方法。

要使用“Fiscal Awards”数据集展开合并列，请执行以下步骤：

1. 在Prime Awards查询中，单击新建列中的双箭头按钮。您会看到一个屏幕，该屏幕允许您根据展开视图或聚合视图进行筛选。
2. 清除“展开的合并列”选项卡上的“选择所有列”复选框。
3. 单击以选中Agency复选框和Sub Agency复选框。
4. 取消选中“使用原始列名作为前缀”复选框。
5. 单击“确定”。现在，您应该看到一组扩展的列，显示刚刚合并的两个表的值。
6. 右键单击Agency列，然后选择Remove column。
7. 右键单击Agency.1列，然后选择Rename。
8. 将Agency.1列重命名为Agency。

所有这些变化后产生的结果如图7-15所示。只有一个Agency和一个Sub Agency栏，标签为“分机构”。1

无论您是从外部源将数据引入Power BI，还是在内部创建数据模型，都没有与列和查询命名约定相关的硬性规则。然而，这并不意味着你应该变得草率。恰恰相反！

当您创建用户友好的命名约定时，可以更容易地处理模型中的数据，并减少访问数据的用户的困惑。Microsoft强烈鼓励Power BI用户在命名表、列和度量值时遵循严格的协议。您希望应用一种不需要太多的命名约定，尤其是对于那些技术背景有限的人。

从数据库中检索数据并不罕见，因为该数据库可能带有不可取的命名约定。例如，如果字段Total Obligated Amount的名称为fin_Total_Obligations，则可能会造成一些混乱。
因此，在创建和管理数据模型时，将命名约定简化为有意义的命名约定非常重要。

调整Power Query的M代码

Power BI中的Power Query Editor功能非常强大。不过，在编写查询时，它也有其局限性。这就是人工干预的作用。编写M查询并不棘手。请记住，所有查询都是区分大小写的，并且都必须遵循Power Query代码引擎背后的规则。

记住，Power BI支持两种语言：M和DAX。这些语言有助于操作、筛选和分析数据。DAX用于在数据加载到数据视图模型后分析数据，而M用于在Power BI Power Query Editor中预处理数据。

要查看和编辑M查询，必须启动Power Query Advanced Editor。要执行此操作，请单击Ribbon的Home选项卡的Query区域中的Advanced Editor按钮。（见图7-16）。另一个选项是右键单击Queries窗格中的任何查询，然后从出现的菜单中选择Advanced Editor，如图7-17所示。无论您选择哪种方法，都会看到一个编辑器窗格，其中显示所选查询的M代码，如图7-18所示。

技术所有M查询都以let开头，以in结尾。在这两者之间，一个查询可以有多个步骤。使用let构造，您可以看到与查询步骤相对应的变量定义。

以下是let and in语句的示例：

• Let a = 2 + 2, (step 1)
  b = 4 + 4, (step 2)
  c = b + 6 (step 3)
• in a + b +c (final step)
   

第一次运行查询时，将创建一个引用。将引用视为查询的起点。如果您对查询进行了修改，Power BI会询问您是否要在查询中创建添加的步骤或另一个迭代。根据您查看查询步骤的方式，您会看到步骤的显示方式略有不同。如果查看“查询步骤”窗格，您可能会发现步骤名称，例如

• Source（源)
• Navigation（导航）
• Promoted Headers(提升的标题）
• Change Type（更改的类型）

查看“查询步骤”窗格时，请找到“应用的步骤”。每次从数据中的参考点进行转换时，都会在“应用的步骤”列表中找到这样的更改。Applied Steps有助于从一开始就跟踪数据中发生的转换次数。
与版本控制一样，您可以使用Applied Steps返回到以前的状态。在图7-18中，您可以看到具有Applied Steps的查询的历史上下文。 提示：如果你认为M编码可能有点笨拙，你还有另一种选择：你可以查看查询的代码，甚至可以通过使用标准公式栏的分步方法来操作公式。但是，首先必须启用它：转到Ribbon的“视图”选项卡，然后单击“公式栏”按钮，如图7-19所示。在“高级查询编辑器”中显示为查询的内容将在“公式”栏中复制。当您对查询进行更改时，您会注意到M query语言输出会实时更改。 在网上如果你想了解更多关于M查询语言及其所有语法功能的信息，请访问 Power Query M formula language reference - PowerQuery M | Microsoft Learn

配置数据加载查询

在开发Power BI数据模型时，用户可以利用Microsoft提供的使用帮助查询的功能。通过使用“获取数据”选项和“变换数据”选项处理模型，可以使用这些辅助工具。此外，当您尝试组合文件甚至合并数据集时，Power Query支持辅助查询。

记住Helper查询嵌入到Power BI Power Query中，以帮助用户创建查询字符串。您可以使用内置的API来简化查询开发中最困难的部分，而不是使编码过程变得复杂。事实上，辅助查询支持常用术语、短语、范围和地理空间功能。

当然，您可能有不需要或不想加载的查询，因为并非所有数据都有帮助。在这种情况下，右键单击“查询”窗格，然后清除“启用加载”部分。当查询已经加载时，可能会出现错误。否则，请选择要从加载过程中省略的查询。

当您不想加载附加或与其他查询合并的查询时，会出现一种常见的情况。要隔离不应包含的查询，请执行以下步骤：

1. 右键单击要省略的第一个查询。
2. 从“查询”窗格中显示的菜单中选择“启用加载”。
3. 请确保取消选择要从查询中省略的每个表。
4. 对每个不想加载的查询重复此过程。

其结果是从数据模型中删除不需要的实体，以便将来进行查询和加载。在图7-20中，您可以看到用于选择或取消选择EnableLoad的下拉菜单示例。任何被取消选择的查询都不会被加载，而是用斜体的文本进行标注。

修改了要包含在查询中的实体后，请按功能区“常用”选项卡上的“关闭并应用”按钮保存更改。
（见图7-21。）

解决数据导入过程中的错误

每隔一段时间，当你加载数据时，你可能会在Power BI中遇到查询错误。不要惊慌！

错误有多种形式。仅值不会导致查询明显失败。Power BI可以让您知道每个查询的错误总数。
错误值或查询过程中忽略的值被视为空值。简单地说，字段中没有文本，甚至没有零。

要了解Power Query中实际导致错误的原因，请使用“查看错误”超链接，该超链接可以在Power Query Editor列中找到，并引发指定的错误。单击超链接时，可以看到与查询相关的特定详细信息。Power Query中引发错误的常见原因通常与数据转换有关。例如，最初被视为文本的N/a值在用于数字的列中不起作用。

要更正此类错误，需要更改列类型。要进行这样的修改，请执行以下步骤：

1. 在Power Query Editor中，选择有问题的查询。
2. 右键单击显示错误的列。
3. 从出现的菜单中选择“更改类型”，然后将所选内容从“数字”更改为“文本”。
4. 当弹出窗口显示以验证是否要更改列数据类型时，选择“替换当前”。

现在，您已将列数据类型从“数值”更改为“文本”。现在，字母数字值，而不仅仅是数字值，可以添加到特定数据集的列中。单击已更正数据集的“关闭并应用”按钮后，错误消息将消失。