Страницы: 1
RSS
Кумулятивные суммы в M-запросе (Power Query)
 
Коллеги, добрый день!

Я новичок в PQ, а M-синтаксис так и вообще -- лес.
Есть необходимость загрузить кумулятивные суммы в теле запроса, тк
впоследствии я буду использовать значения, полученные на основании сумм, в срезах.

Итак, есть DAX-формула:
Код
CALCULATE(
   SUM(Data[Fact]); FILTER(
       ALLEXCEPT(Data; Data[ref_Employee]; 'Data'[ref_ts_Project]);
       Data[Date] <= EARLIER(Data[Date])
   )
)

Хочу представить ее в M-нотации:
Код
RTList = List.Accumulate(
             FactList, {0}, ( SoFar, ThisStep ) =>
             SoFar & { List.Last( SoFar ) + ThisStep })
#"FFWD Changed Type", "Running Total", each RTList{ [INDEX] })

Это корректно, но надо добавить группировку (AllExcept) по полям справочника: Employee, Project. Как это сделать?
 
Доброе время суток.
Лучше вместо List.Accumulate использовать List.Generate - быстрее будет. На 1000000 нарастающий итог у меня выполнился за 45 секунд со всеми трансформациями. Но лучше, если есть конечно возможность, делать это в исходных данных в VBA (выполнится, включая загрузку в массив и выгрузку результата, за секунды) или аналитическими функциями SQL (в большинстве современных баз данных они есть).
 
Доброе

Вас не затруднит привести пример PQ-генератора для моего случая?
Вот он:
Код
CALCULATE(
   SUM(Data[Fact]); FILTER(
       ALLEXCEPT(Data; Data[ref_Employee]; 'Data'[ref_ts_Project]);
       Data[Date] <= EARLIER(Data[Date])
   )
)
 
Пример данных будет или устроит на пальцах?
 
Добрый день!

Привожу сэмпл.
 
Андрей VG, для меня, кстати, вопрос тоже актуальный. Так что тоже жду кусочек вашей мудроты. :idea:
Кстати, вот такое нагуглил:
Код
Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
ChangedType = Table.Buffer(Table.TransformColumnTypes(Source,{{"Date", type date}})),
Iterate = List.Buffer(List.Generate(
()=>[Counter=0, Value_=ChangedType[Sale]{0}],
each [Counter]<=Table.RowCount(ChangedType),
each [Counter=[Counter]+1,
Value_=[Value_]+ChangedType[Sale]{[Counter]+1}],
each [Value_])),
Table = Table.FromColumns({ChangedType[Date], ChangedType[Sale], Iterate}),
Rename = Table.RenameColumns(Table,{{"Column1", "Date"}, {"Column2", "Sale"}, {"Column3", "CumSale"}}),
RemError = Table.RemoveRowsWithErrors(Rename, {"CumSale"})
in
RemError

Андрей, вы хотели предложить что-то подобное?
Изменено: PooHkrd - 05.03.2018 11:44:27
Вот горшок пустой, он предмет простой...
 
Цитата
PooHkrd написал:
вы хотели предложить что-то подобное
Да, собственно, только с учётом локальной группировки. У коллеги ошибка в ограничении цикла
Код
each [Counter]<=Table.RowCount(ChangedType)

Должно быть строго <, а то потом огород городит с устранением ошибок. Плюс, доступ к строкам таблицы выполняется дольше чем к элементу списка.
Так как ТС пример предоставил в стиле, а ну ка угадайте где у меня Fact, а допишите преобразование текста в дату, а приведите мне всё в порядок, а то я новичок. Пришлось свой пример делать. Локальный нарастающий итог по датам артикулов групп.
Изменено: Андрей VG - 05.03.2018 12:47:42
 
Андрей VG, Вы правы. Разрешите взглянуть на текст запроса.
 
Цитата
Lewa написал:
Разрешите взглянуть на текст запроса.
Так в файле же по ссылке есть код запроса. Ну, давайте и тут добавлю
Скрытый текст
 
Андрей VG,Спасибо большое!
 
Интересно, а без генератора не быстрее?
код под катом

По идее, здесь суммирований получается не меньше, но на сэмпле разницу не вижу, а как там вычислятор столбца работает на самом деле - бог его знает
F1 творит чудеса
 
Цитата
Максим Зеленский написал:
Интересно, а без генератора не быстрее?
Максим, в группе же уже обсуждали этот вопрос. Я и пример приводил для 100000 на таком варианте и для 1000000 на Generate.
В чём проблема кода
Код
each List.Sum(List.Range(BuffCount, 0, [Индекс]))

Код для каждой строки начинает суммировать от начала до номера текущей строки. То есть, если перевести на VBA
Код
Const lastRow = 100000
Dim arr(1 To lastRow, 1 To 2) As Double
Dim i As Long, k As Long, sum As Double
' заполнили первый столбец начальными суммами, циклы записи во второй нарастающих сумм
'.....
'Циклы вычисления нарастающей суммы
For i = 1 To lastRow
    sum = 0
    For k = 1 To i
        sum = sum + arr(k, 1)
    Next k
    arr(i, 2) = sum
Next i

Думаю, что в VBA особенно хорошо видна нелогичность такого подхода :)
Ну, и получим N^2 алгоритм. При использовании же Generate будет N * Log(N) (затраты Join). Что быстрее
Изменено: Андрей VG - 05.03.2018 13:54:39
 
Цитата
Андрей VG написал:
в группе же уже обсуждали этот вопрос
ну вот помню, что было, а где - не помню :)
Спасибо!
F1 творит чудеса
 
Цитата
написал:
Так в файле же по ссылке есть код запроса. Ну, давайте и тут добавлю
Отличное решение! Как раз, то, что искал :)
Подскажите, а как можно сделать, чтобы накопленный итог был еще и по годам и по месяцам отдельными полями?
Или это будет уже оффтоп?
Страницы: 1
Наверх