Параметризация путей к данным в Power Query

Если вы уже начали пользоваться инструментами бесплатной надстройки Power Query в Microsoft Excel, то очень скоро вы столкнетесь с одной узкоспециализированной, но очень частой и досадной проблемой, связанной с постоянными разрывами ссылок на исходные данные. Суть проблемы в том, что если в вашем запросе вы ссылаетесь на внешние файлы или папки, то Power Query жестко прописывает в тексте запроса абсолютный путь к ним. На вашем компьютере все работает нормально, но если вы решите отправить файл с просьбой коллегам, то они будут разочарованы, т.к. у них другой путь к исходным данным на их компьютере, и наш запрос не сработает.

Что делать в такой ситуации? Давайте рассмотрим этот случай более подробно на следующем примере.

Постановка задачи

Предположим, у нас в папке есть E:Отчеты по продажам лежит файл Топ-100 продуктов.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.). Этот файл содержит информацию о наиболее популярных товарных позициях и внутри выглядит следующим образом:

Наверное, сразу понятно, что работать с ним в Excel в таком виде практически невозможно: будут мешать пустые строки через одну с данными, объединенные ячейки, лишние столбцы, многоуровневый заголовок и т.д.

Поэтому рядом с этим файлом в той же папке создаем еще один новый файл Обработчик.xlsx, в котором мы создадим запрос Power Query, который будет загружать уродливые данные из исходного файла загрузки. Топ-100 продуктов.xlsи расположим их по порядку:

Выполнение запроса к внешнему файлу

Открытие файла Обработчик.xlsx, выберите на вкладке Данные Command Получить данные – из файла – из книги Excel (Данные — Получить данные — Из файла — Из Excel), затем указываем расположение исходного файла и нужного нам листа. Выбранные данные будут загружены в редактор Power Query:

Приведем их в норму:

1. Удалите пустые строки с помощью Главная — Удалить строки — Удалить пустые строки (Главная страница — Удаление строк — Удаление пустых строк).
2. Удалите ненужные верхние 4 строки через Главная — Удалить строки — Удалить верхние строки (Главная страница — Удалить строки — Удалить верхние строки).
3. Поднимите первую строку в заголовок таблицы кнопкой Используйте первую строку в качестве заголовков таб Главная (Главная страница — используйте первую строку в качестве заголовка).
4. Отделите пятизначный артикул от названия продукта во втором столбце с помощью команды разделить столбец таб трансформация (Преобразование — Разделить столбец).
5. Удалите ненужные столбцы и переименуйте заголовки остальных для лучшей наглядности.

В результате мы должны получить следующую, гораздо более приятную картину:

Осталось загрузить эту облагороженную таблицу обратно на лист в нашем файле. Обработчик.xlsx Команда закрыть и скачать (Главная страница — Закрыть и загрузить) таб Главная:

Поиск пути к файлу в запросе

Теперь посмотрим, как наш запрос выглядит «под капотом», на встроенном в Power Query внутреннем языке с лаконичным названием «М». Для этого вернитесь к нашему запросу, дважды щелкнув по нему на правой панели. Запросы и связи и на вкладке Обзор укажите Расширенный редактор (Вид — Расширенный редактор):

В открывшемся окне вторая строка сразу показывает жестко закодированный путь к нашему исходному файлу загрузки. Если мы сможем заменить эту текстовую строку параметром, переменной или ссылкой на ячейку листа Excel, где этот путь заранее записан, то мы сможем легко изменить его позже.

Добавьте умную таблицу с путем к файлу

Давайте пока закроем Power Query и вернемся к нашему файлу. Обработчик.xlsx. Добавим новый пустой лист и составим на нем небольшую «умную» таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:

Чтобы создать смарт-таблицу из обычного диапазона, вы можете использовать сочетание клавиш Ctrl+T или кнопка Форматировать как таблицу таб Главная (Главная страница — Форматировать как таблицу). Заголовок столбца (ячейка А1) может быть абсолютно любым. Также обратите внимание, что для ясности я дал таблице имя параметры таб Конструктор (Дизайн).

Скопировать путь из Проводника или даже ввести его вручную, конечно, не представляет особой сложности, но лучше минимизировать человеческий фактор и определять путь, по возможности, автоматически. Это можно реализовать с помощью стандартной функции рабочего листа Excel. CELL (КЛЕТКА), который может выдать кучу полезной информации о ячейке, указанной в качестве аргумента, включая путь к текущему файлу:

Если предположить, что файл исходных данных всегда лежит в той же папке, что и наш Процессор, то нужный нам путь можно сформировать по следующей формуле:

=LEFT(CELL("имя файла");FIND("[";CELL("имя файла"))-1)&"100 лучших продуктов.xls"

или в английской версии:

=LEFT(CELL(«имя файла»);FIND(«[«;CELL(«имя файла»))-1)&»Топ-100 товаров.xls»

… где находится функция ЛЕВСИМВ (ОСТАВИЛ) берет кусок текста от полной ссылки до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего файла исходных данных.

Параметризация пути в запросе

Остался последний и самый важный штрих — прописать в запросе путь к исходному файлу. Топ-100 продуктов.xls, обращаясь к ячейке A2 нашей созданной «умной» таблицы параметры.

Для этого вернемся к запросу Power Query и откроем его еще раз. Расширенный редактор таб Обзор (Вид — Расширенный редактор). Вместо текстовой строки-путь в кавычках «E:Отчеты о продажах. Топ-100 продуктов.xlsx» Представим следующую структуру:

Excel.ТекущаяWorkbook(){[Name="Настройки"]}[Содержимое]0 {}[Путь к исходным данным]

Давайте посмотрим, из чего он состоит:

• Excel.ТекущаяWorkbook() — функция языка М для доступа к содержимому текущего файла
• {[Name="Настройки"]}[Содержимое] – это уточняющий параметр предыдущей функции, указывающий, что мы хотим получить содержимое «умной» таблицы параметры
• [Путь к исходным данным] это имя столбца в таблице параметрына который мы ссылаемся
  
• 0 {} это номер строки в таблице параметрыиз которого мы хотим взять данные. Шапка не учитывается и нумерация начинается с нуля, а не с единицы.

Вот и все, собственно.

Осталось нажать Завершить и проверим, как работает наш запрос. Теперь при отправке всей папки с обоими файлами внутри на другой ПК запрос останется работоспособным и автоматически определит путь к данным.

• Что такое Power Query и зачем он нужен при работе в Microsoft Excel
• Как импортировать фрагмент плавающего текста в Power Query
• Преобразование перекрестной таблицы XNUMXD в плоскую таблицу с помощью Power Query