Параметризация путей к данным в Power Query

Как задавать путь к данным в Power Query как переменную (параметр), чтобы ваш запрос работал и у других пользователей. Скачать пример www.planetaexcel.ru/technique...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/

[@user-on6kt1fo1t]

На протяжении 10 лет изучения Ексель, Вы просто пошагово, одну за одной решаете нашу проблему, я даже не знаю как можно выразить безмерную благодарность.! Супер.. и как всегда очень нужно, и в точку: проблема есть! Мучился сам, придумывал всякие костыли и даже не искал ответов, но видео само нашло меня в 1000ыйраз,!

[@tashas836]

Николай, огромное спасибо за Ваши видео, посмотрев которые, за полчаса можно решить проблему, отнимавшую кучу времени.

[@user-11_01]

Николай, спасибо за супер видеоролик!👍👍👍

[@Rustik702]

Благодарю, Николай! Каждое видео, как откровение!

[@AlexLi131]

Николай! Спасибище!!! Как раз ломал голову над решением, этой проблемы и тут такая Манна Небесная)!

[@user-ex8xm7mb2l]

Просто идеально, спасибо за то что вы делаете!!!! Супер.

[@dinylatk4134]

Николай, спасибо Вам большое за Ваш труд! Полезная информация!

[@krekssss1]

Как всегда отличное видео, урок!!! Как уже сказано жду с нетерпением книгу!!!!

[@tursiop777]

Николай, спасибо. Все в лучших традициях лучшего тренера :-) Осталось только добавить, что путь к текущему каталогу запуска файла можно получить при помощи встроенной функции ЯЧЕЙКА. А именно =ПСТР(ЯЧЕЙКА("filename";A1);1;ПОИСК("[";ЯЧЕЙКА("filename";A1))-1)

[@planetaexcel]

А это есть в статье, ссылка на которую есть под видео ;)

[@1iuh]

=ЯЧЕЙКА("содержимое";A1)

[@odmitv]

Просто офигенно! *РАБОТАЕТ!* Николай, спасибо Вам за уроки! *Побольше бы инфы про PQ на канале!*

[@mylovelytravel]

Спасибо Вам огромное за ваши уроки и разборы материала!!! Очень понятно и применимо в задачах!!!

[@User_1808]

Вот это подсказка!👍 У меня большие базы. Теперь с помощью формул мне разбивает их на необходимые. Большое спасибо.

[@odnbsp]

Отличный лайфхак, спасибо!

[@ikoikoporsche]

очень интересно! попробовала query в гугл-таблицах, заинтересовалась экселевской версией. оказывается, классная штука! попробую применять

[@user-id2fc7ed4t]

Николай, как всегда спасибо за доступное изложение. Очень жду Вашу книгу по Power надстройкам.

[@planetaexcel]

Ушла в издательство. Обещали через месяцок первый тираж :)

[@biweb13]

Как всегда, актуально!

[@arkharkh_k9370]

Отличное видео и качество изложения материала. Спасибо! P. S. Как раз вовремя)

[@IYAGODA]

Спасибо Вам большое, как всегда облегчили труд😊

[@yurd563]

Спасибо за видеобзор!

[@wugu42]

Николай, здравствуйте. Спасибо за статьи и ролики. Немало из них почерпнул для себя. Не могли бы вы рассказать про итеративные вычисления в Экселе? Как, для чего и почему. Многим будет интересно, а некоторым даже полезно.

[@Albertus99]

Николай огромное спасибо за Ваши бесценные уроки! Не критики ради а пользы для. Было бы гораздо лучше если бы уроки были предельно компактными с маленькими табличками и содержали только то что касается заявленной темы. Если речь о параметрах то никаких причесываний там уже не надо так как прически уже многократно обсасывались ранее в других роликах и нет смысла повторяться. Чем короче проще и понятнее тем выше ценность ролика! Еще раз огромное спасибо за Ваши бесценные для большого количества народа труды!

[@user-ss2uv3xu4l]

Спасибо огромное за это видео❤❤❤сделала, все работает🎉🎉🎉🎉🎉🎉🎉

[@user-qo1kp9mp4o]

Спасибо! Вы - крутой человек!

[@Albertus99]

Сделал все получилось огромное спасибо.

[@user-hy2no6qi3d]

Великолепно! Огромное спасибо!

[@freemanchronowski2349]

Спасибо огромное, спасаете в который раз

