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