Power Query 真经 - 第 5 章 - 从平面文件导入数据

作为一名数据专家，日常工作很可能都是在使用数据之前对其进行导入、操作和转换。可悲的是，许多人都没有机会接触到拥有精心策划过的数据的大数据库。相反，被不断地喂食 “TXT” 或 “CSV” 文件，并且在开始分析之前，必须经历将它们导入到 Excel 或 Power BI 解决方案的过程。对用户来说，重要的商业信息往往是以以下格式存储或发送给用户的。

1. “文本” 文件（以字符分隔）。
2. “CSV” 文件（以逗号分隔）。

这其实是目前的常态，意味着大量的手工导入和清洗过程，但 Power Query 将改变了这种现状。

5.1 了解系统如何导入数据

“TXT” 和 “CSV” 文件是平常所说的 “平面” 文件，之所以这样命名是因为它们缺少一个称为 “架构（Schema）” 的元数据层，即描述文件内容的信息。这一点至关重要，因为这意味着当数据被导入到另一个程序（如 Excel 或 Power BI ）中时，必须对其进行解析。为了真正掌握使用 Power Query 导入数据，需要清楚默认情况下会发生什么，以及应该如何（以及何时）控制和修改默认设置。

【注意】 虽然 “TXT” 和 “CSV” 文件肯定不是唯一存在的平面文件格式，但它们是迄今为止最常见的平面文件格式。作为一条经验法则，任何表示单个数据 “Sheet” 的文件通常都是平面文件。

（译者注：一个保存在 Excel 文件中的表，通常也被归为平面数据文件，该表又被俗称为：大平表。）

5.1.1 设置系统默认值

需要理解的第一件事是，当从平面文件中导入数据时，工具会按照【Windows 控制面板】中包含的设置进行处理。为了确定（和更改）用户当前的区域设置，需要到【Windows 用户界面】的进行配置，以获得期望的设置。

1. 按 Win 键进入控制面板，单击【控制面板】。
2. 如果【控制面板】【查看方式】是【类别】视图，则单击【更改日期、时间或数字格式】。
3. 如果【控制面板】【查看方式】是【图标】视图，则单击【区域】。

这将启动【区域】面板，用户在这里可以查看（和更改）系统默认值，如图 5-1 所示。

图 5-1 【Windows 控制面板】中的 “区域” 界面

如果期望各种应用程序中对默认日期格式的显示是”yyyy-MM-dd“的用减号连接的方式，则需要在这里进行配置。

用户可以在这里了解整个计算机系统内容是所使用的默认值。在这里将会看到，系统被设置为以 ISO 格式【yyyy-MM-dd】显示有效日期，而不是加拿大默认的标准日期【dd-MM-yyyy】，也不是美国默认的【MM-dd-yyyy】。此外，还对负数格式进行了自定义设置。在欧洲，我们使用逗号的小数点会显示为句号。

【警告】 与 Excel 不同，Power Query 是区分大小写的。MM 用于表示月，mm 用于表示分钟。

这里需要认识到的重要一点是，这些设置是针对本机电脑的，当在 Power Query 中为一个列声明数据类型时，将看到的是，格式将基于用户【控制面板】设置。即使用户建立了解决方案并将其发送给其他人，这也是正确的，他们将看到他们系统中的格式。

现在知道了这些设置的控制位置，来看看为什么在使用 Power Query 时这很重要。

5.1.2 程序如何解析平面数据

程序在解析数据时，需要知道如下三件事。

1. 数据点是否由单个字符、一组字符或一致的宽度分隔。
2. 一个完整的记录和另一个完整的记录是由什么字符或字符列分隔的。
3. 每个单独的数据单元的数据类型是什么。

平面文件的问题在于，文件中没有包含定义这些内容的信息。因此，导入程序必须做出一些分析，以试图获得正确的结果。虽然大多数程序在处理前两点方面做得很好，但推断数据类型却经常出现问题。

例如，考虑这个数据值：1/8/18

假设这是一个日期，这可能是公平的，但具体是哪一天呢？是 2018 年 1 月 8 日，2018 年 8 月 1 日，2001 年 8 月 18 日，甚至是其他什么？答案完全取决于程序导出到文件中的内容，基于编码导出功能的工程师是如何编写的。如果程序员是美国人，那几乎就是肯定是 2018 年 1 月 8 日。但如果他们是欧洲人，那很可能是 2018 年 8 月 1 日。如果程序员决定从用户的 Windows 区域设置中读取首选的日期格式，它几乎可能是任何东西。

