Power Query Базовый №21. Таблица параметров

[@comrade_excel]

Файлы для спонсоров Boosty: boosty.to/comrade-xl/posts/ef6bb2df-95ac-4dd3-b820-d89db6fae7aa?share=post_link Файлы для спонсоров ВК: vk.com/comrade.excel?w=wall-185123800_94 Файлы для спонсоров KZbin: kzbin.infoUgzD3ATyLKD0dm4myzt4AaABCQ Индивидуальное обучение: comrade-xl.ru/pq-training/ Страница урока на сайте: comrade-xl.ru/2020/06/05/power-query-parameter-table/

[@ВладТеляга-п7й]

Отличный и важный урок, спасибо!

[@РоманЛягушин]

Вот как это, оказывается, делают профессионалы. Когда мне нужна была такая "динамическая фильтрация", чтобы каждый день отфильтровывать из данных за всё время только сегодняшние, я создавал два столбца: в одном по формуле определяется сегодняшнее число, во втором делается разница между столбцом даты и столбцом с "сегодня", и фильтр по значениями, где результат 0.

[@Гаяна-л2з]

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

[@laravolhova6565]

Не могу понять, что не так. Пишет: "Formula.Firewall: Запрос "Лист1" (шаг "Источник") ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных. Измените эту комбинацию данных." Формула: = Excel.Workbook(File.Contents(Путь & "Адреса 1 квартал 2019-1.xlsx"), null, true) Данные в той же книге на соседнем листе

[@comrade_excel]

В редакторе Power Query нажмите Файл - Параметры и настройки - Параметры запроса - Конфиденциальность - Всегда игнорировать уровни конфиденциальности. Если не получилось, то посмотрите вводный урок kzbin.info/www/bejne/q33IgGqkj5WUrq8

[@laravolhova6565]

@@comrade_excel Получилось!!!))

[@svetlanap1590]

@@comrade_excel возникла та же проблема, мучилась часа 3 пока не прочитала этот комментарий) Спасибо!

[@romanvasiura6705]

@@comrade_excel , Шикарно - как раз то что было нужно)... Экспериментировал "пол дня"... Оказывается необходимо было изменить Конфиденциальность....

[@comrade_excel]

@@romanvasiura6705 В уроке из модуля "Ошибки" разобрано как и без конфиденциальности обойти эту ошибку.

[@alexpetrenko5952]

Спасибо очень полезные видео. Однако я заметил что вы всегда используете явный номер строки (Источник{1}[Значение]), на мой взгляд более универсальным решением было бы ссылатся на имя параметра (Источник{Параметр="Дата_С"}[Значение]). Тогда даже если вы добавите параметры в таблицу выше, ничего не сломается.

[@ШохбозбекАбдуфаттоев]

Здравствуйте Тимур! Спасибо Вам за уроки. Хотелосьбы узнать как быть если у меня фильтр со значениями > < =, большн выделенной даты или меньше выделенной даты или равно этому дате. не могу сообразить так как новичок. будьте добры как в параметрах фильтрации и в Power Query, решить эту задачу. Спасибо за любой ответ.

[@МариМ-я3д]

Тимур, спасибо Вам за такой интересный и полезный контент! Может, Вы сможете помочь? При решении одной из рабочих задач столкнулась с трудностями при написании относительного пути к файлу. В Вашем примере файлы-источники и файл, содержащий запросы, находятся в одной папке. Помимо этого, насколько я понимаю, Вы рассматриваете вариант, когда меняется только название папки, а названия самих файлов остаются неизменными. Как быть, если в моей рабочей задаче файл-источник находится в одной папке, а файл с запросом к нему - в другой? Также, помимо меня, многие люди имеют доступ к моим рабочим файлам, поэтому название и папки, где находится файл-источник, и самого файла с большой вероятностью может меняться. Как мне прописать относительный путь к файлу в этом случае?

[@comrade_excel]