[@user-qn5by5iv7u]

Круто! Такое сложное название ролика, и такая простая суть 😄

[@user-py6mr6iz3g]

Это просто бомба!!! А ещё капец как вовремя Ютуб предложил мне посмотреть это видео) Спасибо!

[@user-pq8wu5xc8x]

Николай, спасибо, помогли. Правда на практике столкнулась с загводкой: если путь прописан из папки, нужно прописывать тот же путь в Расширенном редакторе Примера файла. - И, вуаля, все работает и у меня, и у мои коллег 🤗

[@writer8629]

Спасибо огромное!

[@user-vi6tf5ox1c]

Великолепно!

[@Albertus99]

Спасибо большое!

[@eflussion8550]

Можно шагнуть дальше и в ячейке прописать автоматическое обновление пути к данным на пк через функцию File, тогда не нужно будет править вручную и можно будет путь к примеру обновляющийся целой папке прописать

[@user-gc6gv7cu7g]

очень круто, спасбо!!

[@mrVladimirKa]

Годное. Всё нннада.

[@user-11_01]

Жду Вашу книгу про PQ)

[@Gamma9971]

откуда информация что он её про power quary пишет?

[@romasky100]

+1

[@user-cw6lp5jy8f]

А как быть, если запрос преобразован в функцию для сборки таблиц из разных файлов, и на месте жестко прописанного адреса уже стоит переменная?

[@user-vg8ou3gn5k]

Очень элегантно, можно быть не знакомым с функцией Excel.CurrentWorkbook получить её и не углубляясь в логику её работы получить относительный путь к файлу, папке. Николай я верно понимаю, что цель была такая? Спасибо за ролик, повторюсь конечно можно по разному рассказать но у Вас это вышло красиво.

[@user-ur5mn8xc2m]

Для копирования пути файла, включая имя самого файла, нужно в меню проводника нажать кнопку "Скопировать путь"

[@1raskumar]

Николай -Бро!

[@goshik73]

СПАСИБО!!! Это можно так и ссылку на веб-отчёт прописать?

[@Godescribe]

Худога шукр, четко бро

[@user-uz7ne3hy3x]

Николай, огромное спасибо за ваши уроки. Пишу проконсультироваться, где можно почитать про язык M?

[@Yevd1977]

Как узнаете - отпишитесь..

[@go_ogle_men8188]

Спасибо все прекрасно работает. Но теперь вопрос в другом )) на защищённый лист не загружается запрос, можно ли решить это??

[@user-rk8ve7sv8l]

Николай, спасибо за видео,как обычно все на высшем уровне,никакой безболезненной болтовни,у меня вопросик есть к вам, в будущем, в вашей надстройке "plex", данная функция будет реализована?

[@planetaexcel]

А зачем, если есть Power Query? :)

[@user-rk8ve7sv8l]

@@planetaexcel для минимизации действий

[@Rice0987]

9:32 Расширенный редактор на вкладке Главная. Вглядитесь. :)

[@user-qo9xw8rr6w]

Николай, не могли бы вы рассказать как можно имея один запрос к данным, например, выгрузить несколько умных таблиц, но в разных разрезах. Или иметь подробный запрос, а ссылаться в других запросах на его промежуточный или дополнительный результат.

[@TigranHayrapetyan-oy2ds]

Если Хотите автоматизировать выяснение пути файла (чтоб не вводить вручную), в строку с путем введите: =SUBSTITUTE(LEFT(CELL("filename"),SEARCH("]",CELL("filename"))-1),"[","") Спасибо Николаю!

[@nastuula]

Николай, как настроить расширенный поиск для телефонной базы данных при этом чтобы таблицы находились в разных файлах?

[@andreyvoronov8210]

Спасибо, все очень доходчиво. А если конечный путь не фаил (док. эксель), а папка с несколькими документами эксель. Как в таком случае прописать путь?

[@SB_77]

= Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь])

[@Lenokk-lr5de]

Уважаемый Николай, спасибо огромное! Выбрать путь к папке и/или файл получается. А если по этому способу сортировать вложенные папки (имя файла соответственно убираю рисую нужный путь по \) #"Строки с примененным фильтром2" = Table.SelectRows(Источник, each [Folder Path] = Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным] ) - наверное синтаксис хромает? Фильтр в Folder Path пуст?

[@taraskorniyenko132]

