如果基于系统日期的年份范围内某些年份的行缺失,则使用 Power Query 生成填充行

如果基于系统日期的年份范围内某些年份的行缺失,则使用 Power Query 生成填充行

我在 Excel 2016 中有一个表格:

在此处输入图片描述

      YEAR     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

问题:

我需要此年份范围内每年至少一行:system year + 9。换句话说,我想要从当前年份(目前为 2023 年)开始的 10 年的行。

如您所见,这些年份缺少行:2023、2030 和 2032。所以我想使用 Power Query 为缺失的年份生成行。

它看起来像这样:

在此处输入图片描述


问题:

使用 Power Query,如何为缺失的年份生成填充行?

  • 我正在寻找一个动态解决方案;我不想手动在表中输入填充行。
  • 2024 年 1 月 1 日,我希望年份范围自动从 2023-2032 切换为 2024-2033。我不想对年份范围进行硬编码。

答案1

假设你的表名为表格1,首先创建一个新的查询 - 名为- 生成您需要的缺失年份的动态列表:

let
  Source = List.Numbers(Date.Year(DateTime.LocalNow()), 10), 
  #"Converted to Table" = Table.FromList(
    Source, 
    Splitter.SplitByNothing(), 
    null, 
    null, 
    ExtraValues.Error
  ), 
  #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table", {{"Column1", Int64.Type}}), 
  #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"Column1", "YEAR"}}), 
  #"Merged Queries" = Table.NestedJoin(
    #"Renamed Columns", 
    {"YEAR"}, 
    Table1, 
    {"YEAR"}, 
    "Table1", 
    JoinKind.LeftAnti
  ), 
  #"Removed Columns" = Table.RemoveColumns(#"Merged Queries", {"Table1"})
in
  #"Removed Columns"

上面的表格标题必须重命名,以匹配YEAR原始表格中的列;因此在#“重命名列”步。

然后您可以创建最终查询:

let
  Source         = Table.Combine({Table1, Years}), 
  #"Sorted Rows" = Table.Sort(Source, {{"YEAR", Order.Ascending}})
in
  #"Sorted Rows"

相关内容