Здравствуйте! В таком случае лучше всего пользоваться макросом указания пути. Вы кликаете на ячейку, из которой PQ будет брать информацию по пути. Далее жмете на кнопку, к которой привязан макрос. В появившемся окне выбираете папку и жмете ОК. Когда придет нужда изменить путь, то точно так же указываете путь к другой папке. Я планирую записать урок на эту тему. Раз есть такая нужда, то запишу его следующим. Выйдет тогда в течение недели. Если вам срочно, то могу в комментариях написать код макроса. Вы разберетесь как пользоваться кодом, если я размещу его здесь?

[@МариМ-я3д]

@@comrade_excel Огромное спасибо за пояснение! Могу подождать урока)

[@НиколайКовалёв-ъ6ц]

Добрый день! Интересует такой вопрос. Можно ли проверить существование пути к файлу перед загрузкой данных? И например если первый путь не существует, то подключится по второму пути. Может быть это можно реализовать с помощью M? Думаю это достаточно легко решить с помощью VBA, но такое решение не подходит.

[@comrade_excel]

Добрый день! Используйте try - otherwise. Это аналог функции ЕСЛИОШИБКА.

[@michaeldivin1101]

Добрый день, Тимур! Спасибо за вашу работу, все понятно и наглядно. Есть вопрос, подскажите, пожалуйста, по какой причине может выдавать ошибку "не найден путь", при повторении вашего примера?

[@comrade_excel]

Добрый день! В приложениях решенный файл и пустой. В моем решенном файле тоже эта ошибка?

[@michaeldivin1101]

@@comrade_excel Я ваш файл не проверял, я просто на своем сделал все действия по уроку и много раз перепроверил, почитал коменты к этому уроку, у людей были похожие, но предлагаемые действия результата не дали, хотя путь определяется правильно в ошибке. Excel 2016 если это важно. И спасибо за ответ!:)

[@michaeldivin1101]

@@comrade_excel Проверил, тоже ошибка

[@comrade_excel]

@@michaeldivin1101 У вас автоматический пересчет формул стоит или ручной? Там путь на ваш компьютер ведет? Файл,вы скачали, на который должен вести путь?

[@michaeldivin1101]

@@comrade_excel 1 да 2 Да 3 Да Но перепроверю. Единственное в моем варианте сам файл лежит на папку выше, и в формуле ячейка я добавляю путь к папке =ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла");1)-1)&"1С_Data_Reports\"

[@ДмитрийНикитин-и8о]

10:10 У меня такой вопрос: когда мы ссылаемся на ячейку, что и в каких случаях мы должны указывать перед фигурными скобками с номером строки? Я имею в виду, что в этом примере = Источник {0} [Значение]: 0 - это первая строка, Значение - имя столбца, а Источник - это имя умной таблицы, верно? А до этого в видео-уроках были конструкции с указанием названия шага в кавычках и с решёткой - #"........................."{..}[..............] , а в других примерах просто указывались названия столбцов без названия шага [..............] У меня путаница в голове)))) Помогите, пожалуйста, разобраться. Когда и как правильно ссылаться на ячейку?

[@comrade_excel]

Решётка и кавычки нужны, если на имени объекта есть пробелы. Если пробелов нет, то обращаться можно без решётки и кавычек. Кавычки обозначают границы имени. Без кавычек программа не поймет, где начало, а где конец имени объекта.

[@dmitriykotov3397]

Тимур, в файле с заданием к уроку запроса «Продажи» нет. Создал его самостоятельно, удалил пустые строки и т.д. Меняю путь в формуле, выпадает ошибка:Expression.Error: Не удается применить оператор & к типам DateTime и Text. Копирую вашу формулу из папки Готово, все тоже самое, ошибка не уходит. Подскажите, пожалуйста, где искать причину.

[@comrade_excel]

Нужно предварительно столбец с датой-временем преобразовать в текст.

[@platonpg]

Не могу найти урок где фильтруют по дате. Можно ссылку? Спасибо

[@comrade_excel]

Возможно, вам подойдет этот урок kzbin.info/www/bejne/kGPZhZurZrF2d8U

[@vladislavsologub6319]

