Executive Summary

In this age of data lakes and petabyte-scale databases, 令人震惊的是，我仍然频繁地收到CSV格式的数据, text, and Excel files. 而现代分析则关注机器学习算法的前沿进展, the day-to-day drudgery of data analysis 仍然需要手工查找、编译和整理不同的数据类型吗.

For the financial analyst, 数据通常以Excel电子表格的形式出现, but just as often, 它是一个数据转储到CSV或查询到 SQL database. Sometimes, 数据排列在一个令人困惑的布局中，或者没有分析所需的所有组件. 对分析人员来说，花在清理这些数据上的时间是宝贵的浪费, 然而，有时这项任务被认为是一种必须容忍的邪恶.

What Does Get & Transform Do?

这个常见问题的解决方案实际上很容易找到:Excel和Power BI有一整套数据转换工具，但很少有用户意识到这一点, named Get & Transform (前身为Power Query). Using its embedded extract, transform, ETL功能使金融分析师能够无缝地链接到他们的数据源，并更快地获得见解.

当我们将数据加载到Excel或Power BI时, 我们通常必须对数据执行一些转换. 数据操作的一些例子包括:

• Removing columns,
• Filtering the data,
• Grouping the data,
• 对数据进行旋转/反旋转，
• 将字符串拆分为子字符串，
• 从字符串中提取关键字，
• 从另一个表追加行，以及
• Joining two dimension tables.

在下面的图表中，我们看到Get & Transform在加载数据之前执行这个繁琐的预处理任务.

Why Should You Use Get & Transform?

为什么值得学习如何使用Get & Transform? Well, 当我看到我个人使用这个功能的时候, 它为我提供了一套灵活的工具:

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

通常，当我接收到新数据时，我会使用Get对其进行探索 & 在将其加载到Power Pivot之前进行变换. 这使我能够看到可能需要哪些转换，并快速对数据执行一些支点和分组，以形成一个分析框架. 在很多情况下，在这个阶段，我会发现我需要更多的数据，或者有数据问题. 通过使用基于excel的平台, 我可以快速地迭代我的数据源来发现这些数据异常.

最终，决定留在Excel还是 将数据分析转移到另一个平台 将取决于受众和分析的可重复性和分布. 如果我的客户只使用Excel，那么我几乎总是使用Get & Transform to load the data, Power Pivot来执行分析, 和Excel来生成数据透视表和图表. 对客户来说，这将是无缝的，因为它都被安置在Excel中. But with Get & 基本上消除了转换、Excel变通和手动格式化.

However, if my client:

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

Then I will use Get & 转换仅用于初始数据探索，然后将繁重的工作转移到 R.

How to Access Get & 在Excel或Power BI中转换

在以前版本的Excel中，Power Query是一个可以安装的插件 ETL functions. 然而，在Excel 2016和Power BI中，这些工具更加紧密地集成在一起. 在Excel 2016中，可以通过 Data tab, and then the Get & Transform Data section.

在Power BI中，该功能存在于 Home tab, in the External Data section.

In this article, 我的例子发生在Power BI中, 但界面与Excel几乎一模一样. 当它们出现时，我会指出它们之间的区别，所以教程应该对这两种类型的用户都有意义.

1. Loading CSV Files

To assist this tutorial, 我为一个虚构的销售户外装备和服装的零售商创建了几个销售数据示例. In each of these examples, 数据将以不同的方式生成，以演示实际的数据转储方法.

作为一个初始示例，我们将看到数据作为大数据转储到CSV文件中. 复杂的因素是，数据用多个列表示不同的存储. 理想情况下，我们希望导入数据并将其转换为更可用的布局.

下面是原始CSV文件的截图:

我们为什么要改变这个? 利用这些应用程序中可能的关系功能. 我们将在进一步的讨论中看到这一点.

For now, 让我们假设我们需要将数据视为“更窄更高”的结构, 而不是“又宽又短”的. The first step is to load the CSV; then, we will start to “unpivot” the data.

