如何基于总里程日期动态增减行数据计算车辆累计里程？

核心逻辑 #

每辆车仅存在一个指定日期的总里程值，计算各日期累计里程的规则：

• 总里程日期之前的日期：总里程 - 「该日期到总里程日期的每日里程总和」
• 总里程日期及之后的日期：总里程 + 「总里程日期到该日期的每日里程总和」

具体操作步骤（Power Query） #

1. 数据预处理

   • 将Date列转换为日期类型，Total Odometer列转换为数值类型（空值保留）
   • 按Vehicle列进行分组，分组操作选择高级，设置分组列为Vehicle，新增列名GroupedData，操作选择所有行

2. 分组内计算逻辑
   对每个分组的数据，添加自定义列实现累计里程计算，核心M代码如下：

   let
       // 导入原始数据（替换为你的数据源路径）
       Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
       // 转换数据类型
       ChangedType = Table.TransformColumnTypes(Source,{{"Vehicle", type text}, {"Date", type date}, {"Daily Odometer", Int64.Type}, {"Total Odometer", Int64.Type}}),
       // 按车辆分组
       GroupedByVehicle = Table.Group(ChangedType, {"Vehicle"}, {{"GroupedData", each _, type table [Vehicle=text, Date=date, Daily Odometer=Int64.Type, Total Odometer=Int64.Type]}}),
       // 对每个分组计算预期结果
       AddExpectedResults = Table.AddColumn(GroupedByVehicle, "Expected Results", (group) =>
           let
               // 获取当前分组的所有行
               groupRows = group[GroupedData],
               // 提取总里程值（每组仅一个非空值）
               totalOdometer = List.RemoveNulls(groupRows[Total Odometer]){0},
               // 提取总里程对应的日期
               totalDate = Table.SelectRows(groupRows, each not List.IsEmpty({[Total Odometer]})){0}[Date],
               // 按日期排序分组内的行
               sortedRows = Table.Sort(groupRows, {{"Date", Order.Ascending}}),
               // 计算每行的预期里程
               calculateResult = Table.AddColumn(sortedRows, "Result", (row) =>
                   let
                       // 根据日期位置计算偏移量
                       offset = if row[Date] <= totalDate then
                           -List.Sum(Table.SelectRows(sortedRows, each [Date] > row[Date] and [Date] <= totalDate)[Daily Odometer])
                       else
                           List.Sum(Table.SelectRows(sortedRows, each [Date] >= totalDate and [Date] <= row[Date])[Daily Odometer])
                   in
                       totalOdometer + offset
               )
           in
               calculateResult[Result]
       ),
       // 展开分组数据并整理列
       ExpandedData = Table.ExpandTableColumn(AddExpectedResults, "GroupedData", {"Date", "Daily Odometer", "Total Odometer"}, {"Date", "Daily Odometer", "Total Odometer"}),
       // 展开预期结果列
       ExpandedExpected = Table.ExpandListColumn(ExpandedData, "Expected Results"),
       // 调整列顺序（可选）
       ReorderedColumns = Table.ReorderColumns(ExpandedExpected,{"Vehicle", "Date", "Daily Odometer", "Total Odometer", "Expected Results"})
   in
       ReorderedColumns
   

代码关键说明 #

• totalOdometer和totalDate：从每组中提取唯一的总里程值及其对应日期
• sortedRows：确保分组内的行按日期排序，保证累计计算的顺序正确
• offset计算：根据当前行日期与总里程日期的关系，动态计算需要加减的累计里程值，最终与总里程相加得到预期结果

验证结果 #

将示例数据代入后，计算结果完全匹配预期的累计里程值。

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