Добрый день, большое спасибо за Ваши уроки. Не могли бы вы помочь с одним вопросом по данному видео. В конце при попытке выгрузить запрос продажи на лист выдаёт следующую ошибку: "[DataFormat.Error] Не удалось выполнить синтаксический анализ входных данных, предоставленных в качестве значения даты" На лист выгружаются только 8 строк с датой 01.10.2018 ...

[@comrade_excel]

Добрый день! Вы смотрели приложенный файл с уже готовым решением? Там все должно быть верно. Ошибка DataFormat.Error - это ошибка типов данных. В столбце находится тип данных, с которым вы пытаетесь обращаться как с другим типом данных.

[@vladislavsologub6319]

@@comrade_excel Не отфильтровал строку "Итого" - разобрался, спасибо)

[@michaeldivin1101]

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

[@comrade_excel]

Я не знаю, что вы имеете в виду.

[@michaeldivin1101]

@@comrade_excel Я имею ввиду - в сводной есть стандартные выпадающие фильтры в полях и значениях сток, а как такие возможно сделать в мерах. если понятно объяснил

[@comrade_excel]

@@michaeldivin1101 Нет, не понятно.

[@michaeldivin1101]

@@comrade_excel Когда форматируешь таблицу как умную, то в каждом столбце появляется выпадающий фильтр в заголовке. Такой же есть в сводной, но только в полях данных. Вопрос - можно ли сделать такие в столбцах сводной, где работает мера. Или дайте ресурс, если есть такая возможность, куда скинуть скрин

[@comrade_excel]

​@@michaeldivin1101 Это делается так же как и в обычной сводной таблице. Фильтр по значению. Он находится в фильтрах для строк.

[@СветланаДемидова-с6г]

Здравствуйте. Не получается заменить в Источнике путь на ссылку. Выходит ошибка: Formula.Firewall: Запрос "Customer" (шаг "Источник") ссылается на другие запросы или этапы и поэтому не может напрямую обращаться к источнику данных. Измените эту комбинацию данных. Почему ошибка? Спасибо.

[@comrade_excel]

Здравствуйте! Посмотрите это видео vk.com/video-185123800_456239025

[@begnameste7267]

Здравствуйте. Подскажите, пожалуйста, можно ли этим методом устанавливать относительный путь к Папке? В моем случае данные получаю из папки.

[@comrade_excel]

Здравствуйте! Можно.

[@begnameste7267]

@@comrade_excel У меня такая проблема: при получении данных из папки выбираю пример файла (сделал шаблон для сбора нужных столбоцов). И автоматически формируется "Преобразовать файл из..." В шаге Источник - меняю путь на относительный и все ок, а в следующем шаге Навигация - уже не получается. Подскажите, пожалуйста, как это все нрастроть правильно?

[@comrade_excel]

@@begnameste7267 Посмотрите все уроки по консолидации (объединение по вертикали). Здесь довольно много примеров разобрано.

[@comrade_excel]

@@begnameste7267 kzbin.info/www/bejne/jpjQdqt-ibBgq7M kzbin.info/www/bejne/bpq1oJdtftmegNU kzbin.info/www/bejne/npqUeYaIa62rr6s kzbin.info/www/bejne/sKLKd4GbpNqIjqs kzbin.info/www/bejne/Y57CiWWHrp10lbs kzbin.info/www/bejne/jHibYql9p6ysj6c kzbin.info/www/bejne/nn-np4WLhMadapo

[@begnameste7267]

@@comrade_excel Спасибо, в первом видео нашел ответ на свой вопрос.

[@АртурХан-о2в]

А разве не возможно в источнике данных выставить - текущая книга?

[@comrade_excel]

Можно.

[@msoffice6037]

@@comrade_excel А можете прописать в комменте как это будет выглядеть?

[@comrade_excel]

@@msoffice6037 Excel.CurrentWorkbook()

[@msoffice6037]

@@comrade_excel ну не до такой же степени:))

[@comrade_excel]

@@msoffice6037 тогда я не понимаю, что имеется в виду.

[@SensualSuare]

