Kai Site

简体中文

English

简体中文

English

外观

Power Query 数据转换与表格整理

25233 字约 84 分钟

2026-01-28

本页定位

这篇笔记聚焦 Power Query 中最常用的数据清洗动作，包括透视、逆透视、分组、追加、合并和日期处理。

学习重点

  • 掌握逆透视、透视、拆分列、筛选、排序和分组。
  • 理解纵向追加与横向合并的适用场景。
  • 用条件判断和日期时间处理把原始表整理成分析表。

文章目录

全文参考《精通 Power Query》， 点此下载本文所有示例文件。

章节知识点操作描述注意事项
8.1 追加查询的三种方式查询 & 连接窗格
（不建议使用）在 Excel 的【查询 & 连接窗格】中
右击查询选择【追加】1. 在 Power BI 中不可用
2. 所有合并操作在一个【Source】步骤中，检查困难
直接追加查询直接在原查询上操作合并操作检查困难（需要阅读【Source】步骤公式才知道来源）
【引用】后追加
（推荐）引用第一个表，然后依次追加其他表清晰记录每个表的追加过程，便于维护和检查
**8.2 在当前文件中
追加表或区域**追加方式使用 =Excel.CurrentWorkbook()
可获取工作簿中的所有表进行追加如果追加的是区域（Range）或工作表（Sheet）
需通过定义打印区域（Print_Area）来选定数据。
递归效应使用 Excel.CurrentWorkbook 函数时
可能会导致查询在刷新时尝试加载自身需要通过筛选来解决递归问题
避免在输出中重复数据
测试在将合并方案发布到生产环境之前
通过刷新进行多次测试。确保数据的一致性和准确性
章节知识点操作描述
10. 横向合并使用多个字段进行匹配按住 Ctrl 键，同时选择多个字段作为连接键进行匹配（选择顺序需要一致）。
笛卡尔积（交叉连接）在合并之前，需检查用于连接的列（键列）是否都是唯一值，没有重复。
近似匹配可用于查找价格区间。
模糊匹配匹配时，可选择使用转换表，将一个术语映射到另一个术语。
**13.1 透视操作的
三种情形**一维表（单列数据）将具有重复格式的单列多行数据转换为表格格式。
二维表（以行分组）通过转置和逆透视操作，将情形2转为情形1。
二维表（以列分组）根据是否有行 ID 列进行逆透视操作，将情形3转为情形2。
13.2 逆透视操作多层行标题通过降低标题、转置表格可将原行标题数据进行填充。
性能优化在处理大型数据集时，方式1会导致运行缓慢。只转置真正需要的数据行，可提高处理效率。
保留 null 值在逆透视操作中，会自动去除包含“null”值的记录，如需保留，先将其替换为占位符。
14. 条件判断错误筛选通过故意触发错误并替换为“null”值，可以基于数据类型创建条件逻辑规则。
try…otherwise 表达式介绍了 try…otherwise 表达式的一般用法，以及如何通过它简化错误筛选步骤。
与上下行进行比较通过合并查询将数据表与自身进行关联，利用索引列的偏移来创建行之间的关联，从而实现与上下行数据的比较。
使用示例列自动提取数据利用 Power Query 的【示例中的列】功能，通过输入示例值自动生成数据处理逻辑。

七、常见数据转换

7.1 逆透视

逆透视是将数据从透视表格式转换回标准表格格式的过程，这种转换在数据分析中非常常见，因为透视表格式的数据在某些情况下不便于进一步分析。比如下面这种已经透视过的表，难以进行进一步分析。

在这里插入图片描述

7.1.1 逆透视操作

以第 07 章 示例文件 UnPivot.xlsx为例，演示【逆透视】过程。右击数据表中有数据的任意单元格，选择从表格/区域获取数据：

在这里插入图片描述

  1. 删除“Changed Type”步骤。Power Query使用硬编码方式进行类型转换，不利于维护，直接将其删除。
  2. 删除Total”列（合计列），因为后续很容易重建它
  3. 右击“Sales Category”列，选择【逆透视其他列】
  4. 将“属性”列和“值”列分别重命名为“Date”和“Units”
  5. 将“Sales Category”、“Date”和“Units”的数据类型设置为【文本】、【日期】和【整数】
  6. 将查询重命名为“Sales”
    在这里插入图片描述
  7. 上载数据：转到【主页】，选择【关闭并上载至】【表】【新工作表】，将“Sales”加载到一个新的工作表中。
7.1.2 重建透视表，更新数据
  1. 重新创建数据透视表：选择“Sales”表中的任意一个单元格，【插入】【数据透视表】【现有工作表】，在【位置】中输入F1；然后将行字段设为“Sales Category”，列字段设为“Date”，值设为“Units”
    在这里插入图片描述
  2. 创建另一个数据透视表：在工作表的“F11”位置插入另一个数据透视表，将行字段设为“Sales Category”和“Date”，值设为“Units”。右击“F12”单元格，折叠“Sales Category”字段。
    在这里插入图片描述
  3. 更新数据：如果用户更新了源数据，比如在Total列之后新增了2021-01-08这一天的数据，以及一个新的类别，Power Query可以自动适应这些变化，并正确刷新数据。在这里插入图片描述
      转到“Sales”工作表，分别单击【全部刷新】【刷新】按钮（第一个用于刷新查询，第二用于刷新【数据透视表】），结果是：
    在这里插入图片描述
7.1.3 三种逆透视方式（逆透视列等价于逆透视其他列）