如您所见，数据的最终结构比初始数据窄，并且长得多. Another point is that, 当我们点击不同的动作时, 右侧的工具生成用于构建查询的应用步骤列表. 重要的是要理解这是在后台进行的，因为它将在稍后重新访问.

Get & 在大多数情况下，转换在Power BI和Excel之间的外观和行为相似. 但是，在Excel中，点击后 Close and Load，还有一个额外的提示. 在下面的图中，我们可以切换是否希望将数据加载到:

1. A table in Excel,
2. 根据数据创建的数据透视表;
3. 根据数据创建的数据透视图，或
4. “Only Create a Connection.”

此外，我们还可以选择是否这样做 将此数据添加到数据模型中. 选中此框将数据加载到 Power Pivot table. 如果我们要分析Power Pivot中的数据，我建议选择 Only Create a Connection 然后确保 将此数据添加到数据模型中 option is selected. 如果数据在Excel行限制内, 我们更喜欢在Excel中进行分析, then just choose Table.

In the next clip, 我们将看到，将数据格式化为长而细的原因是，我们不仅能够按商店，而且能够按地区和州分析销售情况. 为了完成这项任务，我们将导入一个表，将每个商店映射到一个地区和州. 我们将在下面看到，我们可以快速创建按这些不同分组显示销售额的报告.

您可以想象这种类型的功能在Excel中的数据转换, or Power BI, 可以有效地应用于任何情况下，我们有动态分组的数据, such as:

• 将每日数据按周、月和季度进行汇总;
• Grouping sales personnel into departments and regions; or
• Mapping SKUs to product types.

虽然本文讨论的是CSV和其他Excel文件，但Get & Transform处理各种各样的数据类型. 一旦创建了查询，就可以随着数据的变化对其进行刷新.

2. Handling Text Strings

In order to demonstrate Get & Transform操作字符串的能力, 我创建了另一个数据集，它模仿了显示公司总账(GL)中会计交易的文本文件。.

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

In this video, 你可以看到，在我把这列分开之后, 该工具猜测Account字段的新左侧应该是一个数字, 它创建了一个" Changed Type1 "步骤. 因为我们最终希望这个字段是一个字符串, 我们可以在应用的步骤下手动删除该步骤.

接下来，我们用同样的数据 create a chart of accounts 具有到帐户类别的映射.

为什么我们要经历所有这些步骤来映射几个账号? 一个真正的总账可能有数百甚至数千个账户. 正如我们所展示的，这个快速映射查询可以扩展到这个级别，而不需要额外的工作.

3. 使用不同的数据源

Get & Transform支持许多不同的数据源. 虽然不是一个详尽的列表，但下面是一些例子:

就我个人而言，我只尝试了上面列表中大约一半的连接. Each of the connectors I have used has been fairly robust; I have gotten from raw data to insights without a burdensome amount of work. Equally importantly, 它充当不同数据源之间的验证器, 确保最终输出具有标准化的质量控制水平.

4. 用M语言个性化代码

In the background, Get & 每当我们点击工具中的按钮或进行选择时，Transform都会生成代码. 下面是如何访问我们创建的帐户映射查询代码的示例:

代码使用了函数式语言 M，它为基本用例自动生成. 但是，对于更复杂的数据争用，我们可以编辑并编写自己的代码. 在大多数情况下，我只会对这段代码做一些小的修改. 在更复杂的转换中，我可能从头开始编写大部分代码 temporary tables，或执行更复杂的操作 joins.

The Limits of Get & Transform

当您尝试导出超过一百万行时，Excel往往会达到其极限. 在使用Get转换数百万行的情况下 & 转换时，输出未分组行的唯一方法是通过繁琐的hack或变通方法. I have also found that Get & 转换查询部署到多个用户时可能不稳定, 特别是在使用多个数据源和连接时. 在这些情况下，我总是使用R来部署可重复的数据争用. 最后，Excel不是为更高级的数据建模而构建的. 你可以很快地进行线性回归, but beyond that, 你需要使用一个更严格的平台.

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