Параметризация путей к данным в 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 будет на каждой строке запрашивать значение параметра заново или же подобные моменты оптимизированы ? Если нет, то как быть в этой ситуации?
Подскажите, а можно сделать так, чтобы (Дата печати документа) из ячейки А4, разместилась в столбец по всем строчкам.
Чтобы при соединении нескольких файлов, можно было идентифицировать строчки.
Конечно, можно эту беду поправить и в таблице, но нельзя ли как-то удалить "ведущие" пробелы средствами PQ? Был бы признателен за ответ...
Кто-нибудь подскажет?
А если название файла постоянно меняется? ("Сделки от 12.12.2021" - меняется дата в названии).
Можно ли задать примерной название файла?
(запрос на файл сделок идёт с другого файла().
= Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь])
Если нужно чтобы путь менялся, в зависимости где лежит ваш созданный файл PQ прописываем:
=ЛЕВСИМВ(ЯЧЕЙКА("filename"); НАЙТИ("[";ЯЧЕЙКА("filename"))-1)
Добрый день, помогите пожалуйста.
Мне нужно подтянуть в PQ интернет страницу в текстовом виде,
Работает вариант
Источник = Web.Page(Web.Contents(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content]{0}[Ссылка])),
Но он подтягивает в HTML формате, мне это не подходит, мне нужно в открыть как текстовый файл
Подскажите, пожалуйста, как правильно прописать интернет путь, пробую так :
Источник = Table.FromColumns({Lines.FromBinary(Web.Contents(Excel.Workbook(File.Contents(Excel.CurrentWorkbook(){[Name="Таблица3"]}[Content]{0}[Ссылка]), null, true)))})
Вылезает такая ошибка при обновлении...
DataFormat.Error: Предоставленный путь к файлу должен быть допустимым абсолютным путем.
Сведения:
Заранее спасибо.
1. Пришлось вручную внести изменения путей к папкам с источниками данных в каждой умной таблице книги с запросом. Думаю что можно автоматизировать автоматическую выдачу новых путей с помощью формулы, предложенной выше от 25.01.2022 14:42:03, которую я попробую использовать в умных таблицах отдельных книг внутри каждой папки с данными, а затем буду ссылаться на них по предложенной строке в каждом запросе. Проблема, которая возникла выглядит так:
Источник = Excel.Workbook(File.Contents("D:\Users\***\***\Проекты\***\Внутренние\***\Путь - 1.0.xlsx"), null, true),Папка_Table = Источник{[Item="Папка",Kind="Table"]}[Data],#"Путь к папке" = Папка_Table{0}[Путь к папке]2.Даже после исправления всех путей к источникам данных, некоторые (связанные) запросы в книге перестают работать. Ошибка возникает на третьем шаге "Вызвать настраиваемую функцию"
let Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Путь"]}[Content]{0}[Путь к данным]), #"Отфильтрованные скрытые файлы1" = Table.SelectRows(Источник, each [Attributes]?[Hidden]? <> true), #"Вызвать настраиваемую функцию1" = Table.AddColumn(#"Отфильтрованные скрытые файлы1", "Преобразовать файл", each #"Преобразовать файл (5)"([Content])),Решение из статьи помогло наполовину, остальную часть гуглил или брал из тем данного сайта, а после собирал в одно единственное целое, чтобы оно работало правильно и без ошибок.
Задача:
Обновлять PQ запрос из разных папок на компьютере с указанием этой папки (кол-во конечных файлов может меняться), далее обновление сводных таблиц, но только после ожидания обновления всех PQ запросов.
Решение:
Повторяем описанное выше из статьи, только в параметрах пути прописываем так, как на скриншоте ниже:
Код:
Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="ПараметрыЗапроса"]}[Content]{0}[Путь к исходным данным]),А дальше нам необходимо создать запрос на изменение ссылки на папку, где лежат файлы для обработчика. Для этого применяем работу VBA
Создаем новый модуль в листе или в личной книге макросов, куда закидываем следующий код:
Функция:
Sub ПримерИспользования_GetFolderPath() Application.ScreenUpdating = False Application.EnableEvents = False ПутьКПапке = GetFolderPath("Заголовок окна", ThisWorkbook.Path) ' запрашиваем имя папки If ПутьКПапке = "" Then Exit Sub ' выход, если пользователь отказался от выбора папки 'MsgBox "Выбрана папка: " & ПутьКПапке, vbInformation Cells(2, 1).Value = ПутьКПапке For Each CON In ThisWorkbook.Connections CON.OLEDBConnection.BackgroundQuery = False 'Чтобы макрос дальше не выполнялся пока не обновится пивот. CON.Refresh Next Dim pCache As PivotCache For Each pCache In ActiveWorkbook.PivotCaches pCache.Refresh Next Application.ScreenUpdating = True Application.EnableEvents = True End SubВ случае, если существует несколько запросов (подключений) в книге и нужно обновлять конкретный, а не все подряд, можно использовать вместо этого кода: (дабы избежать ошибок обновления запросов)
ActiveWorkbook.Connections("Запрос — Название запроса").OLEDBConnection.BackgroundQuery = False ActiveWorkbook.Connections("Запрос — Название запроса").RefreshНайти название запроса можно так:
- Зайти на вкладку "Данные"
- Выбрать "Подключения"
- Двойной клик на запрос
- Информация по запросу и его свойствах
Создаем кнопку на любой странице, привязываем к кнопке макрос с названием "ПримерИспользования_GetFolderPath"Как оно работает:
Запускаем код, нам предлагают выбрать путь для загрузки данных (где лежат наши файлы, которые мы хотим собрать в PQ)
Далее макрос ждет обновление запроса из выбранной папки, после чего продолжает обновлять все сводные, которые подключены к таблице PQ в активной книге
Здесь сразу несколько решений вопросов и проблем, которые могут возникнуть параллельно этой статье.
Спасибо большое за информацию, и сайту за помощь. Решил поделиться готовым решением, вдруг кому поможет!
Почему-то 2016 проф версии при замене пути к папке выдаёт ошибку : [Expression.Error] Аргументы 2 были переданы функции, которая ожидает 1
При обновлении в версиях 2019 и 2021 такой проблемы нет.
Можете помочь с решением?
Очень хороший способ сослаться в запросе на файл. Только вот если в самом файле переименовать лист, с которого запросом информация тянется в файл-обработчик, то всё ломается...
У меня есть файл-источник, на который делаю запрос. Он выгружается из БД, при этом вместо стандартного "Лист1" записывает своё наименование листа. Постоянно приходится сохраняя файл, переименовывать лист в тот, который зафиксирован в запросе PQ.
Вопрос: как сделать, чтобы еще и жестко к листу, с которого тянутся данные, запрос не привязывался и выгружал всегда, какое-бы имя листа в xls-файле ни было написано?