зміст
Майже на кожному тренінгу Power Query, коли ми розповідаємо, як оновлювати створені запити, і люди бачать, як нові дані замінюють старі під час оновлення, один із слухачів запитує мене: «чи можна переконатися, що під час оновлення старі дані також десь зберігалися і всю історію оновлень було видно?
Ідея не нова, і стандартною відповіддю на неї буде «ні» — Power Query за замовчуванням налаштовано на заміну старих даних новими (що потрібно в переважній більшості випадків). Однак при великому бажанні можна обійти це обмеження. А метод, як ви побачите пізніше, дуже простий.
Розглянемо наступний приклад.
Припустімо, що у нас є файл від клієнта як вхідні дані (назвемо його, скажімо, Source) зі списком товарів, які він хоче купити у вигляді «розумної» динамічної таблиці під назвою додаток:
В іншому файлі (назвемо його за аналогією приймач) ми створюємо простий запит для імпорту таблиці з продуктами з джерела через Дані – Отримати дані – З файлу – З книги Excel (Дані — Отримати дані — З файлу — З книги Excel) і завантажте отриману таблицю на аркуш:
Якщо в майбутньому клієнт вирішить внести зміни в замовлення в своєму файлі Source, а потім після оновлення нашого запиту (клацнувши правою кнопкою миші або через Дані – оновити все) ми побачимо нові дані у файлі приймач — все стандартно.
Тепер давайте переконаємося, що під час оновлення старі дані не замінюються новими, а нові додаються до старих – і з додаванням дати й часу, щоб можна було побачити, коли ці конкретні зміни були зробив.
Крок 1. Додавання дати-часу до вихідного запиту
Давайте відкриємо заявку додатокімпорт наших даних із Sourceі додайте до нього стовпець із датою й часом оновлення. Для цього можна скористатися кнопкою Спеціальний стовпець таб Додавання колонки (Додати стовпець — спеціальний стовпець), а потім увійдіть у функцію DateTime.LocalNow – аналог функції TDATA (ЗАРАЗ) в Microsoft Excel:
Натиснувши на OK у вас має вийти такий гарний стовпець (не забудьте встановити для нього формат дати й часу за допомогою значка в заголовку стовпця):
Якщо хочете, то для таблички, завантаженої на аркуш для цього стовпця, ви можете встановити формат дати-часу з секундами для більшої точності (до стандартного формату доведеться додати двокрапку і «ss»):
Крок 2. Запит старих даних
Тепер давайте створимо інший запит, який діятиме як буфер, що зберігає старі дані перед оновленням. Виділення будь-якої клітинки отриманої таблиці у файлі приймач, виберіть на вкладці дані Command З таблиці/діапазону (Дані — з таблиці/діапазону) or З листям (З аркуша):
Ми нічого не робимо з таблицею, завантаженою в Power Query, ми називаємо запит, наприклад, старі дані і натисніть Головна — Закрити та завантажити — Закрити та завантажити до… — Лише створити з’єднання (Додому — Закрити&Завантажити — Закрити&Завантажити до… — Лише створити з’єднання).
Крок 3. Об'єднання старих і нових даних
Тепер повернемося до нашого початкового запиту додаток і додайте до нього знизу старі дані з попереднього запиту буфера за допомогою команди Головна — Додати запити (Домашня сторінка — Додати запити):
Це все!
Залишилося повернутися в Excel через Головна — закрити та завантажити (Додому — Close&Load) і спробуйте кілька разів оновити всю нашу структуру за допомогою кнопки Оновити всі таб дані (Дані — оновити все). З кожним оновленням нові дані не замінять старі дані, а перемістять їх нижче, зберігаючи всю історію оновлень:
Подібний трюк можна використовувати при імпорті з будь-яких зовнішніх джерел (інтернет-сайтів, баз даних, зовнішніх файлів тощо), щоб зберегти старі значення для історії, якщо вам це потрібно.
- Зведена таблиця в кількох діапазонах даних
- Складання таблиць із різних файлів за допомогою Power Query
- Збір даних з усіх аркушів книги в одну таблицю