Power Query中有三个逆透视功能：逆透视列、逆透视其他列和仅逆透视选定列，它们主要区别在于如何处理未被选中的列。

  • 逆透视其他列：Power Query会自动逆透视除这些列之外的所有其他列。当数据源中添加新列时，Power Query能够正常处理这些新数据。
  • 逆透视列：等价于逆透视其他列。
    • Power Query会检查数据集中的所有列，并确定哪些列没有被选中。然后，它会根据这些未选中的列建立一个“逆透视其他列”步骤，而不是“逆透视列”步骤，所以二者其实是等价的。
    • 还是以上个示例为例，选择“2014-01-01”到“2014-01-07”的所有日期列，然后使用“逆透视列”命令，可以看到这一步的代码还是 = Table.UnpivotOtherColumns(#"Removed Columns", {"Sales Category"}, "属性", "值")，与选中Sales Category列之后选择逆透其它列的公式相同。所以当添加 1 月 8 日的数据后，查询能正确刷新。
  • 仅逆透视选定列：如果用户希望锁定特定的逆透视操作，使得新添加的列不会被逆透视，他们可以使用“仅逆透视选定列”命令，指定将来要逆透视的唯一列。

建议使用 “逆透视其他列”或“仅逆透视选定列”命令，因为逆透视列步骤有一定的误导性。

7.2 透视

以第 07 章 示例文件“/Pivot.xlsx”文件为例，其内容为：

在这里插入图片描述

  1. 导入数据：右击表格任意单元格，选择从表格/区域获取数据
  2. 更改“Date”列为日期类型，将查询重命名为“Sales”
  3. 选择“Measure”列，选项【转换】选项卡下的【透视列】选项，弹出以下对话框。
    在这里插入图片描述
    在这里插入图片描述

7.3 拆分列

拆分列是数据清洗中常用的操作，特别是在从平面文件（如CSV或TXT文件）导入数据时，Power Query提供了多种选项来完成这个工作。以第 07 章示例文件 Splitting Data.txt为例，导入之后结果为：
在这里插入图片描述

7.3.1 将列拆分为多列

右击“Cooks: Grill/Prep/Line”列，选择按分隔符拆分，Power Query 会扫描它认为是分隔符的内容（这里自动选择了/），并且在大多数情况下，会得到正确的结果。拆分后，将新生成的列重命名为“Grill”，“Prep”和“Line”。

在这里插入图片描述

7.3.2 将列拆分为多行

“Days”列中包含了一周中的多个天，需要将其拆分。一种方法是将每天拆分成新的列，然后对这些列使用【逆透视列】功能 ；更好的办法是直接将“Days”列拆分到行。

  • 使用“拆分列” -> “按分隔符”，Power Query自动选择了换行符。
  • 默认情况下，【按分隔符拆分列】功能会将数据将分成几列，这里选择拆分为行
  • 由于换行的存在，【使用特殊字符进行拆分】的选项被自动选中。如果还需要其它特殊的字符，比如【Tab】、【回车】、【换行】或【不间断空格】，都可以在【插入特殊字符】下拉列表选项中仅选择。
    在这里插入图片描述
    在这里插入图片描述
7.3.3 拆分到列后逆透视（保留列标题信息） vs 拆分到行

假设用户不希望“Cook”列中的数据以当前的透视形式出现，可以接着选择“Grill”到“Line”这几列，点击“逆透视列”，然后将得到的“属性”列和“值”列重命名为“Cook”和“Employee”：

在这里插入图片描述

为了简化整个过程，是否可以在一开始直接将“Cooks: Grill/Prep/Line”列拆分为多行来实现呢？我们删除之前的步骤，先将“Days”列拆分到行，再将“Cooks: Grill/Prep/Line”列拆分到行：

在这里插入图片描述
  可以看到这样做会丢失关键信息：厨师的类型（Grill/Prep/Line，烧烤预备流水线），原因在于，厨师的类型信息只包含在列标题中，而不在数据内容中。如果直接拆分为行，每一行只会显示员工的名字，而不会显示他们具体的职位类型。

如果每个人职位是固定的，添加一个自定义列也行啊。

所以，在这种情况下，选择“拆分列”为列是正确的，因为它允许用户将列标题（如“Type Of Cook”）转换为数据的一部分，然后通过“逆透视列”选项将其带入数据中。

上述步骤假设“厨师”的职位总是以正确的顺序输入。如果顺序可能不正确，可能需要采取不同的方法，例如先将员工拆分为几行，然后通过与另一个表的合并来检索他们的位置。

7.4 筛选和排序

本节以第 07 章 示例文件 "FilterSort.csv”文件为例，由于此文件是以美国格式编写的“日期”和“值”的格式，所以导入之后需要将“Date”和“Sales”列以【英语(美国)】区域进行设置，分别设置为日期和货币类型；Quantity”列设为整数类型。

在这里插入图片描述

7.4.1 按特定值进行筛选（手动创建筛选器）

通过点击列标题的下拉箭头，可以筛选特定值；或者使用搜索框输入部分项目名称（不接受通配符和数学运算符）进行筛选。如果使用后者，Power Query会添加一个新的步骤来应用此筛选器。

在这里插入图片描述
  处理大数据集的挑战：Power Query默认只预览部分数据，用户可能会看到“列表可能不完整”的提示。点击“加载更多”会扫描更多数据，直到它扫描达到 1000 个唯一值为止（下拉列表最多显示1000个唯一值）。

在这里插入图片描述

如果需要筛选的值不在前1000行内，也不在此列前 1000 个唯一值之内，此时无法通过筛选器窗格进行筛选，但可通过手动创建筛选器来解决。

  1. 创建筛选器：选择“文本筛选器”->“包含”
  2. 设置筛选条件：设置以下筛选条件

在这里插入图片描述

当用户不能在筛选器列表中看到数据时，或者需要为筛选器配置一些更复杂的条件，如【且】和【或】条件时，【筛选行】对话框的这个视图非常有用。高级视图允许用户一次将筛选器应用于多个列，并添加更多的筛选层（通过【添加子句】按钮）。点击子句右侧的【…】可以删除或重新排序【子句】。

在这里插入图片描述

7.4.2 按上下文筛选

根据列的数据类型，筛选器的选项会有所不同。

  • 【文本筛选器】，它包含【等于】、【开头为】 、【结尾为】 、【包含】等过滤器，以及其中每一种的“不”版本。
  • 【数字筛选器】包含【等于】，【不等于】、【大于】，【大于或等于】，【小于】，【小于或等于】，以及【介于】。
  • 日期筛选器则提供了更多的选项，其中的“当前”、“过去”和“接下来”是相对于系统中的当前日期/时间的。
7.4.3 数据排序

连续排序（也叫层叠排序或多级排序）：用户可以按“State”列的升序对数据进行排序，然后按日期进行升序排序。

在这里插入图片描述

需要注意的是，排序可能会影响性能，用户应考虑是否真的需要对数据进行排序，比如使用数据模型以制作透视表，那么对输出进行排序是不必要的。更好的做法是将数据加载到Excel或Power BI中，在展示层再进行排序。

7.5 数据分组

当数据量过大时，比如FilterSort.csv示例文件包含大量的交易数据（53,513行），涵盖7年和48个州。如果用户只需要按年份查看总销售额和总数量，那么其实没必要将所有的源数据都输入到【数据透视表】或可视化矩阵中。Power Query允许在转换过程中对行进行分组，可以按所需粒度导入数据。

这段文字主要介绍了如何在Power Query中使用数据分组功能来减少导入数据的量，从而提高数据处理的效率和性能。以下是对这段文字的详细解析：

  1. 导入数据：从FilterSort.csv文件导入数据，删除默认的“Changed Type”步骤，设置“Date”列和“Sales”列分别为【英语(美国)】的日期类型和货币类型，“Quantity”列为整数类型
  2. 转换日期列：将“Date”列转换为年份，因为用户只需要按年份查看总销售额和总数量。
  3. 分组数据：选择“Date”列，单击【转换】下的【分组依据】选项，单击【高级】按钮，选择按年份分组数据，并计算总销售额和总销售数量。
    在这里插入图片描述
    在这里插入图片描述
  4. 将“Date”列重命名为“Year”，将查询重命名为“Grouping”。
  5. 关闭并加载数据到目标位置

注意事项：

  1. 没有包括在分组或聚合区域的数据列会被删除；
  2. 分组依据列不能在分组对话框中重命名，需要在分组前或分组后进行
  3. 聚合操作包括求和、平均值、中值、最小值、最大值、计数等

建议在数据导入阶段就尽量减少数据量，只保留对分析有用的列和行（通过分组筛选和聚合），以提高性能、简化分析、节省存储空间。

八、纵向追加数据

在数据处理领域，我们常常需要将多个数据集合并成一个统一的数据表，以便进行更全面的分析。这一过程在技术上被称为 “纵向追加数据”。考虑以下场景：用户每个月从中央数据库中提取对应月份的数据文件（例如，2 月份提取 1 月份的数据，3 月份提取 2 月份的数据，依此类推），分析师需要将这些数据合并到一起进行分析。

8.1 追加外部文件的数据

8.1.1 导入数据

以第 08 章 示例文件 "Jan 2008.csv"、"Feb 2008.csv"和"Mar 2008.csv"为例，逐个导入文件，并进行以下处理：

  1. 删除"Changed Type"步骤。
  2. 更改"Date"列和"Amount"的数据类型分别为【日期】和【货币】，且【使用区域设置】，设为【英语(美国)】；更改"Account"列和"Dept"列的数据类型为【整数】
    在这里插入图片描述
  3. 将三个查询都设为仅限连接（在 Power BI 中可以通过右击查询取消勾选 “启用加载” 复选框来实现；在 Excel 中则需要通过 “关闭并上载至” 选择 “仅创建连接”）。
    在这里插入图片描述
8.1.2 选择合适的追加方式
  1. 在查询 & 连接窗格中进行追加：用户可以通过 Excel 的 查询 & 连接窗格 右击查询选择 “追加” 来合并所有的表，但不推荐这种方法，因为它在 Power BI 中不可用，并且会将所有合并的表合并到一个 “Source” 步骤中，使得检查困难。
    在这里插入图片描述
  2. 直接追加查询：在 Power Query 编辑器中选择"Jan 2008"查询，然后选择【主页】【将查询追加为新查询】的方式，直接从 Excel 用户界面【追加】查询。这种方式操作的步骤比较少，但是也不太推荐，因为只有单击"Source"步骤并阅读公式栏，才知道数据源是怎么来的。
  3. 引用查询后追加（推荐）：推荐的方法是在 Power Query 编辑器引用第一个表（如 “Jan 2008” 查询），然后再执行追加操作。
    1. 引用查询：右击"Jan 2008"查询，选择【引用】，得到的新查询重命名为"Transactions"
    2. 追加查询：转到【主页】【追加查询】，在【要追加的表】中选择"Feb 2008"
    3. 验证追加数据：Power Query 编辑器中显示的是数据预览，并不会加载全部数据，所以需要将数据加载到工作表中进行验证，比如查看 “查询 & 连接” 窗格中的行数来确认数据量；或者通过创建数据透视表来查看：
      在这里插入图片描述

第三种方式的好处是：

  1. 不使用【查询 & 连接】窗格，不存在跨平台兼容性问题（Power BI ）；
  2. 查询步骤的可理解性与可维护性更好。
    在 Power Query 编辑器中对第一个表进行【引用】，然后依次追加其他表的方法，会为每个被追加的表记录一个不同的 “Appended Query（追加的查询）” 步骤，这样在检查查询时会更加容易，可以清楚地看到每个表是如何被逐步追加到查询中的。
  • 对于有多个表进行追加的情况，如果觉得逐个追加的方式步骤太长，可以编辑现有的 “Appended Query” 步骤一次性追加完；如果希望追加路径更清晰，可以逐个追加。
  • 虽然每月手动编辑文件来添加和转换新的数据源然后追加到 “Transactions” 查询中的方法可行，但随着数据量的增加，这种方法会变得过时，后续章节将介绍更简便的方法。
8.1.3 追加列标题不同的数据

当追加的查询中存在列标题不一致的情况时，Power Query 会自动添加新列并填充 “null” 值，比如"Mar 2008"查询的"Date"列名称变成"TranDate"。解决方法是对源查询进行编辑，将列标题统一，然后更新目标查询。
在这里插入图片描述

8.2 在当前文件中追加表或区域

另一个常见场景是，Excel 用户常需要将同一工作簿中的多个数据表进行追加合并，将Excel 像一个准数据库一样进行预处理。比如，某水疗中心的记录员，每月创建一个新表来记录礼品券的交易信息（第 08 章 示例文件，Append Tables.xlsx），每个表格也是以年和月命名（例如 Jan_2008，表格名称中不能有空格，在公式-名称管理器中可以看到）：

在这里插入图片描述

那么，如何才能建立一个解决方案，使它自动包含记账员添加的所有新表，而不是手动调整查询，也就不必教记账员如何编辑 Power Query。方法就是使用Excel.CurrentWorkbook 函数，来自动合并同一工作簿中的多个数据表。

8.2.1 合并Excel表中的所有表（Table）
  1. 创建空白查询（【数据】【获取数据】【自其他源】【空白查询】），将其重命名为"Certificates"；
  2. 导入整个Excel表的数据：在公式栏中输入=Excel.CurrentWorkbook() 来获取工作簿中的所有表格（第6章的技巧）。
    在这里插入图片描述
  3. 扩展表：单击"Content"列的右上角图标（两个反向指向的箭头标志，用于展开每一个表），展开"Content"列，取消勾选【使用原始列名作为前缀】的复选框。
    在这里插入图片描述
  4. 将"Name"列转换为有效的月末日期列：由于"Jan_2008"不是一个有效的日期，需要要用一个小技巧来实现。
    • 右击"Name"列【替换值】，将"_“字符替换为” 1 "（空格1空格）
    • 选择所有列【转换】【检测数据类型】
      在这里插入图片描述
    • 选择"Name"列，转到【转换】标签【日期】【月份】【月份结束值】
    • 双击"Name"列【重命名】"Month End"
      在这里插入图片描述
  5. 上载数据：选择【关闭并上载】，将新生成的"Certificates"工作表移动到工作簿的最后，会发现Certificates查询出发了错误。再次编辑Certificates查询，会发现"Source"步骤的结果比之前多了一个Certificates表，这是因为当使用"=Excel.CurrentWorkbook()"来列举表或范围时，输出的查询在刷新时也会被识别：
    在这里插入图片描述
  6. 处理错误：检查Replaced Value和Changed Types步骤，会发现产生错误是因为Certificates不能被转换为日期，所以只需要右击"Name"列，选择删除错误就行。
    在这里插入图片描述
    在这里插入图片描述
8.2.2 合并Excel表中的所有区域（Range）或工作表（sheet）

如果Excel中不是一个个构建好的表（Table），而是包含原始数据的工作表（sheet），此时可通过定义"打印区域"来指定要读取的数据范围

正如第 6 章所提到的，没有内置函数可以从活动工作簿中的工作表中读取数据，因为excel无法确定整个sheet中哪些是需要的数据，所以需要通过定义打印区域的方式进行明确的指定。

  1. 定义打印区域：继续选择上一章节处理完成的Append Tables表 ，选择"Jan 2008"工作表，点击【页面布局】选项卡【打印标题】，在【打印区域】框中输入：“A:D”。对"Feb 2008"和"Mar 2008"工作表重复这一过程。
    在这里插入图片描述

  2. 创建空白查询，重命名为"FromWorksheets"。

  3. 导入数据：在公式栏中输入= Excel.CurrentWorkbook()，将会看到所有的表格和命名范围的列表，包括"打印区域"。
    在这里插入图片描述

  4. 筛选出打印区域数据：选择"Name"列，在搜索框输入"Print_Area"

  5. 将"Name"列中的'!Print_Area文字替换为空（不输入任何东西）；将'替换为空
    在这里插入图片描述

  6. 展开"Content"列（取消勾选【使用原始列名作为前缀】复选框）
    在这里插入图片描述

  7. 清洗数据：接下来就是清洗数据了。在这种场景中将第一行提升为标题是有风险的，推荐手动重命名列。

    • 删除"Column4"，将其余列重命名为"Certificate"、“Value”、“Service"和"Month End”

    • 右击"Month End"列【替换值】，将单个空格替换为文本"1,"

    • 设置"Certificate"列、"Value"列、Service"列、"Month End"列数据类型分别是【整数】、【整数】、【文本】、【日期】
      在这里插入图片描述

    • 选择所有列，删除错误行，然后清除含有null值的行。

    • 选择"Month End"列【转换】【日期】【月份】【月份结束值】

  8. 上载数据

8.3 思考

Excel.CurrentWorkbook 函数会读取当前 Excel 文件中的所有对象，包括表格、命名范围、查询等。如果生成的新查询也被保存在同一个工作簿中，这就可能导致递归效应，即查询在刷新时会尝试加载自身，从而在输出中重复数据。处理的方式包括：

  • 筛选关键列上的错误：通过筛选掉包含错误的行，可以避免将无效数据或重复数据纳入最终结果。
  • 使用标准命名：为输入和输出列使用统一的标准命名规范，这样可以更容易地识别和筛选出不需要的列，防止它们被错误地包含在合并结果中。

本章介绍了用外部数据源的手动追加、合并当前工作簿中的所有表格或区域两种方式。与传统的复制粘贴方式相比，Power Query 的追加功能大幅缩短了工作时间，并且避免了因手动操作可能导致的数据重复等错误，同时保证了数据的一致性。无论用户选择哪种方法，请确保在将其发布到生产环境之前通过刷新进行多次测试。

最后，有没有可能把这些合并起来，创建一个系统，可以合并整个文件夹中的所有文件，而无需在 Power Query 中手动添加每个文件，答案是肯定的，下一章会介绍具体的方法。

十、横向合并数据

10.1 基础合并

在数据处理领域，将多个数据表进行合并是常见的需求。假设我们有一个销售交易表"Sales"和一个包含产品细节的"Inventory"表。这两个表需要通过"SKU"列进行连接，以获取完整的产品信息：

在这里插入图片描述

  1. 创建暂存查询：创建一个新的查询，连接到"第 10 章 示例文件/Merging Basics.xlsx"文件中的两个表，将每个查询保存为"暂存"查询（PoweBI中右击查询设置【禁用加载】；Excel中设置为【仅限连接】）
  2. 引用查询：右击Sales查询选择【引用】，将其重命名为Transaction；
  3. 选择【合并查询】（不是【将查询合并为新查询】），选择要合并的表和连接字段，默认的连接类型是左外部连接。在本例中，通过"SKU"列将Sales表和Inventory表合并。
    在这里插入图片描述
    • 和【追加】查询一样，在Excel 中可以通过右击【查询 & 连接】窗格中的"Sales"查询来【合并】查询，但是不建议这么做，详见8.1.2章节。
    • 在执行合并之前，始终要确保各个表中，用于连接的列的数据类型是一样的。
  4. 扩展表格：单击"Inventory"列标题的右侧的扩展图标，不选择"SKU"列和"Brand"列，并取消勾选【使用原始列名作为前缀】的复选框。
    在这里插入图片描述

10.2 复合键

在数据合并中，有时需要结合多个字段来形成一个唯一的"复合键"，进行连接，比如合并下面两个表，需要以"Account"字段和"Dept"字段的组合作为复合键。
在这里插入图片描述
  虽然可以通过使用分隔符将这两列合并为唯一的标识符，但实际上没有必要这样做。在合并时按住Ctrl键同时选中"Account"字段和"Dept"字段就可以了。

在这里插入图片描述

  • 连接字段的选择顺序：当选择多个字段作为连接键时，Power Query会按照用户选择的顺序对这些字段进行编号（如"1"、"2"等）。即使字段在两个表中的位置不一样，但只要选择字段的顺序一致，Power Query也能正确匹配数据。
  • 隐含的分隔符：虽然在操作界面上没有明确显示这些字段是如何组合的，但实际上Power Query会使用隐含的分隔符来连接这些字段。例如，如果"Account"字段的值是"64010"，"Dept"字段的值是"150"，那么Power Query会将它们视为"64010-150"这样的组合键。
  • 预览匹配问题：合并界面的底部会根据 Power Query 的数据预览，给出一个预估匹配情况。由于Power Query的预览功能一般只显示前1000行，所以这个预估值不一定准。在实际执行合并操作时，Power Query会处理整个数据集，保证完全匹配。

10.3 连接类型

仔细观察可以看到，在左侧Chart of Accounts"表中不存在某些组合（“64015-150"和"64010-350”），右侧"Transaction"表中也没有"Special"或"Pull Chart"账户。就这一问题而言，又分为不同的情况，其问题严重性也不同。

  • 如果"Chart of Accounts"表中存在从未被使用的账户，这通常不是一个大问题，因为它们可能只是备用账户。
  • 如果"Transaction"表中的交易被记入了不存在的账户或部门组合，这是一个严重的问题，因为它可能导致数据错误或财务混乱。

任何需要在两个列表之间进行匹配、比较或调整的场景，例如：客户与信用额度，销售人员与订单，零件与价格，都需要考虑这个问题，所以选择合适的连接方式非常重要，避免出现业务逻辑错误。

合并查询使用的是Table.NestedJoin函数，其中参数JoinKind.Type控制其联接方式，共有8种。前6种可以在合并查询界面下拉菜单选择，最后两种只能使用公式。

在这里插入图片描述
在这里插入图片描述

10.3.1 左外连接：左表所有行+右表匹配行

打开"第 10 章 示例文件/Join Types.xlsx"文件，其中已经包含了"Transactions"表和"COA"表（即"Chart of Accounts"表）两个"暂存"查询。

  1. 【引用】左表（此示例中为"Transaction"表），将查询【重命名】为"Left Outer"
  2. 转到【主页】选项卡【合并查询】，选择"右"表，即"COA"表，连接方式选择左外
    在这里插入图片描述
  3. 展开Name列，取消勾选【使用原始列名作为前缀】的复选框。
    在这里插入图片描述
10.3.2 右外连接：右表所有行+左表匹配行
  1. 【引用】左表（此示例中为"Transaction"表），将查询【重命名】为"Right Outer"
  2. 【合并查询】，连接方式选择右外
  3. 展开数据：这一次，“COA"列都填入了数值，但是由于"Special"和"Pull Cart”（显示在第 5 行和第 7 行）没有交易被匹配，所以这些列显示为空值。
    在这里插入图片描述
10.3.3 完全外部连接（并集）：查看数据不一致情况
  1. 将引用的合并查询重命名为"Full Outer"
  2. 将【连接种类】选择为【完全外部】
    在这里插入图片描述
      可以看到合并结构中返回两个表中的所有记录，包括匹配和不匹配的记录。当试图了解两表的差异时，这种方式可以非常方便查看到数据不一致的地方。
10.3.4 内部连接（交集，仅限匹配行）

在这里插入图片描述

10.3.5 左反连接、右反连接：只返回不匹配记录（左表或右表）

到目前为止，所探讨的连接主要是针对匹配的数据。当对比两个数据列表的差异时，人们实际上更关心不匹配的数据。使用和之前一样的步骤，连接方式选择左反，将返回只在左表出现的数据：

在这里插入图片描述

如果唯一的目标是识别左表中没有在右表中匹配的记录，就没有必要展开右表的合并的结果。而且可以直接删除右边的列，因为无只会返回空值。

连接方式选择右反：

在这里插入图片描述

10.3.6 全反连接：合并左反连接与右反连接

另一种非常有用的连接类型是"完全反"连接，特别是试图识别两个列表之间不匹配的项时，只需要合并左反连接与右反连接就可以了：

在这里插入图片描述

10.3.7 左半连接（左表的匹配行）与右半连接（右表的匹配行）

连接方式的下拉菜单中没有此选项，只能通过修改M公式来完成。可以先创建左外查询，然后将连接类型JoinKind.LeftOuter改为 JoinKind.LeftSemi即为左半查询，结果是左表的匹配行，右表全为null，所以叫左半（不如叫左内）。

在这里插入图片描述
改为JoinKind.RightSemi即为右半连接：

在这里插入图片描述

10.3 笛卡尔积（交叉连接）

10.3.1 实现方式

笛卡尔积是一种特殊的连接类型，用于创建两个表中所有可能的组合。在Power Query中，可以通过添加一个公共的"MergeKey"列（通常为常量值1），然后基于此列进行左外部连接来实现。

在这里插入图片描述
打开"第 10 章 示例文件\Cartesian Products.xlsx"，本例的目标是获取一个包含固定每月费用的表：

在这里插入图片描述

  1. 导入数据：右击数据表中任意单元格，选择"从表格/区域获取数据"，创建Months表和Expenses表两个查询：
    在这里插入图片描述
  2. 添加自定义列：在Expenses查询和Months查询中，添加"MergeKey"列，公式为 =1
  3. 合并查询：使用"合并查询"，以"MergeKey"列为基础，将Months表与Expenses表合并
  4. 后处理：删除"MergeKey"列，展开"Months"列中除合并键之外的所有列，取消勾选【使用原始列名作为前缀】的复选框，生成最终的预算表，包含每个月的费用类别：
    在这里插入图片描述
10.3.2 笛卡尔积风险

如果在"Months"表中不小心添加了重复的月份（如2021年1月出现两次），刷新后会导致每个费用类别都重复出现两次。解决方法是在"Months"表右击"Month"列并选择【删除重复项】。

但是，在【合并】之前【删除重复项】也应谨慎。比如在本章的第一个示例中，尝试基于"Brand"列合并"Sales"和"Inventory"表将创建笛卡尔"Product"：

在这里插入图片描述
  生成的结果中，Sales列会匹配到多个行，这是因为"Inventory"表中的"Brand"列有重复项。但此时在"Inventory"表中删除"Brand"列的重复项是不可取的，因为这将删除两种产品中的一种。

在这里插入图片描述

为了避免意外生成笛卡尔积，可以使用视图-列分析工具来检查：如果用于连接的列（键列）的"非重复值"和"唯一值"数量匹配，说明该列中的每个值都是唯一的，没有重复。这种情况下，可以安全地使用该列作为连接键，而不会产生意外的笛卡尔积（如SKU"列）；否则将会面临产生笛卡尔积的风险（如"Brand"列）

在这里插入图片描述

10.4 近似匹配（查找价格区间）

在某些情况下，需要查找并返回等于或介于两个数据点之间的值。比如以下示例中，购买者下的订单越多，价格就越优惠。对于源表中订单量为2755的情况，查找表没有这个数据，但其价格应该按订单量2500处理。

在这里插入图片描述

  1. 连接数据表： 打开Approximate Match.xlsx，连接到"源表Prices和"查找表"Orders；
  2. 清洗数据：将Prices表的"Units"列重命名为"Quantity"，使键列名称一致；
  3. 追加查询：引用Prices表，选择追加查询
  4. 填充空白数据：
    • 先对"Quantity"列升排列，再对"Order ID"列升序排列，确保每个区间查找表的"Quantity"在最上方
    • 选择"Price Per"列，右击选择【填充】【向下】，将自动匹配达到条件的优惠价格：
      在这里插入图片描述
  5. 清除"Order ID"列的null值

注意事项：

  • 列名一致性：确保源表和查找表中的"Key"列名称一致。
  • 排序顺序：先对"Key"列排序，再对"ID"列排序，以确保查找表的行始终位于源表的行之前。
  • 数据量限制：即使源表长度超过1000行，方法仍然有效，尽管数据预览可能无法显示完整内容。

10.5 模糊匹配

10.5.1 基本模糊匹配方法

在将人工输入的数据与计算机生成的数据进行匹配时，常常会遇到拼写错误、大小写不一致、缩写、符号差异等问题，导致无法使用传统的精确匹配方法。比如在"Product"表和"Price"表中，由于数据输入的不一致性（如"laptop"与"Laptop"、"Screen"与"Monitor"等），标准的左外部连接只能匹配到部分数据，此时就需要使用模糊匹配。

在这里插入图片描述

  1. 打开"第10 章 示例数据\Fuzzy Match.xlsx"，在创建常规连接时，勾选【使用模糊匹配执行合并】
    在这里插入图片描述
    Power Query 将使用 Jaccard 相似性算法 来度量文本之间的相似性，默认将相似度得分达到 80% 或以上 的内容标记为匹配项。

在这里插入图片描述

  • 一般来说，在使用模糊匹配时，单词越长，拥有的字符越相似，返回精确匹配的可能性就越大
  • 【使用模糊匹配执行合并】功能仅在文本列上的操作上受支持。如果出于任何原因需要对使用不同数据类型的列执行模糊匹配，则需要首先将数据类型转换为【文本】。
10.5.2 使用转换表

虽然基本的模糊匹配解决了一些问题，但有两个记录仍然无法生成匹配：“Mice"与"Mouse”、“Screen"与"Monitor”，因为它们的相似度很低。解决方案是创建一个 转换表，将一个术语映射到另一个术语，然后选择将其作为转换表：

在这里插入图片描述

此表的名称并不重要，但它必须包含"From"列和"To"列，以便正确映射和转换术语，最终所以数据都被正确匹配：

在这里插入图片描述

10.5.3 降低相似度阈值

默认情况下，Power Query 的模糊匹配要求相似度达到80%。如果需要更宽松的匹配，可以在模糊匹配设置中，降低相似度阈值。比如下面的而数据数据，相似度最高才0.67（比如"北京"和"北京市")，所以默认匹配时全部没有匹配成功。而"新疆"和"新疆维吾尔自治区"的相似度更低，只有0.25，所以为了都能匹配成功，我们把这个阈值调到0.25：在这里插入图片描述

