Содержание:
Практически на каждом тренинге по Power Query, когда мы добираемся до того, как обновлять созданные запросы и люди видят, как новые данные заменяют старые данные при обновлении, один из слушателей спрашивает меня: «Можно ли сделать так, чтобы при обновлении старые данные были тоже где-то сохранялись и вся история обновлений была видна?
Идея не нова и стандартным ответом на нее будет «нет» — Power Query по умолчанию настроен на замену старых данных новыми (что требуется в подавляющем большинстве случаев). Однако, если очень захотеть, это ограничение можно обойти. А метод, как вы увидите позже, очень прост.
Рассмотрим следующий пример.
Предположим, что у нас есть файл от клиента в качестве входных данных (назовем его, скажем, Источник) со списком товаров, которые он хочет купить, в виде «умной» динамической таблицы с именем Применение:
В другом файле (назовем его по аналогии Получатель) создаём простой запрос на импорт таблицы с товарами из Источника через Данные – Получить данные – Из файла – Из книги Excel (Данные — Получить данные — Из файла — Из книги Excel) и загрузим полученную таблицу на лист:
Если в дальнейшем клиент решит внести изменения в заказ в своем файле Источник, то после обновления нашего запроса (щелкнув правой кнопкой мыши или через Данные – обновить все) мы увидим новые данные в файле Получатель — все стандартно.
Теперь сделаем так, чтобы при обновлении старые данные не заменялись новыми, а к старым добавлялись новые — причем с добавлением даты-времени, чтобы было видно, когда были эти конкретные изменения. сделал.
Шаг 1. Добавление даты и времени в исходный запрос
Давайте откроем заявку Применениеимпортируем наши данные из Источники добавьте в него столбец с датой и временем обновления. Для этого вы можете использовать кнопку Пользовательский столбец таб Добавление столбца (Добавить столбец — Пользовательский столбец), а затем введите функцию DateTime.LocalNow – аналог функции ТДАТА (ТЕПЕРЬ) в Microsoft Excel:
После нажатия на OK у вас должен получиться вот такой красивый столбец (не забудьте установить для него формат даты и времени с помощью значка в заголовке столбца):
Если хотите, то для таблички, выгруженной на лист по этому столбцу, для большей точности можно установить формат даты и времени с секундами (к стандартному формату придется добавить двоеточие и «сс»):
Шаг 2. Запрос старых данных
Теперь давайте создадим еще один запрос, который будет действовать как буфер, сохраняющий старые данные перед обновлением. Выбор любой ячейки результирующей таблицы в файле Получатель, выберите на вкладке Данные Command Из таблицы/диапазона (Данные — из таблицы/диапазона) or С листьями (Из листа):
С таблицей, загруженной в Power Query, ничего не делаем, вызываем запрос, например, старые данные и нажмите Главная страница — Закрыть и загрузить — Закрыть и загрузить в… — Только создать соединение (Главная страница — Закрыть и загрузить — Закрыть и загрузить в… — Только создать соединение).
Шаг 3. Объединение старых и новых данных
Теперь вернемся к нашему исходному запросу Применение и добавляем к нему снизу старые данные из предыдущего запроса буфера командой Главная — Добавить запросы (Главная страница — Добавление запросов):
Это все!
Осталось вернуться в Excel через Главная — Закрыть и скачать (Главная страница — Закрыть и загрузить) и попробуем пару раз обновить всю нашу структуру кнопкой Обновить все таб Данные (Данные — Обновить все). При каждом обновлении новые данные не будут заменять старые данные, а будут вытеснять их ниже, сохраняя всю историю обновлений:
Подобный трюк можно использовать при импорте из любых внешних источников (интернет-сайты, базы данных, внешние файлы и т. д.), чтобы сохранить старые значения для истории, если вам это нужно.
- Сводная таблица по нескольким диапазонам данных
- Сборка таблиц из разных файлов с помощью Power Query
- Сбор данных со всех листов книги в одну таблицу