Power Query 真经 - 第 7 章 - 常用数据转换

分析师面临的普遍问题是，无论从哪里获得数据，大部分情况都是一种不能立即使用的状态。因此，不仅需要时间把数据加载到文件中，还得花更多的时间来清洗它，改变它的结构，以便后续做分析的时候能更好的使用这个数据。

7.1 逆透视

考虑以下这个经典的 Excel 场景，用户需要每天跟踪销售情况，并以如图 7-1 所示的格式将数据发给分析师。

图 7-1 已经被透视过的数据

虽然已有报表，但用户希望做出不同的分析，问题是这些数据已经是 “数据透视表” 的形态，这正是数据分析中的典型常见问题。

构建数据透视表是为了快速获取数据表格，并将其转化为用户希望能够使用的报告。挑战在于，用户是以透视表的格式来思考问题的，而不是以表格格式来思考问题的，所以习惯于基于 “数据透视表” 格式来进一步构建后续分析，而不是以表格的形式来构建分析。

（译者注：透视表（PivotTable）和表格（Tabular）都是表（Table），由于中文词汇的相似性，导致对含义理解的混淆，表格（Tabular）形态的表又被俗称：一维表，但难以给出精确的定义描述，这里试着给出精确的定义，并称为标准表：以表形式存在的记录的列表。列表是同类记录的集合，作为列表中的行的记录，有同样的结构，结构由完全不同的属性构成。从透视表的表头来看，它并不满足属性不同的特质。值得注意的是：标准表往往具备一个重要的特性，那就是随着时间的推移，只会纵向伸展，而不会横向伸展，这源自其记录结构的稳定性，不会增加新的列。关于此的讨论已经超出本书的范畴，但这里给出以便读者可以对 “表” 这一概念有深度的思考，并对其不同结构，形成系统的认识。）

一些用户认为对数据进行简单的转置就可以，但这仅仅只是改变了数据的外观，而并没有真正将数据转换成标准的表格结构，如图 7-2 所示。

图 7-2 转置的数据（在左边）vs 正确的未透视数据（在右边）

This data is still pivoted：透视数据 This data is unpivoted：未透视数据

关于这个问题最糟糕的部分是，没有任何工具可以轻松地将数据从透视转换为非透视形态，这导致了需要花费大量的时间来处理这部分工作，至少到目前为止是这样的。

看看 Power Query 是如何真正改变用户数据清洗的方式。打开 “第 07 章 示例文件 \UnPivot.xlsx” 文件，并对数据进行【逆透视】。

7.1.1 准备数据

会发现文件中的数据已经存储在一个名为 “SalesData” 的干净的表中，这使得无论是在同一个工作簿中、不同的工作簿或者在 Power BI 中连接到它都变得很容易。

【注意】 为了便于演示这个解决方案且具有扩展性，在 Excel 中演示这一点，但要注意，这些概念对于 “逆透视” 过程是通用的，无论使用哪个工具来操作这个过程，都是一样的。

将数据加载到 Power Query 中。

1. 创建一个新的查询，单击 “第 07 章 示例文件 \UnPivot.xlsx” 有数据的任意单元格，【数据】【自其他源】【来自表格 / 区域】。

此时，数据加载到 Power Query 中并创建两个查询步骤：“Source” 和 “Changed Type”，如图 7-3 所示。

图 7-3 该查询自动添加了一个 “Changed Type” 步骤

在构建任何解决方案时，首先的是要考虑将来更新这些数据时会发生什么。在构建一个 “逆透视” 解决方案时，这一点至关重要。问问自己下个月会发生什么，数据中还会有 1 月 1 日的列吗，还是会在 2 月 1 日重新开始？明年呢？可能仍然有 1 月 1 日，但它仍然是 2014 年呢还是会进入到新的一年？

这个问题之所以如此重要，是因为 “Changed Type” 步骤已经将当前的列名硬编码到解决方案中。如果这些列在未来不存在，用户最终会收到一个步骤级错误，该错误阻止了数据加载，需要解决。根据经验，人们构建 “逆透视” 解决方案是为了让数据在超过一个时期的时候可以继续使用，所以这成为一个大问题。本书的建议是，除非用户特别需要在【逆透视】数据之前设置数据类型，否则删除前面的全部 “Changed Type” 步骤，这些步骤硬编码的列名在未来可能不存在。这将为以后省去很多麻烦。

在这里的总体目标是【逆透视】数据，但还有一列是不需要的。从原始数据源导入的 “Total” 列可以被删除，因为可以简单地用【数据透视表】（或者 Power BI 中的 “矩阵”）重建它。现在来清洗这些数据，确保在未来不会遇到上述问题。