降低相似度阈值可能导致误匹配（假阳性）和意外的笛卡尔积。比如对于下面的数据，将相似度阈值从80%降低到50%，可以匹配"Don A"和"Donald A"，但是产生了7行记录，多了一行。

在这里插入图片描述

在这里插入图片描述
模糊匹配可能导致数据维护困难，尤其是在数据不断刷新的情况下，建议：

  1. 预处理数据：在合并数据之前，替换已知的错误字符或模式；
    如果知道查找表中地址字段从不包含"#“符号，但源表中可能包含这种写法（如”#123 Main St"），可以在合并之前，右击该列，将所有"#"符号替换为空。
  2. 创建异常表：使用完全反连接模式（10.3.6），在每次刷新后生成一个包含所有未匹配的项的异常表；
  3. 监控异常项：使用 Excel 或 DAX 公式计算异常表中未知项的数量，并将其显示在报表页面上进行监控；
  4. 更新转换表：当发现异常表中有未知项时，将异常项及其映射关系添加到转换表中；
  5. 逐步完善：随着数据的不断刷新，逐步完善转换表，减少不匹配项的数量。

模糊匹配算法不仅用于合并操作，还可能出现在其他特性中，如分组特征和聚类值。

10.5.4 总结
  • 模糊匹配 是一种强大的工具，可以解决数据匹配中的拼写错误和不一致性问题。
  • 基本模糊匹配 通过 Jaccard 相似性算法实现，但可能无法解决所有问题。
  • 转换表 可以解决更复杂的术语替换问题。
  • 降低相似度阈值 可以放宽匹配条件，但需要谨慎使用以避免误匹配。
  • 维护策略 包括预处理数据、监控异常项和逐步完善转换表，以确保数据的准确性和可维护性。

