Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Относительный путь к данным PowerQuery

В большинстве случаев создание запросов PowerQuery создается на основании файлов Excel, отличных от того файла, в котором сам запрос. Как пример - одна из статей на этом сайте: План-фактный анализ в Excel при помощи Power Query. Там идет обращение к некоторым вспомогательным файлам с данными и запрос ко всем файлам определенной папки. Т.е. мы можем знать только папку относительно файла с самим запросом и имена некоторых вспомогательных таблиц. И здесь как раз возникает нюанс: если впоследствии переместить или переслать файл с запросом (или даже полностью всю модель данных со всеми файлами) - то придется для каждого запроса к отдельному файлу менять источник. Это можно сделать либо через Параметры источника данных самого запроса:

  • Для пользователей Excel 2010-2013:
    Перейти на вкладку Power Query -группа Настройки(Options) -Параметры источника данных(Data Source Settings)
  • для пользователей 2016 и выше:
    Перейти на вкладку Данные(Data) -Создать запрос(New Query) -Параметры источника данных(Data Source Settings)

либо изменив текст каждого запроса в редакторе запросов, изменив там строку источника:
Смена источника в редакторе
И в том и в другом случае после смены каждого источника придется ждать обновления запросов. Как ни странно, но стандартно, без танцев с бубном, сделать некий относительный путь(указав лишь часть пути, как это делается в web-программировании) к источнику данных нельзя.



Как же вообще сделать возможность обновления источника данных при смене расположения файлов?

Есть два варианта:
Вариант 1
Не совсем автоматический, но способный чуть облегчить жизнь - использовать возможность создания параметров для запросов. Параметр - это некая именованная константа, которая может быть как одним значением(число, текст, дата и т.д.), так и целой таблицей(возможно впоследствии добавлять возможность создания вычисляемых параметров, но на момент написания статьи это не поддерживалось). В нашем случае это будет одно значение - путь к основной папке. Чтобы создать новый параметр необходимо перейти в редактор запросов(выбрать любой запрос в книге -Правая кнопка мыши -Изменить) и на вкладке Главная выбрать Управление параметрами -Создать параметр.
Создание параметра
В появившемся окне задаем необходимые значения:

  • Обязательно - читать как Обязательный, если флажок установлен и Необязательный, если флажок снят. Отвечает за необходимость указывать значение. Если флажок установлен - то параметр не будет создан/изменен, пока мы не укажем Текущее значение. Если флажок снят - то значение параметра допускается оставить пустым.
  • Имя параметра - Без комментариев, я указал здесь sPath
  • по желанию добавляем описание к параметру, ровным счетом это ни на что не влияет
  • Тип данных параметра - в нашем случае это Текст. Здесь лучше не рисковать, указывая Любой(Any), т.к. в этом случае можем получить ошибку запроса.
  • Предлагаемые значения - выбираем Любое значение(логичнее было бы назвать этот пункт "Одно значение". Т.к. это больше подходит на мой взгляд).
  • Текущее значение - это как раз то значение, которое и будет хранить наш параметр. Здесь я указываю путь к основной папке: G:\Готовая модель ПланФактного анализа\. Указываю со слешем на конце пути, чтобы не добавлять его в каждый источник после.

Теперь останется для каждого запроса добавить ссылку на этот параметр вместо жестко прописанного пути. Для этого в расширенном редакторе запросов(Главная -Расширенный редактор) для каждого запроса необходимо изменить переменную часть пути к файлам на наш параметр sPath:
Путь через параметр
Обращаю внимание, что после имени параметра(sPath) идет амперсанд(&). Он необходим для объединения двух текстовых строк в одну.
А теперь пара ложек дегтя для этого способа:

  1. Самый очевидный недостаток: при переносе файлов в любом случае необходимо будет заходить в редактор запросов (Главная -Управление параметрами -Изменить параметры) и изменять путь, указанный в параметре на новый. Так же это можно сделать напрямую из редактора запросов, раскрыв в левой части область запросов, выбрав параметр и вписав новое значение:
    Изменить значение параметра
  2. И не очевидный недостаток: частенько такой подход приводит к ошибке получения данных при слиянии связанных запросов. Что делает этот способ не жизнеспособным в полной мере для большинства распространенных задач

Вариант 2
Создать еще один запрос в основной книге, на основании формул в Excel. Решение основано на возможностях встроенной функции Excel ЯЧЕЙКА(CELL). С её помощью можно получить полный путь к файлу, имя листа и книги. Чуть более подробно синтаксис этой функции и способы получения имени листа и книги я описывал в этой статье: Как получить имя листа формулой.
Итак, для начала нам необходимо на новом листе создать новую таблицу с двумя столбцами: значение и описание. В качестве значения в первой строке у нас и будет как раз формула с функцией ЯЧЕЙКА:
=ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)
данная формула возвращает только путь к файлу, в котором записана. Что нам в общем-то от неё и надо.

При желании можно дописать подсказку к значению. Я это сделал для демонстрации и чтобы было понятно - что мы сможем потом дополнять эту таблицу другими переменными значениями при необходимости.

Теперь из этой таблицы необходимо сделать динамическую, или как их еще называют - умную: выделяем ячейки с данными(A1:B2) -вкладка Вставка(Insert) и выбрать Таблица(Table). Галочку "Мои данные содержат заголовки" оставляем включенной, даем понятное имя таблице - Parameters:
Создание умной таблицы