这一点非常重要的原因是，文件中没有元数据来告诉用户这到底是哪种格式，所以程序在导入数据时进行了猜测。而它将试图应用的默认值是用户在 Windows 区域设置中设置的。

【注意】 问问自己，是否曾经在 Excel 中打开一个 “CSV” 或 “文本” 文件，发现其中一半的日期是正确的，而另一半则显示为文本？如果有这样的经历，实际上已经看到过一半的日期是错误的，另一半是文本的数据。在这种情况下，每年只有 12 天可能是正确的，例如：1/1，2/2 ，3/3 ，等等。

来看一个数据集导入的具体例子，其中有以下假设。

1. 数据集被导出到一个文本文件，并使用【MM/dd/yy】格式。
2. 用户【控制面板】的【区域】设置使用的是【dd/MM/yyyy】的短日期格式。
3. 用户【控制面板】的【区域】设置使用 “.” 作为十进制分隔符和 “,” 作为数字千分位分隔符 。

简而言之，对于文件中的每个数据元素，程序将尝试应用数据类型，然后按照【控制面板】的【区域】设置中定义的默认值对导入的数据进行格式化，如图 5-2 所示。

图 5-2 从文本文件到 Excel ，一路上把日期变得一团糟

Raw Data：原始数据 Derived Values：提取出的数值 Formatted：已格式化状态

背后的真正算法显然要比这里所描述的复杂得多，但可以假设它遵循以下一般流程。

1. 该程序试图将 1/8/18 转换为一个使用【控制面板】中定义的【dd/MM/yyyy】格式的日期。这样就生成了一个日期序列号为 43313（自 1900 年 1 月 1 日以来的天数）的值。在 Excel 中，这个值将被放置在一个单元格中。
2. 程序试图用【dd/MM/yyyy】格式将 1/13/18 转换为一个日期，但由于没有 13 个月，它认为这不可能是一个日期。因此，它将该数据视为文本，并将其放置在一个单元格中。
3. 该程序试图将 45.67 转换为一个值。当转换成功后，该值被放置在一个单元格中。（如果转换不成功，它将被视为文本）。
4. 将对文件中的每个数据元素都会重复这个过程。

一旦所有的数据元素都被转化为数值，程序将对数据套用格式，根据【控制面板】的【区域】设置中定义的偏好来显示数据。

问题出在哪里？2018 年 1 月 8 日的数值，使用系统定义的【MM/dd/yy】格式导出为 1/8/18，被程序错误地解释为【控制面板】认为这个日期字符串应该是 2018 年 8 月 1 日。

而最糟糕的是：一旦它被解释并作为一个值存储在本机程序中，要更改它就太晚了。这一直是将 “TXT” 和 “CSV” 文件导入 Excel 的问题。这些数据很容易出错，人们甚至都认不出来。

日期在这方面的问题特别多。由于许多流行的数据库软件是由美国软件工程师编写的，他们通常以【MM/dd/yy】的格式输入数据，尽管美国是世界上唯一遵循这一日期标准的国家。这可能会给任何遵循不同标准的国家带来问题，例如：在加拿大，这是个令人难以置信的问题。大家开玩笑说，有两种类型的 IT 专家：一种是自豪的爱国者，他们将每个人的日期设置为【dd/MM/yy】，另一种是放弃本国的标准，将默认设置为【美国 (英语)】和【MM/dd/yy】。好玩的是，最大的挑战是，这两个 IT 专家可能在同一家公司工作，这意味着整个组织的设置是混合的。

同样重要的是，要认识到这不仅仅是一个影响日期的问题。它也影响到数字和货币，因为世界上不同的国家使用不同的货币指标和分隔符。而且随着世界经济的日益全球化，不一致的数据格式正在冲击着越来越多的数据，但这种数据也应该要能被工具处理。

