作者都是各自领域经过审查的专家,并撰写他们有经验的主题. 我们所有的内容都经过同行评审,并由同一领域的Toptal专家验证.
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, 数据排列在一个令人困惑的布局中,或者没有分析所需的所有组件. 对分析人员来说,花在清理这些数据上的时间是宝贵的浪费, 然而,有时这项任务被认为是一种必须容忍的邪恶.
这个常见问题的解决方案实际上很容易找到:Excel和Power BI有一整套数据转换工具,但很少有用户意识到这一点, named Get & Transform (前身为Power Query). Using its embedded extract, transform, ETL功能使金融分析师能够无缝地链接到他们的数据源,并更快地获得见解.
当我们将数据加载到Excel或Power BI时, 我们通常必须对数据执行一些转换. 数据操作的一些例子包括:
在下面的图表中,我们看到Get & Transform在加载数据之前执行这个繁琐的预处理任务.
为什么值得学习如何使用Get & Transform? Well, 当我看到我个人使用这个功能的时候, 它为我提供了一套灵活的工具:
通常,当我接收到新数据时,我会使用Get对其进行探索 & 在将其加载到Power Pivot之前进行变换. 这使我能够看到可能需要哪些转换,并快速对数据执行一些支点和分组,以形成一个分析框架. 在很多情况下,在这个阶段,我会发现我需要更多的数据,或者有数据问题. 通过使用基于excel的平台, 我可以快速地迭代我的数据源来发现这些数据异常.
最终,决定留在Excel还是 将数据分析转移到另一个平台 将取决于受众和分析的可重复性和分布. 如果我的客户只使用Excel,那么我几乎总是使用Get & Transform to load the data, Power Pivot来执行分析, 和Excel来生成数据透视表和图表. 对客户来说,这将是无缝的,因为它都被安置在Excel中. But with Get & 基本上消除了转换、Excel变通和手动格式化.
However, if my client:
Then I will use Get & 转换仅用于初始数据探索,然后将繁重的工作转移到 R.
在以前版本的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几乎一模一样. 当它们出现时,我会指出它们之间的区别,所以教程应该对这两种类型的用户都有意义.
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,还有一个额外的提示. 在下面的图中,我们可以切换是否希望将数据加载到:
此外,我们还可以选择是否这样做 将此数据添加到数据模型中. 选中此框将数据加载到 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:
虽然本文讨论的是CSV和其他Excel文件,但Get & Transform处理各种各样的数据类型. 一旦创建了查询,就可以随着数据的变化对其进行刷新.
In order to demonstrate Get & Transform操作字符串的能力, 我创建了另一个数据集,它模仿了显示公司总账(GL)中会计交易的文本文件。.
请注意帐号和名称是如何出现在同一个字符串中的? 在Power BI中,我们可以毫不费力地将帐号和名称解析到单独的字段中.
In this video, 你可以看到,在我把这列分开之后, 该工具猜测Account字段的新左侧应该是一个数字, 它创建了一个" Changed Type1 "步骤. 因为我们最终希望这个字段是一个字符串, 我们可以在应用的步骤下手动删除该步骤.
接下来,我们用同样的数据 create a chart of accounts 具有到帐户类别的映射.
为什么我们要经历所有这些步骤来映射几个账号? 一个真正的总账可能有数百甚至数千个账户. 正如我们所展示的,这个快速映射查询可以扩展到这个级别,而不需要额外的工作.
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, 它充当不同数据源之间的验证器, 确保最终输出具有标准化的质量控制水平.
In the background, Get & 每当我们点击工具中的按钮或进行选择时,Transform都会生成代码. 下面是如何访问我们创建的帐户映射查询代码的示例:
代码使用了函数式语言 M,它为基本用例自动生成. 但是,对于更复杂的数据争用,我们可以编辑并编写自己的代码. 在大多数情况下,我只会对这段代码做一些小的修改. 在更复杂的转换中,我可能从头开始编写大部分代码 temporary tables,或执行更复杂的操作 joins.
当您尝试导出超过一百万行时,Excel往往会达到其极限. 在使用Get转换数百万行的情况下 & 转换时,输出未分组行的唯一方法是通过繁琐的hack或变通方法. I have also found that Get & 转换查询部署到多个用户时可能不稳定, 特别是在使用多个数据源和连接时. 在这些情况下,我总是使用R来部署可重复的数据争用. 最后,Excel不是为更高级的数据建模而构建的. 你可以很快地进行线性回归, but beyond that, 你需要使用一个更严格的平台.
说了这么多,我发现Excel是我的大多数客户最熟悉的. 在金融分析师的武器库中,Excel仍然是最重要的工具. 通过合并Get的功能 & Transform, 通过可接受的数据源范围,Excel和Power BI变得更加强大.
Extract, transform, 加载是将数据从不同的数据源移动到集中式数据仓库的过程. 对如何在Excel中转换数据感兴趣的分析师可以使用Get & Transform的嵌入式ETL功能.
Power BI是微软的一款商业分析软件. 它为可视化数据和创建自动报告和仪表板提供了强大的功能.
数据湖是组织内所有数据源的单一存储库. 这可能包括结构化和非结构化的数据, 最终可以在需要的时候提取和处理.
Located in Park City, UT, United States
Member since September 20, 2016
Ellen在固定收益交易和投资组合管理方面的丰富经验使她成为分析和建模方面无与伦比的专家.
世界级的文章,每周发一次.
世界级的文章,每周发一次.
Join the Toptal® community.