Параметризация путей к данным в Power Query
Если вы уже начали использовать в работе инструменты бесплатной надстройки Power Query в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса. У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет.
Что же сделать в такой ситуации? Давайте рассмотрим этот случай подробнее на следующем примере.
Постановка задачи
Предположим, что у нас в папке E:\Отчеты по продажам лежит файл Топ-100 товаров.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.) Этот файл содержит информацию о наиболее популярных товарных позициях и выглядит внутри примерно так:
С ходу, наверное, понятно, что работать с ним в Excel в таком виде практически невозможно: будут мешать пустые строки через одну с данными, объединенные ячейки, лишние столбцы, многоуровневая шапка и т.д.
Поэтому рядом с этим файлом в той же папке мы создаём ещё один новый файл Обработчик.xlsx, в котором создадим запрос Power Query, который будет загружать страшненькие данные из исходного файла-выгрузки Топ-100 товаров.xls, и приводить их в порядок:
Создаем запрос к внешнему файлу
Открыв файл Обработчик.xlsx, выберем на вкладке Данные команду Получить данные - Из файла - Из книги Excel (Data - Get Data - From file - From Excel), затем укажем местоположение исходного файла и нужный нам лист. Выбранные данные загрузятся в редактор Power Query:
Приведём их в нормальный вид:
- Удалим пустые строки через Главная - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove Empty Rows).
- Удалим ненужные 4 верхних строки через Главная - Удалить строки - Удалить верхние строки (Home - Remove Rows - Remove Top Rows).
- Поднимем первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков на вкладке Главная (Home - Use first row as header).
- Отделим пятизначный артикул от названия товара во втором столбце, используя команду Разделить столбец на вкладке Преобразование (Transform - Split Column).
- Удалим ненужные столбцы и переименуем заголовки оставшихся для лучшей наглядности.
В итоге у нас должна получиться следующая, гораздо более приятная, картина:
Осталось эту облагороженную таблицу выгрузить обратно на лист в наш файл Обработчик.xlsx командой Закрыть и загрузить (Home - Close&Load) на вкладке Главная:
Находим путь к файлу в запросе
Теперь давайте посмотрим как выглядит наш запрос "под капотом", на встроенном в Power Query внутреннем языке с лаконичным названием "М". Для этого вернемся в наш запрос двойным щелчком по нему в правой панели Запросы и подключения и на вкладке Просмотр выберем Расширенный редактор (View - Advanced Editor):
В открывшемся окне во второй строке сразу же обнаруживается жёстко прописанный путь к нашему исходному файлу выгрузки. Если мы сможем заменить эту текстовую строку на параметр, переменную или ссылку на ячейку листа Excel, где этот путь будет заранее прописан, то мы сможем впоследствии легко его менять.
Добавляем умную таблицу с путём к файлу
Закроем пока Power Query и вернёмся в наш файл Обработчик.xlsx. Добавим новый пустой лист и сделаем на нём маленькую "умную" таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:
Для создания "умной" таблицы из обычного диапазона можно использовать сочетание клавиш Ctrl+T или кнопку Форматировать как таблицу на вкладке Главная (Home - Format as Table). Заголовок столбца (ячейка А1) может быть совершенно любым. Также обратите внимание, что для понятности я дал таблице имя Параметры на вкладке Конструктор (Design).
Скопировать из Проводника путь или даже ввести его вручную не представляет, конечно, особой сложности, но лучше всего минимизировать человеческий фактор и определять путь, по возможности, автоматически. Это можно реализовать с помощью стандартной функции рабочего листа Excel ЯЧЕЙКА (CELL), которая умеет выдавать кучу полезной информации об указанной в качестве аргумента ячейке - в том числе и путь к текущему файлу:
Если предположить, что файл с исходными данными всегда лежит в той же папке, что и наш Обработчик, то путь, который нам нужен можно сформировать следующей формулой:
=ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"))-1)&"Топ-100 товаров.xls"
или в английской версии:
=LEFT(CELL("filename");FIND("[";CELL("filename"))-1)&"Топ-100 товаров.xls"
... где функция ЛЕВСИМВ (LEFT) берёт из полной ссылки кусок текста до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего исходного файла с данными.
Параметризуем путь в запросе
Остался последний и самый главный штрих - прописать в запросе путь к исходному файлу Топ-100 товаров.xls, сославшись на ячейку А2 нашей созданной "умной" таблицы Параметры.
Для этого вернемся в запрос Power Query и ещё раз откроем Расширенный редактор на вкладке Просмотр (View - Advanced Editor). Вместо текстовой строки-пути в кавычках "E:\Отчеты по продажам\Топ-100 товаров.xlsx" введём туда вот такую конструкцию:
Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]
Давайте разберемся из чего она состоит:
- Excel.CurrentWorkbook() - это функция языка М для обращения к содержимому текущего файла
- {[Name="Параметры"]}[Content] - это уточняющий параметр к предыдущей функции, указывающий, что мы хотим получить содержимое "умной" таблицы Параметры
- [Путь к исходным данным] - это имя столбца в таблице Параметры, к которому мы обращаемся
- {0} - это номер строки в таблице Параметры, из которой мы хотим взять данные. Шапка - не в счет и нумерация начинается от нуля, а не от единицы.
Вот и всё, собственно.
Осталось нажать на Готово и проверить как работает наш запрос. Теперь при пересылке всей папки с обоими файлами внутри на другой ПК запрос будет сохранять работоспособность и определять путь к данным автоматически.
Ссылки по теме
- Что такое Power Query и зачем он нужен при работе в Microsoft Excel
- Как импортировать в Power Query плавающий фрагмент текста
- Редизайн двумерной кросс-таблицы в плоскую с помощью Power Query
Формулу лучше писать, всегда на ENG (это будет работать как в Русской версии Excel так и в Английской:
LEFT(CELL("filename");FIND("[";CELL("filename"))-1)&"Топ-100 товаров.xls"
Вот этот вариант не работает в Английской из-за вставки шаблона "имяфайла":
ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"))-1)&"Топ-100 товаров.xls"
=ЛЕВСИМВ(ЯЧЕЙКА("filename");НАЙТИ("[";ЯЧЕЙКА("filename"))-1)&"Топ-100 товаров.xls"
Тогда эта формула сработает и в Английской версии Excel
У меня так было..
1) в таблицу "Параметры" формула =ЯЧЕЙКА("имя файла")
2) создаю отдельный запрос "Адрес":
let Источник = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content], #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Адрес", type text}}), #"Извлеченный текст перед разделителем" = Table.TransformColumns(#"Измененный тип", {{"Адрес", each Text.BeforeDelimiter(_, "]"), type text}}), #"Замененное значение" = Table.ReplaceValue(#"Извлеченный текст перед разделителем","[","",Replacer.ReplaceText,{"Адрес"}), Адрес = #"Замененное значение"{0}[Адрес] in АдресДобавлю свой способ.
Я делаю так:
Создаю новый лист, далее так же создаю таблицу и пишу в нее формулу:
=ПОДСТАВИТЬ(ЛЕВСИМВ(ЯЧЕЙКА("имяфайла";A2);НАЙТИ("]";ЯЧЕЙКА("имяфайла";A2))-1);"[";"")А потом в Power Query извлекаю этот путь так:
=Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]),true)Помогает не менять каждый раз путь к файлу и ссылается на конкретный файл, а не на все файлы с таким названием в папке и подпапках
Источник = Web.Page(Web.Contents(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к отчёту])),
1) формула =ЯЧЕЙКА("имя файла" )
2) создаю отдельный запрос :
let
Источник = Excel.CurrentWorkbook(){[Name="Путь"]}[Content],
#"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Путь", type text}}),
#"Извлеченный текст перед разделителем" = Table.TransformColumns(#"Измененный тип", {{"Путь", each Text.BeforeDelimiter(_, "[", type text}}),
Путь1 = #"Извлеченный текст перед разделителем"{0}[Путь]
in
Путь1
Способ похож на спочсоб Алексея Леонтьева
Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]По своему опыту обучения мышкоклацающих начинающих могу сказать, что данная схема воспринимается на ура, и уже после этого легче объяснять, за что и какая функция на языке М в этом выражении отвечает.
Если что, то это делается специально - чтобы текстовая и видеоверсия немного различались по деталям. Тогда народ привыкает и смотреть и читать, а значит лучше усваивает
Но главный вопрос: зачем вам 150 запросов?
Если вам нужно собрать данные из 150 файлов, то поместите их в одну папку и делайте импорт из нее через Данные - Создать запрос - Из файла - Из папки.
И ведь не возвращается к исходному виду, если ломающую путь книгу закрыть. Только перерасчет спасает.
Дюже мне непонятно, в чем проблема создать параметр для функции Excel.CurrentWorkbook(), который бы формировал ссылку на папку книги.
отправляет на простой редизайнер таблицКак применить параметрический подход к названиям листов книги Ексель?
В файлах источниках меняются имена листов - бух программа при выгрузке данных вставляет в имя листа название месяца и прочую ненужную лабуду.... Каждый месяц надо переписывать запрос.
Хорошая новость - Книга с исходными данными всегда содержит один-единственный лист.
Пожалуйста помогите!
Следуя Вашему примеру, в расширенном редакторе запросов Power Query запись:
#"Строки с примененным фильтром" = Table.SelectRows(Источник, each [#"Дата осн. документа"] >= #date(2020, 1, 10) and [#"Дата осн. документа"] <= #date(2020, 2, 15))
меняю на запись:
#"Строки с примененным фильтром" = Table.SelectRows(Источник, each [#"Дата осн. документа"] >= Excel.CurrentWorkbook(){[Name="Период"]}[Content]{0}[с:] and [#"Дата #(lf)осн. документа"] <= Excel.CurrentWorkbook(){[Name="Период"]}[Content]{0}[по:])
В итоге получаю ошибку: «Formula.Firewall: Запрос "Анализ" (шаг "Строки с примененным фильтром") ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных. Измените эту комбинацию данных.».
Подскажите, пожалуйста, что я не так делаю?
Подскажите пожалуйста. В вашем случае параметр используется всего один раз для указания пути, а я хочу использовать указываемый пользователем параметр в логике настраиваемого столбца. В таблице несколько сотен тыс строк. PQ будет на каждой строке запрашивать значение параметра заново или же подобные моменты оптимизированы ? Если нет, то как быть в этой ситуации?