Относительный путь к данным PowerQuery
В большинстве случаев создание запросов PowerQuery создается на основании файлов Excel, отличных от того файла, в котором сам запрос. Как пример - одна из статей на этом сайте: План-фактный анализ в Excel при помощи Power Query. Там идет обращение к некоторым вспомогательным файлам с данными и запрос ко всем файлам определенной папки. Т.е. мы можем знать только папку относительно файла с самим запросом и имена некоторых вспомогательных таблиц. И здесь как раз возникает нюанс: если впоследствии переместить или переслать файл с запросом (или даже полностью всю модель данных со всеми файлами) - то придется для каждого запроса к отдельному файлу менять источник. Это можно сделать либо через Параметры источника данных самого запроса:
Для пользователей Excel 2010-2013:
Перейти на вкладку Power Query -группа Настройки(Options) -Параметры источника данных(Data Source Settings) для пользователей 2016 и выше:
Перейти на вкладку Данные(Data) -Создать запрос(New Query) -Параметры источника данных(Data Source Settings)
либо изменив текст каждого запроса в редакторе запросов, изменив там строку источника:
И в том и в другом случае после смены каждого источника придется ждать обновления запросов. Как ни странно, но стандартно, без танцев с бубном, сделать некий относительный путь(указав лишь часть пути, как это делается в web-программировании) к источнику данных нельзя.
Как же вообще сделать возможность обновления источника данных при смене расположения файлов?
Есть два варианта:
Не совсем автоматический, но способный чуть облегчить жизнь - использовать возможность создания параметров для запросов. Параметр - это некая именованная константа, которая может быть как одним значением(число, текст, дата и т.д.), так и целой таблицей(возможно впоследствии добавлять возможность создания вычисляемых параметров, но на момент написания статьи это не поддерживалось). В нашем случае это будет одно значение - путь к основной папке. Чтобы создать новый параметр необходимо перейти в редактор запросов(выбрать любой запрос в книге -Правая кнопка мыши -Изменить) и на вкладке Главная выбрать Управление параметрами -Создать параметр.
В появившемся окне задаем необходимые значения:
- Обязательно - читать как Обязательный, если флажок установлен и Необязательный, если флажок снят. Отвечает за необходимость указывать значение. Если флажок установлен - то параметр не будет создан/изменен, пока мы не укажем Текущее значение. Если флажок снят - то значение параметра допускается оставить пустым.
- Имя параметра - Без комментариев, я указал здесь sPath
- по желанию добавляем описание к параметру, ровным счетом это ни на что не влияет
- Тип данных параметра - в нашем случае это Текст. Здесь лучше не рисковать, указывая Любой(Any), т.к. в этом случае можем получить ошибку запроса.
- Предлагаемые значения - выбираем Любое значение(логичнее было бы назвать этот пункт "Одно значение". Т.к. это больше подходит на мой взгляд).
- Текущее значение - это как раз то значение, которое и будет хранить наш параметр. Здесь я указываю путь к основной папке:
G:\Готовая модель ПланФактного анализа\ . Указываю со слешем на конце пути, чтобы не добавлять его в каждый источник после.
Теперь останется для каждого запроса добавить ссылку на этот параметр вместо жестко прописанного пути. Для этого в расширенном редакторе запросов(Главная -Расширенный редактор) для каждого запроса необходимо изменить переменную часть пути к файлам на наш параметр sPath:
Обращаю внимание, что после имени параметра(sPath) идет амперсанд(&). Он необходим для объединения двух текстовых строк в одну.
А теперь
Самый очевидный недостаток: при переносе файлов в любом случае необходимо будет заходить в редактор запросов (Главная -Управление параметрами -Изменить параметры) и изменять путь, указанный в параметре на новый. Так же это можно сделать напрямую из редактора запросов, раскрыв в левой части область запросов, выбрав параметр и вписав новое значение:
- И
не очевидный недостаток: частенько такой подход приводит к ошибке получения данных при слиянии связанных запросов. Что делает этот способ не жизнеспособным в полной мере для большинства распространенных задач
Создать еще один запрос в основной книге, на основании формул в Excel. Решение основано на возможностях встроенной функции Excel ЯЧЕЙКА
Итак, для начала нам необходимо на новом листе создать новую таблицу с двумя столбцами: значение и описание. В качестве значения в первой строке у нас и будет как раз формула с функцией ЯЧЕЙКА:
данная формула возвращает только путь к файлу, в котором записана. Что нам в общем-то от неё и надо.
При желании можно дописать подсказку к значению. Я это сделал для демонстрации и чтобы было понятно - что мы сможем потом дополнять эту таблицу другими переменными значениями при необходимости.
Теперь из этой таблицы необходимо сделать динамическую, или как их еще называют - умную: выделяем ячейки с данными(
Далее выделяем любую ячейку внутри созданной таблицы и создаем новый запрос: вкладка Данные -Из таблицы(для пользователей PowerQuery 2013 и 2010 - вкладка PowerQuery -Из таблицы). Имя запроса у нас будет совпадать с именем таблицы - Parameters и этот запрос будет содержать как раз все значения нашей умной таблицы.
И теперь нам надо из этого запроса получить значение только одной конкретной ячейки - первой ячейки столбца "Значение"(в которой у нас формулой возвращается путь к папке). Для этого придется чуть пошаманить. Нам необходимо получить ссылку на таблицу "Parameters" и уже из неё получить значение нужной ячейки. Все это придется проделать на языке M, но звучит страшнее, чем выглядит - это всего одна строка:
Теперь разберем по шагам:
Т.е. строка
И эта строка возвращает исключительно путь к папке - именно тот, который у нас получается в результате вычисления формулы с ЯЧЕЙКА.
Теперь, после того как разобрались что делает чудо-строка - осталось понять как это применить. Надо просто для каждого запроса перейти в редактор и в строке с источником:
вместо части пути указать созданную строку запроса, добавив амперсанд(&) для объединения разных значений:
Все, теперь при перемещении книги с запросом или всей модели данные будут обновляться без какого-то ручного вмешательства.
В общем-то, такой подход можно использовать для задания даже имен файлов, используемых в модели. Например, можно дополнить таблицу именем файла с данными плана или путем к вспомогательным таблицам.
Готовая модель План-фактного анализа - относительный путь (491,0 KiB, 2 151 скачиваний)
Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылкиКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Отличная статья. Жалко, что PQ не дает инфо о текущем файле через тот же Excel.CurrentWorkbook()
Может быть, добавят какую-нибудь функцию в будущем... Excel.CurrentWorkbookPath()
Можно, кстати, не писать строку руками, а сделать так: подключиться к таблице параметров, далее правой мышью по ячейке с путем, "Детализация углублением". И дальше ссылаемся на этот запрос.
Да сам пытался найти что-то вроде пути через функции Excel.Workbook и иже с ними. Но нет...пока не внедрили. Благо хоть так можно :)
Максим, а можешь чуть подробнее про детализацию с углублением именно в данном случае? Что это такое я знаю, но не будут ли здесь те же грабли, что и с созданием отдельного параметра - ошибки получения данных, т.к. используются другие запросы внутри шагов?
Статья отличная, написано просто для новичков!Буквально недавно нашел второй вариант в ютубе, тоже столкнулся с такой проблемой. Гифки в статье для наглядности - крутяк!
Александр, спасибо
Дмитрий, добрый день!
Подскажите, пожалуйста, как добавить относительный путь в Power BI. Есть две таблицы "Данные" и "ТПуть"(в которой обновляется путь от текущего месяца) и соотвественно данныз загружаются за март, апрель и т.д. Для таблицы "Данные" указываю путь следующим образом "Источник = Folder.Files(ТПуть[Path])", пробовал разные варианты не удается получить путь из таблицы. Спасибо.
Дмитрий, добрый день! Подскажите как указать источник напрямую из ONE DRIVE
Спасибо!
Дмитрий!
Огромное Вам спасибо!
Спасибо большое, Дмитрий!