十三、转换表格数据

13.1透视

13.1.1 一维表透视（单列数据）

一维表透视适用于将具有重复格式的单列多行数据转换为表格格式。以在第 13 章 示例文件"Stacked Data.txt"为例，存储了信用卡交易数据。除了第一行显示为标题外，数据格式非常一致，都是“日期”、“供应商”、“金额”、“空白”。忽略标题，每个记录有 4 行。

在这里插入图片描述
如果用户在数据中看到此重复格式，则将其展开为表格格式的标准步骤为：

  1. 导入数据：创建新查询（【获取数据】）【从文本/CSV】选择“第 13 章 示例文件\Stacked Data.txt”，然后将第一行提为标题。

  2. 添加索引列，将数据分组 ：从0开始添加索引列，添加整数除法和取模运算，根据记录行数确定确定除数，这样就将记录进行了分组和组内索引。

    • 选择“索引”列【添加列】【标准】【除(整数)】弹出的对话框【值】输入“4”【确定】
    • 选择“索引”列【转换】【标准】【取模】弹出的对话框【值】输入“4”【确定】
      在这里插入图片描述

  3. 透视列 ：选择 “索引” 列，执行透视操作，设置 “值” 列字段为目标数据列（本例中是“Transactions”），展开【高级选项】并选择 “不要聚合”。
    在这里插入图片描述
    可以看到索引列元素成了列标题，第一列是“整除”列，“Transactions”列被正确透视

  4. 清理数据 ：删除多余的“整除”列和空白列，重命名新创建的列（“Date”、“Vendor”、“Amount”），设置数据类型。

在处理包含重复空白行的数据集时，建议先执行透视操作，然后再删除空白列。这是因为如果在透视操作前删除空白行，可能会误删包含重要数据的行（例如供应商名称为空的行）

13.1.2 二维表透视（以行分组）

在第 13 章 示例文件"Vertical Sets – Begin.xlsx”中，每N行一组数据，每组数据都有多个列，需要将这个二维表进行透视。

在这里插入图片描述
  实现的方式有多种，这里只用Power Query界面进行操作，先将二维表转为一维表，再按上一节的标准步骤进行操作。

  1. 导入数据
    在这里插入图片描述

  2. 转置数据 ：转到【转换】选项卡，单击【转置】，对数据进行转置操作。
    在这里插入图片描述

  3. 添加索引列 ：从0添加索引列。

  4. 逆透视其他列 ：右击“索引”列，【逆透视其他列】。这里的一个关键技巧是在【逆透视其他列】之前对数据进行了【转置】。于是得到了拥有多列值的一维表，接下来和上一节操作类似。
    在这里插入图片描述

  5. 删除列：删除"值"列之外的其它列

  6. 一维表透视的标准步骤 ：对得到的数据再次执行透视操作，添加索引列、整数除法与取模运算（除数为3）、透视列等步骤，完成数据转换。
    在这里插入图片描述

13.1.3 二维表透视（以列分组）

在第 13 章 示例文件"CourseSchedule.csv”中，每N列一组数据，透视时还需要将行ID也写入数据中。这种表格只需要将行 ID 之外的其它列进行逆透视，就可以转换为第二种情形（以列分组->以行分组）。

在这里插入图片描述
以下总结了标准的操作步骤，根据实际数据集的不同结构进行调整。

  1. 准备数据 ：导入数据集，避免提升标题行，然后根据是否有行 ID 列进行操作。

    • 是：选择行 ID 列并右击列标题【逆透视其他列】
    • 否：选择所有列，右击列标题【逆透视列】
    • 逆透视完之后，删除“属性”列
      在这里插入图片描述

  2. 标准透视操作：接下来就可以使用二维表透视（以行分组）标准步骤，聚合【值】列。包括添加索引列，执行整数除法和取模运算，非聚合方式透视列等等。
    在这里插入图片描述

  3. 清理数据 ：提升标题行、删除多余行、重命名列、设置数据类型、清除空白行（本例中 是清除“Date”列中的空白行）。
    由于删除了“Promoted Headers”步骤，标题信息仍然保留在数据中，从而产生了现在在数据中看到的三行列标题。所以现在选择【将第一行用作标题】，转到【主页】【删除行】【删除最前面几行】，输入行数“2”。
    在这里插入图片描述

根据导入的数据源，用户可能看不到上图中显示的两个空行。在这种情况下，Power Query 将空白记录解释为空文本字符串，因此在透视操作时会保留它们。如果在初始导入时这些值被解释为空值（null），那么在透视操作过程中会被消除掉。

13.2 逆透视

之前所述的每一个逆透视示例都相对简单，因为它们都只有一个级别的标题。在这一节中，将使用多个级别的标题透视数据。

13.2.1 多层行标题

当本节提到子类别数据时，指的是具有多个标题行的数据集，比如第 13 章 示例文件"Financial Statement.xlsx”需要做如下转换：

在这里插入图片描述

这个数据集的棘手之处在于 Power Query 只支持一个标题行。这个数据集中不仅有两个标题行，而且还需要将第一个标题行的值“April”和“May”分配给下一行中的三列。首先导入数据然后进行检查，确保数据集的每个标题行都在数据预览区域中，而不是在列标题中。

在这里插入图片描述
接下来按照标准步骤对此类子类别数据进行逆透视：

  1. 降级标题 ：将标题降级到数据中（如果需要），分两种情况：
    - 数据集包含标题：此时 Power Query 将自动沿用这些列名作为标题，不会有“Promoted Headers”步骤，此时用户需要将标题“降级”到数据中；
    - 数据集不包含标题：此时需要删除自动生成的“Promoted Headers”和“Changed Type”步骤。本例中还需要删除前2行（第3行和第4行才是数据表中的两个标题行）。
    在这里插入图片描述
  2. 转置数据 ：转置之后，才有可能将原先的第一个标题行向下填充
    在这里插入图片描述
  3. 填充数据 ：根据需要向上或向下填充数据。本例中，右击“Column1”列【填充】【向下】
    在这里插入图片描述