1. 删除 “Changed Type” 步骤。
2. 选择 “Total” 列（未显示），按下键盘上的 DEL 键。

现在只剩下关键数据：“Sales Category” 列和每一天的列。

7.1.2 逆透视其他列

现在是时候展示【逆透视列】能力背后的魔力了。

1. 右击 “Sales Category” 列，【逆透视其他列】。

【注意】 对于这个数据集，只需要在每一行上重复 “Sales Category”，但用户应该知道，在数据【逆透视其他列】之前，也可以选择多个列。只需按住 Shift 或 Ctrl 键，来选择在输出的每一行上需要的列，然后选择【逆透视列】。

（译者注：在逆透视时，仅仅使用 “逆透视其他列” 这一特性即可，其口诀在于：选中固定不变的列后选择【逆透视其他列】。）

结果简直令人震惊，它已经完成了，如图 7-4 所示。

图 7-4【逆透视其他列】的魔法

能想象这有多容易吗？

用户只需要在这里再做一些修改，数据集就可以最终完成。

1. 将 “属性” 列和 “值” 列的名称分别更改为 “Date” 和 “Units”。
2. 将 “Sales Category”、“Date” 和 “Units” 的数据类型设置为【文本】、【日期】和【整数】。
3. 将查询重命名为 “Sales”。

【注意】 在这个示例中不需要使用【使用区域设置】来更改数据类型。由于数据已经存在于 Excel 中，无论用户本机的【区域设置】是什么，Power Query 都能正确识别这些数据。

完成后，数据看起来应该如图 7-5 所示。

图 7-5 事实上，这非常的容易

7.1.3 重新透视

由于数据现在已经非常干净，可以使用，现在来加载它，然后使用数据建立几个【数据透视表】。

1. 将 “Sales” 加载到一个新的工作表中（【主页】【关闭并上载至】【表】【新工作表】【确定】）。
2. 选择 “Sales” 表中的任意一个单元格，【插入】【数据透视表】【现有工作表】【位置】在工作表的 “F1” 中（光标放在【位置】下面，鼠标选择 “F1”）。
3. 将 “Sales Category” 放在【行】上，“Date” 放在【列】上，“Units” 放在【值】上。

接下来，可以在同一数据集中建立另一个数据透视表。

1. 选择 “Sales” 表中的任意一个单元格，【插入】【透视表】【现有工作表】【位置】在工作表的 “F11” 中。
2. 将 “Sales Category” 放在【行】上，“Date” 放在【行】上，“Units” 放在【值】上。
3. 右击 “F12” 单元格，展开 / 折叠，折叠整个字段。

现在有两个完全不同的透视表，来自同一组未透视的数据，如图 7-6 所示。

图 7-6 两个数据透视表由一个未透视的数据集生成

7.1.4 应对变化

此时，保存文件并把它发送回给用户，让用户继续更新它，数据分析师可能会感到相当舒服。毕竟，Power Query 解决方案可以在任何时候刷新。

当然，数据分析师会这样做，用户进行了更新，然后将更新的文件发回给数据分析师。打开文件后，数据分析师看到用户做了一些只有终端用户才能接受的事情，如图 7-7 所示。

图 7-7 最终用户返回的表

纵观这些变化，用户会惊奇地发现以下问题。

1. 新的一天被添加到 “Total” 列之后。
2. 一个新的销售类别已经出现，被放入了数据源。
3. 用户没有计算新列的 “Total” 值。

问题是，在这些变化的情况下，刷新将如何进行？来找出答案，转到 “Sales” 工作表，分别单击【全部刷新】【刷新】按钮（第一个用于刷新查询，第二用于刷新【数据透视表】）。

（译者注：这里展示了业务用户很可能出现的行为，一种不可理解的违反数据直觉的操作，在 Total 列后继续加入数据，但由于在 Power Query 中构造的查询，删除了 Total 列，且使用了 “逆透视其他列”，就可以自动适配增加的新的日期下的数据。）

结果是非常惊人的，如图 7-8 所示。

图 7-8 数据不仅全部显示出来了，而且还显示在正确的地方

用户向数据分析师提出的每个问题都得到了处理。“Total” 值在那里，数据的顺序是正确的，历史值也已经得到更新。

7.1.5 逆透视之间的区别

在 Power Query 的【转换】菜单中，实际上有三个【逆透视】的功能：【逆透视列】、【逆透视其他列】以及【仅逆透视选定列】。

根据用户界面的术语，如果用户最初采取这些操作，会期望发生什么？

1. 选择 “2014-01-01” 列，按住 Shift 键后选择 “2014-01-07” 列（此时所有的日期列被选中）。
2. 转到【转换】【逆透视列】。