Николай, спасибо за ценный совет! Описанный метод прекрасно работал, пока я не передал файл человеку, у которого в Office 365 включена синхронизация с облаком. При этом Excel считает, что первичным местом хранения файлов является облако и, соответственно, пути прописывает https:/ и дальше и в таком случае описанный Вами способ параметризации перестает работать, если синхронизация включена и восстанавливается при отключении синхронизации. Подскажите, нет ли способа обойти это ограничение?

[@user-qh6dz5vy8f]

В ячейках, где осталось наименование товара, есть еще количество или вес. Можно ли с помощью функционала Power Query отсоединить другие данные, которые пойдут после наименования? Разделение по принакам не всегда подойдёт, тот пример, который нужен мне, имеет большое количество разрозненных данных в одной ячейке. Мне бы пригодилась "умная таблица", которая обновляет данные по новым версиям документа, но есть ли там такой инструмент, который бы отделял один тип данных от другого?

[@Midavok]

В свое время я именно из-за этой проблемы отказался от использования power query в пользу макросов.

[@xyzw777]

а там нельзя просто удалить абсолютный путь чтобы не морочиться с кодом?

[@ppr8117]

Николай, спасибо! Так ведь можно в качестве параметра, не копируя адрес файла, вставить формулу, которая сама будет определять своё местоположение на любом компьютере...

[@thghtfl]

Поясните, пожалуйста

[@Anton_Harchenko]

@@thghtfl Наверное он имеет ввиду формулу что то типа =ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("]";ЯЧЕЙКА("имяфайла");1)), но я заметил Николай вводил без прямоугольных скобок в конце, так что формула будет по сложнее, но не на много.

[@Anton_Harchenko]

Тогда придётся бросать и исходный файл и файл с данными в одну и ту же папку. И уже всю папку переносить с пк. на пк. Другое дело можно сделать две ячейки где будет формула и ячейка для самостоятельного ввода тогда если файлы находятся в одной папке то формула работает если нужно прописать самому путь то формула берёт данные с ячейки где введены данные. Но это уже мелкие доработки принцип понятен в видео)))

[@ppr8117]

@@Anton_Harchenko , скорее так... =ЛЕВСИМВ(ЯЧЕЙКА("имяфайла";A1);НАЙТИ("[";ЯЧЕЙКА("имяфайла";A1))-1)

[@Anton_Harchenko]

@@ppr8117 Да, но это только путь, а нужно и полное имя книги. Да и ячейку можно не уточнять будет. В любой будет работать.

[@user-ew5is5of5s]

А можно ли использовать диспетчер имён для параметризации пути в Power Query?

[@Yevd1977]

А ещё, думаю, можно макросом при открытии файла пусть перезаписать автоматом. И скрыть его с глаз пользователя 🙋

[@user-cb3qq2do3j]

Николай, вы не подскажите как создать переменный источник данных в запросах Power Query, допустим URL у которого меняются только страницы

[@alexgr2575]

Николай, спасибо за видео. Подскажите, пожалуйста, как можно обновить данные в запросе, реализованном по следующую схему: 1. Запрос PQ формируется на основании источника данных (файл1.xlsx) 2. Источник данных (файл1.xlsx) имеет ссылку на другой файл Excel (файл2.xlsx) 3. Со временем файл2.xlsx меняется, при этом файл1.xlsx не открывается и не обновляется. Возможно ли это в принципе?

[@planetaexcel]

Тут либо напрямую тянуть данные в PQ из файла2, либо использовать макросы для обновления.

[@Locomotiv23530]

Николай спасибо. Но есть один вопрос. Зачем вручную прописывать путь во второй кладке? Пусть путь сам прописывался с помощью Power Query

[@savannah633]

Доброго времени суток! *Подскажите мне, пожалуйста* . Есть макрос, написанный для определенной таблицы. Нужно, чтобы макрос, написанный для этой таблицы работал и и такой же таблице в ДРУГОМ документе excel. *Вопрос* : как это сделать? При условии, что шапка таблицы всегда одна и та же, *будут различаться только данные в таблице и название файла/листа с исходными данными* Я так полагаю, нужно изменить путь к файлу в теле макроса? Или можно ли вообще убрать привязку к определенному файлу?

[@planetaexcel]

Без файла тут ничего путного сказать не выйдет - нужно видеть код :)

[@kostyakostya655]

