Массовая замена текста в Power Query функцией List.Accumulate

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

[@valarg5756]

Как всегда очень круто и практично, хотелось бы видео чаще. Один из лучших каналов на Ютубе.

[@planetaexcel]

Спасибо, будем стараться :)

[@27EMERGENCY]

Да, как раз именно тот случай, когда объяснить сложнее, чем сделать. Вот посмотрел видео и двоякое ощущение, вроде всё просто и понятно, но если выключить видео и самому сесть сделать, не факт, что с первого раза что-то получится. Надо самому руками попробовать несколько раз, сначала поглядывая в видео, а потом несколько раз без подсказок. Как всегда, отличное видео. Спасибо.

[@im_buddha]

Просто Microsoft давно не является лидером в средствах обработки данных. Скажем, тому, кто изучал и использует соверменный pandas, понять этот Accumulate непросто. у разрабов PQuery своя логика. Но Pandas - мировой тренд, а что такое P Query думаю знают единицы Вообще у Николая получилась отличная антиреклама Power Query :)

[@statanaliz]

Как всегда, отличный и полезный урок. Спасибо!

[@planetaexcel]

Спасибо, Дмитрий! Не уверен, что большинство начинающих пользователей проникнется :) , но если хотя бы некоторым зайдет, то я не зря старался :)

[@statanaliz]

@@planetaexcel Точно не зря. Тут даже дело не в том, чтобы применить конкретно этот прием, а в том, чтобы расширить кругозор. Ведь есть похожие задачи и их будет проще решить, зная, в каком направлении нужно копать. )

[@hhhohlick]

Одно из лучших объяснений работы аккумулятора хоть по-русски, хоть по-английски. Спасибо, Николай, теперь есть куда отправить посмотреть (самому же лень объяснять 😁 )

[@user-rc3ye7gm3l]

Ну если Дмитрий Езепов (я Ваш подписчик тоже) комментирует это видео, то Николаю однозначно Оскар!

[@user-11_01]

@@user-rc3ye7gm3l 100% согласно👍👍👍🔥

[@sevakworld]

Гениально! Хочется пожать Вам руку и сказать спасибо за Ваш труд!

[@ivanmamchych5802]

Замечательно! Большое Спасибо, Николай

[@user-mc8tn9cq4e]

Nikolay Pavlov - волшебник, дарящий людям время!!!

[@securityghm6490]

Николай, Вы большой молодец, так как делитесь своими знаниями абсолютно бесплатно. Рекомендовал - рекомендую и буду рекомендовать Вас своим сотрудникам!!!! Респект!!!

[@planetaexcel]

Спасибо!

[@Ukks1886]

Как же я люблю Ваши видео!!! Очень своевременно.

[@niknik4069]

Как всегда круто! Суперский ликбез сложных для обывателя в PQ вещей

[@auto8008]

Николай, как всегда все Суперинформативно!!!

[@senge_v4871]

И как обычно все шикарно и изящно. Спасибо большое за обучение. Успехов!

[@winterfirework]

Николай, спасибо большое!!! Очень полезное видео!!

[@user-xx1wu3xh9n]

Какая хорошая подача ! всё понятно с первого раза .

[@Albertus99]

Огромное спасибо и низкий поклон за труды ваши праведные!

[@user-gc5nm6tv9k]

Николай, спасибо! в очередной раз меня спасаете

[@olegn.4237]

Спасибо Николай за полезную информацию и понятное объяснение!

[@nasharussian4003]

Маг и волшебник!!! Респект Николай!!!

[@user-kg4cd2kx5h]

Все самое актуальное нахожу на данном канале. Автору огромное спасибо!

[@vs_gaming3013]

Лайк не глядя. Спасибо вам огромное!

[@maxpower6900]

Вы как всегда на высоте!

[@dobrinyanicitich7514]

В очередной раз спасибо, за хорошее объяснение!

[@yurd563]

Большое спасибо за видеоурок!

[@dmitrizhdanov6536]

Николай, спасибо!

[@di_moligan]

Шикарно, Николай ❤️

[@Techman52]

Николай спасибо. Очень круто.

[@user-kj1kr7qz5h]

Гениально, как всегда!

[@alt84970197]

Николай здравствуйте, спасибо большое Вам