答案是，用户将得到一个名为 “Unpivoted Columns（逆透视的列）” 的新步骤，它提供的结果与在 “Sales Category” 列上使用【逆透视的其他列】命令时的结果相同。但是如果用户使用这个命令，会期望当添加 1 月 8 日的数据时它能正常刷新，但是它正常刷新吗？

事实证明，确实如此。虽然用户可能认为 Power Query 会记录一个 “Unpivoted Only Selected Columns（已仅逆透视选定列）” 的步骤，但情况并非如此。Power Query 实际做的是查看数据集中的所有列，并确定有（至少）一列没有被选中。它不会为用户建立一个特定的 “Unpivoted Columns” 的命令，而是根据用户没有选择的列建立一个 “Unpivoted Other Columns（逆透视的其他列）” 步骤。

好消息是，这使得当新的日常数据列被添加到数据源时，很难触发错误或者出现不可控制的情况。从本质上讲无论是使用【逆透视列】还是【逆透视其他列】，都会得到一个可以兼容未来变化的解决方案，并假设未来新增的列将始终会被 “逆透视”。

但是，如果用户想锁定一个特定的【仅逆透视选定列】的命令，从而使添加到数据集中的新列不会被 “逆透视” 呢？这正是【仅逆透视选定列】的作用。它将记录一个 “Unpivoted Only Selected Columns” 命令，而不是一个 “Unpivoted Columns” 命令，该命令指定了将来要【逆透视】的唯一列。

【注意】 本书建议是使用【逆透视其他列】或【仅逆透视选定列】命令。这样的话，用户就不会失去任何功能，但会在 【应用的步骤】窗口中得到一个明确的步骤名称，当用户以后查看数据转换过程时，这个步骤名更容易阅读。

7.2 数据透视

无论是使用【数据透视表】、【矩阵】还是其他可视化，大多数数据集都需要以未透视的格式提供数据。但也有一些时候，却需要对数据进行透视。请看图 7-9 所示的示例数据，可以在 “第 07 章 示例文件 / Pivot.xlsx” 文件中找到。

图 7-9 完全未透视的数据

这个数据是完全没有透视的。但是，如果想把它变成一种别的格式，为 “Actual” 和 “Budget” 设置为不同的列呢？这就是【透视列】功能的用武之地。接下来就来探讨这个问题。

1. 单击 “第 07 章 示例文件 / Pivot.xlsx” 有数据的区域任意一个单元格，创建一个新的查询，【数据】【获取数据】【自其他源】【来自表格 / 区域】。
2. 更改 “Date” 列的数据类型，单击 “Date” 左边的【日期 / 时间】小图标，选择【日期】【替换当前转换】。
3. 将查询名称更新为 “Sales”。

随着前期工作的完成，现在是时候更改它了，这样就可以通过以下操作得到 “Actual” 和 “Budget” 不同的列。

1. 选择 “Masure” 列。
2. 转到【转换】【透视列】。

然后，会看到【透视列】对话框，如图 7-10 所示。

图 7-10 配置【透视列】时所需进行的选择

切记要确保在启动【透视列】命令前，选择希望用于【透视列】列标题，因为一旦进入对话框，就会提示用户选择包含想根据列标题进行汇总值的列，用户不能在对话框中更改它。

【警告】 【透视列】对话框中的【值列】总是默认为数据集中的第一列，这很少是用户需要的。不要忘了更改它。

【注意】 如果单击【高级选项】左边的小三角，会发现也可以更改数值的聚合方式。就像在 Excel 数据透视表中一样，会发现默认值是数字列的【求和】和基于文本列的是【计数】。但与 Excel 不同的是，还会发现一个【不要聚合】 的选项，将在本书后面的章节中将使用这个选项。

为了完成【透视列】操作。

1. 在【值列】下面选择 “Units”。
2. 单击【确定】。

此时的结果是已经将 “Actual” 和 “Budget” 提取到单独的列中，如图 7-11 所示。

图 7-11 现在有了 “Actual” 和 “Budget” 单独的列

此时，如果需要，可以进一步转换数据，或者加载数据以供使用。

（译者注：逆透视的本质是将表示结构的多个属性转换为一个属性的多个值；透视的本质是将某个属性内容转换为结构。透视一词的英文是：PivotTable，表示行列可以互转，这只是其特征，在繁体中文的翻译中，称为：枢纽表，保持了英文的语义；而在简体中文的翻译中，称为：透视表，其含义更加深刻，表达了选择了某结构属性，并将其内容作为新的结构，以体现了穿透这层结构的含义。此时，判断透视表有一个精准的判别条件，就是：若某表转置后，含义完全不变，则为透视表；而若转置后，无法正确解读其语义，则该表不是透视表。这个透视表的判别方法用到的是其绕轴转动不变原理特性，也就是其英文本意的 Pivot。