Николай, здравствуйте! Подскажите пожалуйста, как на языке "м" в строке функций power query ввести дату в формате 01.01.2021 чтобы она правильно отображалась? Если просто год, например 2021, тогда проблем нет, а вот с датой в формате 01.01.2021 работать формула не хочет.

[@planetaexcel]

=#date(2021,1,1)

[@user-nq4ip7qv9c]

Д.Д. А где можно скачать Power Query&

[@Fodintsov]

8:24 В колонке "Товар" каждое значение начинается с пробела., а то и двух. Дурацкое ограничение, конечно, что путь должен быть абсолютным. Так бы можно было прописать имя файла без пути и брал бы Эксел файл из той же папки. Null произносится как "нал".

[@user-iy1wl1cg2n]

Добрый день, Николай! А можно прописать в таблице с адресом динамически обновляемый, текущий адрес книги?

[@user-tg5ss4wb7i]

С функцией ЯЧЕЙКА не игрались?

[@user-iy1wl1cg2n]

@@user-tg5ss4wb7i Да, разобрался, спасибо.

[@user-qq8of2pe5n]

Николай добрый день! У меня вопрос... А можно параметиризировать путь не к файлу Excel, а к файлу базы данных Access?

[@planetaexcel]

Да, конечно! А какая разница? :)

[@1iuh]

14:00 "Две строки можно сократить в одну". Следовало выделить мышкой что можно удалить,и дать время зрителям посмотреть на экране, и удалить кнопкой, а не предыдущие символы.

[@1iuh]

12:25 В адресной строке видно до "Детализации запроса" докуда после удалить.

[@stasbz]

Подскажите как удалить выпадающие списке. Таблица закачана через интернет с помощью Power query.

[@hellyhansen914]

Добрый день. Я вам написал в личку в фб. Но ответа так и не получил. Мне нужна помощь с одним макросом. Как мне с вами связаться?

[@henryscoot6312]

Здравствуйте, подскажите по текстовику Исходник пример: ааааааа бббббббб ааааааа бббббббб Надо вот так: ааааааа:ббббббб ааааааа:ббббббб как и в какой программе так сделать? Спасибо!

[@user-sc5yk5wk3x]

Добрый день. Подскажите. Как в повер квери сохранить параметры запроса для любого файла. Тоесть при открытии таблицы она автоматом подстраивалась под заданные заранее параметсы. Возможно ли это и как сделать? Спасибо

[@planetaexcel]

Ну, как бы видео об этом и есть :) Нужно больше подробностей, чтобы ответить на ваш вопрос по конкретно вашей таблице.

[@user-sc5yk5wk3x]

@@planetaexcel Добрый день. Разобрался спасибо)) Только вот постоянно виснет и долго грузится во время работы в повер квери. Не могли бы вы подсказать как решить проблему

[@user-qo9xw8rr6w]

Добрый день. Николай, расскажите, пожалуйста, как правильно обновить запрос, если в исходную таблицу добавить или удалить столбец, или переименовать столбец. Меняю данные в расширенном редакторе, но можно ли проще?

[@planetaexcel]

Я бы тоже в расширенном редакторе делал - проще не получится.

[@user-qo9xw8rr6w]

@@planetaexcel спасибо за ответ. Но все же может сделаете видео? Думаю, что у многих подобные проблемы.

[@user-mg2mt7bb7r]

Добрый день Николай! Если мой путь ссылается на папку с файлами (2 файла в папке), нужно добавлять 2 имени файла? Если да, то как?

[@SB_77]

= Folder.Files(Excel.CurrentWorkbook(){[Name="Путь_к_файлу"]}[Content]{0}[Путь])

[@user-mg2mt7bb7r]

@@SB_77 Спасибо большое

[@SB_77]

@@user-mg2mt7bb7r сам искал очень, в итоге сообразил, решил со всеми поделиться!

[@user-mg2mt7bb7r]

@@SB_77 да круто, очень помог, удачи в делах!

[@LenoTaar]

Николай, а как сделать так, чтоб эксель сам определял путь по которому находится файл, который открываешь и прописывал его в ячейку, если это возможно?

[@planetaexcel]

Погуглите функцию ЯЧЕЙКА - она такое может с некоторыми танцами с бубном :)

[@LenoTaar]

@@planetaexcel спасибо, гляну на Вашем сайте:)

[@user-ns9wp5cc6h]