【填充】命令可用于向上或向下填充，但仅在填充为“null”值时有效。如果单元格显示为空格，则需要先将空格替换为“null”。

  1. 合并原始表的多级标题 ：使用分隔符将原数据集中的标题进行合并。注意选择一个数据集中没有出现过的字符作为分隔符。本例中，使用|合并“Column1”和“Column2”。

  2. 转置数据 ：将数据转置回原始形式。到了这一步，可以看到原先的两级标题已经合并到一起，且第一级标题的值“April”和“May”已经分配到数据中。
    在这里插入图片描述

  3. 设置标题行 ：

    • “Column1”和“Column2”列第一行还显示为分隔符，将其分别替换为标题"Class”和“Category”。

    • 将第 1 行提升为标题，去除自动应用的“Changed Type”步骤（因为后面逆透视，数据类型又要变）。
      在这里插入图片描述

  4. 数据清理 ：在进行最终的逆透视操作之前，执行必要的数据清理操作。
    在本示例中，则需要对“Class”列的空值进行向下填充，并筛选掉不属于规范化数据集的总计行和小计行（通过去除“Category”列中的null值）。
    在这里插入图片描述

  5. 逆透视列 ：对数据进行逆透视操作。本例中，选择“Class”列和“Category”列，右击列标题选择【逆透视其他列】。
    在这里插入图片描述

  6. 拆分属性列 ：按上面使用的分隔符拆分 “属性” 列。

  7. 最终清理 ：完成最终的数据清理。比如将拆分后的“属性.1”列和属性.2”列分别重命名为“Month”和“Measure”（也可修改上一步M公式完成）；转换所有列的数据类型
    在这里插入图片描述

13.2.2 性能优化

对于小型数据集，上一节的方法非常有效。但在处理大型数据集时，逆透视操作可能会导致运行缓慢，并且转置后数据可能只显示几行，用户无法判断是否需要填充，原因在于：

  • Power Query 更适合处理长而窄的表，而不是短而宽的表。
  • 转置数据计算成本高，且需要进行两次。

由于两次转置主要是为了解决原始数据中的多级标题行问题，所以可以把标题行数据取出来单独处理，再与数据行进行合并，这样即使数据集有100万条数据，也只需要转置标题行就行。为了做到这一点，这里将标准步骤分解为四个不同的查询，试验证明这会大大加快处理速度。

在这里插入图片描述
查询优化如下（见示例文件：Unpivoting Subcategories - Complete.xlsx）：

  1. Raw Data：创建查询，导入数据，重命名为 Raw Data查询，并将其设为仅限连接；
    在这里插入图片描述

  2. Data：引用Raw Data查询，将其中第3行之后的数据行提取为Data查询，并将其设为仅限连接；

  3. Headers：引用Raw Data查询，将其中前两行提取为Headers查询，然后执行步骤2到6，也将其设为仅限连接；
    在这里插入图片描述

  4. Output：将Data查询与 Headers查询进行合并（追加查询）
    在这里插入图片描述
    接着执行后续的步骤就行。

如果事先预计到了此性能问题，可在初始设计时将标准步骤分解为四个不同的查询；如果未预见，也可重构现有查询。

13.2.3 保留 null 值 （null->占位符->null）

在逆透视操作中，通常会自动去除包含“null”值的记录。这在大多数情况下是用户期望的结果，但在某些场景下，用户可能需要保留这些“null”值。比如下图中（示例文件Preserving Nulls - Complete.xlsx），对“Product”列进行逆透视操作，可导致某些包含“null”值的记录（“Mango”）丢失。

在这里插入图片描述
  在逆透视操作中保留 null 值的方法很简单，就是先将其替换为一个数据集中不存在的占位符，逆透视完毕之后再替换回null就行。

选择占位符是因为其比较特殊，很容易找到数据集中 没有的占位符，比如|。由于占位符是文本类型，如果要处理的列是数字或日期等其他类型，可以先将其转为文本类型再使用占位符。以下是具体的演示：

  1. 替换 null 值 ：选择含有null值的列，将null替换为一个占位符。此示例中，右击“In Stock”列，将null替换为|。
    在这里插入图片描述

  2. 逆透视列 ：选择“Product”列，【逆透视其他列】。
    在这里插入图片描述

  3. 还原占位符值 ：将占位符值还原为 null 值。

  4. 还原标题 ：将“属性”列和“值”列重命名为原先的列名。
    在这里插入图片描述

13.3 分组

分组操作用于对数据进行聚合和排序，下面使用第 13 章 示例文件BeerSales.txt，介绍其简单应用。

在这里插入图片描述

13.3.1 占总计的百分比

  1. 数据分组 ：

    • 启动分组操作：转到【转换】选项卡【分组依据】【高级】

    • 删除分组依据中自动填入的 “Class” 列：将鼠标悬停在“Class”列上，单击它右边出现的“…”按钮，选择【删除】）

    • 配置分组操作：对Sales”列进行求和操作，列名设为 “Total Sales” ；对所有行进行聚合，列名设为 “Data” 。
      在这里插入图片描述

      使用聚合所有行的方式得到Data列，后续直接展开，否则还需要进行连接操作。

    • 展开数据 ：展开 “Data” 列，获取原始数据行。

    • 计算百分比 ：将 “Sales” 列除以 “Total Sales” 列，得到 “% of Total Sales” 列。

    • 更换数据类型
      在这里插入图片描述

最好是使用数据透视表或 DAX 度量值生成“% of Total Sales”，但是用户也可以使用 Power Query在源数据级别执行此占比计算操作

13.3.2 数据排名
排名方法并列值处理排名序号特点示例
顺序排序并列值获得连续的序号每个项目都有唯一的排名，序号是连续的1, 2, 3, 4
标准竞争排序并列值获得相同的排名后续排名跳过并列项的数量1, 2, 2, 5
密集排序并列值获得相同的排名后续排名不跳过并列项的数量1, 2, 2, 3
13.3.2.1 顺序排序

按照销售额降序排列；如果销售额相同，则按项目名称排列，这样即使销售额相同，也会有不同的排名，所以叫顺序排列。

  • 对 “Sales” 列降序排序，对 “Item” 列升序排序
  • 添加索引列（从 1 开始），并将其重命名为 “Rank-Ordinal”。
    在这里插入图片描述
13.3.2.2 竞争排序（数值相同排名并列）
  • 数据分组：对“Sales” 列进行分组操作，选择【高级】，按如下方式进行配置
    在这里插入图片描述
  • 展开原数据：这里仅展开“Data”表中的“Item”列和“Rank-Ordinal”列查看结果
    在这里插入图片描述
13.3.2.3 密集排序（数值相同排名不同）
  • 数据分组：对“Sales” 列进行分组操作，选择【高级】，按如下方式进行配置
    在这里插入图片描述
  • 从1开始添加索引列，并将其重命名为 “Rank-Dense”
  • 展开“Data”列查看结果

在这里插入图片描述

13.3.3 分组编号（在每个分组级内添加序号）

在这里插入图片描述

  • 准备数据 ：对 “Class” 列升序排序，对 “Sales” 列降序排序，对 “Item” 列升序排序。

  • 数据分组 ：选择 “Class” 列，添加 “Data” 列（所有行操作）。此时，应该有三行数据，其中有一列表值包含每个组中所有行的详细信息。
    在这里插入图片描述

  • 添加编号 ：使用公式 =Table.AddIndexColumn([Data], "Group Rank", 1, 1) 添加自定义列，生成每个分组内的序号（通过用户界面做到这一点）。
    在这里插入图片描述

  • 展开数据 ：删除自定义列之外的其它列，展开自定义列，获取最终结果。

在这里插入图片描述

十四、条件判断

14.1 if表达式 与 非空数据的判断

if 表达式用于根据条件选择两个表达式中的一个。例如：

if 2 > 1 then
    2 + 2
else
    1 + 1

对于简单的条件判断，可以通过添加条件列来实现。比如有以下成绩表，通过添加条件列，可以实现评级结果：

在这里插入图片描述
在这里插入图片描述
其M公式为嵌套的if 语句：

if [科目一] >= 90 
then "优秀" 
  else if [科目一] >= 75 
  then "良好" 
   else if [科目一] >= 60 
     then "及格" 
      else "不及格"

如果有两列数据，需要多个判断条件来判定，就无法直接通过添加条件列来实现，而要用IF语句来完成（用 and/or 来连接多个条件）。另外，对于非空数据的判断，还可以用??来简化多重条件的判断。

假设有以下产品销量数据，有些产品是从第一年就有数据，有些产品是从后面的某一年才产生业务，如何添加一列，来显示每个产品首年业务数据呢？
在这里插入图片描述

这是一种很常见的需求，从上面的表格直观来看，就是如何找出第一个非空的数据。普通的做法可以通过IF嵌套判断，比如在PowerQuery中可以这样添加自定义列：

第一个非空数据=
if [2020]=null and [2021]=null and [2022]=null 
then [2023]
    else if [2020]=null and [2021]=null
    then [2022]
        else if [2020]=null
        then [2021]
            else [2020]

还有一种更简单的写法：

=[2020]??[2021]??[2022]??[2023]

双问号??是一种非空运算符，它会尝试返回符号前面的数据，如果前面的是空值，则返回后面的数据。

在这里插入图片描述
如果要同时获得首年的年份（非空列名），可以添加以下自定义列：

在这里插入图片描述

14.2 基础条件逻辑（引发错误，筛选数据）

以第14章示例文件timesheet.txt为例，记录了每位员工两周之内的上班天数和上班时间。

在这里插入图片描述
  文件中每条记录以标题行开始，包含工作人员的全名（分布在“Work Date”和“Out”两列）和“Worker ID”。记录以总计行结束，包含记录数量和总小时数。现在要将其转为如下的标准格式，使工作人员信息能正确关联到相关行。

在这里插入图片描述
  棘手的是，没有明显的数据点可以用来提取所需信息。不能依赖“Hrs”列中的值（因为“Worker ID”可能与有效小时数混淆），也不能依赖记录的数量（因为不同员工的记录数量可能不同）。那么，该如何完成任务呢？

  1. 连接到数据

    • 在 Excel 或 Power BI 中，通过“获取数据”功能导入 timesheet.txt 文件。
    • 删除前 4 行，将第一行用作标题。
    • 将查询设置为“暂存”查询，命名为“Raw Data – Timesheet”，并作为“仅限连接”加载。
    • 引用该查询并重命名为“Basics”，以便后续操作。
      在这里插入图片描述

  2. 通过界面操作（添加条件列）的方式，创建条件逻辑

    • 使用逗号合并“Work Date”和“Out”列，得到完整的员工姓名。

    • 将“Work Date”列的数据类型设置为“日期”，“Out”列设置为“时间”，故意触发数据类型错误（标题列的姓名是文本值，无法转为日期或时间格式）。
      在这里插入图片描述

    • 替换“Work Date”列和“Out”列错误值为“null”（主要是下一步判断条件中，如法使用Error)。

    • 添加如下条件列“Worker”。
      在这里插入图片描述
      在这里插入图片描述

    • 向下填充“Worker”列的“null”值，去除Worker Date”列中的null行

    • 删除不必要的“已合并”列，设置所有列的数据类型
      在这里插入图片描述

