Power Query 真经 - 第 18 章 - 处理日期时间
共 28606字，需浏览 58分钟
· 2023-10-26
🔈不想再错过干货？点击↑上方↑蓝字 关注 星标，第一时间收到最新资讯。
本章 PQ 真经主要是关于如何处理日期的，大家日常工作中少不了处理日期的操作，建议收藏观看噢。
在构建分析报告时，需要按日期来筛选和切片数据，这通常需要在解决方案中使用日期表。许多 Excel 专业人员是通过在工作表中构建硬编码日期表来解决这一需求的，通过公式实现，将其链接到或导入到数据模型中。然而，问题在于这些表格通常需要在财年结束时手动修改，如果能变得自动化而不再依赖手工维护，那岂不是更好吗？
在本章中，将研究如何构建完整日期表，以及基于已有数据自动生成与之匹配的日期表的方法。
首先需要说明的是，用 Power Query 动态构建一个日期表的确需要一定工作量，也需要定期【刷新】。用户可能会问，直接从公司数据库中获取日期表是否更好？答案是肯定的，如果用户可以从公司数据库中获得日期表，那应该优先用这个方法。但是如果无法从公司数据中获得日期表怎么办？或者用户只有手头的一大堆 Excel 或文本文件，而无法访问公司 IT 管理的日期表，那又该怎么办呢？这就是本章内容要为用户打开的大门。
在构建日期表时，需要考虑三个重要部分：日期表的开始日期、结束日期以及所需的“颗粒度”（即日期需要每日记录、每周记录、每月记录还是其他明细程度）。在本章中，将提供生成每个部分的方法，希望用户能够利用这些方法来生成满足自己需求的日期表。
18.1 边界日期
每个日期表都有边界：开始日期和结束日期。事实证明，至少能使用以下三种方式生成日期表的边界。
【参数】。
动态参数表（如第 17 章所示）。
从数据集动态生成的数据。
使用【参数】的挑战在于，这些值是硬编码的，需要在 Power Query 编辑器中进行手动更新。对于 Excel 来说，动态参数表更好，从经验来看，构建日期的推荐方法是直接根据实际数据动态获取日期。这样，【刷新】文件后，将始终确保日期表覆盖实际数据所需的所有日期。
本章构建日期表的方法蕴含大量高级技巧，同时涵盖了多种不同的使用场景。为了保持本章的简洁，就不再提供基于示例数据创建这些表的逐步演练。不过，案例文件是完整的参考，包含了所有不同的日期表模板。可以在“第 18 章 示例文件\Calendar Tables.xlsx”文件中找到。
18.1.1 计算边界日期
如果按照以下流程操作，用 Power Query 为日期表生成边界日期实际是相对容易的。然而，在具体操作之前，先给出需要遵守的规则。
本书强烈建议构建的日期表涵盖整个财年数据。
考虑所有的表，从具有最早日期的表中获取起始日期，如：销售表通常是一个很好的选择。
考虑所有的表，从具有最晚日期的表中获取终止日期，如：预算表通常是一个很好的选择。
鉴于上述情况（并假设保存日期的列名为“Date”），创建日期表的边界日期，方法如表18-1所示。
步骤 |
获取开始日期的流程 | 获取结束日期的流程 |
|---|---|---|
| 1 | 参考包含最早日期的表 | 参考包含最晚日期的表 |
| 2 | 删除除“Date”列以外的所有内容 | 删除除“Date”列以外的所有内容 |
| 3 | 筛选“Date”列【日期筛选器】【最早】 | 筛选“Date”列【日期筛选器】【最晚】 |
| 4 | 删除重复值 | 删除重复值 |
| 5 | 【转换】【日期】【年】 【年份开始值】 | 【转换】【日期】【年】 【年份结束值】 |
| 6 | 可选：为非标准的年终更改日期。 请参阅下面的“调整非标准会计年度的开始/结束日期”部分。 |
可选：为非标准的年终更改日期。 请参阅下面的“调整非标准会计年度的开始/结束日期”部分。 |
| 7 | 将数据类型更改为【日期】 | 将数据类型更改为【日期】 |
| 8 | 右击日期单元格向下钻取（【深化】） | 右击日期单元格向下钻取（【深化】） |
| 9 | 将查询重名命为“StartDate” | 将查询重名命为“EndDate” |
| 10 | 加载为【仅限连接】 | 加载为【仅限连接】 |
表 18-1获取日期表开始日期和结束日期的标准步骤
请注意，此标准步骤将破坏查询折叠。同样，如果用户的公司 IT 在 SQL 数据库中为用户提供了日期表，那么用户应该使用它。这个标准步骤是为那些不能做到这一点，只能自行完成他们工作的用户准备的。
这些步骤中的大多数都相当简单，但要特别指出如下三个步骤。
步骤 6：对于以 12 月 31 日结束的 12 个月日期，可以跳过此步骤。在接下来的章节中，将对交替财年结束的这一步骤进行详细介绍。
步骤 7：在步骤 7 中重新定义日期数据类型的原因，是为了确保步骤 8 中使用“{0}”格式可以正确地钻取数据 （有时跳过步骤 7 时不会发生这种情况）。
步骤 8：执行向下钻取时，右击日期而不是列标题来执行向下钻取非常重要，如图18-1所示。
图18-1 在起始日期向下钻取
如果用户正确地执了步骤 8，则应该以基元值的形式获得日期，如图18-2所示。
图18-2 “StartDate”应基于“{0}”并显示为原始日期数据类型
日期将根据用户本机的默认日期格式显示，因此可能与本书所展示的格式有所区别。
如果右击列标题，将得到转换得到列表，但这样做与整个流程操作是不匹配的。
18.1.2 处理财年日期
到目前为止，世界上最常用的日期表格式是 12 个月的日期，但当然每个公司的财年可能不同，并不会在 12 月 31 日这天正好结束。幸好，改变日期表的开始日期和结束日期很容易，可以用此来匹配用户财年的开始日期和结束日期。
为了尽可能简化此操作，本书建议创建“YEMonth”查询作为参数变量，如下所示。
创建新的【空白查询】。
将查询重命名为“YEMonth”。
在公式栏中输入财政年度最后一个月的数值。
仅将查询加载为【仅限连接】。
假设用户的公司有一个 9 月 30 日的年末，查询如下所示，如图18-3所示。
图18-3 以 9 月 30 日作为年末的日期表
创建“YEMonth”查询后，用户可以遵循（或修改）上文所示的“StartDate”和“EndDate”流程步骤，其中步骤 6 扩展为包括如表18-2所示的步骤。
步骤 |
开始日期的标准流程 | 结束日期的标准流程 |
|---|---|---|
| 6A | 转到【添加列】【自定义列】 | 转到【添加列】【自定义列】 |
| 6B | 将列命名为“Custom”，并使用公式： = Date.AddMonths( [Date] , YEMonth - 12 ) |
将列命名为“Custom”，并使用公式： = Date.AddMonths( [Date] , YEMonth ) |
| 6C | 右击“Custom”列【删除其他列】 | 右击“Custom”列【删除其他列】 |
| 6D | 将“Custom”列重命名为“Date” | 将“Custom”列重命名为“Date” |
表 18-2 非标准 12 个月的日期表如何在标准流程的步骤 6 中获取开始日期和结束日期
如果已经创建了“StartDate”和“EndDate”查询，并且需要插入这些步骤，请确保从计算的年初（年末）步骤开始。
图18-4将原始“Start Date”模式的结果与完全遵循步骤 6 生成的“FiscalStartDate”的结果进行比较。关于这个数据集，需要认识到的重要一点是，最早的销售交易是 2018 年 1 月 1 日，因此该模式将日期转换为涵盖整个财政年度的日期，无论是从1 月 1 日至 12 月 31 日，还是从 10 月 1 日至 9 月 30 日。
图18-4 比较原始的“StartDate”和以 9 月 30 日为年末的“FiscalStartDate”的结果
为了在同一文件中显示常规日期结束和财政年度结束，完成的示例文件包含依赖于本节所示修改的“FiscalStartDate”和“FiscalEndDate”查询。
18.1.3 处理 364 日型
虽然公司日期格式有很多种，但除了 12 个月的日期结构之外，还有一种流行的日期结构是 364 天的日期结构，包括“4-4-5”、“4-5-4”、“5-4-4”和“13 x 4”周。虽然每一个都是根据用于推导季度或年份的周数来定义的，但它们都有一个共同点，即它们每年跨越 364 天，每年的年终日期都不同。
再次，需要对原始的“StartDate”和“EndDate”查询步骤进行调整，以使其起作用。同样，为了使这一过程尽可能简单，建议创建一个新的查询来完成这个任务。这一次，将把查询命名为“Start364”，并使用它记录公司历史记录中任何有效会计年度的第一天。为此，需要进行如下的操作。
创建新的【空白查询】。
将查询重命名为“Start364”。
在公式栏中输入任何会计年度第一天的日期。
仅将查询加载为【仅限连接】。
假设公司会计年度开始于 2017-01-01、2017-12-31 和 2018-12-30（每个日期都是星期日），可以在“Start364”查询中使用其中任何一个值，如图18-5所示。
图18-5 2017 年 1 月 1 日（星期日）是本公司会计年度的有效起始日期
如果查询未在查询窗格中显示日期图标，请尝试按以下格式输入：“=#date(2017,1,1)”。
有了“Start364”查询，现在可以采用最初的流程，并使用如表18-3所示步骤 6 的版本成“StartDate”和“EndDate”查询。
表18-3 按步骤 6 生成基于 364 天年终的日期“StartDate”和“EndDate”查询
步骤 |
开始日期的标准流程 | 结束日期的标准流程 |
|---|---|---|
| 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” |
就样本数据而言，最早的数据点是 2018 年 1 月 1 日。这意味着，基于上述定义的日期模式，364 天日期模式必须从 2017 年 12 月 31 日开始，因为这是包括最早记录的财年的第一个日期。正如看到的，实际计算结果符合预期，如图18-6所示。
图18-6 “StartDate364”是正确的，尽管“Start364”是上一财年的第一个日期
同样，为了允许对同一文件中的所有日期进行比较，完整的示例提供了“StartDate364”和“EndDate364”查询。
18.2 日期表
现在知道了如何为各种不同的日期模式生成开始日期和结束日期，接下来就来构建一个日期表。此日期将从开始日期到结束日期，以每日为粒度（每天记录一次，没有间隔），创建一个日期表。
18.2.1 原子日期表
一旦定义了“StartDate”和“EndDate”，就会发现创建日期表只需要遵循一个非常简单的方法。
创建新的【空白查询】。
在公式栏中输入以下公式：
= { Number.From( StartDate ) .. Number.From( EndDate ) }进入【列表工具】【转换】【到表】【确定】。
将“Column1”列重命名为“Date”。
将“Date”列的数据类型更改为【日期】。
将查询重命名为“Calendar”（或任何用户喜欢的名称）。
不幸的是，无法使用“{..}”创建日期列表结构，但可以创建一个从一个数字到另一个数字的列表。因此，使用 Number.From 函数将日期转换为它们的等价序列号。虽然可以将单个查询转换为数值，但这种方法将暂存查询显示保留为日期，使得以后查看它们更容易。
最终结果是一个具有每日粒度的完全动态日期表，该表涵盖了整个（财政）年度，数据现在看起来将如图18-7所示。
图 18-7 一个完美的 100% 动态的日期表
此时，用户应该认识到一些非常重要的事情。虽然这是使用从每年 1 月 1 日到 12 月 31 日的标准年终的开始日期和结束日期生成的，但还可以用上一节中计算了各种（财政）年度的开始日期和结束日期。如果使用它们，日期表将覆盖相应范围。
如果提供的“EndDate”早于“StartDate”，那么表将生成一个空列表，然后在尝试重命名不存在的“Column1”时将会返回步骤级错误。因此，在基于不同查询的开始日期和结束日期时，都需要小心，确保选择的列永远不会导致这种情况。
还要认识到的另一件事，此方法是填充两个日期之间连续日期的通用方法，它完全不局限于构造日期表，将在本章后面看到。
18.2.2 增强日期表
如果用户计划对日期表进行任何实际分析，那么将需要创建一些辅助列来表示周期：如月份、月份名称、年份等。幸运的是，Power Query 使这一过程变得非常简单，因为它包含了一组内置的日期转换，很容易实现它。要使用它们，需要遵循以下三个步骤。
选择【日期列】【添加列】【日期】。
选择需要的时间段。
根据实际情况需要循环操作以上步骤 1。
图18-8 显示了将“Year”、“Month”和“Month Name”列添加到查询中的结果。
图18-8 向“Calendar”表中添加一些有用的列
如果创建的列返回的日期类似于月末列，请不要忘记返回到步骤 1。创建下一个转换非常容易，而且只需从新创建的列而不是原始的日期列中提取数据。
18.2.3 财政日期列
虽然内置的日期转换非常方便，但当应用于使用 12 月 31 日以外的年终的日期时，它们可能会崩溃。幸运的是，可以通过【自定义列】中的公式计算财政日期（例如财政月）。
表18-4包含一些关键公式，如果需要提取不是以 12 月 31 日为截止日期的 12 个月年终报告期，可能会发现这些公式很有用。
表 18-4 以 12 个月为年终日期的一些有用的会计期间公式
| 列名 | 需要的列 | 公式 |
|---|---|---|
| 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] ) ) |
此处显示的结果是使用上面的会计年度和会计月公式生成的，月份名称使用标准的月份名称转换，从“Date”列转换而来，如图18-9所示。
图18-9 在 9 月 30 日年末后重置的会计年度和会计月列
18.2.4 全局日期列
构建 364 天日期的最大挑战是，无法使用适用于 12 个月日期的标准日期转换。相反，需要一组特殊的列，以便构造 364 天日期的变体日期表，进而用于构建财务报告。无论该日期是“4-4-5”版本还是基于 13 个月的 4 周时间，它都从一个非常特定的列开始：“DayID”。
“DayID”列本质上表示日期的行号，该行号每天递增，并且在整个表中从不重复。然后，它将用于驱动需要的其他每个报告周期。幸运的是，它非常容易创建。生成跨越日期表的日期范围的列（如前所示）后，可以按如下方式创建“DayID”列。
转到【添加列】【索引列】【从1】。
将新列重命名为“DayID”。
在关键列准备好后，可以添加所需的其余“PeriodID”列。每个都需要使用一个【自定义列】 ，诀窍在于知道要使用的公式。表18-5包含“4-4-5”日期变量的列公式，请注意所需的变量不同，所以每个日期变量的“MonthID”公式将不同。
（译者注：关于如何生成“4-4-5”等日期表的逻辑并未在书中给出仔细解释，但提供了技术实现细节，读者可对照实现和其他参考自行理解。）
表18-5 用于驱动 364 天日期表的关键：全局日期列
| 列名 | 需要的列 |
公式 |
|---|---|---|
| WeekID | “DayID” | Number.RoundUp([DayID]/7) |
| MonthID (for 4-4-5 Calendars) | “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 (for 4-5-4 Calendars) | “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 (for 5-4-4 Calendars) | “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) |
如果熟悉罗布·科利（Rob Collie） 的 GFITW DAX 模式，则会意识到这里实现的列是在该模式中使用 DAX 处理日期表的关键所在。
（译者注：这里用 Power Query 生成的日期表常常用于构建数据模型，并与 DAX 搭配使用。在 DAX 中的日期智能函数不能处理自定义日期区间的相关计算，需要用更通用的 DAX 处理模式，对此，在 Power BI 领域著名的 Rob Collie 总结了一个编写 DAX 公式的套路，被俗称：GFITW（Greatest Formula In The World），以便于识记和反映该公式模式具有的通用性。根据需要，读者可以自行搜索该 DAX 模式并理解细节。）
图18-10 “4-4-5”模式日期在第 1 年年底显示的各个日期区间“ID”列
关于这些列，需要认识到的关键一点是它们可能并不直接用于报告，而是用于驱动其他与日期有关的逻辑计算。
这里提供的模式没有考虑 364 天日期模式可能的进一步的变化，如果用户公司实际会几年后补一周，即每 x 年增加一周，则需要调整使用的逻辑以实现这一需求。
18.2.5 自定义日期表
与使用 Power Query 标准日期转换的 12 个月日期不同，自定义日期需要用户为希望生成的每个报告期编写自定义公式。幸运的是，有了“DayD”列，现在就能完成这个工作了。
可以想象，生成这个逻辑有一点难度，因此本书提供了一组公式，允许用户生成按“4-4-5”日期（以及其“4-5-4”和“5-4-4”变体）的财政期间报告的列。在使用这些公式时，请确保非常仔细地注意“Required Columns”部分，因为其中许多都需要先创建额外的查询或列。
不希望或不需要最终表中的某个必需列？不用担心。最后把它删掉就可以了， Power Query 不会阻止用户这样做的，结果将如表18-6所示。
表 18-6 “Fiscal Year”列的公式
列名 |
需要的列 | 公式 |
|---|---|---|
| Fiscal Year | “StartDate445”，“YearID” | Date.Year(Date.From(StartDate))+[YearID] |
需要牢记的一点是，根据使用的第一个日期和希望代表的财政年度，可能需要在最终结果中加上或减去 1，如表18-7所示。
表 18-7 年份和周期组合的公式
| 列名 | 需要的列 | 公式 |
|---|---|---|
| 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 |
不要忘记 Power Query 是区分大小写的，这意味着“Day Of Year”和“Day of Year”是不同的。如果收到一个错误，当知道自己创建了字段时，但字段不存在，请检查大小写和拼写，因为这可能是导致问题的原因，标准的大小写如表18-8至表18-10所示。
（译者注：关于如何生成“4-4-5”等日期表的逻辑并未在书中给出仔细解释，但提供了技术实现细节，读者可对照实现和其他参考自行理解。）
表18-8 公式的 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 (for 4-4-5 Calendars) | “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 (for 4-5-4 Calendars) | “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 (for 5-4-4 Calendars) | “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 |
表18-9 x列的天数公式
| 列名 | 需要的列 | 公式 |
|---|---|---|
| Days in Year | N/A | 364 |
| Days in Quarter | N/A | 91 |
| Days in Month (for 4-4-5 Calendars) | “Week of Quarter” | if [Week of Quarter] > 8 then 35 else 28 |
| Days in Month (for 4-5-4 Calendars) | “Week of Quarter” | if [Week of Quarter]>4 and [Week of Quarter]<10 then 35 else 28 |
| Days in Month (for 5-4-4 Calendars) | “Week of Quarter” | if [Week of Quarter] < 5 then 35 else 28 |
| Days in Week | N/A | 7 |
表18-10 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) |
在完成的示例文件中，会发现使用了本章演示的技术来构建的每个日历表的完整版本。
Calendar：每年 12 月 31 日结束的标准 12 个月日期。
Calendar-Sep30：12 个月的日期，会计年度结束日期为每年的 9 月 30 日。
Calendar-445：使用“4-4-5”周模式的 364 天日期。
Calendar-454：使用“4-5-4”周模式的 364 天日期。
Calendar-544：使用“5-4-4”周模式的 364 天日期。
每个表都加载到数据模型中，并连接到“Sales”表和“Budgets”表，如图18-11所示。
图18-11 有 5 个不同的日期表通过日期列连接到“Sales”表和“Budgets”表
文件中还创建了“Sales $”和“Budget $”度量，以及比较页面上的一些示例（用于比较结果），可以直观地发现日期报告数据的方式之间的异同。
对于自定义日期，每个步骤也已在 Power Query 【应用的步骤】窗口中命名，使用户能够轻松识别每个步骤中使用了哪些公式。
18.3 日期时间填充
虽然上面的日期模式非常有用，但它的目标是在两个日期之间填写完整的会计年度。在本节中，将介绍生成基于日期和时间的表的替代方法。
18.3.1 日期级别填充
之前看到的解决方案展示了如何在两个特定日期之间填充日期。但是如果只知道开始日期，想从开始日期中找出一组特定的日期，那该怎么办呢？考虑下面的示例，它是基于“第 18 章 示例文件\Fill Dates-Begin.xlsx”文件中保存的数据填充日期开始。在此场景中，需要生成每日列表，来跟踪在任何给定日期哪些访客在现场，如图18-12所示。
图18-12 需要按日期生成访客列表
所需要的输出并不复杂，目前知道访客到达的日期，他们将访问多少次，并且每次访问都是一整天。
事实证明，有一个函数可以用来获取需要的数据，即 List.Dates 函数。如果检查一下清单：List.Dates 文档，它不仅准确地告诉用户需要为这个函数提供什么内容，还显示了用户可以期望返回什么。输出描述看起来非常有希望，如图18-13所示。
图18-13 出现在公式 Intellisense 中的 List.Dates 文档
唯一棘手的部分是最后一个参数，它需要一个持续时间。获取每日持续时间值的最简单方法是使用以下代码声明一个值：
#duration(1,0,0,0)持续时间值的四个参数是天、小时、分钟、秒。
来试一试。
创建一个新查询，从“Visitors”表中读取。
转到【添加列】【自定义列】。
将列命名为“Pass Date”，并使用以下公式：
=List.Dates( [Arrival], [Days on Site], #duration(1,0,0,0) )(译者注：添加列前可能要先把“Arrival”列设置成【日期】格式（默认是日期时间数据类型），不更改数据类型，添加列并写入上述公式时可能会报错）。
右击“Pass Date”列和“Visitor”列【删除其他列】。
单击“Pass Date”列的扩展箭头，选择【扩展到新行】。
设置数据类型。
正如看到的，结果符合预期，如图18-14所示。
图18-14 访客 Miguel 有两次访问，Ken 有五次访问，Matt 有四次访问
在生成完整的日期表时，本书不推荐使用 List.Dates 函数来实现，这个函数的参数需要一个持续时间，由于无法直接表示“月”的持续时间，则要计算每个月的实际天数，这导致了复杂性（每年 2 月的天数都不同），因此，使用前面的模式创建日期表更简单。
18.3.2 小时级别填充
前面的示例工作得非常好，但是关于使用 List.Dates 函数需要认识到的重要一点是。持续时间的“计数”从开始日期的 0 小时开始。可以看到这一点，因为每个访客的第一条记录与表中包含的到达日期相同。
如果使用的持续时间少于一天，这实际上会产生影响。例如，请注意，如果通过提供“#duration(0,1,0,0)”将持续时间设置为 1 小时间隔，将收到不同的结果，如图18-15所示。
图18-15 有正确的间隔次数，但都在开始日期，时间在哪里呢
幕后发生的事情是 Power Query 正在创建一个日期列表，从开始日期的午夜开始，每小时重复一次，持续 x 小时。不幸的是，这有点难以理解，正如 List.Dates 只返回日期部分，从而切断了说明这一点的精度。
那么，如果想像上一个例子那样记录日期，但又想从每天上午 9:00 开始添加 8 个小时记录，该怎么办？显然不能使用 List.Dates 函数，因此需要使用 List.Times 函数，下面进行介绍。
#time(9,0,0)
持续时间值的三个参数是小时、分钟、秒。请记住，此功能依赖于 24 小时的时间，因此下午 1:00 通过“#time(13,0,0)”实现。
要做到这一点，需要进行如下操作。
【复制】上一个查询并将其命名为“Pass Times”。
转到【添加列】【自定义列】。
命名列为“Hour”并使用以下公式：
=List.Times( #time(9,0,0), 8, #duration(0,1,0,0) )此时，可以预览新创建的列表，以验证确实为每个日期创建了正确的时间。由于列表是在每天的级别上创建的，因此将“Hour”列扩展到新行将在“Pass Date”列中为每天提供正确的时间，如图18-16所示。
图18-16 刚刚为数据集中的每个日期添加了 8 个小时记录
还有一个 List.DateTimes 函数，该函数允许用户提供完整的“#datetime”值作为起点。
处理日期的挑战之一是可变性，因为月份甚至年份之间的天数不一致。时间通常没有这个问题，因为每天包含 24小时，其中每小时包含 60 分钟，60 秒长。因此，请使用 List.Times 是构建包含时间段的表的首选方法。
18.3.3 带间隔的填充
继续探讨的下一个挑战，如图18-17所示。
图18-17 如何生成一个以 y 为间隔重复 x 次的日期表呢
令人惊讶的是，实际上已经找到了答案。使用与前面相同的 List.Dates 公式。只需修改最后一个参数，来提供时间间隔，而不是是每一天都重复。唯一真正的技巧是 List.Dates 的最后一个参数：持续时间，所以需要创建一个持续时间，该持续时间等于频率列中指示的天数。这可以通过利用 Duration.From 函数来实现。
假设创建了一个查询，该查询连接到“第 18 章 示例文件\Fill Every x Dates-Begin.xlsx”文件的“Contracts”表，可以通过以下步骤实现目标。
添加一个名为“Follow Up”的新【自定义列】，该列使用以下公式：
（译者注：如果出现错误，检查：在添加【自定义列】之前需要将“Contract Start”列数据类型设置为【日期】数据类型。）
=List.Dates( [Contract Start], [Check Ins], Duration.From( [Frequency] ) )单击“Follow Up”列的扩展箭头【扩展到新行】。
选择“Customer”列和“Follow Up”列后，右击列标题选择【删除其他列】。
设置数据类型。
结果正是所需要的，如图18-18所示。
图18-18 创建了 x 条记录，每 y 天重复一次
这种技术不仅限于使用 List.Dates 函数。它可以应用于 List.DateTimes 函数或 List.Times 函数，如果需要使用时间值，可以执行此操作。
18.4 按日期分摊
经常遇到的另一个问题是如何将上面知识应用到按日期分摊既定的计划，特别是当希望在多个月末分摊收入或支出时。
公平地说，根据业务的具体需求，这个问题有无数细微差别，但在本节中，将介绍两种处理此任务的常用方法。在 “第 18 章 示例文件\Allocations-Begin.xlsx”文件中提供了一个名为“Sales”的表，已经删除了不必要的列，只有本节示例会用到的列，以演示每个特定场景。
为了简化工作，需要先一个可以连接到每个示例的暂存查询。
从“Sales”表读取数据创建新查询。
将“Start Date”列和“End Date”列的数据类型更改为【日期】。
将查询命名为“Raw Data”。
查询加载为【仅创建连接】。
18.4.1 起止日内按日分摊
第一个场景利用了在本章前面生成日期表时所看到的列表概念，可用于在给定天数内分摊目标，如图18-19所示。
图18-19 按至月底的月内天数在每月进行分摊
前两位客户的分摊看上去很容易理解，但尼克拉斯（Niklas）的分摊记录看起来有点奇怪，要在约四个月内（122天）分摊 200 美元，那么为什么没有看到每月 50 美元呢？这里需要注意的是：分摊是按时间段内的天数进行的，因此，每个月都不同。
在这种情况下，面临的重大挑战是：如何创建正确的月末列表，以及如何根据期间的天数计算出每个期间的分摊金额？
将从计算每天应分摊的交易金额开始。为了计算这一点，需要知道从开始日期到结束日期期间的总天数。虽然 Power Query 不允许从一个日期减去另一个日期，但已经知道如何将日期转换为序列号。
【引用】“Raw Data”查询。
删除“Month”列（因为本例中不需要该列）。
添加一个名为“Amount”的【自定义列】，该列使用以下公式：
= [Sale] / ( Number.From( [End Date] ) - Number.From( [Start Date] ) + 1 )当从一天减去另一天时，不要忘记在结果中加一。
结果如图18-20所示。
图18-20 已经计算了每天分摊的金额
计算完每天的分摊后，现在可以进入下一步：展开表以包含按客户分摊的每个分摊日的记录。
添加一个名为“Date”的【自定义列】，该列使用以下公式：
= { Number.From( [Start Date] ) .. Number.From( [End Date] ) }单击“Date”列的扩展箭头【扩展到新行】。
将“Date”列的数据类型更改为【日期】。
利用在创建日期表时使用的列表技术，现在有一个完整的日常事务表，如图18-21所示。、
图18-21 按日分摊
此时，可以选择两个不同的方向。如果希望数据处于这种日期粒度级别，那么只需删除“Sale”、“Start Date”和“End Date”列并设置数据类型，然后就可以加载数据了。然而，在本例中，则希望在月末汇总这些数据，因此在这里还要做额外的工作来实现，如下所示。
选择“Date”列【转换】【日期】【月份】【月结束值】。
选择“Client”、“Sale”、“Start Date”、“Date”【转换】【分组依据】。
在弹出的【分组依据】对话框，【新列名】输入“Amount”，【操作】选择“求和”，【柱】下面选择“Amount”列。
单击【确定】。
删除“Sale”和“Start Date”列，此时数据应如图18-22所示。
图18-22 成功！销售额现在根据期间的天数分摊到正确的月份
这里按这么多字段分组的原因是为了减少错误地将两个客户合同聚合在一起的机会。如果数据包含所分摊的每个项目的唯一标识符（如销售订单号），则只需按该列分组（除非希望在最终表中保留其他列）。
关于这项技术需要认识到的一点是，这里没有以任何方式对每日分摊进行舍入，因为这会增加舍入错误的机会。如果需要对数据进行四舍五入，本书强烈建议用户在最后执行此操作，以降低分摊总额不等于原始金额的可能性。
18.4.2 起止日内按月分摊
下一种分摊方法稍有不同，它要求根据从开始日期到结束日期的总月数平均分摊销售金额。请注意，销售发生在这个月的第几天，或者结束日期在这个月的第几天并不重要，只要它与某个月存在重叠，都会认为它是一个有效的分摊月。此处显示了如何应用该方法的示例，如图18-23所示。
图18-23 在该期间的月数上平均分摊
这个场景的令人沮丧之处在于，如果 Power Query 有一个可以根据起始日期创建月末日期列表的函数，那么这将非常容易，但是并没有。不过，好消息是，可以使用自定义函数轻松创建返回月末日期列表的函数。首先，要基于两个参数建立一个示例转换，如图18-24所示。
打开 Power Query 编辑器。
转到【主页】选项卡【管理参数】【新建参数】。
创建两个新【参数】，如下所示。
“FromDate”作为【日期】数据类型，【当前值】输入“2021-06-01”。
“ToDate”作为【日期】数据类型，【当前值】输入“2021-08-31”。
图18-24 为函数创建所需的【参数】
现在有了【参数】，可以按如下方式构建“getMonthEnds”查询。
创建一个新的【空白查询】并将其命名为“getMonthEnds”。
在公式栏中输入以下内容：
= { Number.From( FromDate ) .. Number.From( ToDate ) }进入【列表工具】【转换】【到表】【确定】。
将“Column1”列设置为【日期】数据类型。
选择“Column1”列【转换】【月份】【月份结束值】。
右击“Column1”列【删除重复项】。
将“Column1”列重命名为“Month End”。
结果是生成“FromDate”和“ToDate”【参数】之间的一个简短的月末日期表，如图18-25所示。
图18-25 已经生成了两个日期之间的月末值表
正如在第 17 章中了解到的，到目前为止，所遵循的步骤允许用户将“getMonthEnds”查询作为示例转换查询，同时轻松地将其转换为函数，现在将执行以下操作。
右击“getMonthEnds”【创建函数】，【函数名称】“fxGetMonthEnds”【确定】，如图18-26所示。
图18-26 fxGetMonthEnds 函数接受两个参数，可以使用了
现在是保存进度的好时机，将所有新创建的查询作为【仅限连接】查询加载，因为这里不需要将它们加载到工作表或数据模型中。
这个函数做好以后，现在可以设置按月生成分摊。
【引用】“Raw Data”查询。
删除“Months”列（因为本例中不需要该列）。
转到【添加列】【调用自定义函数】“fxGetMonthEnds”。
【FromDate】选择“Start Date”列，【ToDate】选择“End Date”列。
提交调用后，现在可以预览应用于数据集的函数的结果如图18-27所示。
图18-27 此时自定义函数工作正常
在这一点上，真正想做的是计算出每个月分摊的值。但是怎么做呢？如果展开“fxGetMonthEnds”列，用户将无法轻松计算嵌套在每个表中的月末值的数量。是的，可以使用 Power Query 的分组功能来计算行数，但是获取一个表，展开它，重新分组，然后再次展开它，这有些复杂，肯定有更好的方法。
当然，还有，在【自定义列】中使用 Table.RowCount 函数。
添加一个名为“Amount”的【自定义列】，该列使用以下公式：
= [Sale] / Table.RowCount( [fxGetMonthEnds] )选择“Client”、“fxGetMonthEnds”和“Amount”列【删除其他列】。
将“fxGetMonthEnds”列展开（取消勾选【使用原始列名作为前缀复选框】）。
设置每列的数据类型。
结果是完美的。Table.RowCount 函数的作用是统计 fxGetMonthEnds 函数为每个客户返回的行数，并用销售额除以它。将处理后的表在该函数处扩展，最终得到每个客户的销售额在以下范围内的所有月份中的分摊，如图18-28所示。
图18-28 值已经按月进行了分摊
Table.RowCount 可以通过快速浏览 M 文档来找到和使用，既然正在处理的是一个表，所以自然想到 M 文档的表部分，通过浏览不难发现该函数可以解决问题，用后果然可以，就又学到新东西了。
请记住，当用户尝试执行 Power Query 默认不支持的函数时，或者当用户需要为复杂模式生成可重用逻辑时，自定义函数是非常有用的。如果试图构建更复杂的逻辑，以便让分摊方法在该场景中正常工作，请不要忘记可以先考虑构造自定义函数的模式。
18.4.3 起点日后按月分摊
现在来探讨的最后一个挑战是在前面场景基础上增加了一个细微差别。如果交易开始日期早于或等于当月 15 日，财务部要求在开始月份分摊一整月的目标，但如果在 15 日之后发生，则从开始日期的下一个月进行分摊。对于这种情况，还将假设基于开始日期和分摊的月数，但没有具体的结束日期列，如图18-29所示。
图18-29 如何在 x 月末分摊收入呢
为了解决这个问题，需要将用于创建日期表的列表技术、几个日期公式和一些条件逻辑结合起来。
从计算每月应分摊的销售额开始。
【引用】“Raw Data”查询。
删除“EndDate”列（因为本例中不需要该列）。
选择“Sales”和“Month”列【添加列】【标准】【除】。
将新的“除”列重命名为“Amount”。
此时的结果应如图18-30所示。
图18-30 此时已经有了分摊每月金额的计算
在计算了需要分摊的金额后，现在需要展开表，为每个月生成一个新行。知道需要多少记录，这在“Month”列中有所说明，但在这里面临一些挑战。不能使用列 List.Dates 函数来创建此列表，因为没有“月”持续时间，并且也不能使用简单的“{1..[Months]}”来设置，因为还需要基于开始日期来判断开始分摊的月份从本月开始还是次月开始，需要进行如下操作。
转到【添加列】，添加一个名为“Custom”的【自定义列】使用以下公式：
=if Date.Day([Start Date]) <= 15 then { 0 .. [Months] - 1 } else { 1 .. [Months] }单击“Custom”列的扩展箭头【扩展到新行】。
此时，可以看到创建的条件列的结果如图18-31所示。
图18-31 新【自定义列】的结果将显示出来，以便于查看
在这一点上，有如下两件重要的事情需要注意。
每个客户现在包含的行数是由需要分摊的月份数决定的’
对于前两个客户，“Custom”列值从 0 开始，但对于第三个客户，“Custom”列值从 1 开始。现在来利用这个新列来确定开始日期，并为每条记录创建一个表示月末的新日期。
创建一个名为“Date”的新【自定义列】，该列使用以下公式：
= Date.EndOfMonth( Date.AddMonths( [Start Date], [Custom] ) )选择“Date”、“Client”和“Amount”列【删除其他列】。
设置每列的数据类型。
正如所见，这里的技巧是生成所需月份的列表（每个月都有正确的偏移量），并配合用 Date.AddMonths 函数，结果将如图18-32所示。
图 18-32 记录被分摊到正确的月份
如果认为把分摊日期结束日期设置未月底不合理？也没有问题，可以在用于生成最终日期的公式中删除 Date.EndOfMonth 函数。然后，它将返回从提供的开始日期起 x 个月的同一天。
18.4.4 关于分摊
正如前面提到的，用户可能拥有无数种方法来分摊数据，这些方法只是在实践中看到的最常见的方法。希望上述场景能够为用户提供启发，使用户能够创造性地用于自己的场景，并构建业务所需的完美分摊方法。
PQ 真经连载
往期推荐
一数据分析师训练营 课程表一
时间：2023年10月 班
主题：中国式商业智能分析师 - 基于 FineBI 实现国内企业数据分析
时间：2023年11月 班主题：基于指标体系构建的数据分析师全流程实训
主题：十大通用数据分析模型通用模式与模板
时间：2023年12月 班主题：小白用 Power BI 秒懂企业经营状况2日速成班
主题：数据分析 DAX 实战班 - 36计让业务分析师轻松搞定
企业咨询 | 企业培训 | 个人学习 | 职业规划
点击“阅读原文”进入学习中心