[@elenamikhay2914]

только когда по-настоящему знаешь, тогда просто и можно с легкостью донести до других. Низкий поклон за этот труд. Смотрю разные источники по Эксель. Но если у вас ответа не нахожу, то задача не имеет решения.

[@planetaexcel]

Спасибо, Елена! Но мне еще учиться и учиться. Так что задача может быть и вполне решаемой ;)

[@isok.atyrau]

Как раз то что нужно. Одназначно лайк)))

[@inesatananyan1191]

bravo...ochen xorosho obyasnyaete...100%

[@Viazm]

Фантастика. Спасибо!

[@aleksei_k3233]

Спасибо. Очень полезно

[@user-11_01]

💥 спасибо!

[@ValeriMetov]

Очередное заслуженное браво! Хотелось бы попросить как нибудь разобрать примеры работы с конкретными ячейками в PQ или взаимодействия со строками выше или ниже

[@user-ly9ji1uu6e]

Супер! Спасибо!

[@zombie-fido]

Как всегда браво

[@Komsgl]

спасибо, вот просто СПАСИБО (уже троих подсодил на PLEX )

[@alexhommel6341]

Лихо, я до такого не додумался, попробую завтра.

[@nbuhblhfrjy]

Важный нюанс! Таблица-справочник должна быть отсортирована по убыванию по параметру длина строки ) Иначе замена будет некорректной

[@TigranHayrapetyan-oy2ds]

Сам сделал почти такую же задачу в Microsoft access. И сделал макрос, который сортирует все справочники до замены текста. Хорошо, что вы об этом написали)

[@irinazaitseva1024]

Спасибо! Миллион раз!)

[@DDeniskin]

Гениально!!! Чётко и понятно. Моё почтение!!! Вы меня спасли!!! 12.000 строк за 1.5 секунды поменялись, как по маслу.

[@NotEmpty2011]

Николай, все как всегда супер! Сделайте пжлст видео про: 1. Общий синтаксис Power Query - там же есть универсальные моменты. Например, *все* формулы состоят из: название формулы, круглых скобок, аргументов, аргументы разделяются запятой. *Все* ссылки на имена запросов, таблицы, списки, записи состоят из ... Вообщем, когда понятна азбука, то можно самостоятельно создавать слоги, предложения, сочинения. 2. Функцию List.Generate

[@jollyjuln1054]

Горячо плюсую, особенно про list.generate. И, конечно, от души благодарю за все ролики, за сайт и книги!

[@nadezdavol8471]

Спасибо большое :))

[@dmitrievsergey]

Это очень круто 😎

[@user-iq3db6re4h]

Супер)

[@user-fr8md5fb9e]

Огромное спасибо за функцию буферизации ))) List.Buffer забрал себе на вооружение!

[@planetaexcel]

Еще есть Table.Buffer, кстати ;)

[@vitaliysavarets390]

@@planetaexcel Ни "Table.Buffer", ни "List.Buffer" не подошли для "умной таблицы", которую в список не преобразовывал.

[@user-ku2qh3rv8f]

Как всегда 🔥👍

[@antonkharitonov9007]

Посмотрю позже, поставлю лайк сейчас!

[@Advanced_vision]

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

[@Tatyana_my_name]

Спасибо!

[@Gamma9971]

Спасибо

[@biweb13]

Круть!

[@user-mn9vz2uj4m]

Amazing

[@kuatle]

Делал данную функцию через объединение запросов и через логический столбец. Указанный в видео способ очевидно быстрее, но гораздо более трудоемкий.

[@I.r.i.n.a_]

Николай, добрый день! Если возможно, расскажите, пожалуйста, подробнее о кэшировании данных в Power BI desktop. Как это влияет на скорость обновления. И как, управляя кэшированием, увеличить скорость обновления. (да и вообще обо всем, что может помочь увеличить скорость работы). Благодарю заранее ,т.к. надеюсь, что Вы поможете)))

[@iwonder5132]

Николай, спасибо за простое объяснение сложного вопроса, у вас это получается лучше всех. Такой вопрос: вы планируете написать книгу по Power Query для продвинутых пользователей, с глубоким рассмотрением языка M? Ваша замечательная книга "Скульптор данных" у меня уже есть и я заканчиваю ее изучение, но хотелось бы еще более глубокое рассмотрение этой темы. Так же интерисуют ваши планы по DAX. Планируете ли вы выпустить книгу по этой теме?