处理以上数据的主要逻辑是：利用数据类型转换错误来识别和分离工作人员姓名和其它数据，然后通过条件逻辑将工作人员姓名正确关联到每一行数据中。关键点在于通过故意触发错误并替换为“null”值，可以基于数据类型创建条件逻辑规则。

14.3 使用 try…otherwise 表达式简化步骤

14.3.1 try…otherwise 表达式

在Power Query中，没有类似DAX那样直接的 IFERROR 函数，但可以通过 try...otherwise 语法实现类似的功能。 try 表达式一般用于捕获和处理Error，例如：

let Sales = [
    Revenue = 2000,
    Units = 0,
    UnitPrice = if Units = 0 then error "No Units" else Revenue / Units
]
in try Sales[UnitPrice]

上面的代码表示如果 Units = 0，则返回一个错误 error "No Units"，否则，计算 Revenue / Units。try 表达式捕获到 Sales[UnitPrice] 的错误，并将其转换为一个包含错误信息的记录。

在这里插入图片描述
  常见的情况是使用默认值替换错误。 otherwise 子句允许你在捕获错误时直接提供一个默认值，而不是返回一个包含错误信息的记录。

try error "negative unit count" otherwise 42
  • error 是一个关键字，用于显式地抛出错误，并提供错误消息 “negative unit count”。
  • 如果计算过程中引发错误，则返回 otherwise 子句中指定的默认值 42

使用场景：

# 处理文本转换错误
try Number.FromText("abc") otherwise null
# 除零错误
try 10 / 0 otherwise 0
14.3.2 简化步骤

  1. 复制查询：右击“Raw Data - Timesheets”查询，选择“引用”，并重命名为“IFError”。

  2. 添加自定义列：添加自定义列“Worker”，公式为：

    // 尝试将Out列转换为时间，如果失败则返回null
try Time.From([Out]) otherwise null

    在这里插入图片描述

    从一种数据类型到另一种数据类型的转换可以通过在“Time.from”，“Date.from”，“Number.from”等函数中输入数据类型，后跟“.from([ Column ])”来完成。在这里选择使用时间格式而不是日期，以避免处理潜在的日期区域设置问题的复杂性。

    上述公式返回的“null”值并不是用户期望的结果，我们期望的是用员工姓名代替上述“null”值，用“null”值代替所有日期。所以正确的做法是将 try 语句包装在条件逻辑中。

    • 如果是“null”，说明转换失败，此行是标题行，应该返回“Work Date”和“Out”列的合并值（工作人员姓名）
    • 如果不是“null”，说明转换成功，此行是日期行，应该返回“null”。
      在这里插入图片描述

  3. 完成数据清理：后续步骤不变，最终从9个步骤简化到了5个步骤。

14.3.3 总结
  • Power Query 中没有直接的 IFERROR 函数，但可以通过 try...otherwise 语法实现类似功能： try 语句尝试执行某个操作，失败时返回备用结果。
  • 将 try 语句嵌入条件逻辑中，可以根据转换结果动态返回不同的值，减少数据清理的步骤，提高效率。

14.4 多条件判断

当需要对多个列进行条件判断，或者判断的值类似[A]-8这种需要包含列植表达式时，无法通过【条件列】对话框进行构建，而需要手动书写if表达式。比如在第 14 章 示例文件DuesList.txt中，Member Type列用于判断具体的缴费情况，只有同时缴纳高尔夫费（Golf Dues）和冰壶费（Curling Dues）的会员才会获得“All Access”通行证；Pays Options列用于判断会员是否缴纳了其中任何一种费用。

在这里插入图片描述

  1. 数据准备

    • 创建新查询，从文本/CSV文件导入“DuesList.txt”，将查询重命名为“Dues List”。
    • 将第一行用作标题，替换空单元格为“null”，完成数据准备工作。
      在这里插入图片描述

  2. 构建“Member Type”列：新建条件列，根据高尔夫费和冰壶费的缴纳情况，判断会员类型。其公式为：

    if [Golf Dues] <> null and [Curling Dues] <> null then "All Access" else "Not sure yet..."

    在这里插入图片描述
    现在优化此公式，区分只交了一种费用的情况，即将原先的"Not sure yet..."替换为新的逻辑：

    if [Golf Dues] <> null and [Curling Dues] <> null then "All Access"
else if [Golf Dues] <> null then "Golf Course"
else if [Curling Dues] <> null then "Curling Club"
else "None"

    在这里插入图片描述

  3. 创建“Pays Dues”列：判断会员是否支付了任何可选费用，公式为：

    if [Golf Option 1]<> null or [Golf Option 2] <> null then "Yes" else "No"

在这里插入图片描述

  1. 转换数据类型，并上载数据

这段话是在解释 Power Query 中使用逻辑运算符 and 的方式，以及如何在 Power Query 中构建复杂的条件逻辑。下面是对这段话的详细解释：

注意事项：

  1. 与 Excel 和 DAX 的区别
    在 Excel 和 DAX（数据分析表达式）中，逻辑运算符通常使用大写字母，并且位于要测试的项目之前，例如 AND([Condition1], [Condition2])。 而在 Power Query 中，逻辑运算符使用小写字母，并且位于逻辑测试之间，例如 <test 1> and <test 2>。

  2. 使用括号：当使用 多个 运算符进行嵌套测试或多个条件测试时，可能需要将逻辑测试用括号括起来，以确保逻辑运算的优先级和顺序正确，避免逻辑错误

  3. 逐步构建公式：如果用户不能够一次性写出完美的公式，可以分步骤进行。首先创建一个新的自定义列来构建每一部分的逻辑，然后逐步剪切和复制这些逻辑，最后将它们合并。在这个过程中，用户可以删除任何不再需要的步骤，以简化最终的公式。

14.5 与上下行进行比较

使用 Power Query 时的一大挑战是，没有简单的方法访问前一行或后一行，下面介绍一种技巧，通过合并查询来实现与上下行数据的比较。

在这里插入图片描述

  1. 导入数据：导入第 14 章 示例文件"Sales.txt"，将第一行用作标题，替换所有空单元格为“null”，以便统一处理。
    在这里插入图片描述

  2. 添加索引列： 添加两个索引列，一个从1开始，另一个从0开始。

  3. 合并查询：将查询合并到自身，通过索引列的匹配来创建行之间的关联，实现与前一行的比较。
    在这里插入图片描述

  4. 展开并排序，将“Qty”列的值移到下一行

    • 展开合并结果中的“Qty”列，取消勾选【使用原始列名作为前缀】复选框，将前一行的值移动到当前行
    • 将“索引”列按升序重新排序，确保数据的逻辑顺序正确
      在这里插入图片描述

  5. 创建自定义列：创建一个名为“Category”的新自定义列，用于提取类别值，公式为：

    if [Qty.1] = "---" then [Sales Item] else null

  6. 数据填充与清洗：

    • 右击“Category”列向上填充
    • 筛选掉“Qty.1”列中的“—”项。
    • 删除不再需要的索引列和“Qty.1”列。
    • 筛选掉“Sales Item”列中的“null”值。
    • 转换所有列的数据类型
      在这里插入图片描述

关键点：

  • 使用索引列和合并查询来实现与上下行的比较，或者根据前一行的数据提取类别值，从而实现复杂的数据处理逻辑。
  • 合并查询对话框中，与前一行进行比较时，“索引.1”列在上“索引”列在下；与下一行比较时“索引.1”列在下“索引”列在上（反转选择）
  • 与前一行比较时，展开合并结果会导致数据乱序，而与下一行比较则不会
  • 无论是将一个列或多个列的值移动到另一行，还是将值与其他行的值进行比较，此方法都是有效的（比如比较每个股票的当天与前一天的收盘价，以确定价格是上涨、下跌还是持平）

14.6 使用示例列自动提取数据

Power Query 还有一个功能叫“示例中的列”，它利用 Power Query 的智能分析能力，根据用户输入的示例自动生成和调整数据处理逻辑，使得用户即使不具备深入的 Power Query 知识，也能有效地处理复杂的数据转换任务。

为了演示【示例中的列】的强大功能，假设有一个非常复杂的数据集（“第 14 章 示例文件 FireEpisodes.txt），Power Query 无法轻松导入。我们强制将其作为单列数据来导入处理，最终我们需要提取每一行文本中的剧集名和剧集号。

  1. 使用固定宽度分隔符导入数据：导入文件时，将【分隔符】更改为【–固定宽度–】并将宽度设置为“0”，强制 Power Query 将整个文件内容作为单一列导入。
    在这里插入图片描述

  2. 启动【示例中的列】功能：转到【添加列】【示例中的列】，启动此功能。启动后，通过输入期望的输出示例，可以让 Power Query 自动生成提取逻辑。
    在这里插入图片描述

  3. 提取剧集名和剧集号：在列1中，先输入前两行数据“Pilot, Episode 1”和“Mon Amour, Episode 2”，Power Query 生成正确的提取公式，填充整个列。
    在这里插入图片描述

  4. 调整示例值，优化剧集名提取逻辑：可以看到在第10行数据中有一个多余的引号，所以需要继续优化

    • 优化1：更改错误示例：直接在在第十行输入“Merry Christmas, Etc., Episode 10”，此时其它行都变成空值，表示这三行示例使得提取逻辑太过复杂，结果公式直接变成了if语句（除这三行之外全为空值）。
      在这里插入图片描述
    • 优化2：简化提取逻辑：清除之前手动输入的所有示例值，只提取剧集名。在第一行输入剧集名称“Pilot”，第二行输入剧集名称“Mon Amour”，此时Power Query自动使用 Text.BetweenDelimiters 函数从文本中提取剧集名称。然后将列名更改为“Episode Name”。
      在这里插入图片描述

  5. 提取剧集号：再次使用“示例中的列”功能，输入剧集编号“Episode 1”和“Episode 2”；对于特殊剧集（自动提取结果为“Merry Christmas”），手动输入正确的编号“Episode 10”。最后将列名更改为“Episode Nbr”。
    在这里插入图片描述

  6. 自动合并列：再次启用【示例中的列】功能，在第一行输入“Pilot, Episode 1”，Power Query自动使用逗号合并Episode Name和Episode Nbr两列的值！
    在这里插入图片描述

  7. 清理数据：将合并的列将此列重命名为Episode，右击新的“Episode”列【删除其他列】

  8. 上载数据

