探索 Excel 的获取和转换功能

执行摘要

在这个数据湖和 PB 级数据库的时代，我仍然以 CSV、文本和 Excel 文件形式接收数据的频率令人震惊。 尽管现代分析侧重于机器学习算法的前沿进展，但数据分析的日常苦差事仍然是查找、编译和整理不同数据类型的手动过程。

对于财务分析师来说，数据通常以 Excel 电子表格的形式出现，但同样经常的是，它是将数据转储到 CSV 或查询到 SQL 数据库。 有时，数据排列在混乱的布局中，或者没有所有必要的分析组件。 花在清理这些数据上的时间对分析师来说是浪费的宝贵时间，但有时这项任务被认为是必须容忍的。

获取和转换做什么？

这个常见问题的解决方案实际上很容易获得：Excel 和 Power BI 拥有一整套很少有用户知道的数据转换工具，名为 Get & Transform（以前称为 Power Query）。 使用其嵌入式提取、转换和加载 (ETL) 功能，金融分析师能够无缝链接到他们的数据源并更快地获得洞察力。

当我们将数据加载到 Excel 或 Power BI 中时，我们通常必须对数据执行一些转换。 数据操作的一些示例包括：

• 删除列，
• 过滤数据，
• 分组数据，
• 透视/反透视数据，
• 将字符串拆分为子字符串，
• 从字符串中提取关键字，
• 附加来自另一个表的行，以及
• 连接两个维度表。

在下图中，我们看到 Get & Transform 执行了在加载数据之前对数据进行预处理的繁琐任务。

为什么要使用 Get & Transform？

为什么值得学习如何使用 Get & Transform？ 好吧，当我查看我个人使用此功能的目的时，它为我提供了一组可塑的工具：

• 将整个文本文件文件夹加载到单个数据表中
• 将导出的会计文件转换为易于理解的布局
• 将数百万个销售行直接加载到 Power Pivot
• 将每日数据分组为可管理的每月结果，然后将其导入 Excel
• 通过加入匹配的列来拼接来自另一个表的数据

通常，当我收到新数据时，我会在将其加载到 Power Pivot 之前使用 Get & Transform 对其进行探索。 这让我可以看到可能需要进行哪些转换，并快速对数据执行一些数据透视和分组，以制定分析框架。 很多时候，在这个阶段，我会发现自己需要更多的数据，或者说有数据问题。 通过使用基于 Excel 的平台，我可以快速迭代我的数据源以查找这些数据异常。

最终，是留在 Excel 中还是将数据分析转移到另一个平台将取决于受众以及分析的可重复性和分布。 如果我的客户只使用 Excel，那么我几乎总是使用 Get & Transform 加载数据，使用 Power Pivot 执行分析，使用 Excel 生成数据透视表和图表。 对于客户来说，这将是无缝的，因为它都包含在 Excel 中。

但是，如果我的客户：

1. 想使用另一个可视化工具，
2. 有多个用户将刷新数据，或者
3. 需要使用机器学习模型，

然后我将仅使用 Get & Transform 进行初始数据探索，然后将繁重的工作转移到 R 中。

如何在 Excel 或 Power BI 中访问获取和转换

在早期版本的 Excel 中，Power Query 是一个可以安装以帮助使用 ETL 功能的加载项。 但是，在 Excel 2016 和 Power BI 中，这些工具的集成更加紧密。 在 Excel 2016 中，可以通过“数据”选项卡和“获取和转换数据”部分访问它们。

在 Power BI 中，该功能位于“主页”选项卡的“外部数据”部分中。

在本文中，我的示例在 Power BI 中进行，但界面几乎与 Excel 相同。 当它们出现时，我会指出它们之间的差异，因此本教程应该对两种类型的用户都有意义。

1. 加载 CSV 文件

为了协助本教程，我为一家销售户外装备和服装的虚构零售商创建了一些销售数据示例。 在每个示例中，数据将以不同的方式生成，以演示数据转储的实际方法。

作为一个初始示例，我们将看到数据以大型数据转储到 CSV 文件的形式呈现。 复杂的因素是数据以代表不同商店的多列呈现。 理想情况下，我们希望将数据导入并转换为更可用的布局。

下面是原始 CSV 的截图：

我们为什么要改变这一点？ 利用这些应用程序中可能存在的关系功能。 我们将在讨论中看到这一点。

现在，让我们假设我们需要将数据视为“更窄和更高”的结构，而不是“更宽和更短”的结构。 第一步是加载 CSV； 然后，我们将开始“反透视”数据。

如您所见，数据的最终结构比初始数据要窄，而且要长得多。 另一点是，当我们单击不同的操作时，右侧的工具正在生成用于构建查询的应用步骤列表。 重要的是要了解这是在后台进行的，因为稍后将对其进行重新访问。

在大多数情况下，Power BI 和 Excel 之间的 Get & Transform 外观和行为相似。 但是，在 Excel 中，单击Close and Load后，会出现一个附加提示。 在下图中，我们可以在是否要加载数据之间切换：

1. Excel中的表格，
2. 针对数据创建的数据透视表，
3. 针对数据创建的数据透视图，或
4. “只创建一个连接。”

此外，我们还可以选择是否将此数据添加到数据模型中。 选中此框会将数据加载到 Power Pivot 表中。 如果我们要分析 Power Pivot 中的数据，我建议选择“仅创建连接” ，然后确保选中“将此数据添加到数据模型”选项。 如果数据在 Excel 行限制内，并且我们更喜欢在 Excel 中进行分析，那么只需选择Table 。

在下一个剪辑中，我们将看到我们将数据格式化为长而细的原因是为了我们不仅可以按商店分析销售额，还可以按地区和州分析销售额。 为了完成这个任务，我们将导入一个将每个商店映射到一个区域和状态的表。 我们将在下面看到，我们可以快速创建按这些不同分组显示销售额的报告。

您可以想象 Excel 或 Power BI 中的这种类型的数据转换功能如何强大地应用于我们具有动态数据分组的任何情况，例如：

• 将每日数据汇总为周、月和季度；
• 将销售人员分组到部门和区域； 要么
• 将 SKU 映射到产品类型。

虽然本文介绍了 CSV 和其他 Excel 文件，但 Get & Transform 处理了广泛的数据类型。 创建查询后，它可以随着数据的变化而随着时间的推移而刷新。

2.处理文本字符串

为了演示 Get & Transform 处理字符串的能力，我创建了另一个数据集，该数据集模拟了一个文本文件，显示了来自公司总账 (GL) 的会计交易。

请注意帐号和名称如何出现在同一个字符串中？ 在 Power BI 中，我们可以毫不费力地将帐号和名称解析为单独的字段。

在此视频中，您可以看到，在我拆分列后，该工具猜测 Account 字段的新左侧应该是一个数字，并创建了一个“Changed Type1”步骤。 由于我们最终希望此字段为字符串，因此我们可以继续并在应用的步骤下手动删除该步骤。

接下来，我们采用相同的数据并创建一个会计科目表，其中包含到科目类别的映射。

为什么我们要通过所有这些步骤来映射一些帐号？ 一个真正的总账可以是数百甚至数千个账户。 正如我们所展示的，这个快速映射查询无需额外工作即可扩展到该级别。

3. 使用不同的数据源

Get & Transform 支持许多不同的数据源。 虽然不是一个详尽的列表，但以下是一些示例：

就个人而言，我只尝试了上述列表中大约一半的连接。 我使用的每个连接器都相当坚固。 我无需大量工作即可从原始数据获得洞察力。 同样重要的是，它充当不同数据源之间的验证器，确保最终输出具有标准化的质量控制水平。

4. 使用 M 语言个性化代码

在后台，每次我们单击工具中的按钮或进行选择时，Get & Transform 都会生成代码。 下面是一个示例，说明您将如何访问我们创建的帐户映射查询的代码：

该代码使用一种名为 M 的函数式语言，它会为基本用例自动生成。 但是，对于更复杂的数据整理，我们可以编辑和编写自己的代码。 在大多数情况下，我只会对这段代码进行微小的修改。 在更复杂的转换中，我可能会从头开始编写大部分代码来暂存临时表，或者执行更复杂的连接。

Get & Transform 的极限

当您尝试导出超过一百万行时，Excel 往往会达到其极限。 在我使用 Get & Transform 转换了数百万行的情况下，发送未分组行的唯一方法是通过乏味的 hack 或解决方法。 我还发现 Get & Transform 查询在部署到多个用户时可能不稳定，尤其是在您使用多个数据源和联接的情况下。 在这些情况下，我将始终使用 R 来部署可复制的数据整理。 最后，Excel 不是为更高级的数据建模而构建的。 您可以非常快速地执行线性回归，但除此之外，您将需要使用更严格的平台。

说了这么多，我发现 Excel 是我的大多数客户最喜欢的。 Excel 仍然是金融分析师武器库中最重要的工具。 通过合并 Get & Transform 功能，Excel 和 Power BI 通过它们可以接受的数据源范围变得更加强大。