图 7-11 的表将 “Measure” 进行了透视，得到了两列，其好处是后续可以单独对任何一列进行计算，这是合理的。但请考虑如果是财务数据的科目，或者是来自电商的各种指标，这样的列就可能会出现几十列，这种场景下，将科目列或指标列逆透视显然更加合理。

作为一个规律，若某表的列会因为未来的业务扩展，而不断增加新列，如：财务科目或业务指标，则此列应该逆透视，以变保持固定的结构。反之，如果形如图 7-11 的表未来不会增加可能列，则可以进行透视，以便更方便的对不同的列进行计算。

可以看出，这里是没有统一的答案的，这也是一种魅力所在，简单讲就是：要确保表处于一种适度透视或逆透视的状态，彻底逆透视或彻底透视并不一定是最合适的状态，要考虑未来的变化，一个重要的原则是：保持结构的稳定以应对新增的数据。）

7.3 拆分列

拆分列，是另一种常用操作（特别是在从 “平面” 文件导入时），是根据某种分隔符或模式将数据点从单个列中拆分出来。幸运的是，Power Query 为用户提供了一些不同的选项来完成这个工作，这取决于用户对最终数据的输出需求。

在这个示例中，将看的是一个相当奇怪的数据导出。这个数据包含在 “第 07 章示例文件 \Splitting Data.txt” 文件中，当通过【从文本 / CSV】连接器导入 Power Query 编辑器时，看起来如图 7-12 所示。

图 7-12 讨厌的东西，如何将其规范化

在这个文件中，有如下两个问题需要考虑。

1. 厨师职位包含 “Grill，Prep 和 Line” 都在一列中，用 “/” 字符分开。
2. 在 “Days” 列中包含了一周中的多个天。

为什么有人会以这种方式设置他们的数据，这超出了用户的工作范围，但现实是，清理这些数据的工作是留给用户的。用户的目标是建立一个每天一行的表格（继承适当的开始和结束时间，以及小时）。此外，要求的规范是将 “Cooks: Grill/Prep/Line” 列不同职位拆分成单独的列。

（译者注：观察数据的结构来体会其实际含义，该数据表示，每周的不同工作时间，不同的岗位由谁来担任的场景。）

7.3.1 将列拆分为多列

将从 “Cooks: Grill/Prep/Line" 列开始，因为这看起来相当简单。

1. 右击 “Cooks: Grill/Prep/Line" 列【拆分列】【按分隔符】。

对话框的关键部分如下如图 7-13 所示。

图 7-13 【按分隔符拆分列】对话框

在这个对话框中，有如下几件事需要注意。

1. Power Query 会扫描它认为是分隔符的内容，并且在大多数情况下，会得到正确的结果。然而，如果它做出了一个错误的选择，用户可以简单地更改它（就目前而言，“/” 作为分隔符是完美的）。
2. 下拉框提供了几种常见的分隔符，但如果发现需要的分隔符不在这个列表中，则有一个【-- 自定义 --】选项。由于 “/” 字符不像逗号或制表符那样常见，Power Query 在这种情况下将其设置为【-- 自定义 --】。
3. 【-- 自定义 --】分隔符的选项并不局限于单个字符。事实上，用户可以使用整个单词，如果这在所使用的数据集中是必要的。

（译者注：分隔符不一定是一个标点符号，可能是一个字母，也可能是一个单词，甚至可能是一个句子，这是使用分隔符时的重要技巧。）

在分隔符选项下面，会发现还可以选择应用拆分动作的【拆分位置】。可以通过只出现一个分隔符（【最左侧的分隔符】或【最右侧的分隔符】）的位置进行拆分，或通过【每次出现分隔符时】进行拆分。在示例中，想按【每次出现分隔符时】进行拆分，因为在 “Cooks: Grill/Prep/Line” 列下面每一个单元格中都有三个职位。

在确认了默认值后，将新拆分的列重命名为 “Grill”，“Prep” 和 “Line” 后，输出结果将如图 7-14 所示。

图 7-14 现在，不同职位厨师已经被分成了单独的列

当然，这仍然留下了 “Days” 列的问题，所以接下来就来处理这个问题。

7.3.2 将列拆分为多行

要做的下一步是拆分 “Days” 列，来将每天分开。做到这一点的一个方法是将每天拆分成新的列，然后对这些列使用【逆透视列】功能 。但也可以利用【拆分列】的一个选项，在一个步骤中完成这一工作。

1. 右击 “Days” 列，【拆分列】【按分隔符】。

