Изменить источник в запросах Power Query
Автоматический поиск файла источника в папке с помощью Power Query.
При создании запроса к данным другой книги в Power Query путь к файлам статичен, и представляет собой полный путь C:\Users\Пользователь\Documents\отчёт май\продажи.xlsx
как видно из примера , запрос ссылается на папку «май» где лежит исходный файл «продажи май.xlsx»
в этой же папке находиться файл «отчёт» с запросом который собирает данные из файла продаж
Если переместить файл с запросом другую папку или переименовать исходную папку или файл, то путь к файлам сбивается и запрос не будет работать.
Чтобы не править руками каждый запрос, нужно добавить параметр, который может Power Query автоматически подключаться к файлу, из той же папке где они находятся.
1.Создаем таблицу, которая будет содержать обновляемый адрес папки, в которой находиться файл.
- добавить новый лист
- создать таблицу имя столбца «имя»
- преобразовать её в динамическую (умную таблицу);
- имя таблицы «путь»
В ячейку А2 пишем формулу которая будет показывать адрес папки в которой находиться файл
=ЛЕВСИМВ((ЯЧЕЙКА(«имяфайла»;A1));(ПОИСК(«[«;(ЯЧЕЙКА(«имяфайла»;A1))))-1)&»продажи»
разберем формулу:
| 1. Полный путь к текущей ячейки: C:\Users\Oksana\Documents\май\[отчёт май.xlsx]Лист6 |
|
=ЯЧЕЙКА(«имяфайла») |
|
2. Оставить только путь к папке, убрав всё, что после квадратной скобки [ : C:\Users\Oksana\Documents\май\ |
|
=ЛЕВСИМВ((ЯЧЕЙКА(«имяфайла»;A1));(ПОИСК(«[«;(ЯЧЕЙКА(«имяфайла»;A1))))-1) |
|
Добавить имя файла к которому будет подключаться запрос: C:\Users\Oksana\Documents\май\продажи.xlsx |
|
=ЛЕВСИМВ((ЯЧЕЙКА(«имяфайла»;A1));(ПОИСК(«[«;(ЯЧЕЙКА(«имяфайла»;A1))))-1)&»продажи.xlsx» |
2.Теперь мы можем обратиться к таблице «путь» и взять из неё информацию
создаем пустой запрос: Данные → Создать запрос →Из других источников → Пустой запрос
пишем в строке формул запрос к ячейке содержащей путь к файлу
= Excel.CurrentWorkbook(){[Name=»путь»]}[Content]{0}[имя]
3.Заменяем статический адрес на сделанный запрос
Готово!
Теперь куда бы не переместили файл с отчётом, он будет сам определять где он находиться и в этой же папке находить файл «продажи».
Таким образом, для отчёта в следующем месяце достаточно просто перенести файл, и нажать обновить.