[@planetaexcel]

По DAX уже пишу потихоньку, а вот насчет М даже не знаю. Возможно, когда-нибудь тоже возьмусь :)

[@TheNinesku]

@@planetaexcel Николай, это очень хорошо! Когда ориентировочно старт продаж? Буду первым!

[@Gennadii_Aleksandrovich]

Спасибо. Очень интересно и информативно. Очень ждал продолжение этой темы. А есть возможность чтоб справочник был примерный? При оцифровки документов пдф очень много ошибок, и даже плекс не помогает, много лопачу.

[@user-nw8hr5hm2z]

Скажите, а планируете ли Вы (и когда) учебные ролики по EXCEL Power BI Desktop ???

[@user-ld4st3wx9v]

Космос)))

[@Techman52]

Николай есть ли у вас в планах он-лайн курс по pq? Если да, то сообщите ориентировочно дату выпуска, очень бы хотел приобрести и освоить, с вашей помощью, данный раздел в экселе

[@isaevaamina7594]

👏👏👏

[@sergeynayda7778]

Добрый день, Николай, подскажите пожалуйста как сделать замену ячейки вцелом. Например в ячейках которые содержат СПБ - заменять их на Санкт-Петербург (как 1й способ в замене формулами!) Заранее благодарен

[@tkh9619]

Добры день! Очень интересует буферизация, у меня сложные запросы и их обновление занимает внушительное время. Пыталась использовать в конце запроса команду Table. Buffer но ощутимой разницы не заметила. Не понимаю в коком месте запроса нужно буферизацию делать? У меня запрос ссылается на другой запрос который уже рассчитан и его пересчитывать не нужно. Но когда обновляншь последний видно как он грузит файлы первого запроса. Буду благодарна ответу

[@Rice0987]

Куда делся мой лайк? Исправил. :)

[@user-bj9mo8il3b]

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

[@planetaexcel]

На русском пока нет, к сожалению :(

[@StasSykora]

Пока сложно, но кажется все возможно😊!

[@planetaexcel]

Это, наверное, одно из самых сложных видео у меня на канале, так что ОК :)

[@StasSykora]

@@planetaexcel На самом деле всё очень круто, спасибо вам огромное! Без лиших похвал, ваши обзоры пожалуй самые лучшие.

[@alexivanov8021]

Добрый вечер! Подскажите, пожалуйста, как настроить двустороннюю печать документов Excel. Например, если нужно распечатать многостраничный документ Excel, то при открытии окна печати нет ссылки на двустороннюю печать или на печать чётных и не чётных страниц. Приходится печатать по одной странице, выбирая ручным способом. Но если нужно распечатать документ Word, то при нажатии кнопки "печать" и открытии диалогового окна печати, есть возможность выбрать двустороннюю печать. Таким образом, вероятно, можно сделать вывод, что настройки принтера позволяют применять двустороннюю печать.

[@user-ou7oz8xe2o]

Здравствуйте, при помощи PQ можно сохранить результат после замены в текстовый файл? То есть шаг 1: загрузили через pq данные, заменили текст по справчнику через List. Accumulate, выгрузили в txt. Или проще не заморачиваться и скомбинировать с макросом на этапе выгрузки в файл?

[@user-st9mf4ei9f]

добрый вечер, подскажите пожалуйста, как изменить вашу формулу, что бы она заменяла проверяемую ячейку на значение из справочника "заменить" полностью, то есть: "ул. Николы д. 6, Спб" на "Санкт-Петербург" . Спасибо за видео.

[@alexeast555]

@Nikolay Pavlov , спасибо за видео, подскажите чем отличается этот способ от массовой замены посредством функции замены (например из видео "Bulk Find and Replace" kzbin.info/www/bejne/poKQd2mahtxjr8k ), может быстродействием или чем то иным?

[@batordashitsyrenov9455]

Добрый день! Подскажите, как заменить Значения в столбце по условию из другого столбца:? Например в Вашем примере - добавить слово "СПБ" перед значением в столбце "Адрес" , если в столбце "Исправленный адрес" будет встречаться слово "Санкт-Петербург" или "МСК" - если "Москва" это шаг после всех Ваших шагов...