Power Query 允许用户控制导入过程中发生的事情。虽然它将提供基于相同的经典导入逻辑的默认值（译者注：按照本机操作系统中的配置），但它确实允许用户重新配置这些步骤，并告诉它究竟如何正确解释数据。它不是通过用 “Changed Type” 的步骤来做到这一点，而是通过一个明确的【区域设置】来更改类型，允许用户定义数据来源的地域。

（译者注：通过指定数据源来自的地域，Power Query 会根据当地的默认规则来识别数据，而 “Changed Type” 是基于已经解析好的数据再更改数据格式，是两件不同的事。）

5.2 导入带分隔符的文件

导入带分隔符的文件，如 “CSV” 或带分隔符的 “TXT” 文件的过程是相当直接的，并且遵循基本的 ETL 过程：提取、转换和加载数据。事实上，已经在第 1 章中看到了这一点，但这次要导入一个具有数据的文件，这个文件有点挑战性。

5.2.1 源数据文件

将首先导入一个名为：“Ch05-Delimited.CSV” 的带逗号分隔符的平面文件。该文件中的数据，可用记事本打开，看起来如图 5-3 所示。

图 5-3 用逗号分隔的源数据

用户要问自己的第一个问题是这些日期是什么格式。在这个例子中，假设它们是【MM/dd/yy】的格式。那要怎么确定呢？Power Query 将扫描数据集的前 1000 行，看看它能找出什么规律。除此之外，还需要回到导出数据的程序中去，并进行一些测试，以弄清数据的来源。好消息是，一旦用户这样做了一次，通常可以依赖这样一个事实：每次使用同样的选项运行报告时，系统都会执行相同的操作。

此时会注意到，该文件还包含数字格式，这对欧洲的人来说将是一个挑战。它们不仅包含一个 “$” 字符，而且数值使用逗号作为千位数的分隔符，使用句号作为小数。

5.2.3 提取数据

在一个新的工作簿中，执行如下操作。

1. 创建一个新的查询，【自文件】【 CSV / 文本】。
2. 选择 “第 05 章 示例文件 \Ch05-Delimited.csv” 文件并【导入】。
3. 单击【转换数据】，进入 Power Query 编辑器。

此时数据预览应该显示类似如图 5-4 所示的内容。

图 5-4 导入带有分隔符的文件到 Power Query 编辑器中

【注意】 请记住，Power Query 会尝试解析数据类型，使用【控制面板】的【区域】设置来识别这些数据元素。读者显示的数据和数值可能与这里显示的不同。

这在不同系统上的显示可能不同，说明前面所述原理正在起作用，第一个日期是显示为 2008 年 12 月 1 日，还是别的什么？“Amount” 列中的数值是显示为数值、文本，还是错误？欢迎来到在 Power Query 中处理数据的挑战，因为对于不同的人来说，答案是不同的，这取决于用户【控制面板】中的设置。

5.2.4 错误的解析

在上面显示的预览中，可以看到日期已经被解释为日期，并且按照用户本机【控制面板】设置，以【yyyy-MM-dd】格式显示。这很好，但日期没有被正确解释。没有报错，它们只是解读错了，因为 2008 年 12 月 1 日被解释为 2012 年 1 月 8 日。

为了解决这个问题，需要对 “Changed Type” 步骤进行明确控制。删除现有的内容，并从头开始重新创建它，这样它就可以适用于世界上的任何人，无论他们的设置如何。

1. 删除 “Changed Type” 步骤（单击步骤名称左边的 “x”）。

【警告】 一旦 “Changed Type” 步骤被应用，数据类型已经被转换，不会再还原到数据原来的类型。为了覆盖 “Changed Type” 步骤，以便在导入时强制使用【区域设置】，必须删除这个步骤，或者在现有的 “Changed Type” 步骤之前插入一个新的步骤。

现在，在应用 “Promoted Headers” 骤之后，数据将恢复到它在为列定义任何数据类型之前的状态，如图 5-5 所示。

图 5-5 所有的都是文本，所以可以看到正在处理的内容

5.2.5 使用区域设置

此时，希望对 “Date” 列进行明确的控制，告诉 Power Query 如何解释日期并将其转换为正确的日期序列号。为了做到这一点，将在定义数据的原始区域设置的同时更改数据类型 。(换句话说，告诉 Power Query 用于生成这些数据的格式)。

1. 单击 “Date” 列顶部的【ABC】数据类型图标。
2. 选择【使用区域设置】（在菜单的底部）。

