Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Как в запросе Power Query в колонке посчитать разницу между числом текущей строки и предыдущей, и результат (дельту) поместить в новую колонку?
 
#1
22.05.2018 12:15:29
Здравствуйте, уважаемое сообщество! Помогите пожалуйста решить задачу. У меня есть запрос Power Query. В нем сформирован последний столбец Old Timestamp (см. приложенный скриншот), где в каждой ячейке столбца хранится число секунд от начала события. Мне нужно вычислить разницу между текущим и предыдущим значением. На скриншоте, к примеру, нужно посчитать дельту между второй строкой и первой как 2719-2585=134, и полученный результат сохранить во второй ячейке нового столбца, который надо добавить в конец таблицы. Но это не главная задача. Главная - это посчитать дельту. Я это уже сделал в умной таблице, в которую возвращается результат запроса - там это выглядит как "=[@[Old Timestamp]]-K2", "=[@[Old Timestamp]]-K3" и т.д. автозаполнением до конца столбца. Это работает, но хотелось бы решить эту задачу сразу внутри запроса Power Query, чтобы не разбивать процесс вычислений на части внутреннюю (запрос) и внешнюю (обработка результатов запроса).
 
 
 
#2
22.05.2018 12:23:49
Смотрите пример, если что не понятно - спрашивайте.
Код
let
    Источник = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Дата", type date}}),
    #"Сортированные строки" = Table.Sort(#"Измененный тип",{{"Дата", Order.Ascending}}),
    #"Добавлен индекс" = Table.AddIndexColumn(#"Сортированные строки", "Индекс", 0, 1),
    #"Добавлен индекс1" = Table.AddIndexColumn(#"Добавлен индекс", "Индекс.1", 1, 1),
    #"Объединенные запросы" = Table.NestedJoin(#"Добавлен индекс1",{"Индекс"},#"Добавлен индекс1",{"Индекс.1"},"Предыдущая",JoinKind.LeftOuter),
    #"Развернутый элемент Предыдущая" = Table.ExpandTableColumn(#"Объединенные запросы", "Предыдущая", {"Дата"}, {"Предыдущая.Дата"}),
    #"Добавлен пользовательский объект" = Table.AddColumn(#"Развернутый элемент Предыдущая", "Разница с предыдущим значением", each [Дата]-[Предыдущая.Дата]),
    #"Удаленные столбцы" = Table.RemoveColumns(#"Добавлен пользовательский объект",{"Индекс", "Индекс.1", "Предыдущая.Дата"})
in
    #"Удаленные столбцы"
Изменено: PooHkrd - 22.05.2018 12:34:19
Вот горшок пустой, он предмет простой...
 
 
 
#3
22.05.2018 12:55:43
О да! Спасибо большое! Это то, что нужно! Задача решена.
 
 
 
#4
22.05.2018 15:36:44
С числами видать Power Query немного по-другому работает. Сделал как у вас в примере, и в окне предварительного просмотра все выглядело отлично. Но когда PQ вернул результат на лист, я был немного в шоке... Почему-то таблица стала начинаться со второй строки, а первая уехала в самый её низ (см. скриншоты):
 
 
 
#5
22.05.2018 15:42:34
Извините, но по скриншотам я гадать не умею.
Подозреваю что это произошло после разворачивания столбца с предыдущими значениями при слиянии по двум столбцам индексов. Для этого перед их удалением попробуйте сортировать массив по одному из них.
Изменено: PooHkrd - 22.05.2018 15:43:25
Вот горшок пустой, он предмет простой...
 
 
 
#6
22.05.2018 15:44:39
Для решения проблемы пришлось ввести сортировку по первому индексу перед тем, как удалять столбцы с промежуточными данными. После введения сортировки PQ стал возвращать результат в таблицу в правильной последовательности:
Изменено: GrayMagellan - 22.05.2018 15:52:13
 
 
 
#7
22.05.2018 17:24:45
Цитата
PooHkrd написал:
Извините, но по скриншотам я гадать не умею
Да вы что! У меня к вам никаких претензий нету! Вы дали мне решение задачи, как я могу вам что-то предъявлять? Я вам искренне благодарен за помощь.

P.S. А то, что в моем случае с числами не отработало так, как в вашем примере с датами, я списываю на особенности работы Excel и PQ с другими типами данных в автомате, когда юзер не задал каких-то доп. критериев. С датой на автомате прокатило, а с числами - нет. Ну так мы ручками заставили PQ отсортировать строки в правильной последовательности, раз он автоматом не додумался :).
Изменено: GrayMagellan - 22.05.2018 17:27:14
 
 
 
#8
22.05.2018 17:30:05
PooHkrd, O_o элегантно. Спасибо!
 
 
 
#9
22.05.2018 17:34:48
Илья Демид, если что, мопед не мой - это я все у АндрейVG понабрался.
GrayMagellan, я ничего не предъявлял, просто по скриншотам правда не умею  :D
А вообще при слиянии сортировка всегда слетает, подозреваю что массив сортируется по ключевому полю таблицы из которой вы тянете столбцы. Но это догадки сам не проверял.
Вот горшок пустой, он предмет простой...
 
 
 
#10
22.05.2018 18:17:50
Цитата
PooHkrd написал:
это я все у АндрейVG понабрался.
Это где это я, Алексей, NestedJoin использовал?  :)
 
 
 
#11
23.05.2018 09:25:41
Ну, я ж про прием с двумя индексами. У вас только Join, только хардкор. 8)
Изменено: PooHkrd - 23.05.2018 09:25:55
Вот горшок пустой, он предмет простой...
 
 
 
