Сохранение истории обновлений запросов 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). При каждом обновлении новые данные будут не заменять старые, а выталкивать их ниже, сохраняя всю историю обновлений:
Подобный трюк можно использовать при импорте из любых внешних источников (сайтов в интернете, баз данных, внешних файлов и т.д.), чтобы сохранять старые значения для истории, если она вам нужна.
Ссылки по теме
- Сводная таблица по нескольким диапазонам данных
- Сборка таблиц из разных файлов с помощью Power Query
- Сборка данных со всех листов книги в одну таблицу
Всё отлично, но хотелось бы понять последовательность действий при желании удалить одну ветку(данные) сравнения.
Второй момент: натолкните как лучше отобразить динамику изменений.
А как возможно записывать обновления только тех записей, которые поменяли свое значение?
Например если запрос составляет несколько тысяч строк, а обновились несколько десятков или сотен значений, нецелесообразно перезаписывать все несколько тысяч строк