然后会出现【使用区域设置更改类型】的对话框，在这里可以指定 Power Query 关于数据的原始来源和格式，如图 5-6 所示。

图 5-6 这是一列以美国标准编码的日期

虽然第一个下拉菜单是相当明确的，但这个对话框令人困惑的部分是，【使用区域设置更改类型】总是以语言为先，国家为后。如果没有所希望的国家也不用担心，例如：无论选择【英语 (英国)】还是【英语 (澳大利亚)】，都会以【d/M/y】格式解释日期。相反，要担心在做出选择后出现的示例输入值会正确解释数据。

在这个数据样本的情况下，选择很容易。需要【英语 (美国)】，因为这是唯一遵循 “M/d/y” 标准的国家。

【注意】 英语区域的列表是巨大的，因为世界上几乎每个国家都有某种形式的英语。要想快速进入美国英语区域，请输入这个地区的首个英文字母，当然，前提是此时的 Power Query 正在英文环境下运行。

（译者注：由于使用了中文环境的 Power Query，无法通过键入首字母的方式快速选择区域。）

一旦单击【确定】选择，注意数据预览窗格现在按选择来解析 “Date”，如图 5-7 所示。

图 5-7 这些日期看起来更像 2008 年 12 月

接下来，要确保文件在被欧洲人刷新时能正确解释 “Amount” 列。这将再次要求在设置【使用区域设置】时转换该列。

1. 【使用区域设置】 更改 “Amount” 列的数据类型。
2. 将数据类型设置为【货币】。
3. 将地区设置为【英语 (加拿大)】。
4. 单击【确定】。

此时，该列头显示货币图标，并且数值被对齐到 Power Query 单元格的右侧，如图 5-8 所示。

图 5-8 应用第二个 “Changed Type with Locale” 步骤后的数据。

根据以上内容，有三个要点需要强调。

1. 每当添加一个 “Changed Type with Locale” 步骤时，将在【应用的步骤】列表中得到一个单独的步骤。它们不会被合并成一个步骤。
2. “Amount” 列的设置使用了与日期不同的国家设置。可以这样做的原因是，在这种情况下，选择拿大货币和美国货币没有区别。这个操作并不会改变货币数据本身，而是告诉 Power Query 如何读取像 $1,000.00 这样的文本并将其转换为一个合理的数值。
3. 数据集中的每一列都可以使用不同的【使用区域设置】进行设置，这使得用户在导入多地区数据时有了巨大的灵活性。

【注意】 记住，用【使用区域设置】转换的整个目标是告诉 Power Query 如何解释一个基于文本的值，并将文本转换为正确的数据类型。

【警告】 如果用户在一个日期和数字格式可能不一致的文化下或公司工作，强烈建议用户总是用【使用区域设置】来设置日期和货币数据类型。对于那些【控制面板】设置与数据完全一致的用户来说，这不会有什么影响，反之，使用区域设置的功能就很重要了。

此时，只剩下一列需要处理，那就是 “Account” 列。将它的类型设置为【整数】数据类型，并更新查询名称。

1. 将 “Account” 列的数据类型更改为【整数】数据类型。
2. 将查询名称改为 “Transactions”。

如你所见，在这个查询中总共有三个 “Changed Type” 的步骤，其中前两个具体定义了每一列的【使用区域设置】，如图 5-9 所示。

图 5-9 实现通用的查询

现在任何人都可以刷新这个查询了，只要重新配置 “Source” 步骤，是配好 “Ch05-Delimited.csv” 文件的路径，就可以刷新这个查询。

最后一步关闭并加载数据到用户选择的目的地。

【注意】 如果需要覆盖【使用区域设置】，可以选择在 Excel 工作簿或 Power BI 文件中这样做。在 Excel 中，进入【获取数据】 【查询选项】当前工作簿的【区域设置】，在那里定义【区域设置】。所有新的连接都将使用该【区域设置】作为默认值来创建。在 Power BI 桌面版中，需要进入【文件】【选项】【选项和设置】。Power BI 在全局或当前文件层面都有【区域设置】选项，这取决于用户希望讲更改应用到的范围。

5.3 导入无分隔符的文本文件