这一次，需要对【按分隔符拆分列】选项进行更多的控制，在这个对话框中从上到下操作如下所示。

1. 【分隔符】是换行符，这需要使用一个特殊的字符代码来实现。幸运的是，Power Query 已经为用户在对话框中设置了字符代码模块。
2. 仍将通过【每次出现分隔符时】进行拆分。请注意，与 “Cooks: Grill/Prep/Line” 列不同的是，“Cooks: Grill/Prep/Line” 列的每行总是有三个值，而 “Days” 这一列里每行中有时有两个值，有时有五个值。
3. 默认情况下，【按分隔符拆分列】功能会将数据将分成几列。需要在这里重新选择默认选项，强制 Power Query 将数据拆分成行而不是拆分成列。
4. 【使用特殊字符进行拆分】的选项被选中（由于换行的存在）。如果用户发现需要一个特殊的字符，比如【Tab】、【回车】、【换行】或【不间断空格】，都可以通过勾选如图 7-15 所示的【使用特殊字符进行拆分】复选框，并从【插入特殊字符】下拉列表中选项插入殊字符。

图 7-15 这一次，对话框在打开【高级选项】区域时被打开了

【注意】 在这里，将会注意到的第一件事是，对话框在打开时，【高级选项】区域已经打开了。这样做的原因实际上完全是基于 Power Query 为这些数据确定的分隔符：【换行】（硬回车）字符。如果这是一个简单的逗号，用户将不得不自己打开【高级选项】部分。

【警告】 【使用特殊字符进行拆分】可能是件痛苦的事，因为用户可能不会马上就清楚的知道需要使用哪些字符进行拆分，而且在回车和换行的情况下，用户可能需要正确的字符或字符的组合。如果 Power Query 最初没有提供正确的分隔符，事情就不会按照预期的方式进行，那么处理这个问题的唯一方法就是通过反复试验重新配置这个对话框。

总的来说，需要对 Power Query 默认设置进行的唯一更改是将【拆分为】的【列】改为【行】。一旦这样做，数据就会很好地拆分成新的行，如图 7-16 所示。

图 7-16 每天都有对应的厨师

如果这些是真正的需求，那么现在是时候加载数据了。

7.3.3 拆分后逆透视 vs 拆分到行

来看看与最初的要求有什么不同，比如说用户已经决定，数据中的 “Cook” 不想要现在这样透视的结果。为了在尽可能在少的点击中做到这一点，可以进行如下操作。

1. 选择 “Grill” 列，按住 Shift 后选择 “Line”。
2. 右击所选列之一【逆透视列】。
3. 将 “属性” 列重命名 “Cook”。
4. 将 “值” 列重命名数 “Employee”。

结果将如图 7-17 所示。

图 7-17 真正【逆透视】的数据集

那么，在这个过程中，用户是否可以为自己省去这一堆的点击次数呢？与其把 “Cooks: Grill/Prep/Line” 列分成必须重新命名的几列，然后把结果逆透视，再重新命名这几列，不如直接把原来的 “Cooks: Grill/Prep/Line” 列分成新的几行。

可以这样做，只是会丢失一个关键信息：厨师的类型。原因是什么？该信息只包括在列标题中，而不在数据内容中，如图 7-18 所示。

图 7-18 请记住，这里只是根据标题知道 “Don” 是一个烧烤（Grill）厨师

虽然拆分 “Cooks: Grill/Prep/Line” 列为多行会把员工放到行中，但事实是 “厨师” 的类型并不存在于记录中，所以会丢失。在这种情况下，【拆分列】为列才是正确的，因为它允许用户将标题改为 “Type Of Cook”，然后通过【逆透视列】选项将其带入数据。

【注意】 当然，上面的步骤假设 “厨师” 总是以正确的顺序输入。如果情况并非如此，就需要采取不同的方法。此时，最可能的方法是将员工拆分成几行，然后通过与另一个表的合并来检索位置，这一点将在本书第 10 章介绍。

好消息是，有多种方法来实现用户的最终目标，有时确实需要执行一些额外的步骤，来生成解决方案所需的所有数据。

（译者注：在 Power Query 中进行数据转换，其方案不是唯一的，正如还原一个打乱的魔方，还原方法和所用的步骤也可以完全不同，通常只要能转换为目标形态就可以接受，作为教学目的，去体会其中不同方法的差别有助于理解每种方式的特点。例如在本例中，拆分列为多行与拆分列为多列后再逆透视是等价的，而列头带有额外信息与内容位置一一对应，导致使用拆分列为多列后再逆透视成为了本场景下的正确方法，虽然步骤多了一点，但正确性是第一位的。）

