Power Query数据加载至数据模型后Excel公式引用结果不符

核心问题 #

你的Power Query代码最终返回的是单个标量值（某一行的Start Date数值），而非标准的表格结构。当这种标量值加载到数据模型后，Excel无法正确识别其数据类型和结构，导致公式引用时出现日期/数值不匹配的异常。

修复步骤 #

1. 调整M代码，输出表格结构
   移除提取单个值的步骤，保留过滤后的表格（仅保留需要的Start Date列），确保数据模型能识别为标准列数据：

   Source = Excel.CurrentWorkbook(){[Name="PayrollDates"]}[Content],
       #"Changed Type" = Table.TransformColumnTypes(Source,{{"Start Date", type date}, {"End Date", type date}, {"Process Date", type date}, {"Pay Date", type date}}),
       #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "CurrentPayperiod", each if [Start Date] < DateTime.Date(DateTime.LocalNow()) and DateTime.Date(DateTime.LocalNow()) < [Process Date] and DateTime.Date(DateTime.LocalNow()) > [End Date] then true else null),
       #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([CurrentPayperiod] = true)),
       #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows", {"Start Date"})
   in
       #"Removed Other Columns"
   

2. 统一数据类型
   全程保持Start Date为type date类型，不要中途转换为Int64.Type。Excel数据模型会自动处理日期的序列号转换，避免手动转换导致的类型混乱。

3. 刷新并验证公式

   • 刷新Power Query连接，确保数据模型加载最新的表格数据
   • 使用精准的列引用公式：=MAX(PayrollDates[Start Date])，而非整表引用=MAX(PayrollDates)，避免Excel误解析数据结构

额外说明 #

之前返回单个值时，Excel数据模型会将其存储为独立的“值对象”，而非表格列。当你用整表引用公式时，Excel可能错误地读取了数据的元信息，导致日期序列号异常。输出标准表格后，数据模型能正确识别列的类型和内容，公式引用就会匹配Power Query中的结果。

内容的提问来源于stack exchange，提问作者Ogre Sasqwatch