一旦习惯了使用【使用区域设置】，导入带分隔符的文件的过程就相当简单了。当然，有时原始数据可能会很看着乱，至少这个功能已经可以很好地将数据分成几列。另一方面，如果用户不得不导入和清洗无分隔符的文本文件，就知道这有多痛苦。它们通常有一些默认的名字，如 “ASCII.TXT”，并且基本上是一个字符一个字符地表示输出时应该是什么样子。这意味着它们含了各种疯狂的问题，包括（但不限于）以下几点。

1. 字符按位置对齐，而不是按字符分隔。
2. 不一致的对齐方式。
3. 非打印字符（如换行符等）。
4. 重复的标题行。

对于许多 Excel 专业人员来说，他们工作的一个主要部分就是将这些信息导入 Excel 并进行清理。而这些工作，实际和业务无关，也不能创造任何商业价值。

如果用户有过这样的经历，就会知道这个过程遵循如下的基本流程。

1. 通过【从文本 / CSV】将文件导入 Excel。
2. Excel 提供了一个很小的窗口中进行拆分列的功能。
3. 结果会被转入一个工作表中，再人工转换成一个 Excel 表格。
4. 需要对该表进行排序和筛选，以删除垃圾行。
5. 需要对列中的文本进行清洗和调整。

最重要的是，下个月当用户拿到新的数据文件时，还需要再次重复这个令人兴奋的过程。如果有一个可以重复的自动化方法该多好，而 Power Query 将一切完美实现。

5.3.1 连接到文件

连接到一个没有分隔符的文本文件的方式与其他文本文件的方式相同。

1. 创建一个新的查询，【获取数据】【自文件】【从文本 / CSV】。
2. 浏览：“第 05 章 示例文件 \GL Jan-Mar.TXT” 并【导入】 。
3. 单击【转换数据】。

这样做之后，会看到 Power Query 将数据放在一个单列中，如图 5-10 所示。

图 5-10 无分隔符文本文件的 Power Query 中的视图

【注意】 注意到一些行的末尾有 “...” 了吗？这表明该单元格中文本的数量超过了适合该单元格目前可以显示的数量。如果列太窄，只需将鼠标放在列标题的右侧，按住并将其拖宽。

【注意】 如果文字都挤在一起，可以到【视图】选项卡，确保勾选【等宽字体】和 【显示空白】的选项。在清洗这样的文件时，需要打开这些选项。

当用户扫描预览窗口时，会注意到该文件没有用任何一致的分隔符分隔，而且，正因为如此，Power Query 没有对数据进行任何猜测，在【应用的步骤】窗口中也只有 “Source” 这个步骤。它把整个后续转换过程留给了用户，鉴于这个文件的状态无法预处理，与其胡乱处理，不如留给用户定义。

在深入研究这个问题之前，应该注意到有很多方法来完成这个任务，没有一种方法是正确的或错误的。本章中的例子已被设计为通过用户界面展示了大量的转换，以及一个 Excel 专业人员可能处理这项任务的典型路线。随着经验的增加 ，会发现更快的路径到达最终目标，这是很可能的。

5.3.2 清洗无分隔符文件

当开始清理一个无分隔符文件时，第一件事是将数据转换成含有一列的表。在本例中，由于前 10 行没有什么价值，可以删除，从第 11 行开始才是表中的列数据。

（译者注：清洗无分隔符的文件，首先要观察规律，这个过程需要依赖于人脑对数据的理解和判断，工具无法识别规律，因此要靠用户一步步定义数据转换的步骤。）

1. 转到【主页】【删除行】【删除最前面几行】【行数】下面填写 “10”。

这些行被删除且不会被导入到最终的解决方案中，如图 5-11 所示。

图 5-11 删除顶部的行，使标题更接近顶部

接下来，需要选择一个方向来拆分这些数据。可以尝试从左边或右边切入，但目前有一大堆额外的前置空格和中间重复的空格。如果能去掉这些就更好了。

在 Excel 中，通过 TRIM 和 CLEAN 函数来清理文本数据是一种标准的做法，以便删除所有开头、尾部和重复的空格，以及去除所有非打印字符。Power Query 也有这个功能，所以现在就来应用这个功能吧。

1. 右键单击 “Column1”，选择【转换】菜单下的【修整】。
2. 右键单击 “Column1”，选择【转换】菜单下的【清除】。