А если в папке не один файл (источников данных), а несколько и Power Query собирает данные со всех этих файлов, а сам файл, куда всё собирается находится не в этой папке, а выше на одну ступень? Я попробовал много вариантов, как например вот этот: Источник = Folder.Files(Путь\"Сметы"), Но у меня выдаётся ошибка(( Подозреваю, что xls файлы как-то тоже обозначать нужно, но т.к. названия у всех разные и количество может меняться, не пойму как их обозначить.

[@comrade_excel]

Чтобы соединять текст с именем нужно использовать знак амперасанд. В вашем примере должно быть так: Путь & "\Сметы" А лучше смотрите все уроки с самого начала. Вы изначально задали вопрос под модулем 6, когда не усвоили модуль 1. Рекомендую хотя бы модуль "Основы" пройти полностью с самого начала.

[@SensualSuare]

@@comrade_excel Да вот в том то и дело, что и так тоже пробовал и прямо сейчас ещё раз попробовал и всё равно ошибку выдаёт. Уже просто не знаю, что ему не хватает... Буду смотреть сначала Ваши видео))

[@SensualSuare]

@@comrade_excel Пишет, что развёрнутый элемент Custom ссылается на другие запросы. Как я понимаю, это запрос, когда я создавал функцию, как алгоритм работы со всеми файлами в папке (MainFunction = (filename)=>), скорее всего надо менять ещё его, а там всё вообще по другому и путь там вообще не прописывается... В общем надо сидеть разбираться.

[@comrade_excel]

Про ссылаемость на другие запросы смотрите урок из моду Ошибки. Урок называется Formula.Farewall

[@SensualSuare]

@@comrade_excel Спасибо!

[@СергейСергей-п7з]

Добрый день! Тимур, KZbin это здорово, но ... как можно получить пакетом Ваши уроки + файлы образцы? например через спонсорство Яндекс.Деньги: @t

[@comrade_excel]

Добрый день! Вы хотите скачать уроки себе на компьютер?

[@СергейСергей-п7з]

@@comrade_excel да с файлами

[@СергейСергей-п7з]

мне удобней когда на планшете идет видео о файлы на ноутбуке и я сними сразу делаю то чему Вы учите

[@comrade_excel]

@@СергейСергей-п7з Пока даже не знаю. Я их никуда не размещал, а трафика у меня столько нет, чтобы разом все разместить. Интернет ограничен 30гб в месяц. Может быть разве, что постепенно. Вы на каком сейчас уроке и с какой скоростью планируете учиться? Сколько уроков в неделю разбирать?

[@СергейСергей-п7з]

@@comrade_excel сейчас видео № 27 (просмотрел и то что не знал - записал в блокнот чтобы попробовать потом, но мне тк не нравиться - поэтому и пишу Вам) , я не с нуля начал PQ учить, был опыт, сейчас решил все вспомнить и прокачать себя в Эксель чтобы потому в Power BI хочу всю аналитику перевести.

[@laravolhova6565]

Не видно, что происходит на последних секундах - реклама следующих видео всё загораживает :(

[@comrade_excel]

Там происходит просто загрузка на лист.

[@avi5847]

Это видео очень нелогичное, больше времени потратил на то чтобы разобраться что за ошибки. Во первых долго не мог понять что не так с ошибкой Formula.Firewall, которую ниже описывали, т.к. файл О_продажи из приложенных не тот, который используете на видео. В файлах по ссылке в облаке есть запросы путь, с, по. Но нет продаж и справочника (соответственно запрос на справочник делал сам и думал что-то не то делаю, либо у вас там уже данные обработаны). И файл справочника здесь не используется, в общем только больше сбивает с толку). Лист продажи тоже не используется, сбивает с толку, т.к. запрос на 2018.10.txt тоже переименован в продажи, но это еще надо понять, т.к. файл не тот что на видео) p.s. eще вылетает ошибка [DataFormat.Error]Не удалось выполнить синтаксический анализ входных данных, предоставленных в качестве значения даты, когда закружаю на лист результат запроса продажи.На предпросмотре в редакторе все норм, но в таблицу запрос не выгружается ppps, может конечно 4 утра тоже както влияет)

[@МарияПоничева]

У меня такая же ошибка [DataFormat.Error]. Не разобралась, в чем причина