7.4 筛选和排序

在大多数情况下，筛选对于 Power Query 的用户来说是相当容易的，因为使用过 Excel 或其他办公软件的人对筛选结构是相当熟悉的。在本节中，将探讨 Power Query 中筛选和排序的一些不同选项（以及潜在的麻烦）。

为了开始，需要从 “第 07 章 示例文件 \FilterSort.csv” 文件中导入数据。由于这个文件包含了以美国格式编写的 “日期” 和 “值” 的格式，用户还应该确保 “Date” 和 “Sales” 列都是【使用区域设置】来具体定义数据类型。因此，最初的导入工作是按如下方式进行的。

1. 创建一个新的查询【来自文件】【从文本 / CSV】。
2. 删除默认生成的 “Changed Type” 步骤。
3. 更改 “Date” 列的数据类型，【使用区域设置】【日期】【英语 (美国)】。
4. 更改 “Sales” 列的数据类型，【使用区域设置】【货币】【英语 (美国)】。
5. 更改 “Quantity” 列的数据类型，【整数】。

初始导入的结果现在应该如图 7-19 所示。

图 7-19 “FilterSort.csv” 文件的初始导入

这里显示了数据的前 11 行。事实证明，这个文件的时间跨度从 2020 年 1 月 1 日到 2026 年 5 月 31 日，包含 53,500 多行，实际上客户只需要用到其中一小部分数据。

7.4.1 按特定值筛选

筛选特定值相对简单。只需单击该列列标题的下拉箭头，取消勾选不需要保留的项目，或取消勾选【全选】的复选框，勾选需要的项目。甚至还有一个方便的搜索框，允许用户输入项目的一部分来筛选表，如图 7-20 所示。

图 7-20 将 “State” 列筛选为只包含 “ia” 的装态

这个搜索框显然是相当方便的，因为用户可以迅速将列表中的项目缩减到只有一部分，取消勾选【全选】复选框，然后只勾选需要保留的项目。

【注意】 如果用户提交了图中的筛选器，Power Query 会添加一个新的步骤，将数据筛选为包括任何含有字母 “ia” 的状态。

【警告】 这个搜索框应用了一个筛选器，显示包含用户输入的字符模式的任何值。不接受通配符和数学运算符。

在处理列中的过程中有超过 1,000 行的数据集时，将遇到一个挑战。由于 Power Query 默认情况下只扫描预览中的数据，用户偶尔会看到【列表可能不完整】的信息，并有一个可以单击【加载更多】的选项。单击这个选项会要求 Power Query 扫描更多的数据，它会这样做，直到它扫描达到 1000 个唯一值为止，因为这是可以在下拉列表中显示的最大值。此时，会看到一个脚注，说明已经达到了 1000 个值的限制，如图 7-21 所示。

图 7-21 “Sales” 列有超过 1000 个唯一值

这里可能出现的挑战是，当需要筛选的值不在预览的前 1000 行之内，而且在这个列的前 1000 个唯一值之外。此时，无法让它显示在筛选器的搜索区域，从而无法通过筛选器窗格进行选择。

如果发生这种情况，先不要失望。只需要手动创建筛选器。尽管数据集没有显示出这个问题，先假设它表现出了这个问题，需要设置一个手动筛选器如下所示。

1. 筛选 “State” 列【文本筛选器】【包含】。

此时界面会弹出一个如图 7-22 所示的【筛选行】对话框，允许用户手动创建筛选器，即使要筛选的数据不存在于可视化筛选器窗格中。

图 7-22 手动创建一个包含 “ia” 的筛选器

当用户不能在筛选器列表中看到数据时，或者需要为筛选器配置一些更复杂的条件，如【且】和【或】条件时，【筛选行】对话框的这个视图非常有用。当单击【高级】按钮时，它将变得更加有用，如图 7-23 所示。

图 7-23 【筛选行】对话框的【高级】视图

【基本】视图中的筛选器都是应用于用户所选择的原始列，而【高级】视图允许用户一次将筛选器应用于多个列，添加更多的筛选层（通过【添加子句】按钮），并以任何用户认为合适的方式混合和匹配筛选器。请注意，【且】筛选器两者同时成立的情况，而【或】筛选器是任意一者成立的情况，如图 7-24 所示。

【注意】 如果用户需要重新配置筛选器设置，来删除或重新排序【添加子句】，这可以通过把鼠标放在子句右侧的【...】单击这个菜单来完成。

图 7-24 对 “State” 应用筛选器为包含 “ia”，且 “Sales” 要大于 1000

【警告】 当配置多列的筛选器时，将创建一个单一的应用步骤，当选择这个步骤时，只有最初的一列显示出活动的筛选器图标。如果想要留下一个更清晰的检查线索，需要将每个列的筛选器作为单独的步骤来应用。

