使用PowerQuery在几个月内按比例计算结果

Question

我目前陷入以下问题：

我必须使用两张表，一张表包含船只的财务信息，另一张表则包含船只的到达和离开时间。我从不同的文件夹中组合多个excel表来获取数据：

财务表

航行时间表

我必须计算上述航行的结果，并将结果分摊到6月、7月和8月，用于估计和更新。

6月时间：4小时(2020年6月20日20:00-23:59(+10天(2020年06月21日00:00-30/2020年06月23:59(=10.1666

7月时间：31整天

8月时间：1天+14小时(2020年8月2日00:00-14:00(=1.5833

总航程=10.1666+31+1.5833=42.7499

对于"；更新的"；财务项目如下：

6月结果：100*(10.1666/42.77499(=23.7816

7月结果：100*(31/42.7499(=72.5148

8月结果：100*(1.5833/42.77499(=3.7036

总和=100

然后对于"；估计的"；这将是上面所有东西的两倍。

这是我理想中想要的格式：

按比例分配结果表

我必须为多艘船只做这件事，有多个时间跨度和多个航行编号。急切等待回应(如果有的话(。非常感谢。

Brds，

Answer 1

不确定您是否仍在寻找答案，但下面的代码给出了您的预期输出：

let
financialTable = Table.FromRows({{"A", 1, "profit/loss", 200, 100}}, type table [vesselName = text, vesselNumber = Int64.Type, financialItem = text, estimated = number, updated = number]),
voyageTimeTable = Table.FromRows({{"A", 1, #datetime(2020, 6, 20, 20, 0, 0), #datetime(2020, 8, 2, 14, 0, 0)}}, type table [vesselName = text, vesselNumber = Int64.Type, voyageStartDatetime = datetime, voyageEndDatetime = datetime]),
joined = 
let
joined = Table.NestedJoin(financialTable, {"vesselName", "vesselNumber"}, voyageTimeTable, {"vesselName", "vesselNumber"}, "$toExpand", JoinKind.LeftOuter),
expanded = Table.ExpandTableColumn(joined, "$toExpand", {"voyageStartDatetime", "voyageEndDatetime"})
in expanded,
toExpand = Table.AddColumn(joined, "$toExpand", (currentRow as record) =>
let
voyageInclusiveStart = DateTime.From(currentRow[voyageStartDatetime]),
voyageExclusiveEnd = DateTime.From(currentRow[voyageEndDatetime]),
voyageDurationInDays = Duration.TotalDays(voyageExclusiveEnd - voyageInclusiveStart),
createRecordForPeriod = (someInclusiveStart as datetime) => [
inclusiveStart = someInclusiveStart,
exclusiveEnd = List.Min({
DateTime.From(Date.EndOfMonth(DateTime.Date(someInclusiveStart)) + #duration(1, 0, 0, 0)),
voyageExclusiveEnd
}),
durationInDays = Duration.TotalDays(exclusiveEnd - inclusiveStart),
prorataDuration = durationInDays / voyageDurationInDays,
estimated = prorataDuration * currentRow[estimated],
updated = prorataDuration * currentRow[updated],
month = Date.MonthName(DateTime.Date(inclusiveStart)),
year = Date.Year(inclusiveStart)
],
monthlyRecords = List.Generate(
() => createRecordForPeriod(voyageInclusiveStart),
each [inclusiveStart] < voyageExclusiveEnd,
each createRecordForPeriod([exclusiveEnd])
),
toTable = Table.FromRecords(monthlyRecords)
in toTable
),
expanded = 
let
dropped = Table.RemoveColumns(toExpand, {"estimated", "updated", "voyageStartDatetime", "voyageEndDatetime"}),
expanded = Table.ExpandTableColumn(dropped, "$toExpand", {"month", "year", "estimated", "updated"})
in expanded
in
expanded

代码尝试：

• 连接financialTable和voyageTimeTable，因此对于每个vesselName和vesselNumber组合，我们知道：estimated、updated、voyageStartDatetime和voyageEndDatetime
• 为voyageStartDatetime和voyageEndDatetime之间的时段生成月份列表(这些月份将扩展为新的表行(
• 对于(列表中的(每个月，做你在问题中提到的所有算术运算
• 去掉一些列(如旧的estimated和updated列(

---

我建议用数据集中不同的vesselNames和vesselNumbers进行测试，看看输出是否总是正确的(我认为应该是正确的(。

您应该能够手动检查(toExpand步骤/表达式的($toExpand列中的单元格，以便在展开嵌套行之前查看它们。