[@ckotukboom3792]

Вы Профи. За 2 дня всё в захлеб посмотрел. Сейчас пытаюсь Plex в работе использовать. Очень понравилась емайл рассылка. Только не могу никак побороть то, что не могу выбрать с какой почты будет отправлять. Выбор дает сделать, а отправляет с одной и той же. Пробовал в outlook по умолчанию почту сменить, все равно с одной отправляет.

[@lizpak]

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

[@planetaexcel]

Думаю, вполне можно.

[@Vayolka33]

Подскажите, пожалуйста, как можно склеить две большие таблицы между собой, сопоставив их по некоторым данным (из 2-х колонок)...это вообще реально просто сделать? При чем количество строк может не совпадать, или к некоторым строкам одной из таблиц нет соответствующих из второй таблицы

[@user-kr6xe4pt8b]

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

[@userhfftfdf]

Спасибо, Вы помогли мне избавится от постоянного гемроя - кривые списки контрагентов, договоров, номенкулатуры...гигатонны геморя

[@planetaexcel]

Долой геморрой :)

[@user-hm3qs1mn4k]

В хозяйстве пригодится

[@NotEmpty2011]

Николай, как сделать аналогичное для Имени Запроса в PQ? Что-то типа динамическое Имя Запроса, синтаксис Имени Запроса. Например, есть 100500 Запросов с Именами: каталог смартфонов, каталог телевизоров, каталог аксессуаров и т.д. Первая часть Имени Запроса неизменна - "каталог ". А вот вторая часть Имени Запроса динамическая - чтобы в Имя Запроса из Таблицы или List подставлялось: "смартфонов", "телевизоров", "аксессуаров".

[@user-dj5qz1ks7v]

А есть другие способы, программы, скрипты? К примеру: есть большой текст (хтмл) и есть два столбца. поиск идёт по длинному тексту по словам из одного столбца и заменой соотвественного текста во втором столбце. Любые изменения в тексте (кроме замены текста) не допустимы.

[@Petroalex52]

Николай, добрый день. Как сделать замену содержимого ячейки ЦЕЛИКОМ?. List.Accumulate делает замену ВХОЖДЕНИЯ подстроки в заменямый текст...😩

[@andreiarxipov8854]

Каша!!!

[@sbn4862]

Очень полезны трюк но у меня половина не менялась.Хотя пробовал одним и тем же данным Хотелось бы понят как М работает Например state Как это аргумент связывается с таблицей (Данные) Чём-то похоже на Fazzy с вашего книги

[@im_buddha]

Николай, добрый день! Получилась отличная антиреклама Power Query :) проще загнать данный файл в csv и на питоне обработать, чем догадываться, как там "странные" разрабы в Microsoft думают о функциях и как работает этот Accumulate вот я изучаю изучаю его, но вот после этого видео больше не буду. Это ненужный, неуниверсальный и неоднозначый инструмент на практике. Либо VBA, либо уже что то соверменное, типа Python. Данный пример это два списка и какой нибудь map с replace.

[@planetaexcel]

Ну, тут дело вкуса. Кроме того, не всегда и не везде можно использовать Python :)

[@im_buddha]

@@planetaexcel Николай. Ради эксперимента посидел пару дней и сделал для HR статистику по сотрудникам на power query (распределение по стажу, статистику увольнений, графики роста численности и т.д). данные закачивал из базы HR. Как то быстро изучилось. Согласен на python могло быть и дольше с учётом диаграмм и конструирования отчёта для выгрузки. Короче для задач без сложной логики подойдет

[@LionRoiz]

А у меня ошибка. Что не так делал? Expression.Error: Не удается преобразовать значение null в тип Text. Сведения: Value= Type=[Type]

[@viktorsukhoj2509]

А как сделать, чтобы целиком ячейка менялась? Ну нужно мне найти Спб, и чтобы не текст внутри текста, а вся ячейка менялась на то что в "заменить"🤔?

[@viktorsukhoj2509]

После объединения 10 столбцов в один, получилась мешанина из текста и цифр. Нужный текст в разных столбцах находился. Я бы мог конечно и через условия бахнуть, но много условий придётся делать, да и текст может меняться искомый.

[@sergeynayda7778]