总结：

  • 【示例中的列】功能允许用户在不了解 Power Query 函数的情况下，通过输入示例值来指导 Power Query 自动生成数据处理逻辑，比如找不到或记不起某个命令。
  • 用户可以通过取消勾选不需要参考的列来提升推导效率。

十八、处理日期时间

在数据分析中，日期表是组织和分析时间序列数据的关键工具，比如在构建分析报告时，需要按日期来筛选和切片数据。传统方法是手动创建硬编码日期表，但这种方法在财年结束时需要手动修改，效率低下且容易出错。本章中将研究如何构建完整日期表，以及基于已有数据自动生成与之匹配的日期表的方法。

直接从公司数据库中获取日期表是更好的方法，本文讨论的是无法公司数据中获得日期表的情况，比如只有手头的一大堆 Excel 或文本文件。

18.1 日期表的边界日期

在构建日期表时，需要考虑两个重要部分：日期表的边界日期（开始日期和结束日期） 以及所需的 “颗粒度”（即日期的明细程度，比如每日记录、每周记录、每月记录）。

每个日期表都有明确的边界，这些边界决定了日期表的时间范围，是构建日期表的基础。日期表的边界可以通过以下三种方式生成：

  • 参数：直接在Power Query中设置固定的开始日期和结束日期。
  • 动态参数表：通过动态参数表生成边界日期，这种方法更加灵活。
  • 从数据集动态生成：根据实际数据动态计算边界日期，这种方法能够确保日期表始终与数据匹配。

对于Excel 来说，动态参数表更好；对于数据集来说，从数据集动态生成日期更好，下面给出具体的操作思路，具体操作步骤可见“第 18 章 示例文件\Calendar Tables.xlsx”文件，其内容数据如下（销售表从2018-1-1到2019-12-31；预算表从2018-1-31到2019-12-31）：

在这里插入图片描述

18.1.1 计算边界日期

在具体的操作之前，先给出需要遵守的规则。

  1. 强烈建议构建的日期表涵盖整个财年数据
  2. 考虑所有的表来获取日期。例如从具有最早日期的表（比如销售表）中获取起始日期；从具有最晚日期的表（比如预算表）中获取结束日期。

鉴于上述情况，创建日期表的边界日期方法如下（假设日期列名为“Date”）：

步骤获取开始日期的流程获取结束日期的流程
1直接引用包含最早日期的表（如销售表）直接引用包含最晚日期的表（如预算表）
2删除除“Date”列以外的所有内容删除除“Date”列以外的所有内容
3筛选“Date”列中的最早日期（日期筛选器->最早）筛选“Date”列中的最晚日期
4删除重复值删除重复值
5转换为年份开始值（转换->日期->年->年份开始值）转换为年份结束值（如12月31日）
6可选：调整非标准财年日期可选：调整非标准财年日期
7将数据类型更改为“日期”将数据类型更改为“日期”
8右击日期单元格(而不是列标题)->深化，钻取日期值钻取日期值
9将查询重命名为“StartDate”将查询重命名为“EndDate”
10加载为“仅限连接”加载为“仅限连接”
  • 请注意，此标准步骤将破坏查询折叠。同样，如果用户的公司 IT 在 SQL 数据库中为用户提供了日期表，那么用户应该使用它。这个标准步骤是为那些不能做到这一点，只能自行完成他们工作的用户准备的。
  • 日期将根据用户本机的默认日期格式显示，因此可能与本书所展示的格式有所区别