Добрый день, одну проблему никак не могу решить, почему-то после того как закрываю книгу power query теряется подключение к внешним данным

[@wako9681]

а если одновременно открыть оба файла? разве ссылки не перестраиваются?

[@rusoption]

мне нужна помошь. Есть динамическая ячейка с дельтой . мне нужно сделать диаграмму по этим значения. типо графика . ломаю голову ничего не могу найти

[@user-nw8hr5hm2z]

Это для office 2019 или для office 365 ?

[@ppr8117]

Это для любого office, 2010, 2013, 2016, 2019 и 365. Только для 2010 и 2013 эту надстройку надо отдельно (бесплатно) скачать с сайта Microsoft, которая установится отдельной вкладкой в Excel. В остальных версиях эта надстройка установлена по умолчанию на вкладке "Данные" (Data).

[@Encarexport]

Добрый день. Недавно начал изучать Excel по Вашим урокам, сложновато , но по чуть чуть получается. По данному уроку , у меня не получается. Уже 2 часа, выдает ошибки: Expression.Error: Ключу не соответствует ни одна строка в таблице. Сведения: Key=Record Table=Table Подскажите в чем дело. Заранее спасибо.

[@Encarexport]

ВСЕ СПАСИБО)))) Нашел ошибку))))) неверно указал путь))))))))))) УРА УРА УРА

[@talyblyruslan8628]

Николай, Добрый день. Попытался создать путь на своем компе к файлам с определенным названием в определенной папке, все норм. Однако, когда заменяю файл в папке (естественно переименовываю на название, заданное в Power Query) он выдает ошибку из-за формата Excel. Как можно устранить эту ошибку?

[@planetaexcel]

Надо видеть запрос и файл - без этого не скажешь :(

[@rifesta6652]

Правильно ли я понял, что человек, получивший файл эксель со связанным запросом должен: 1) иметь файл - оригинал источника данных, используемого в запросе на своем ПК. 2) разместить полученный файл в той же папке что и источник данных?

[@rifesta6652]

3) не изменять имя файла источника данных

[@GrizzlySerj]

Полученный файл не обязательно размещать в папке с файлами источников данных при ручном вводе адреса источника данных. Здесь в комментариях предложили использовать формулу для автоматического определения пути к файлу-обработчику. Если использовать это предложение, то ответ на вопрос положительный - нужно будет размещать файл-обработчик в папке с файлами-источниками данных.

[@zuzikgaliyev2035]

Добрый день! Как сделать параметризацию для папки?

[@SB_77]

= Folder.Files(Excel.CurrentWorkbook(){[Name="Путь_к_файлу"]}[Content]{0}[Путь])

[@user-qn5by5iv7u]

2022

[@tashliadyyev6560]

извините, можете помочь пожалуйста народ? что делать если даты не вертикально а горизонтально что делать ?

[@user-de5ok3ve4z]

Как сделать путь к папке

[@SB_77]

= Folder.Files(Excel.CurrentWorkbook(){[Name="Путь_к_файлу"]}[Content]{0}[Путь])

[@esh_chitay_lyubi]

Спасибо! Но это только для Эксель, в BI не работает((

[@planetaexcel]

Работает, но немного по другому, т.к. листов и ячеек в BI, как в Excel, нет.

[@esh_chitay_lyubi]

@@planetaexcel видео есть такое? Я искала не нашла, если не сложно оставьте, пожалуйста, ссылку в описании. Спасибо!

[@user-kp1ho8og6y]

А если макросом путь прописывать так вообще сказка!

[@kppptt]

Не уловил в чем прелесть. А если в источнике (самая первая строка запроса) щелкнуть шестерёнку и выбрать нужный файл? И будет любой нужный адрес и для меня лично быстрее. Или я что-то важное упускаю?

[@ppr8117]

Я так понимаю, видео рассчитано на конечного пользователя, поэтому никаких шестерёнок... Хотя, как вариант, почему бы и нет!

[@KenForest707]

Сделай пж урок как сделать в excel как в видео kzbin.info/www/bejne/inPXdHiJh52toc0

[@SensualSuare]

У меня выдаёт вот такую ошибку: DataSource.Error: Произошла ошибка ввода-вывода при попытке доступа к файлу "C:\Users\ Уже по всякому пытался, всё равно ошибка. Первая строка вот такая получается: Источник = Folder.Files(Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]),

[@Yevd1977]

Уберите фолдер, скобки и запятую..