7.4.2 按上下文筛选

乍一看，无论用户试图筛选哪一列，筛选器的下拉菜单看起来都非常相似。它们的长度是一致的，而且还在筛选区显示可选择的值。但如果仔细观察，会发现搜索框上方的弹出菜单会根据列的数据类型来命名，并提供特定于该数据类型的筛选器。

如下所示。

1. 对于文本类型，会看到【文本筛选器】，它包含【等于】、【开头为】 、【结尾为】 、【包含】等过滤器，以及其中每一种的 “不” 版本。
2. 对于数字数据类型，菜单变成【数字筛选器】，并显示以下选项。【等于】，【不等于】、【大于】，【大于或等于】，【小于】，【小于或等于】，以及【介于】。

虽然每种数据类型都有自己合适的筛选器选项，但此时想关注其中筛选项目最多的一个：【日期筛选器】，如图 7-25 所示。

图 7-25 这么多的日期筛选选项

这个列表看起来令人生畏，其实许多选项会帮助用户完成所期望的事情。

如下所示。

1. 将数据筛选为【一月】，只显示【月份】为【一月】的日期。当然，如果有六年的数据，会有六个不同年份的一月份结果，这可能是或者也可能不是用户想要的数据结果。
2. 将数据集筛选到【最早】的日期，只筛选与所选列中最早的日期相匹配的行。
3. 使用【介于】筛选器将允许用户对开始日期和结束日期范围进行硬编码。

但是在使用上下文敏感的【日期筛选器】时，最棘手的部分是理解 “当前”、“过去” 和 “接下来” 的实际含义。与其他基于【数字筛选器】不同，这些筛选器是相对于系统中的当前日期 / 时间的。

假设有这样一个场景，现在是 2021 年 12 月 1 日，用户设置了一个对 “Sales” 数据进行筛选的解决方案，使用【今年】（在【年】子菜单下找到【今年】）。

2022 年 1 月 5 日，用户在休息一段时间后回到办公室，打开报告来查看 2021 年的销售数据，此时将看到报告数据结果从 600 万美元下降到 1 万美元以下。为什么？因为现在对应【今年】的年份是 2022 年，而不是 2021 年。

此外 Excel 的默认筛选器允许用户选择【年】、【月】或【日】，即使数据集中只有一个日期列。与此不同，Power Query 的筛选器没有这种分层功能。用户不能在【年】子菜单下找到特定的数字年份。那么，在这种情况下，如何筛选才能只得到 2021 年的日期？一种方法是使用【介于】过滤器。

1. 筛选 “Date” 列，【日期筛选器】【且】。
2. 按如图 7-26 所示，设置筛选器。

图 7-26 筛选出 2021 年内的日期

【注意】 或者，也可以添加一个新的列，提取年份，然后筛选到特定年份。要做到这一点，可以选择 “Date” 列【添加列】【日期】【年】【年】，然后筛选需要的年份。

以这种方式设置筛选器的一个缺点是，它们不是动态的。当需要强制它们筛选 2022 年时，需要编辑查询并手动更改它。

7.4.3 数据排序

在本章中，要探讨的最后一项技术是排序。继续上一节的内容，用户希望按 “State” 列的升序对数据进行排序。然后，按日期对数据进行升序排序，但将其作为 “State” 的一个子排序。换句话说，这些排序需要相互叠加，而不是相互取代。

做到这一点的步骤如下所示。

1. 单击 “State” 列上的筛选箭头【升序排序】。
2. 单击 “Date” 列上的筛选箭头【升序排序】。

而结果正是用户所期望的，如图 7-27 所示。

图 7-27 Power Query 首先按 “State” 排序，然后按 “Date” 排序

如你所见，Power Query 默认应用连续排序，与 Excel 不同。它甚至在筛选器图标旁边放置了一个微妙的指示器，显示应用排序的顺序。

【注意】 在 Excel 中使用该模式时，排序顺序的图标几乎不可见，但它们确实存在。

虽然排序很有用，而且在查看原始数据时可以给用户带来很大的安慰，但也需要认识到，这是以牺牲性能为代价的。用户应该问问自己，是否真的需要对数据进行排序。有时候，为了使数据的形状正确，这当然是需要的。但如果数据将被加载到 Excel 或 Power BI 中的数据模型为了后续制作透视表，那么对输出进行排序是不必要的，因为在展示层可以再进行排序，解决这个问题。

7.5 数据分组

另一个挑战是数据量过大。以前面的示例文件为例。它包含 53,513 行交易数据，涵盖 7 年和 48 个州。如果用户只想看到按年份划分总销售额和总数量呢？