关键点解析：

  • 步骤6：对于以12月31日结束的财年，可以跳过此步骤。如果财年结束日期不是12月31日，则需要调整日期。
  • 步骤7：定义日期数据类型是为了确保步骤8中使用“{0}”格式可以正确地钻取数据(右击深化）。
    在这里插入图片描述
18.1.2 处理非标准财年日期（结束日期不是12月31日）

许多公司的财年并不以结束，而是以其他月份结束（如9月30日）。为了适应这种情况，文章建议创建一个“YEMonth”查询作为参数变量，用于定义财年的最后一个月。创建“YEMonth”查询的步骤：

  1. 创建新的空白查询， 并将其重命名为“YEMonth”。
  2. 在公式栏中输入财年最后一个月的数值（如9表示9月30日）。
  3. 将查询加载为“仅限连接”。
    在这里插入图片描述

调整开始日期和结束日期的步骤：

步骤开始日期的标准流程结束日期的标准流程
6A转到“添加列”->“自定义列”转到“添加列”->“自定义列”
6B将列命名为“Custom”，并使用公式：
= Date.AddMonths([Date], YEMonth - 12)将列命名为“Custom”，并使用公式：
= Date.AddMonths([Date], YEMonth)
6C右击“Custom”列删除其他列右击“Custom”列删除其他列
6D将“Custom”列重命名为“Date”将“Custom”列重命名为“Date”

在这里插入图片描述

如果开始日期是2018年1月1日，而财年以9月30日结束，那么调整后的财年开始日期将是2017年10月1日，结束日期将是2018年9月30日。

18.1.3 处理非标准财年日期（每年364天）

除了常见的12个月日期结构，还有一种流行的日期结构是364天日期结构，包括“4-4-5”、“4-5-4”、“5-4-4”和“13 x 4”周。这些日期结构每年跨越364天，年终日期每年不同。

日期结构类型每个季度分布每年总周数
4-4-54周 + 4周 + 5周52周（364天）
4-5-44周 + 5周 + 4周52周（364天）
5-4-45周 + 4周 + 4周52周（364天）
13 x 452周（364天）

为了使这一过程尽可能简单，建议创建一个新的查询来完成这个任务。

  1. 创建新的空白查询，并将其重命名为“Start364”。
  2. 在公式栏中输入任何会计年度第一天的日期。
    假设公司会计年度开始于 2017-01-01、2017-12-31 和 2018-12-30（每个日期都是星期日），可以在“Start364”查询中使用其中任何一个值
  3. 将查询加载为“仅限连接”。

在这里插入图片描述

调整开始日期和结束日期的步骤：

步骤开始日期的标准流程结束日期的标准流程
6A转到“添加列”->“自定义列”转到“添加列”->“自定义列”
6B将列命名为“Custom”，并使用公式：
=Date.AddDays(Start364, 364 * Number.Round(Duration.Days([Date] - Start364) / 364, 0))将列命名为“Custom”，并使用公式：
=Date.AddDays(Start364, 364 * Number.RoundUp(Duration.Days([Date] - Start364) / 364, 0) - 1)
6C右击“Custom”列删除其他列右击“Custom”列删除其他列
6D将“Custom”列重命名为“Date”将“Custom”列重命名为“Date”
  • Duration.Days([Date] - Start364)计算从会计年度第一天到当前日期的天数。Number.Round(...)和Number.RoundUp(...)用于计算完整的364天周期。
  • 如果开始日期是2018年1月1日，而财年开始日期是2017年1月1日，财年是364天，那么调整后的开始日期将是2017年12月31日，结束日期将是2018年12月29日。

在这里插入图片描述

18.2 原子日期表（每日）

18.2.1 标准财年

原子日期表是最基础的日期表，它包含了从开始日期到结束日期的每一天。构建原子日期表的步骤如下：

  1. 创建新的空白查询，并将其重命名为“Calendar”；

  2. 创建开始日期到结束日期的列表：在公式栏输入

    = { Number.From( StartDate ) .. Number.From( EndDate ) }

    在这里插入图片描述

  3. 使用【列表工具】【转换】【到表】工具，将列表转换为表，然后重命名为“Date”列；

  4. 将“Date”列的数据类型更改为【日期】

  5. 增强日期表：按业务需要，可使用【添加列】【日期】功能，添加需要的日期类型数据
    在这里插入图片描述

18.2.2 非标准财年（结束日期不是12月31日）

对于非标准财年（如以9月30日为财年结束），可用以下公式来计算财政日期列。其中，YEMonth是财年结束的月份（如9表示9月30日）。

列名需要的列公式
Fiscal Year“Date”Date.Year(Date.AddMonths([Date],12-YEMonth))
Fiscal Month“Date”Date.Month(Date.AddMonths([Date],-YEMonth))
Fiscal Quarter“Fiscal Month”Number.RoundUp([Fiscal Month]/3)
Fiscal Month of Quarter“Fiscal Month”if Number.Mod([Fiscal Month],3) = 0 then 3 else Number.Mod([Fiscal Month],3)
End of Fiscal Year“Date”、“Fiscal Month”Date.EndOfMonth(Date.AddMonths([Date], 12-[Fiscal Month]))
End of Fiscal Quarter“Date”、“Fiscal Month Quarter”Date.EndOfMonth(Date.AddMonths([Date], 3-[Fiscal Month of Quarter] ) )

在这里插入图片描述

18.2.3 非标准财年（每年364天）

构建364天日期表的最大难点在于其周期划分方式与传统日期结构不同，需要特殊的列来支持其独特的日期逻辑和报告周期。

  1. 创建“DayID”列：创建从1开始的索引列，并将其重命名为“DayID”
  2. 创建“PeriodID”列：使用以下公式创建各种PeriodID”列。注意：对于不同的日期结构，“MonthID”公式将不同
列名需要的列公式
WeekID“DayID”Number.RoundUp([DayID]/7)
MonthID (4-4-5)“DayID”Number.RoundDown([DayID]/91)*3+ ( if Number.Mod([DayID],91)=0 then 0 else if Number.Mod([DayID],91)<= 28 then 1 else if Number.Mod([DayID],91)<= 56 then 2 else 3 )
MonthID (4-5-4)“DayID”Number.RoundDown([DayID]/91)*3+ ( if Number.Mod([DayID],91)=0 then 0 else if Number.Mod([DayID],91)<= 28 then 1 else if Number.Mod([DayID],91)<= 63 then 2 else 3 )
MonthID (5-4-4)“DayID”Number.RoundDown([DayID]/91)*3+ ( if Number.Mod([DayID],91)=0 then 0 else if Number.Mod([DayID],91)<= 35 then 1 else if Number.Mod([DayID],91)<= 63 then 2 else 3 )
MonthID (for 13x4 Calendars)Number.RoundUp([DayID]/28)
QuarterID“DayID”Number.RoundUp([DayID]/91)
YearID“DayID”Number.RoundUp([DayID]/364)
Fiscal Year“YearID”Date.Year(Date.From(StartDate))+[YearID]，例如2018

在这里插入图片描述

列名需要的列年份和周期组合的公式，可能需要在最终结果中加上或减去 1
Quarter of Year[QuarterID]Number.Mod([QuarterID]-1,4)+1
Month of Year[MonthID]Number.Mod([MonthID]-1,12)+1
Week of Year[WeekID]Number.Mod([WeekID]-1,52)+1
Day of Year[DayID]Number.Mod([DayID]-1,364)+1
列名需要的列x 季度，x 月和 x 周的列公式
Month of Quarter“Month of Year”Number.Mod([Month of Year]-1,3)+1
Week of Quarter“Week of Year”Number.Mod([Week of Year]-1,13)+1
Day of Quarter“Day of Year”Number.Mod([Day of Year]-1,91)+1
Day of Month (4-4-5)“Day of Quarter”、 “Month of Quarter”if [Month of Quarter] = 1 then [Day of Quarter]
else if [Month of Quarter] = 2 then [Day of Quarter] - 28 else [Day of Quarter] - 35
Day of Month (4-5-4)“Day of Quarter”、 “Month of Quarter”if [Month of Quarter] = 1 then [Day of Quarter]
else if [Month of Quarter] = 2 then [Day of Quarter] - 28 else [Day of Quarter] - 63
Day of Month (5-4-4)“Day of Quarter”、 “Month of Quarter”if [Month of Quarter] = 1 then [Day of Quarter]
else if [Month of Quarter] = 2 then [Day of Quarter] - 35 else [Day of Quarter] - 63
Week of Month“Day of Month”Number.RoundUp([Day of Month]/7)
Day of Week“Day of Year”Number.Mod([Day of Year]-1,7)+1
列名需要的列x列的天数公式
Days in YearN/A364
Days in QuarterN/A91
Days in Month (4-4-5)“Week of Quarter”if [Week of Quarter] > 8 then 35 else 28
Days in Month (4-5-4)“Week of Quarter”if [Week of Quarter]>4 and [Week of Quarter]<10 then 35 else 28
Days in Month (5-4-4)“Week of Quarter”if [Week of Quarter] < 5 then 35 else 28
Days in WeekN/A7
列名需要的列x 列开始或者 x 列结束的公式
Start of Week“Date”、“Day of Week”Date.AddDays([Date],-([Day of Week]-1))
End of Week“Start of Week”Date.AddDays([Start of Week],6)
Start of Month“Date”、“Day of Month”Date.AddDays([Date],-([Day of Month]-1))
End of Month“Start of Month”、“Days in Month”Date.AddDays([Start of Month],[Days in Month]-1)
Start of Quarter“Date”、“Day of Quarter”Date.AddDays([Date],-([Day of Quarter]-1))
End of Quarter“Start of Quarter”Date.AddDays([Start of Quarter],91-1)
Start of Year“Date”、“Day of Year”Date.AddDays([Date],-([Day of Year]-1))
End of Year“Start of Year”Date.AddDays([Start of Year],364-1)
18.2.4 示例文件介绍

示例文件中包含了以下几种日期表：

  1. Calendar：标准的12个月日期表，每年12月31日结束。
  2. Calendar-Sep30：12个月日期表，会计年度结束日期为每年9月30日。
  3. Calendar-445：使用“4-4-5”周模式的364天日期表。
  4. Calendar-454：使用“4-5-4”周模式的364天日期表。
  5. Calendar-544：使用“5-4-4”周模式的364天日期表。

下图显示了每个表都加载到数据模型中，并通过日期列连接到“Sales”表和“Budgets”表：

数据模型中还创建了“Sales ”和“Budget”和“Budget”和“Budget ”度量，并在Comparisons工作簿中构建透视表进行不同财年统计结果的比较，可以直观地发现日期报告数据的方式之间的异同。

在这里插入图片描述

以下是对https://www.excel120.com/#/pq/c-18中18.3章节“日期时间填充”的详细总结：

18.3 日期时间填充

上述解决方案展示了如何在两个特定日期之间填充日期，但是如果只知道开始日期以及持续时间，该怎么填充日期呢？

18.3.1 日期级别填充

以“第 18 章 示例文件\Fill Dates-Begin.xlsx”文件为例，只知道每位访客的开始访问日期，以及访问持续天数，此时可使用List.Dates函数来生成每个人的访问日期表。

在这里插入图片描述

  1. 导入数据：创建一个新查询，从“Visitors”表中读取开始日期和持续天数；

  2. 填充日期列表：使用以下公式添加自定义列，并重命名为“Pass Date”。

    // 三个参数分别是开始日期、天数（列表元素个数）以及日期间隔
// duration的四个参数分别表示天、小时、分钟、秒。
=List.Dates([Arrival], [Days on Site], #duration(1,0,0,0))

    在这里插入图片描述

  3. 数据清洗：

    • 保留“Pass Date”列和相关的主列（如“Visitor”列），删除其它不必要的列
    • 扩展“Pass Date”列，选择【扩展到新行】，将列表中的每个日期展开为单独的行。
    • 转换所有列的数据类型，将该查询重命名为Pass List
      在这里插入图片描述

不推荐使用List.Dates函数生成完整的日期表，因为它无法直接表示“月”的持续时间(每个月的实际天数都不同，计算起来较为复杂），而是应该使用上两节介绍的方式。

18.3.2 小时级别填充

如果需要以小时为单位进行日期时间填充，比如从每天上午 9:00 开始添加 8 个小时的访问记录，可以使用List.Times函数来完成，其语法和List.Dates是一样的。

  • 导入数据：复制上一个查询，并将其命名为“Pass Times”。

  • 填充日期时间列表：使用以下公式添加自定义列，并重命名为““Hour””。

    =List.Times( #time(9,0,0), 8, #duration(0,1,0,0) )

  • 将“Hour”列扩展到新行，最后设置数据类型
    在这里插入图片描述

  • 持续时间值的三个参数是小时、分钟、秒，且依赖于24小时制的时间。
  • 如果需要在日期和时间之间进行转换，可以使用#datetime函数。
18.3.3 带时间间隔的填充

如果需要生成一个以一定间隔（Frequency）重复特定次数（Check Ins）的日期表，可以通过修改List.Dates函数的最后一个参数来实现。

在这里插入图片描述

  • 打开Fill Every x Dates-Begin.xlsx文件，创建一个查询，连接到Contracts表。

  • 添加自定义列“Follow Up”，其公式为：

    // Duration.From(days)函数会返回一个持续时间为days天的值。
=List.Dates([Contract Start], [Check Ins], Duration.From([Frequency]))

18.4 按日期分摊

在财务和业务分析中，经常需要将收入或支出按日期分摊到不同期间，比如按实际天数分摊、按月数平均分摊（每个月有几天不重要）、 按特定规则(如15日规则)分摊：
在这里插入图片描述

首先创建“Raw Data”查询：使用= Excel.CurrentWorkbook()公式获取当前Excel表的所有表，然后导航到其中的Sales数据表，并将查询加载为【仅创建连接】。
在这里插入图片描述

18.4.1 起止日内按日分摊

在这里插入图片描述

  1. 导入数据： 引用“Raw Data”查询，删除不必要的“Month”列；

  2. 计算每日分摊金额： 添加自定义列“Amount”，公式为=[Sale] / (Number.From([End Date]) - Number.From([Start Date]) + 1)，计算每天的分摊金额。
    在这里插入图片描述

  3. 生成日期列表并展开：

    • 添加自定义列“Date”，公式为={Number.From([Start Date]) .. Number.From([End Date])}，生成从开始日期到结束日期的日期列表。
    • 将“Date”列扩展到新行，然后将数据类型更改为日期。
      在这里插入图片描述

  4. 数据清洗

    • 如果希望数据处于这种日期粒度级别，那么只需删除“Sale”、“Start Date”和“End Date”列并设置数据类型，然后就可以加载数据了。
    • 如果需要按月汇总分摊金额，那么执行以下步骤
      • 将“Date”列转换为月末值。
      • 按“以下方式进行分组，并对“Amount”列求和，汇总每个月的分摊金额。
      • 删除不必要的列，设置数据类型。
        在这里插入图片描述
  • 计算日期差时需要加1，以确保包括起止日期在内的所有天数都被计算。
  • 分摊金额时未进行四舍五入，以避免舍入误差累积；如果需要，建议在最后一部进行舍入。
18.4.2 起止日内按月分摊

在这里插入图片描述

Power Query 没有一个可以根据起始日期创建月末日期列表的函数，所以第一步是先自定义一个函数来实现此功能。

  1. 创建自定义函数生成月末日期列表：

    • 创建两个日期类型参数“FromDate”和“ToDate”，当前值分别为“2021-06-01”和“2021-08-31”
    • 创建空白查询“getMonthEnds”，参考18.1.1章节，生成从“FromDate”到“ToDate”之间的月末日期列表。
    • 将该查询转换为函数fxGetMonthEnds。
      在这里插入图片描述

  2. 计算每月分摊金额：

    • 引用“Raw Data”查询，删除不必要的“Months”列
    • 调用自定义函数fxGetMonthEnds，【FromDate】选择“Start Date”列，【ToDate】选择“End Date”列，生成月末日期列表。
    • 添加自定义列“Amount”，公式为=[Sale] / Table.RowCount([fxGetMonthEnds])，计算每月的分摊金额。

  3. 展开并整理数据：

    • 选择“Client”、“fxGetMonthEnds”和“Amount”列【删除其他列】
    • 展开“fxGetMonthEnds”列，将月末日期展开为单独的行。
    • 设置数据类型。

在这里插入图片描述

  • 自定义函数fxGetMonthEnds可以重复使用，适用于任何需要生成月末日期列表的场景。
  • 使用Table.RowCount函数统计月末日期的数量，从而计算每月分摊金额。
18.4.3 起点日后按月分摊(15日规则)

本质上还是按月分摊，但是需要根据开始日期是否在当月15日之前或之后来决定是否从当月开始分摊：
在这里插入图片描述

  1. 计算每月分摊金额：

    • 引用“Raw Data”查询，删除不必要的EndDate”列；
    • 添加自定义列“Amount”，公式为=[Sale] / [Months]，计算每月的分摊金额。
      在这里插入图片描述

  2. 生成月份偏移量列表：

    • 使用以下公式添加自定义列“Custom”，根据开始日期的天数生成偏移量列表。

      =if Date.Day([Start Date]) <= 15 then {0 .. [Months] - 1} else {1 .. [Months]}

    • 将“Custom”列展开为新行
      在这里插入图片描述

  3. 计算分摊日期：

    • 创建一个名为“Date”的新【自定义列】，该列使用以下公式，根据偏移量计算分摊的月末日期。

      = Date.EndOfMonth( Date.AddMonths( [Start Date], [Custom] ) )

    • 选择“Date”、“Client”和“Amount”列【删除其他列】，设置数据类型。

  • 条件公式if Date.Day([Start Date]) <= 15用于判断是否从当月开始分摊。
  • 使用Date.AddMonths函数根据偏移量计算分摊日期，如果不需要将分摊日期设置为月末，可以删除Date.EndOfMonth函数。

本文转自 https://blog.csdn.net/qq_56591814/article/details/147497066，如有侵权，请联系删除。

2023-2025 Powered by Kai with ❤️