Параметризация путей к данным в Power Query
HTML-код
- Опубликовано: 8 дек 2018
- Как задавать путь к данным в Power Query как переменную (параметр), чтобы ваш запрос работал и у других пользователей. Скачать пример www.planetaexcel.ru/technique...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
На протяжении 10 лет изучения Ексель, Вы просто пошагово, одну за одной решаете нашу проблему, я даже не знаю как можно выразить безмерную благодарность.! Супер.. и как всегда очень нужно, и в точку: проблема есть! Мучился сам, придумывал всякие костыли и даже не искал ответов, но видео само нашло меня в 1000ыйраз,!
Николай, огромное спасибо за Ваши видео, посмотрев которые, за полчаса можно решить проблему, отнимавшую кучу времени.
Благодарю, Николай! Каждое видео, как откровение!
Николай, спасибо за супер видеоролик!👍👍👍
Николай! Спасибище!!! Как раз ломал голову над решением, этой проблемы и тут такая Манна Небесная)!
Николай, спасибо Вам большое за Ваш труд! Полезная информация!
Просто офигенно! *РАБОТАЕТ!* Николай, спасибо Вам за уроки! *Побольше бы инфы про PQ на канале!*
Спасибо Вам огромное за ваши уроки и разборы материала!!! Очень понятно и применимо в задачах!!!
Николай, спасибо. Все в лучших традициях лучшего тренера :-)
Осталось только добавить, что путь к текущему каталогу запуска файла можно получить при помощи встроенной функции ЯЧЕЙКА.
А именно =ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)
А это есть в статье, ссылка на которую есть под видео ;)
=ЯЧЕЙКА("содержимое";A1)
Просто идеально, спасибо за то что вы делаете!!!! Супер.
Николай, как всегда спасибо за доступное изложение. Очень жду Вашу книгу по Power надстройкам.
Ушла в издательство. Обещали через месяцок первый тираж :)
Как всегда отличное видео, урок!!! Как уже сказано жду с нетерпением книгу!!!!
Отличный лайфхак, спасибо!
Вот это подсказка!👍 У меня большие базы. Теперь с помощью формул мне разбивает их на необходимые. Большое спасибо.
Николай огромное спасибо за Ваши бесценные уроки!
Не критики ради а пользы для.
Было бы гораздо лучше если бы уроки были предельно компактными с маленькими табличками и содержали только то что касается заявленной темы.
Если речь о параметрах то никаких причесываний там уже не надо так как прически уже многократно обсасывались ранее в других роликах и нет смысла повторяться.
Чем короче проще и понятнее тем выше ценность ролика!
Еще раз огромное спасибо за Ваши бесценные для большого количества народа труды!
Николай, здравствуйте.
Спасибо за статьи и ролики. Немало из них почерпнул для себя.
Не могли бы вы рассказать про итеративные вычисления в Экселе? Как, для чего и почему. Многим будет интересно, а некоторым даже полезно.
Как всегда, актуально!
Спасибо Вам большое, как всегда облегчили труд😊
Спасибо за видеобзор!
очень интересно! попробовала query в гугл-таблицах, заинтересовалась экселевской версией. оказывается, классная штука! попробую применять
Спасибо!
Вы - крутой человек!
Спасибо огромное за это видео❤❤❤сделала, все работает🎉🎉🎉🎉🎉🎉🎉
Отличное видео и качество изложения материала. Спасибо!
P. S. Как раз вовремя)
Великолепно! Огромное спасибо!
Сделал все получилось огромное спасибо.
Круто!
Такое сложное название ролика, и такая простая суть 😄
Спасибо огромное, спасаете в который раз
Спасибо большое!
Это просто бомба!!! А ещё капец как вовремя Ютуб предложил мне посмотреть это видео) Спасибо!
Спасибо огромное!
Николай, спасибо, помогли.
Правда на практике столкнулась с загводкой: если путь прописан из папки, нужно прописывать тот же путь в Расширенном редакторе Примера файла. - И, вуаля, все работает и у меня, и у мои коллег 🤗
Великолепно!
Жду Вашу книгу про PQ)
откуда информация что он её про power quary пишет?
+1
очень круто, спасбо!!
А как быть, если запрос преобразован в функцию для сборки таблиц из разных файлов, и на месте жестко прописанного адреса уже стоит переменная?
Годное. Всё нннада.
Можно шагнуть дальше и в ячейке прописать автоматическое обновление пути к данным на пк через функцию File, тогда не нужно будет править вручную и можно будет путь к примеру обновляющийся целой папке прописать
Очень элегантно, можно быть не знакомым с функцией Excel.CurrentWorkbook получить её и не углубляясь в логику её работы получить относительный путь к файлу, папке.
Николай я верно понимаю, что цель была такая?
Спасибо за ролик, повторюсь конечно можно по разному рассказать но у Вас это вышло красиво.
СПАСИБО!!!
Это можно так и ссылку на веб-отчёт прописать?
Николай, огромное спасибо за ваши уроки.
Пишу проконсультироваться, где можно почитать про язык M?
Как узнаете - отпишитесь..
Худога шукр, четко бро
Николай -Бро!
Николай, как настроить расширенный поиск для телефонной базы данных при этом чтобы таблицы находились в разных файлах?
Спасибо все прекрасно работает. Но теперь вопрос в другом )) на защищённый лист не загружается запрос, можно ли решить это??
Уважаемый Николай, спасибо огромное! Выбрать путь к папке и/или файл получается. А если по этому способу сортировать вложенные папки (имя файла соответственно убираю рисую нужный путь по \) #"Строки с примененным фильтром2" = Table.SelectRows(Источник, each [Folder Path] = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным] ) - наверное синтаксис хромает? Фильтр в Folder Path пуст?
Если Хотите автоматизировать выяснение пути файла (чтоб не вводить вручную), в строку с путем введите:
=SUBSTITUTE(LEFT(CELL("filename"),SEARCH("]",CELL("filename"))-1),"[","")
Спасибо Николаю!
Николай, не могли бы вы рассказать как можно имея один запрос к данным, например, выгрузить несколько умных таблиц, но в разных разрезах. Или иметь подробный запрос, а ссылаться в других запросах на его промежуточный или дополнительный результат.
Николай, спасибо за ценный совет! Описанный метод прекрасно работал, пока я не передал файл человеку, у которого в Office 365 включена синхронизация с облаком. При этом Excel считает, что первичным местом хранения файлов является облако и, соответственно, пути прописывает https:/ и дальше и в таком случае описанный Вами способ параметризации перестает работать, если синхронизация включена и восстанавливается при отключении синхронизации. Подскажите, нет ли способа обойти это ограничение?
Спасибо, все очень доходчиво. А если конечный путь не фаил (док. эксель), а папка с несколькими документами эксель. Как в таком случае прописать путь?
= Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь])
Николай, спасибо за видео,как обычно все на высшем уровне,никакой безболезненной болтовни,у меня вопросик есть к вам, в будущем, в вашей надстройке "plex", данная функция будет реализована?
А зачем, если есть Power Query? :)
@@planetaexcel для минимизации действий
В ячейках, где осталось наименование товара, есть еще количество или вес. Можно ли с помощью функционала Power Query отсоединить другие данные, которые пойдут после наименования? Разделение по принакам не всегда подойдёт, тот пример, который нужен мне, имеет большое количество разрозненных данных в одной ячейке.
Мне бы пригодилась "умная таблица", которая обновляет данные по новым версиям документа, но есть ли там такой инструмент, который бы отделял один тип данных от другого?
А можно ли использовать диспетчер имён для параметризации пути в Power Query?
Николай спасибо. Но есть один вопрос. Зачем вручную прописывать путь во второй кладке? Пусть путь сам прописывался с помощью Power Query
Николай, вы не подскажите как создать переменный источник данных в запросах Power Query, допустим URL у которого меняются только страницы
Для копирования пути файла, включая имя самого файла, нужно в меню проводника нажать кнопку "Скопировать путь"
Николай, спасибо за видео.
Подскажите, пожалуйста, как можно обновить данные в запросе, реализованном по следующую схему:
1. Запрос PQ формируется на основании источника данных (файл1.xlsx)
2. Источник данных (файл1.xlsx) имеет ссылку на другой файл Excel (файл2.xlsx)
3. Со временем файл2.xlsx меняется, при этом файл1.xlsx не открывается и не обновляется.
Возможно ли это в принципе?
Тут либо напрямую тянуть данные в PQ из файла2, либо использовать макросы для обновления.
Подскажите как удалить выпадающие списке.
Таблица закачана через интернет с помощью Power query.
Д.Д. А где можно скачать Power Query&
Добрый день. Я вам написал в личку в фб. Но ответа так и не получил.
Мне нужна помощь с одним макросом. Как мне с вами связаться?
Николай, здравствуйте! Подскажите пожалуйста, как на языке "м" в строке функций power query ввести дату в формате 01.01.2021 чтобы она правильно отображалась? Если просто год, например 2021, тогда проблем нет, а вот с датой в формате 01.01.2021 работать формула не хочет.
=#date(2021,1,1)
Доброго времени суток! *Подскажите мне, пожалуйста* .
Есть макрос, написанный для определенной таблицы. Нужно, чтобы макрос, написанный для этой таблицы работал и и такой же таблице в ДРУГОМ документе excel. *Вопрос* : как это сделать?
При условии, что шапка таблицы всегда одна и та же, *будут различаться только данные в таблице и название файла/листа с исходными данными*
Я так полагаю, нужно изменить путь к файлу в теле макроса? Или можно ли вообще убрать привязку к определенному файлу?
Без файла тут ничего путного сказать не выйдет - нужно видеть код :)
Здравствуйте, подскажите по текстовику
Исходник пример:
ааааааа
бббббббб
ааааааа
бббббббб
Надо вот так:
ааааааа:ббббббб
ааааааа:ббббббб
как и в какой программе так сделать? Спасибо!
Добрый день, одну проблему никак не могу решить, почему-то после того как закрываю книгу power query теряется подключение к внешним данным
Николай, спасибо!
Так ведь можно в качестве параметра, не копируя адрес файла, вставить формулу, которая сама будет определять своё местоположение на любом компьютере...
Поясните, пожалуйста
@@thghtfl Наверное он имеет ввиду формулу что то типа =ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("]";ЯЧЕЙКА("имяфайла");1)), но я заметил Николай вводил без прямоугольных скобок в конце, так что формула будет по сложнее, но не на много.
Тогда придётся бросать и исходный файл и файл с данными в одну и ту же папку. И уже всю папку переносить с пк. на пк. Другое дело можно сделать две ячейки где будет формула и ячейка для самостоятельного ввода тогда если файлы находятся в одной папке то формула работает если нужно прописать самому путь то формула берёт данные с ячейки где введены данные. Но это уже мелкие доработки принцип понятен в видео)))
@@Anton_Harchenko , скорее так... =ЛЕВСИМВ(ЯЧЕЙКА("имяфайла";A1);НАЙТИ("[";ЯЧЕЙКА("имяфайла";A1))-1)
@@ppr8117 Да, но это только путь, а нужно и полное имя книги. Да и ячейку можно не уточнять будет. В любой будет работать.
Николай добрый день! У меня вопрос... А можно параметиризировать путь не к файлу Excel, а к файлу базы данных Access?
Да, конечно! А какая разница? :)
Добрый день, Николай! А можно прописать в таблице с адресом динамически обновляемый, текущий адрес книги?
С функцией ЯЧЕЙКА не игрались?
@@user-tg5ss4wb7i Да, разобрался, спасибо.
9:32 Расширенный редактор на вкладке Главная. Вглядитесь. :)
Спасибо! Но это только для Эксель, в BI не работает((
а если одновременно открыть оба файла?
разве ссылки не перестраиваются?
мне нужна помошь. Есть динамическая ячейка с дельтой . мне нужно сделать диаграмму по этим значения. типо графика . ломаю голову ничего не могу найти
Добрый день. Подскажите. Как в повер квери сохранить параметры запроса для любого файла. Тоесть при открытии таблицы она автоматом подстраивалась под заданные заранее параметсы. Возможно ли это и как сделать? Спасибо
Ну, как бы видео об этом и есть :)
Нужно больше подробностей, чтобы ответить на ваш вопрос по конкретно вашей таблице.
@@planetaexcel Добрый день. Разобрался спасибо)) Только вот постоянно виснет и долго грузится во время работы в повер квери. Не могли бы вы подсказать как решить проблему
Добрый день. Николай, расскажите, пожалуйста, как правильно обновить запрос, если в исходную таблицу добавить или удалить столбец, или переименовать столбец. Меняю данные в расширенном редакторе, но можно ли проще?
Я бы тоже в расширенном редакторе делал - проще не получится.
@@planetaexcel спасибо за ответ. Но все же может сделаете видео? Думаю, что у многих подобные проблемы.
Николай, а как сделать так, чтоб эксель сам определял путь по которому находится файл, который открываешь и прописывал его в ячейку, если это возможно?
Погуглите функцию ЯЧЕЙКА - она такое может с некоторыми танцами с бубном :)
@@planetaexcel спасибо, гляну на Вашем сайте:)
Добрый день. Недавно начал изучать Excel по Вашим урокам, сложновато , но по чуть чуть получается. По данному уроку , у меня не получается. Уже 2 часа, выдает ошибки: Expression.Error: Ключу не соответствует ни одна строка в таблице.
Сведения:
Key=Record
Table=Table
Подскажите в чем дело. Заранее спасибо.
ВСЕ СПАСИБО)))) Нашел ошибку))))) неверно указал путь))))))))))) УРА УРА УРА
Добрый день Николай!
Если мой путь ссылается на папку с файлами (2 файла в папке), нужно добавлять 2 имени файла? Если да, то как?
= Folder.Files(Excel.CurrentWorkbook(){[Name="Путь_к_файлу"]}[Content]{0}[Путь])
@@SB_77 Спасибо большое
@@user-mg2mt7bb7r сам искал очень, в итоге сообразил, решил со всеми поделиться!
@@SB_77 да круто, очень помог, удачи в делах!
Николай, Добрый день. Попытался создать путь на своем компе к файлам с определенным названием в определенной папке, все норм. Однако, когда заменяю файл в папке (естественно переименовываю на название, заданное в Power Query) он выдает ошибку из-за формата Excel. Как можно устранить эту ошибку?
Надо видеть запрос и файл - без этого не скажешь :(
В свое время я именно из-за этой проблемы отказался от использования power query в пользу макросов.
а там нельзя просто удалить абсолютный путь чтобы не морочиться с кодом?
Это для office 2019 или для office 365 ?
Это для любого office, 2010, 2013, 2016, 2019 и 365. Только для 2010 и 2013 эту надстройку надо отдельно (бесплатно) скачать с сайта Microsoft, которая установится отдельной вкладкой в Excel. В остальных версиях эта надстройка установлена по умолчанию на вкладке "Данные" (Data).
А ещё, думаю, можно макросом при открытии файла пусть перезаписать автоматом. И скрыть его с глаз пользователя 🙋
Правильно ли я понял, что человек, получивший файл эксель со связанным запросом должен: 1) иметь файл - оригинал источника данных, используемого в запросе на своем ПК. 2) разместить полученный файл в той же папке что и источник данных?
3) не изменять имя файла источника данных
Полученный файл не обязательно размещать в папке с файлами источников данных при ручном вводе адреса источника данных.
Здесь в комментариях предложили использовать формулу для автоматического определения пути к файлу-обработчику. Если использовать это предложение, то ответ на вопрос положительный - нужно будет размещать файл-обработчик в папке с файлами-источниками данных.
Добрый день! Как сделать параметризацию для папки?
= Folder.Files(Excel.CurrentWorkbook(){[Name="Путь_к_файлу"]}[Content]{0}[Путь])
8:24 В колонке "Товар" каждое значение начинается с пробела., а то и двух.
Дурацкое ограничение, конечно, что путь должен быть абсолютным. Так бы можно было прописать имя файла без пути и брал бы Эксел файл из той же папки.
Null произносится как "нал".
извините, можете помочь пожалуйста народ? что делать если даты не вертикально а горизонтально что делать ?
2022
14:00 "Две строки можно сократить в одну". Следовало выделить мышкой что можно удалить,и дать время зрителям посмотреть на экране, и удалить кнопкой, а не предыдущие символы.
12:25 В адресной строке видно до "Детализации запроса" докуда после удалить.
Как сделать путь к папке
= Folder.Files(Excel.CurrentWorkbook(){[Name="Путь_к_файлу"]}[Content]{0}[Путь])
А если макросом путь прописывать так вообще сказка!
Не уловил в чем прелесть. А если в источнике (самая первая строка запроса) щелкнуть шестерёнку и выбрать нужный файл? И будет любой нужный адрес и для меня лично быстрее. Или я что-то важное упускаю?
Я так понимаю, видео рассчитано на конечного пользователя, поэтому никаких шестерёнок... Хотя, как вариант, почему бы и нет!
У меня выдаёт вот такую ошибку: DataSource.Error: Произошла ошибка ввода-вывода при попытке доступа к файлу "C:\Users\ Уже по всякому пытался, всё равно ошибка. Первая строка вот такая получается: Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]),
Уберите фолдер, скобки и запятую..
Сделай пж урок как сделать в excel как в видео ruclips.net/video/TBtCESR8zkk/видео.html