#12
23.05.2018 22:58:40
Сделал еще один вариант без Join-ов. PooHkrd, с Вашего позволения, в том же файлике.
Pravé bohatství se skrývá uvnitř
 
 
 
#13
24.05.2018 06:26:16
Цитата
Islander написал:
еще один вариант без Join-ов
Коллега, вы бы тысячах так на 100 попробовали б. Будет грустно.
Дешевле уж тогда
rList = Table.ToRecords
lList = {null} & rList
combo = List.Zip({lList, rList})
calc = List.Transform(combo, ....)
Далее, думаю понятно.

P. S. За что вы так не любите Join?
 
 
 
#14
24.05.2018 08:31:00
Андрей VG, Join люблю, умею и практикую, как говорится. А вот List.Generate хотелось в деле попробовать, поэтому выложил (чего добру пропадать). Спасибо за замечание)
P.S. А Вы с высоты опыта не посоветуете, как лучше замерять производительность в PQ? Просто пробовать на большом массиве или есть поинтереснее способы?
Pravé bohatství se skrývá uvnitř
 
 
 
#15
24.05.2018 08:37:08
Цитата
Islander написал:
как лучше замерять производительность в PQ?
В Excel - простейшим скриптом
Код
Public Sub getUpdateTime()
    Dim pLo As ListObject, vStart As Single
    vStart = Timer
    Set pLo = ActiveSheet.ListObjects(1)
    pLo.QueryTable.Refresh False
    MsgBox CStr(Timer - vStart)
End Sub

В Power BI Desktop - секундомером :)
Судя по обсуждениям коллег, в силу ленивых алгоритмов выполнения, фактически не возможно получить достоверные данные по времени со стороны PQ. Может я не прав, коллеги подправят.
Изменено: Андрей VG - 24.05.2018 08:37:41
 
 
 
#16
24.05.2018 09:11:53
Если интересно, то вот здесь обсуждался вопрос замера скорости обновления запросов PQ собственными средствами.
Вот горшок пустой, он предмет простой...
 
 
 
#17
24.05.2018 12:40:12
Цитата
Андрей VG написал:
Дешевле уж тогда rList
Имеет право на жизнь, но издевательство же :)
F1 творит чудеса
 
 
 
#18
24.05.2018 13:40:49
Цитата
Максим Зеленский написал:
но издевательство же
Привет, Максим.
Ну, это ещё не совсем. Вот если бы ещё Table.SelectRows был задействован с сортировкой для поиска ближайшей меньшей даты, тогда да...
 
 
 
#19
24.05.2018 13:57:24
Цитата
Андрей VG написал:
Вот если бы ещё Table.SelectRows был задействован
Это уже мазохизмом отдает.  :)
Джойны рулят, пробовал при расчете значений в столбцах использовать работу со списками безо всяких List.Generate, только комбинацию List.Zip и List.Sum так в итоге табличка на 46 тыс строк считается 10 минут. По факту в каждой ячейке рассчитывается СУММПРОИЗВ двух списков по 5 элементов. один из списков генерится один раз за время запроса и кладется в буфер, второй генерится из значений 5 столбцов для каждой строки.
Когда банально приджойнил забуферизованный список в виде столбцов и прописал простую формулу что куда умножить и с чем сложить время расчета снизилось до 6-7 минут.
Не исключаю, конечно, что это я там нагородил чего, но как бы нагородил я тогда в обоих вариантах расчетов, а с джойном работает быстрее. Но, к сожалению в работе оставить пришлось вариант со списками, т.к. они динамически меняют свой размер, и прописать формулу обработки списка в этом случае гораздо проще, чем продумывать все варианты вывода непонятного заранее количества столбцов и как для них всех писать формулы с расчетами.
Изменено: PooHkrd - 24.05.2018 14:02:45
Вот горшок пустой, он предмет простой...
 
 
 
#20
24.05.2018 16:12:32
Цитата
PooHkrd написал:
только комбинацию List.Zip и List.Sum так в итоге табличка на 46 тыс строк считается 10 минут. По факту в каждой ячейке рассчитывается СУММПРОИЗВ двух списков по 5 элементов. один из списков генерится один раз за время запроса и кладется в буфер, второй генерится из значений 5 столбцов для каждой строки.
8-0  8-0  8-0
Что-то там вы намудрили, или не про всё рассказали
Код
// Таблица1
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Столбец1", Int64.Type}, {"Столбец2", Int64.Type}, {"Столбец3", Int64.Type}, {"Столбец4", Int64.Type}, {"Столбец5", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "tmp1", each List.Sum(List.Transform(List.Zip({List2,{[Столбец1],[Столбец2],[Столбец3],[Столбец4],[Столбец5]}}), each _{0}*_{1})))
in
    #"Added Custom"

// List2
let
    Source = Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Столбец1", type number}}),
    Столбец1 = List.Buffer(#"Changed Type"[Столбец1])
in
    Столбец1

Время обновления таблицы на листе (50.000 строк), 100 рефрешей таблицы, секунд:
Медиана 1,03515625    
Среднее 1,0319921875
F1 творит чудеса
 
 
 
#21
25.05.2018 09:26:03
Да мне тут Андрей тоже уже указал, что я намудрил - буду разбираться поэтапно со своим кодом теперь.
Вот горшок пустой, он предмет простой...
 
 
 
Страницы: 1
Наверх