Мне тоже интересно как бы так сделать! Если найдёте решение буду Благодарен!

[@user-vl2ct3ph6f]

Супер, а можно не обновлять справочник, который ссылается на файл и содержит таблицу?

[@owl1891]

Комбинация Ctrl+H вроде проще))

[@kppptt]

Вот бы еще наполнение справочника автоматизировать - тогда бы ваще было вундебар.

[@user-rc3ye7gm3l]

Отличное видео! Но пока остановился на РЕГУЛЯРНЫХ ВЫРАЖЕНИЯХ (не поленитесь изучить ещё старую версию автора kzbin.info/www/bejne/g4SZc4Wqo7VnqdU)- это атомная бомба! Где-то в глубине я понимаю, что это темы связанные, но не хватает профильного образования! Или прямого чата с автором, чтоб задать много вопросов. А к стати, есть такая возможность, пусть даже платная, но с решением результата?

[@user-ui2wu6mm9l]

15:20 Я только никак не могу понять: как работает эта конструкция - current[Найти] и current[Заменить]? Когда мы обращаемся к конкретному полю в списке записей, конструкция выглядит вот так - Справочник{0}[Найти]. И тут по идее переменной может выступать номер строки в списке - {current}. Но что такое current[Найти] и current[Заменить] - я, ей-богу, понять не могу никак! current - тут что в этой конструкции???

[@mandrazhiev82]

🤦‍♂️ круто. На середине видео я поплыл.

[@planetaexcel]

Значит я плохо объясняю :)

[@mandrazhiev82]

@@planetaexcel нет всё Ок. Понять не просто 😊

[@Rusia-16]

Это мне напоминает программу Питон.

[@planetaexcel]

Microsoft оттуда вдохновение и черпали, видимо :)

[@user-mi2cj4jh2i]

Купил Плех надстройку. Как что работает вообще не понятно.Прошу подробнее инструкции.

[@planetaexcel]

Да, надо бы и по ней видеоуроки поснимать, наверное...

[@user-mi2cj4jh2i]

@@planetaexcel Николай, а Plex обновляется? Можно предложить функцию для Вашей надстройки?

[@user-ui2wu6mm9l]

Есть формула Table.ToRecords, а есть - Record.FromTable И это, блин, разные формулы и работают они по-разному! )))

[@NimostorEvil]

А возможно, массово добавить индексы в адреса, где не хватает??

[@planetaexcel]

О, это подключение к какому-нибудь ФИАСу или КЛАДРу надо, наверное... Не пробовал пока :)

[@NimostorEvil]

@@planetaexcel будем ждать)

[@Vayolka33]

Жаль у нас на работе нет никаких надстроек в ексель

[@vasboriskin1]

Ох, Николай, это уже за пределами моего понимания... Воспроизводить это на практике только пошагово. Самому это не запомнить, не воспроизвести

[@planetaexcel]

Откровенно говоря, я тоже не сразу в эту функцию въехал - пришлось потанцевать вокруг сначала пару часиков :)

[@vinosserg]

(text,regex,textForRepalce)=> Web.Page( "document.write('" & text & "'.replace(new RegExp('" & regex & "','gi'),'" & textForRepalce & "'))" )[Data]{0}[Children]{0}[Children]{1}[Text]{0} функция для замены в тексте, не имеет указанных в видео проблем, но имеет проблемы со скоростью!

[@im_buddha]

Да, Сергей. этот Power Query очень "кривой". Языком программирование можно назвать также как и формулы в Excel - это язык программирования. Считаю что Microsoft c ним проиграли. Он не приживется. Это какой то "костыль". Если для того чтобы решить такую простую задачу нужен какой то странный метод с накапливанием, то более сложную задачу просто не решить, если не знать все фукцнии наизусть VBA тогда проще исепользовать

[@vinosserg]

@@im_buddha он лучшее из того что есть, встроенное, для подготовки данных!

[@user-ce7cn9zt3y]

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

[@user-ce7cn9zt3y]

Не понятно, почему state берет 10, а не {3,2,5}. Ведь state записан первым, так же, как и {3,2,5}. Почему второй аргумент (current) берет значения из списка, хотя список стоит первым. А первый аргумент (state) - берет 10, хотя 10 стоит вторым аргументом :(