Блог

В настоящее время я застрял на приведенной ниже проблеме:

У меня есть две таблицы, с которыми я должен работать, одна содержит финансовую информацию для судов, а другая — время прибытия и отправления судов. Я получаю свои данные, объединяя несколько листов Excel из разных папок:

Финансовая таблица

voyageTimeTable

Я должен вычислить результат для вышеупомянутого рейса и распределить результат за июнь, июль и август как для предполагаемого, так и для обновленного.

Время в июне: 4 часа (20/06/2020 20:00 — 23:59) 10 дни (21/06/2020 00:00 — 30/06/2020 23:59) = 10.1666

Время в июле: 31 полный день

Время в августе: 1 день 14 часов (02/08/2020 00:00 — 14:00) = 1.5833

Общая продолжительность рейса = 10.1666 31 1.5833 = 42.7499

Результат для «обновленного» финансового элемента будет следующим:

Результат июнь : 100*(10.1666/42.7499) = 23.7816

Результат июль : 100*(31/42.7499) = 72.5148

Результат август : 100*(1.5833/42.7499) = 3.7036

сумма = 100

и тогда для «предполагаемого» это было бы в два раза больше, чем указано выше.

Это формат, который я в идеале хотел бы получить:

prorataResultTable

Я должен сделать это для нескольких судов с несколькими временными интервалами и несколькими номерами рейсов. С нетерпением ждем ответов, если таковые имеются. Заранее большое спасибо.

Brds,

Не уверен, что вы все еще ищете ответ, но приведенный ниже код дает мне ожидаемый результат:

 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 columns)

Я рекомендую протестировать его с различными vesselName s и vesselNumber -ями из вашего набора данных, просто чтобы убедиться, всегда ли результат корректен (я думаю, так и должно быть).

Вы должны иметь возможность вручную проверять ячейки в $toExpand столбце ( toExpand шага / выражения), чтобы увидеть вложенные строки до того, как они будут расширены.