Сохранение истории обновлений запросов Power Query

Почти на каждом тренинге по Power Query, когда мы доходим до способов обновления созданных запросов и люди видят как новые данные заменяют при обновлении старые, меня кто-нибудь из слушателей спрашивает: "а можно сделать так, чтобы при обновлении старые данные тоже где-нибудь сохранялись и было видно всю историю обновлений?"

Мысль не новая и стандартным ответом на неё будет "нет" - Power Query по умолчанию настроен именно на замену старых данных новыми (что и требуется в абсолютном большинстве случаев). Однако, если очень хочется, то можно обойти это ограничение. Причем способ, как вы дальше увидите, весьма простой.

Рассмотрим следующий пример.

Предположим, что в качестве исходных данных у нас имеется файл от клиента (назовем его, допустим, Источник) со списком товаров, которые он хочет купить в виде "умной" динамической таблицы с именем Заявка:

Источник данных

В другом файле (назовём его по аналогии Приемник) мы создаем простой запрос для импорта таблицы с товарами из Источника через Данные - Получить данные - Из файла - Из книги Excel (Data - Get data - From file - From Excel workbook) и выгружаем полученную таблицу на лист:

Загруженные в Приемник данные из Источника

Если в будущем клиент решит внести изменения в заказ у себя в файле Источник, то после обновления нашего запроса (правой кнопкой мыши или через Данные - Обновить всё) мы увидим новые данные в файле Приемник - все стандартно.

Теперь давайте сделаем так, чтобы при обновлении старые данные не заменялись новыми, а новые дописывались к старым - причем с добавлением даты-времени, чтобы было видно когда внесены эти конкретные изменения.

Шаг 1. Добавляем дату-время к исходному запросу

Откроем запрос Заявка, импортирующий наши данные из Источника, и добавим к нему столбец с датой-временем обновления. Для этого можно воспользоваться кнопкой Настраиваемый столбец на вкладке Добавление столбца (Add column - Custom column), а затем ввести функцию DateTime.LocalNow - аналог функции ТДАТА (NOW) в Microsoft Excel:

Добавляем дату-время обновления

После нажатия на ОК должен получиться вот такой симпатичный столбец (не забудьте установить для него формат даты-времени значком в шапке столбца):

Готовый столбец с датой обновления

Если хотите, то для выгруженной на лист таблички для этого столбца можно установить формат даты-времени с секундами для пущей точности (придётся дописать двоеточие и "сс" к стандартному формату):

Добавляем формат с секундами

Шаг 2. Запрос для старых данных

Теперь создадим ещё один запрос, который будет играть роль буфера, сохраняющего старые данные перед обновлением. Выделив любую ячейку результирующей таблицы в файле Приемник, выберем на вкладке Данные команду Из таблицы/диапазона (Data - From table/range) или С листа (From sheet):

Создаем буферный запрос для старых данных

С загруженной в Power Query таблицей ничего не делаем, называем запрос, допустим, Старые данные и жмём Главная - Закрыть и загрузить - Закрыть и загрузить в... - Только создать подключение (Home - Close&Load - Close&Load to... - Only create connection).

Шаг 3. Стыкуем старые и новые данные

Теперь возвращаемся в наш исходный запрос Заявка и добавляем к нему снизу старые данные из предыдущего буферного запроса командой Главная - Добавить запросы (Home - Append Queries):

Добавляем старые данные

Вот и всё!

Осталось вернуться в Excel через Главная - Закрыть и загрузить (Home - Close&Load) и попробовать пару раз обновить всю нашу конструкцию кнопкой Обновить всё на вкладке Данные (Data - Refresh All). При каждом обновлении новые данные будут не заменять старые, а выталкивать их ниже, сохраняя всю историю обновлений:

История обновлений

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

Ссылки по теме




28.09.2021 06:13:54
Отличный лайфхак для PQ. Больше бы таких приемов
30.09.2021 08:28:28
Очень круто, только не хватает того, как сделать так, чтобы определенные выделенные строки не изменялись при обновлении ;)
03.10.2021 16:25:48
Перед добавлением к изначальной таблице сначала сделать антиджойн по ключевым столбцам, и только то что в исходнике не нашлось - добавлять.
Наверх