数据看起来好多了，如图 5-12 所示。

图 5-12 【修整】和【清除】后的数据

Power Query 的【修整】功能与 Excel 的【修整】功能不太一样，Excel 的 TRIM 函数可以删除所有开头和结尾的空格，并将数据中间的任何重复的空格替换成一个空格，而 Power Query 的 TRIM 函数并不做最后这一部分。它只【修整】开头和结尾的空格。

Power Query 中的 CLEAN 函数功能与 Excel 中的 CLEAN 函数功能一样，只不过这个函数的运行难以看到视觉上的效果。在 Excel 的用户界面中，非打印字符被显示为一个方框中的小问号。在 Power Query 中，它们显示为一个空格。如果在 “Trimmed Text” 和 “Cleaned Text” 步骤之间来回切换，会看到 “Avis & Davis” 周围的空格已经被 “Cleaned Text” 步骤清理掉了。

5.3.3 按位置拆分列

下一步是开始拆分列。此时，基本的方法是按字符数进行拆分，对所需要的字符数做一个有根据的猜测，然后再完善这个猜测。由于日期中的字符数是 “10” 个，先尝试 “12” 个字符。

1. 转到【主页】【拆分列】【按字符数】，弹出的对话框中【字符数】下面填写 “12”，【拆分】下面选择【重复】【确定】。

这显然是行不通的，日期列可能没问题，但其他列肯定不行，如图 5-13 所示。

图 5-13 数据没有像预期的那样拆分

（译者注：第二列和第三列将一个整体的数字给拆散了，所以 12 个字符不合适，再试试更大的字符数。）

这不是问题，再试一次就可以了。

1. 删除 “Changed Type step” 步骤。
2. 单击 “Split Column by Position” 步骤旁边的齿轮。
3. 把【字符数】改为 “15”，单击【确定】 这样就好多了，结果如图 5-14 所示。

图 5-14 对数据进行了更有启发性的观察

【注意】 还值得一提的是，在拆分列时没有任何东西强迫用户在选项中选择【重复】设置。如果文件不一致，用户可以选择从左边 / 右边分割一次。这允许用户在每一列的基础上进行非常细粒度的控制。

现在可以再做两个更改。

由于 “Changed Type” 的步骤只是将所有的列声明为文本（当完成后，它们不应该是文本），所以可以删除 “Changed Type” 的步骤，因为它无关紧要。然后可以将第一行提升为列标题。

1. 删除 “Changed Type” 的步骤。
2. 转到【转换】选项卡，单击【将第一行用作标题】选择【将第一行用作标题】（另一个选项是【将标题用作第一行】）。

5.3.4 利用查询中的错误

数据现在看起来干净多了，即使想在操作过程中更改一些列标题。此时，通常建议用户从左到右清洗数据，依次确保它们都是有效的。

如果在这里向下滚动鼠标，会发现这个数据中有大量垃圾行，主要是来自文件中重复的列标题和分隔。出现这些问题的第一个位置是在第 40 行，并引入了一堆丑陋的东西，如图 5-15 所示。

图 5-15 不相关的行与真实数据混在一起

问题是如何处理这些数据。有些是日期，有些是文本，有些是空行。试试下面的操作。

1. 更改 “Tran Date” 列的数据类型，【使用区域设置】【日期】【英语 (美国)】【确定】。

立即可以看到，在 “Tran Date” 列的标题中弹出一个红色的条，向下滚动预览窗口，发现在 “Tran Date” 列中有一堆错误，如图 5-16 所示。

图 5-16 由于试图转换为日期而产生的错误

在第 3 章中，讨论了在假定所有错误都是无意义的错误的情况下，如何修复错误。但是没有提到的是，与其他程序不同的是，在 Power Query 中，错误是真正令人兴奋的，原因是用户可以控制它们，并对它们做出反应。

如果仔细观察这些数据，会发现 “Error” 只发生在那些恰好是用户无论如何都要筛选掉的行中。此外，每一条在 “Tran Date” 列中出现 “null” 的行都在后续的列中保持 “null” 值，这些列也是用户希望剔除掉的交易数据的一部分。所以，把这两个都去掉吧。

