M - Преобразование кумулятивного значения (промежуточного итога) в фактическое значение

Пользователь получает ежемесячные отчеты клиентов с промежуточными суммами продаж за месяц (несколько десятков тысяч строк). Продажи отображаются в виде промежуточной суммы, я пытаюсь найти решение powerquery, чтобы получить фактические продажи в виде (* требуется *) вывода:

╔══════╦═══════╦═════════╦═══════╦════════════╗
║ Year ║ Month ║ StoreID ║ Sales ║ *Required* ║
╠══════╬═══════╬═════════╬═══════╬════════════╣
║ 2017 ║    10 ║       1 ║     5 ║          5 ║
║ 2017 ║    11 ║       1 ║    11 ║          6 ║
║ 2017 ║    12 ║       1 ║    18 ║          7 ║
║ 2017 ║    11 ║       2 ║    10 ║         10 ║
╚══════╩═══════╩═════════╩═══════╩════════════╝

В tSQL я бы сделал что-то вроде

Sales - LAG(Sales,1,0)OVER(Partiton by Year, StoreID Order by Month)

На аналогичный вопрос (но наоборот) был дан ответ, но предлагаемое решение выглядит как эквивалент перекрестного соединения. Я новичок в powerquery (поэтому я могу ошибаться), но я не вижу, чтобы это было жизнеспособно, поскольку наборы данных растут каждый месяц. Есть ли лучший способ подойти к этому, я не могу найти пример в «М»?

Изменить: List.Range выглядит многообещающе


excel powerquery m
person M O'Connell    schedule 08.12.2017    source источник

Ответы (1)


arrow_upward
5
arrow_downward

На самом деле вам нужны значения из предыдущей строки в текущей строке. Обычный подход состоит в том, чтобы добавить 2 столбца индекса, один из которых начинается с 0, а другой — с 1. Затем объедините таблицу с самой собой, используя столбцы индекса в качестве столбцов соединения. После расширения необходимых столбцов из вложенных таблиц можно добавить пользовательский столбец с требуемым расчетом.

let
    Source = Sales,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter),
    #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"StoreID", "Sales"}, {"Previous.StoreID", "Previous.Sales"}),
    #"Sorted Rows" = Table.Sort(#"Expanded Previous",{{"Index", Order.Ascending}}),
    #"Added Custom" = Table.AddColumn(#"Sorted Rows", "Required", each [Sales] - (if [StoreID] = [Previous.StoreID] then [Previous.Sales] else 0), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index", "Index.1", "Previous.StoreID", "Previous.Sales"})
in
    #"Removed Columns"
person MarcelBeug    schedule 08.12.2017
comment
Спасибо за этот фантастический ответ. Я пройдусь по этому коду завтра с файлом. Я отмечу как ответ после подтверждения - person M O'Connell; 10.12.2017
comment
Спасибо, Марсель. Визуализация двух индексов сбила меня с толку, но видеть разбитые шаги было фантастически. Спасибо за ваше время и ответ - person M O'Connell; 11.12.2017