Примечания на самоссылающемся запросе в Power Query
Предположим, что вы загрузили в Microsoft Excel с помощью Power Query любые нужные вам данные из внешнего мира. Я возьму в качестве примера вот такую таблицу, загруженную из файла CSV. Само собой, в запросе может быть множество шагов и всяческих преобразований - это сейчас не важно. Главное, что в результате мы получаем таблицу, которую затем выгружаем на лист в текущей книге командой Главная - Закрыть и загрузить (Home - Close & Load):
Теперь представим, что нам необходимо добавить свои произвольные комментарии к некоторым заказам в этой таблице. Создадим для них новый столбец справа от таблицы и впишем их руками:
На первый взгляд всё выглядит нормально, но есть одна проблема - если в будущем порядок строк в источнике или в самом запросе изменится (например, в середину таблицы добавятся или удалятся строки или нам потребуется отсортировать строки в запросе), то наши примечания конечно же останутся на прежних местах и уже не будут соответствовать тем строкам, для которых их сделали.
Обойти эту проблему можно простым, но весьма неочевидным, но оригинальным способом - сделать то, что называют самоссылающийся запрос. Помните, как барон Мюнгхаузен вытащил себя за волосы из болота? Сейчас будет что-то похожее, но в Power Query.
Алгоритм действий следующий:
- Берем нашу таблицу с результатами запроса и добавленным столбцом примечаний и грузим её обратно в Power Query командой Данные - Из таблицы/диапазона (Data - From Table/Range).
- Называем этот запрос Примечания и выбираем Главная - Закрыть и загрузить - Закрыть и загрузить в - Только создать подключение (Home - Close & Load - Close & Load to - Only create connection).
- Возвращаемся в наш исходный запрос и объединяем его с только что созданным запросом Примечания. Ключевым общим столбцом для привязки выбираем Код заказа:
- Разворачиваем полученные вложенные таблицы и достаём оттуда столбец Примечания.
- После выбора команды Главная - Закрыть и загрузить возвращаемся в Excel. Ненужный более дубликат столбца примечаний (Примечания.2) - удаляем:
Собственно всё.
Теперь можно спокойно вносить примечания в новый столбец, который будет при обновлении запроса загружаться в Power Query и корректно (по совпадению номера заказа) подтягиваться к имеющимся строкам, даже если они будут идти в другом порядке.
Для примера, давайте внесем новую сделку в исходный файл:
После обновления видно, что порядок строк изменился, но ничего не сломалось и примечания соответствуют тем строкам, куда мы их писали:
То же самое будет, если мы отсортируем строки в нашем запросе или будет вводить новые или редактировать старые примечания - всё останется на своих местах.
Важные нюансы
В завершении хотелось бы озвучить несколько важных моментов:
- Чтобы однозначно привязать комментарий к строке в данных с помощью такого самоссылающегося запроса, в вашей таблице должен быть столбец с каким-либо уникальным идентификатором строки. В нашем примере эту роль сыграл уникальный код заказа. Если такого столбца в готовом виде у вас нет, то его придется сделать - например, можно склеить артикул товара и дату покупки, имя файла и номер строки в нём и т.п.
- Такие самоссылающиеся запросы могут быть весьма медленными, если делать их на больших таблицах. Возможно, придётся подождать при обновлении дольше обычного.
- После удаления первичного столбца с примечаниями (который стал называться Примечание2, помните?) наши примечания сохраняются теперь только внутри запроса. Так что если вы впоследствии случайно, например, в процессе отладки запроса удалите столбец с примечаниями, то потеряете их насовсем. Так что будьте внимательны, пожалуйста, и почаще делайте бэкапы такой таблицы или всего файла. Как говорится "люди делятся на 2 категории: те, кто делает бэкапы и те, кто УЖЕ делает бэкапы" :)
Ссылки по теме
- Сохранение истории обновлений запросов в Power Query
- Сборка таблиц с разными шапками из нескольких книг
- Параметризация путей к данным в Power Query
Уникальным идентификатором может быть и набор столбцов. Можно не склеивать артикул товара и дату покупки, а выбрать указанные столбцы в качестве ключевых.