1. 选择 “Tran Date” 列转到【主页】选项卡【删除行】【删除错误】。
2. 选择 “Tran Date” 列，单击 “Tran Date” 列右边向下的小三角色，弹出的对话框中，取消勾选 “(null)”【确定】 。

结果相当令人开心，现在已经有了一个从上到下都是有效的日期的 “Tran Date” 列，如图 5-17 所示。

图 5-17 从上到下显示有效日期的 “Tran Date” 列

【警告】 如果用户的数据在第 42 行末尾出现了一行错误，那是因为用户以相反的顺序应用了最后两个步骤。在试图筛选某一列之前，处理该列中的错误是至关重要的。如果用户对一个包含错误的列应用筛选器，它将会截断数据集。

尽管已经取得了进展，但似乎有一些行还是有问题的。面临的挑战是，用户并不想筛选掉这些日期，因为其中有些日期可能有些天是有效的（嘿，Power Query 相当有用，但它能包含四位数的时间，并持续到 0123 年 3 月 1 日吗？）。

先看看后面的列，看看是否能在那里解决这些问题。

1. 双击 “Tran Date” 列，重命名为 “Date”。
2. 双击 “Tran Amount” 列，重命名为 “Amount”。
3. 更改 “Amount” 列的数据类型，【使用区域设置】【货币】【英语 (美国)】【确定】。

现在用户将会看到 Power Query 试图将 “Amount” 列所有数据设置为数值类型，但再次触发一些错误。经过检查，它们都是不需要的行，进行如下操作即可。

1. 选择 “Amount” 列，【主页】【删除行】【删除错误】。
2. 筛选 “Amount” 列，单击 “Amount” 列右边向下的小三角色，弹出的对话框中，取消选中 “(null)”【确定】。

而如果检查第 40 行左右（或者再往后）的数据集，将会发现所有的垃圾行都完全消失了。

5.3.5 删除垃圾列

删除多余的列是非常简单的，只是想在这样做的时候遵循一个过程，以确保它们确实是空的。这个过程很简单如下所示。

1. 筛选该列。
2. 确保筛选的列表中显示的所有值都是空白或空的。

或者，如果通过查看【视图】选项卡打开了【列质量】和【列分发】功能。那么用户将会在列的标题中得到一个的图表。如果列中存在不同的值，用户可以在预览中看到，以预知是在加载数据时会得到的内容，如图 5-18 所示。

图 5-18 一个不同的值，但没有空值，是这样吗

在这一列的情况下，可以看到，虽然只有一个值，但它没有填充空单元格。由于这个文件充满了空格，并根据宽度进行分割，每个单元格都包含 15 个空格（可以通过单击单元格并在左下方的值预览中选择字符来确认）。这并不是真正的空，但它是一致的和不需要的。

检查数据集中的每一列，可以看到第 3 列（有一个空白的标题）似乎只包含空白值。那这一列可以删除。

同样地，如果滚动到窗口的右边，“Column9” 列只保留了 “(null)” 值。这样的列也可以删除的。

1. 选择第 3 列，按 DEL 键。
2. 选择列 “Column9”，按 DEL 键。

5.3.6 合并列

此时，很明显最初对列的分割有点激进。似乎有四列被错误地分开了，如图 5-19 所示。

图 5-19 列被错误的拆分

幸运的是，这里并没有失去一切，当然不需要回最初的位置去重新开始。只需要把它们重新组合起来。

1. 选择 “Reference Infor” 列，按住 Shift 键，选择 “Column8”。
2. 右击其中一个列的标题，单击【合并列】。

然后，可以选择使用分隔符，并为（新）列提供一个新的名称。在这种情况下，不需要任何类型的分隔符。因为马上就要以不同的方式来分割这一列，所以名称在此时并不重要。

然后，您可以选择使用分隔符并为 (新) 列提供一个新名称。在这种情况下，我们不需要任何类型的分隔符。因为我们马上要把这一列分成不同的部分，名字对我们来说并不重要。

1. 单击【确定】。

列就重新组合起来了，如图 5-20 所示。

图 5-20 处理的结果

5.3.7 通过分隔符拆分列

根据重新聚合的数据，很明显新的列是由 “-” 字符分隔的。要把它分成几个部分，需要考虑到的一件事是，不知道是否有供应商在他们的公司名称中使用了连字符，所以不希望在分割时过于激进。

1. 右击合并后的列（“已合并” 列），【拆分列】【按分隔符】。
2. 在【选择或输入分隔符】下面选【-- 自定义 --】，并输入一个 “-”（减号）。
3. 【拆分位置】选择【最左侧的分隔符】进行分割，单击【确定】。

【注意】 在按分隔符进行分隔时，并不局限于单个字符的分隔符。实际上，如果想按整个单词分隔，可以输入该单词作为分隔符。

然后，数据被分割成 2 个独立的列：“已合并.1” 和 “已合并.2”，把它们重新命名为更加合理名称。

1. 双击列 “已合并.1” 的名称，更改为 “Category”。
2. 双击列 “已合并.2” 的名称，更改为 “Vendor”。

这样就得到了一个几乎完美的数据集，如图 5-21 所示。

图 5-21 现在的数据集几乎是完美的

5.3.8 修剪重复的空格

在这个数据集中需要做的最后一件事是处理在 “Vendor” 列中的单词之间的重复空格。由于不能依靠 Power Query 的【修剪】功能，所以看起来必须自己来处理这个问题。

1. 右击 “Vendor” 列标题，【替换值】。
2. 将【要查找的值】设置为 2 个空格。
3. 将【替换为】设置为 1 个空格，单击【确定】。

现在有一个完全干净的数据集，可以加载到表中。

【注意】 不幸的是，没有一个简单的功能可以从文本字符串中删除内部的 “空白”。如果怀疑有一些实例有两个以上的空格，可能不得不多运行几次这个修剪过程，以便清洗数据完全。

终于到了可以最终确定查询并从中建立一个报告的时候了。当然，将通过创建一个【数据透视表】来做到这一点。

1. 将查询名称改为 “Transactions”。
2. 转到【主页】【关闭并上载至】【表】【新工作表】【确定】。

5.3.9 Power Query 的闪耀时刻

此时，应该暂停并认识到一些重要的事情。目前数据是干净的，与使用 Excel 的标准方法从文本文件中导入数据不同，不需要进一步清理。数据是在一个专用于该流程的用户界中面加载、清理和转换。现在所处的位置可以实际使用数据。

单击表格中的任何地方，选择插入一个新的【数据透视表】将其放在当前工作表的 G2 中。配置方法如下所示。

1. “Date” 在行上，按月分组。
2. 行上的 “Vendor”，按组排列。
3. 列放 “Category”。
4. 数值放 “Amount”。

完成以上操作后，【数据透视表】应该看起来如图 5-22 所示。

图 5-22 从文本文件构建的【数据透视表】

很多人会提出问题，到目前为止，本章中完成的所有工作都完全可以用标准的 Excel 来完成。那么为什么需要 Power Query 呢？是因为很大的预览窗口吗？这很好，但不是关键。

Power Query 之所以如此重要，是因为在处理数据的最后一部分时。下个季度来了，用户得到一个新的数据文件。在 Excel 专业人员的世界里，这意味着又是一个繁琐的下午，需要导入、清理和重新格式化。但有了 Power Query ，这一切都会改变。

1. 转到【获取数据】【数据源设置】。
2. 选择 【当前工作簿中的数据源】【更改源】【浏览】。
3. 更新文件路径为 “第 05 章 示例文件 \GL Apr-Jun.TXT”。
4. 单击【确定】【关闭】。
5. 转到【数据】【全部刷新】。

查询的输出将更新表格，但需要刷新下【数据透视表】。所以需要再次进行最后一步。

1. 单击【数据透视表】任意单元格，转到【数据】【刷新】。

【注意】 数据加载到数据模型（在 Excel 或 Power BI 中）只需要一次更新，就可以更新数据以及针对数据模型创建的所有透视表 / 图。

这就是使用 Power Query 的好处，此时结果如图 5-23 所示。

图 5-23 数据透视表更新为下一季度的数据

新的供应商，新的交易，新的日期，所有的工作都没有问题。这是革命性的，用户会想，如果没有它，是如何完成这些工作的。

【注意】 如果只是在旧文件上保存新文件，甚至不需要编辑 “Source” 文件步骤来更新文件路径。相反，用户只需要去【数据】选项卡，【刷新全部】【刷新】来更新解决方案。