Далее выделяем любую ячейку внутри созданной таблицы и создаем новый запрос: вкладка Данные -Из таблицы(для пользователей PowerQuery 2013 и 2010 - вкладка PowerQuery -Из таблицы). Имя запроса у нас будет совпадать с именем таблицы - Parameters и этот запрос будет содержать как раз все значения нашей умной таблицы.
И теперь нам надо из этого запроса получить значение только одной конкретной ячейки - первой ячейки столбца "Значение"(в которой у нас формулой возвращается путь к папке). Для этого придется чуть пошаманить. Нам необходимо получить ссылку на таблицу "Parameters" и уже из неё получить значение нужной ячейки. Все это придется проделать на языке M, но звучит страшнее, чем выглядит - это всего одна строка:
Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Значение]
Теперь разберем по шагам:

    Excel.CurrentWorkbook() - непосредственно функция, которая получает данные обо всех умных таблицах(и именованных диапазонах) внутри книги Excel, в которой создан этот запрос (CurrentWorkbook - текущая книга).
    {[Name="Parameters"]} - так мы даем понять функции Excel.CurrentWorkbook, что нам нужны данные исключительно из объекта с именем "Parameters"(это наша умная таблица). На всякий случай уточню: получить просто ссылки на ячейки листа не получится, т.к. функция Excel.CurrentWorkbook данных о листах не получает вообще. Только именованные диапазоны и умные таблицы.
    [Content] - все содержимое таблицы "Parameters"
    {0} - пожалуй, самая хитрая часть для "не программистов" :) Это номер строки в указанной таблице("Parameters"). При этом номера строк в запросе начинают отсчет с 0 и заголовки при этом не учитываются. Т.е. наш параметр находится физически во второй ячейке столбца "Значение" таблицы "Parameters" на листе. Но в рамках самой таблицы это первая её строка, т.к. заголовок не учитываем. Но т.к. в таблице отсчет начинается с 1, а в запросах с 0 - то и нам надо указывать, учитывая эту особенность. Если бы мы обращались ко второй строке таблицы - указать необходимо было бы 1. И да - указывать обязательно в фигурных скобках.
    [Значение] - здесь в квадратных скобках указывается имя столбца(без всяких кавычек). Если бы столбец в таблице был всего один - то можно было бы его не указывать вовсе. Но т.к. у нас их больше - то указание обязательно, иначе запрос вернет всю строку - т.е. значения всех столбцов таблицы.

Т.е. строка Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Значение] означает: из книги с запросом обратиться к таблице "Parameters"({[Name="Parameters"]}), считать все данные([Content]) и отобрать оттуда значение первой строки({0}) столбца "Значение"([Значение])
И эта строка возвращает исключительно путь к папке - именно тот, который у нас получается в результате вычисления формулы с ЯЧЕЙКА.
Теперь, после того как разобрались что делает чудо-строка - осталось понять как это применить. Надо просто для каждого запроса перейти в редактор и в строке с источником:
Источник = Folder.Files("C:\Готовая модель ПланФактного анализа\Факт"),
вместо части пути указать созданную строку запроса, добавив амперсанд(&) для объединения разных значений:
Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Parameters"]}[Content]{0}[Значение] & "\Факт"),
Изменить источник данных на динамический

Все, теперь при перемещении книги с запросом или всей модели данные будут обновляться без какого-то ручного вмешательства.
В общем-то, такой подход можно использовать для задания даже имен файлов, используемых в модели. Например, можно дополнить таблицу именем файла с данными плана или путем к вспомогательным таблицам.

Скачать готовую модель план-фактного анализа с динамически изменяющимся путем к данным:

  Готовая модель План-фактного анализа - относительный путь (491,0 KiB, 949 скачиваний)


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 7 комментариев
  1. Отличная статья. Жалко, что PQ не дает инфо о текущем файле через тот же Excel.CurrentWorkbook()
    Может быть, добавят какую-нибудь функцию в будущем... Excel.CurrentWorkbookPath()

    Можно, кстати, не писать строку руками, а сделать так: подключиться к таблице параметров, далее правой мышью по ячейке с путем, "Детализация углублением". И дальше ссылаемся на этот запрос.

    • Да сам пытался найти что-то вроде пути через функции Excel.Workbook и иже с ними. Но нет...пока не внедрили. Благо хоть так можно :)
      Максим, а можешь чуть подробнее про детализацию с углублением именно в данном случае? Что это такое я знаю, но не будут ли здесь те же грабли, что и с созданием отдельного параметра - ошибки получения данных, т.к. используются другие запросы внутри шагов?

  2. Дмитрий:

    Дмитрий, добрый день!
    Подскажите, пожалуйста, как добавить относительный путь в Power BI. Есть две таблицы "Данные" и "ТПуть"(в которой обновляется путь от текущего месяца) и соотвественно данныз загружаются за март, апрель и т.д. Для таблицы "Данные" указываю путь следующим образом "Источник = Folder.Files(ТПуть[Path])", пробовал разные варианты не удается получить путь из таблицы. Спасибо.

  3. Дмитрий:

    Дмитрий, добрый день! Подскажите как указать источник напрямую из ONE DRIVE
    Спасибо!

  4. Дмитрий:

    Дмитрий!
    Огромное Вам спасибо!

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти