Power Query实操：从Horas trabajadores表生成对应整月日期表

我有一张名为Horas trabajadores的表，其中Fecha列包含当月的部分日期，示例数据如下：

Fecha
01/03/2024
03/03/2024
05/03/2024
10/03/2024
31/03/2024

需要创建一张名为NumberFromTo的新表，其中WholeMonth列要列出Fecha列所在月份的所有日期，示例结果如下：

WholeMonth
01/03/2024
02/03/2024
03/03/2024
04/03/2024
05/03/2024
...
31/03/2024

解决方案 #

方法1：Power Query（Excel/Power BI适用） #

通过Power Query编辑器可快速生成完整日期序列：

1. 将Horas trabajadores表导入Power Query
2. 复制以下M语言代码替换编辑器中的默认代码，执行后即可得到目标表：

let
    Source = Excel.CurrentWorkbook(){[Name="Horas trabajadores"]}[Content],
    // 转换Fecha列为日期类型
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Fecha", type date}}),
    // 计算月份起始和结束日期
    #"Add Month Bounds" = Table.AddColumns(#"Changed Type", {
        "MonthStart", each Date.StartOfMonth([Fecha]),
        "MonthEnd", each Date.EndOfMonth([Fecha])
    }),
    // 保留唯一的月份边界（避免重复计算）
    #"Unique Bounds" = Table.Distinct(#"Add Month Bounds", {"MonthStart", "MonthEnd"}),
    // 生成该月完整日期列表
    #"Generate Date List" = Table.AddColumn(#"Unique Bounds", "DateList", 
        each List.Dates([MonthStart], Duration.Days([MonthEnd]-[MonthStart])+1, #duration(1,0,0,0))),
    // 展开日期列表并整理列
    #"Expand Dates" = Table.ExpandListColumn(#"Generate Date List", "DateList"),
    #"Clean Columns" = Table.RemoveColumns(#"Expand Dates",{"Fecha", "MonthStart", "MonthEnd"}),
    #"Rename Column" = Table.RenameColumns(#"Clean Columns",{{"DateList", "WholeMonth"}}),
    #"Final Type" = Table.TransformColumnTypes(#"Rename Column",{{"WholeMonth", type date}})
in
    #"Final Type"

3. 将结果表命名为NumberFromTo并加载回Excel或Power BI

方法2：SQL（以SQL Server为例） #

如果是在数据库中操作，使用递归CTE生成日期序列：

-- 第一步：获取目标月份的起始和结束日期
WITH MonthBounds AS (
    SELECT
        DATEFROMPARTS(YEAR(MIN(Fecha)), MONTH(MIN(Fecha)), 1) AS MonthStart,
        EOMONTH(MIN(Fecha)) AS MonthEnd
    FROM [Horas trabajadores]
),
-- 第二步：递归生成所有日期
DateSeries AS (
    SELECT MonthStart AS WholeMonth
    FROM MonthBounds
    UNION ALL
    SELECT DATEADD(DAY, 1, WholeMonth)
    FROM DateSeries
    WHERE WholeMonth < (SELECT MonthEnd FROM MonthBounds)
)
-- 第三步：将结果插入新表
SELECT WholeMonth
INTO NumberFromTo
FROM DateSeries
ORDER BY WholeMonth;

注意：如果Fecha列是字符串格式，需先转换为日期类型，例如CONVERT(date, Fecha, 103)（适配dd/mm/yyyy格式）

内容的提问来源于stack exchange，提问作者Luis Miguel