Параметризация путей к данным в 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:

Загруженные данные в Power Query

Приведём их в нормальный вид:

  1. Удалим пустые строки через Главная - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove Empty Rows).
  2. Удалим ненужные 4 верхних строки через Главная - Удалить строки - Удалить верхние строки (Home - Remove Rows - Remove Top Rows).
  3. Поднимем первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков на вкладке Главная (Home - Use first row as header).
  4. Отделим пятизначный артикул от названия товара во втором столбце, используя команду Разделить столбец на вкладке Преобразование (Transform - Split Column).
  5. Удалим ненужные столбцы и переименуем заголовки оставшихся для лучшей наглядности.

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

Приведенный в порядок файл

Осталось эту облагороженную таблицу выгрузить обратно на лист в наш файл Обработчик.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} - это номер строки в таблице Параметры, из которой мы хотим взять данные. Шапка - не в счет и нумерация начинается от нуля, а не от единицы.

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

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

Ссылки по теме




10.12.2018 12:35:39
Актуальная тема!

Формулу лучше писать, всегда на ENG (это будет работать как в Русской версии Excel так и в Английской:

LEFT(CELL("filename");FIND("[";CELL("filename"))-1)&"Топ-100 товаров.xls"

Вот этот вариант не работает в Английской из-за вставки шаблона "имяфайла":

ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"))-1)&"Топ-100 товаров.xls"
10.12.2018 20:48:42
Сергей, извини конечно, но ерунду пишешь - формула на английском языке не работает в русском Excel.
10.12.2018 21:15:49
Да, Николай, ты прав я опечатался в примерах, нужно писать для русского Excel формулы на русском а шаблон на английском, вот так:

=ЛЕВСИМВ(ЯЧЕЙКА("filename");НАЙТИ("[";ЯЧЕЙКА("filename"))-1)&"Топ-100 товаров.xls"

Тогда эта формула сработает и в Английской версии Excel

У меня так было..
10.12.2018 16:43:53
Николай, для себя взял на вооружение такой способ:
1) в таблицу "Параметры" формула =ЯЧЕЙКА("имя файла")
2) создаю отдельный запрос "Адрес":
let
    Источник = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content],
    #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Адрес", type text}}),
    #"Извлеченный текст перед разделителем" = Table.TransformColumns(#"Измененный тип", {{"Адрес", each Text.BeforeDelimiter(_, "]"), type text}}),
    #"Замененное значение" = Table.ReplaceValue(#"Извлеченный текст перед разделителем","[","",Replacer.ReplaceText,{"Адрес"}),
    Адрес = #"Замененное значение"{0}[Адрес]
in
    Адрес 
3) в качестве источника основного запроса указываю:  
Источник = Excel.Workbook(File.Contents(Адрес), null, true) 
Данный способ страхует от несанкционированного переименования файла (помогает, прежде всего, при пересылании файлов по почте и сохранении под другим именем)
10.12.2018 20:50:57
Хорошая идея. Но тогда надо добавить, что параметризация источников данных в виде явных параметров работает не во всех версиях Power Query. В последних обновлениях надо обязательно заходить в параметры и ставить там галочку Разрешить параметризацию в источнике данных - иначе будет ругаться.
12.12.2018 16:25:27
Спасибо, буду знать об этой особенности:)
10.12.2018 23:49:28
Спасибо, Николай, за очередное понятное и нужное видео.

Добавлю свой способ.
Я делаю так:
Создаю новый лист, далее так же создаю таблицу и пишу в нее формулу:
=ПОДСТАВИТЬ(ЛЕВСИМВ(ЯЧЕЙКА("имяфайла";A2);НАЙТИ("]";ЯЧЕЙКА("имяфайла";A2))-1);"[";"")
С помощью нее я получаю полный путь к файлу, включая название и расширение (при этом удаляя и заменяя лишние символы)
А потом в Power Query извлекаю этот путь так:
=Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]),true)
Ну, собственно, и все. После этого я могу переименовывать файл, перемещать его на любые компьютеры, пересылать. Путь к файлу в ячейке изменяется автоматически каждый раз. Запрос работает всегда без вмешательства в настройки пользователя.
11.12.2018 13:43:47
Если нужно натравить Power Query на Web отчёт, то команда немного меняется:
Источник = Web.Page(Web.Contents(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к отчёту])),
11.12.2018 19:51:27
А как быть, если запрос преобразован в функцию для сборки таблиц из разных файлов, и на месте жестко прописанного адреса уже стоит переменная?
16.12.2018 12:10:40
Тогда это всё надо применять к исходному запросу, получающему имена файлов, откуда собираются данные. Там тоже должен быть шаг Источник с указанной корневой папкой, по идее.
12.12.2018 13:09:41
Николай, для себя взял на вооружение такой способ:
1) формула =ЯЧЕЙКА("имя файла" )
2) создаю отдельный запрос :


let
   Источник = Excel.CurrentWorkbook(){[Name="Путь"]}[Content],
   #"Измененный тип" = Table.TransformColumnTypes(Источник,{{"Путь", type text}}),
   #"Извлеченный текст перед разделителем" = Table.TransformColumns(#"Измененный тип", {{"Путь", each Text.BeforeDelimiter(_, "[", type text}}),
   Путь1 = #"Извлеченный текст перед разделителем"{0}[Путь]
in
   Путь1

Способ похож на спочсоб Алексея Леонтьева
13.12.2018 07:03:48
Замечательно, как раз искал решение. Теперь использую это решение для получения значений параметров отбора с ячейки листа. Спасибо.
16.12.2018 12:10:51
Не за что :)
18.12.2018 13:20:37
Николай, возможно было бы полезным добавить в текст прием как можно кнопками получить выражение:
Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]
Т.е. показать что при нажатии кнопки "Из таблицы" на ленте "Данные" создается кусок формулы
Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]
а добавка
{0}[Путь к исходным данным]
получается путем правого клика мыши по ячейке с адресом в редакторе запросов и выбора пункта "Детализация".
По своему опыту обучения мышкоклацающих начинающих могу сказать, что данная схема воспринимается на ура, и уже после этого легче объяснять, за что и какая функция на языке М в этом выражении отвечает.
20.12.2018 14:13:10
Алексей, собственно, в видео к этому уроку я так и делаю.
Если что, то это делается специально - чтобы текстовая и видеоверсия немного различались по деталям. Тогда народ привыкает и смотреть и читать, а значит лучше усваивает ;)
20.12.2018 14:27:03
А, пардон, видео с работы не доступно. Тогда статья+видео просто огонь!
21.02.2019 18:14:03
Мне нужно написать 150 веб запросов и в адресе запроса меняется только несколько букв. Можно ли создать умную таблицу в строках, которых будет адрес для источника запроса..а потом как то быстро создать 150 отдельных запросов, источником которых будет строка, начиная для первого запроса со 2-й строки и заканчивая 151 строкой для 150 запроса?
22.02.2019 09:11:07
Самое простое решение - включить макрорекордер и создать первый запрос вручную. Затем посмотреть код VBA и дописать к нему цикл перебора 150 ячеек и создания 150 запросов, соответственно.
Но главный вопрос: зачем вам 150 запросов?
Если вам нужно собрать данные из 150 файлов, то поместите их в одну папку и делайте импорт из нее через Данные - Создать запрос - Из файла - Из папки.
Наверх