当然，用户可以导入所有的源数据，并将其输入到【数据透视表】或可视化矩阵中，但如果用户永远不需要钻取到细节行中呢？用户真的需要导入全部数据吗？

幸运的是，Power Query 有一个分组功能，允许用户在转换过程中对行进行分组，使用户能够以所需要的精确粒度导入数据。这对于减小文件的大小非常有用，因为它可以避免导入过多不需要的细节行。

现在使用与上一个例子中相同的原始数据文件。在一个新的工作簿或 Power BI 文件中进行如下操作。

1. 创建一个新的查询【来自文件】 【从文本 / CSV】选择 “第 07 章 示例文件 \FilterSort.csv”【导入】【转换数据】。
2. 删除默认生成的 “Changed Type” 步骤。
3. 更改 “Date” 列的数据类型【使用区域设置】【日期】【英语 (美国)】【确定】。
4. 更改 “Sales” 列的数据类型【使用区域设置】【货币】【英语 (美国)】【确定】。
5. 更改 “Quantity” 列的数据类型【整数】。

此时，结果将如图 7-28 所示。

图 7-28 “FilterSort.csv” 文件初始导入

这次的报告目标并不是特别关注按天或按月分析数据，所以把 “Date” 列转换为年。

1. 选择 “Date” 列【转换】【日期】【年】【年】。

这样就好了，但现在仍然有超过 53,000 行数据，现在来解决这个问题。

1. 选择 “Date” 列【转换】【分组依据】。
2. 单击【高级】按钮。

此时，会弹出【分组依据】对话框，如图 7-29 所示。

图 7-29 【分组依据】对话框的【高级】视图

【注意】 此时直接进入【高级视图】的原因是，这个视图提供了【添加分组】和【添加聚合】的选项。

正如看到的，用户在分组前选择的 “Date” 列已经被放到了【分组依据】区域。如果需要，用户也可以在这里更改或添加新的【添加分组】。就现在的目的而言，按年份分组将完全可行。

【注意】 当用户把鼠标放在【分组依据】的对话框中的字段上时，会注意到一个小的【...】菜单弹出，变得可见。这个菜单隐藏了【上移】和【下移】字段的功能，以及如果用户需要删除分组或聚合时，也可以使用【删除】功能删除它们。

现在已经配置好了数据分组方式，接下来看看如何对数据进行聚合。默认情况下，Power Query 会通过计算表的行数对所选的字段进行计数。这不是用户需要的，所以需要把它改成按 “Date” 列和 “Sate” 列来计算总销售额和总销售数量。在对话框底部的聚合部分进行如下操作。

1. 将【新列名】从 “计数” 更改为 “Total Sales $”。
2. 将【操作】从【对行进行计数】更改为【求和】。
3. 将【柱】从 “Date” 改为 “Sales”。
4. 单击【添加聚合】。
5. 将它配置【新列名】输入 “Total Quantity”，【操作】选择【求和】，【柱】选择 “Quantity”。

完成后，对话框应如图 7-30 所示。

图 7-30 按年份（“Date” 列）对数据进行分组并返回销售额和数量的求和

单击【确定】按钮后，数据将被立即汇总，共产生 7 行数据（对于这个数据集），结果将如图 7-31 所示。

图 7-31 分组后的数据集共产生了 7 行

这非常酷，但是关于这个功能需要注意以下几点。

1. 没有包括在分组或聚合区域（“State” 列）的源数据列会被删除。在进行分组操作之前，不需要删除它们。
2. 虽然可以在对话框中定义聚合区域中使用的列，但不能在这个对话框中重命名分组级别。它们必须在分组前或分组后重新命名。
3. 虽然在这个示例中【操作】选项只使用了【求和】功能，但用户在【操作】选项中可以使用的选项包括【平均值】、【中值】、【最小值】、【最大值】、【对行进行计数】、【非重复行计数】和【所有行】功能。

【注意】 在【分组依据】对话框中还有一个聚合选项可用【所有行】。这个神秘的选项将在第 13 章进行探讨。

现在是时候完成这个数据集并将其加载到目的地了。

1. 将 “Date” 列重命名为 “Year”。
2. 将查询重命名为 “Grouping”。
3. 转到【主页】选项卡，【关闭并上载】来加载数据。

看到自助式商业智能专家最常见的问题之一是，他们经常导入大量他们不需要的数据。在导入数据时，挑战一下自己，看看是否可以减少所携带细节的列和行的数量。请记住，如果过度缩减数据，总是可以回到分组步骤并删除它（或重新配置它）。当数据集变得更小，解决方案将更加稳定和性能会更好。