Хитрости »

Основные понятия (26) Сводные таблицы и анализ данных (10) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (22)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (68) Разное (43) Баги и глюки Excel (4)

---

Относительный путь к данным 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, 2 989 скачиваний)

---

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

---

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика