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

Как бързо и групово да заменим текста според справочния списък с формули - вече го подредихме. Сега нека се опитаме да го направим в Power Query.

Както често се случва изпълнява тази задача е много по-лесна от обяснението защо работи, но нека се опитаме да направим и двете 🙂

И така, имаме две „умни“ динамични таблици, създадени от обикновени диапазони с клавишна комбинация Ctrl+T или екип Начало – Форматиране като таблица (Начало — Форматиране като таблица):

Обадих се на първата маса Дата, втората маса – указателизползване на поле Име на таблица (Име на таблица) етикет конструктор (Дизайн).

Задача: заменете адресите в таблицата Дата всички събития от колона Да намеря Наръчник към съответните им правилни двойници от колоната заместител. Останалият текст в клетките трябва да остане недокоснат.

Стъпка 1. Заредете директорията в Power Query и я превърнете в списък

След като зададете активната клетка на произволно място в референтната таблица, щракнете върху раздела Дата (Дата)или на раздела Запитване за захранване (ако имате стара версия на Excel и сте инсталирали Power Query като добавка в отделен раздел) на бутона От таблица/обхват (От таблица/обхват).

Справочната таблица ще бъде заредена в редактора на заявки на Power Query:

За да не пречи, автоматично добавена стъпка модифициран тип (Променен тип) в десния панел приложените стъпки могат безопасно да бъдат изтрити, оставяйки само стъпката източник (Източник):

Сега, за да извършим допълнителни трансформации и замествания, трябва да превърнем тази таблица в списък (списък).

Лирическо отклонение

• Маса е двуизмерен масив, състоящ се от няколко реда и колони.
• Запис (Запис) – едномерен масив-низ, състоящ се от няколко полета-елементи с имена, напр [Име = „Маша”, Пол = „f”, Възраст = 25]
  
• списък – едномерен масив-колона, състоящ се от няколко елемента, напр {1, 2, 3, 10, 42} or { "Вяра Надежда Любов" }
  

За да разрешим нашия проблем, ще се интересуваме преди всичко от типа списък.

Номерът тук е, че списъчните елементи в Power Query могат да бъдат не само банални числа или текст, но и други списъци или записи. Именно в такъв сложен списък (списък), състоящ се от записи (записи), трябва да обърнем нашата директория. В синтактичната нотация на Power Query (записи в квадратни скоби, списъци във къдрави скоби) това би изглеждало така:

{

    [ Намерете = „Св. Петербург”, Заменете = „Св. Петербург” ] ,

    [ Намерете = „Св. Петербург”, Заменете = „Св. Петербург” ] ,

    [ Find = “Петър”, Replace = “Св. Петербург” ] ,

и т.н.

}

Такава трансформация се извършва с помощта на специална функция на езика M, вградена в Power Query – Table.ToRecords. За да я приложите директно в лентата с формули, добавете тази функция към кода на стъпката там източник.

Беше:

След:

След добавяне на функцията Table.ToRecords външният вид на нашата таблица ще се промени – тя ще се превърне в списък със записи. Съдържанието на отделните записи може да се види в долната част на прозореца за преглед, като щракнете върху фона на клетката до която и да е дума Рекорд (но нито с една дума!)

В допълнение към горното има смисъл да добавим още един щрих – да кешираме (буферираме) нашия създаден списък. Това ще принуди Power Query да зареди списъка ни за търсене веднъж в паметта и да не го преизчислява отново, когато по-късно имаме достъп до него, за да го заменим. За да направите това, обвийте нашата формула в друга функция – Списък.Буфер:

Такова кеширане ще даде много забележимо увеличение на скоростта (с няколко пъти!) с голямо количество първоначални данни за изчистване.

С това подготовката на наръчника е завършена.

Остава да кликнете върху Начало – Затвори и зареди – Затвори и зареди в... (Начало — Close&Load — Close&Load to..), изберете опция Просто създайте връзка (Само създаване на връзка) и се върнете към Excel.

Стъпка 2. Зареждане на таблицата с данни

Тук всичко е банално. Както преди със справочника, стигаме до всяко място в таблицата, кликваме върху раздела Дата бутон От таблица/обхват и нашата маса Дата влиза в Power Query. Автоматично добавена стъпка модифициран тип (Променен тип) можете също да премахнете:

С него не се изискват специални подготвителни действия и преминаваме към най-важното.

Стъпка 3. Извършете замени с помощта на функцията List.Accumulate

Нека добавим изчислена колона към нашата таблица с данни с помощта на командата Добавяне на колона – Персонализирана колона (Добавяне на колона — Персонализирана колона): и въведете името на добавената колона в прозореца, който се отваря (напр. коригиран адрес) и нашата магическа функция Списък.Натрупване:

Остава да кликнете върху OK – и получаваме колона с направените замени:

Отбележи, че:

• Тъй като Power Query е чувствителен към главни и малки букви, нямаше замяна в предпоследния ред, защото в директорията имаме „SPb“, а не „SPb“.
• Ако има няколко подниза за замяна наведнъж в изходните данни (например в 7-ия ред трябва да замените и „S-Pb“ и „Проспект“), тогава това не създава проблеми (за разлика от замяната с формули от предишния метод).
• Ако няма нищо за замяна в изходния текст (9-ти ред), тогава не възникват грешки (за разлика, отново, от замяната с формули).

Скоростта на такава заявка е много, много прилична. Например, за таблица с първоначални данни с размер от 5000 реда, тази заявка беше актуализирана за по-малко от секунда (без буфериране, между другото, около 3 секунди!)

Как работи функцията List.Accumulate

По принцип това може да е краят (аз да пиша, а вие да прочетете) тази статия. Ако искате не само да можете, но и да разберете как работи „под капака“, тогава ще трябва да се гмурнете малко по-дълбоко в заешката дупка и да се справите с функцията List.Accumulate, която направи цялата масова подмяна работи за нас.

Синтаксисът за тази функция е:

=List.Accumulate(списък, семена, акумулатор)

където

• списък е списъкът, чиито елементи итерираме. 
• семена - Първоначално състояние
• акумулатор – функция, която извършва някаква операция (математическа, текстова и др.) върху следващия елемент от списъка и акумулира резултата от обработката в специална променлива.
  

Като цяло синтаксисът за писане на функции в Power Query изглежда така:

(аргумент1, аргумент2, … аргументN) => някои действия с аргументи

Например функцията за сумиране може да бъде представена като:

(a, b) => a + b

За List.Accumulate тази акумулираща функция има два задължителни аргумента (те могат да бъдат наименувани произволно, но обичайните имена са са и ток, както в официалната помощ за тази функция, където:

• са – променлива, в която се натрупва резултатът (първоначалната й стойност е споменатата по-горе семена)
  
• ток – следващата итерирана стойност от списъка списък
  

Например, нека да разгледаме стъпките на логиката на следната конструкция:

=List.Accumulate({3, 2, 5}, 10, (състояние, ток) => състояние + ток)

Това е последното натрупано са стойността е нашата функция List.Accumulate и извежда като резултат:

Ако фантазирате малко, тогава с помощта на функцията List.Accumulate можете да симулирате например функцията на Excel CONCATENATE (в Power Query нейният аналог се нарича Текст. Комбинирайте) използвайки израза:

Или дори потърсете максималната стойност (имитация на функцията MAX на Excel, която в Power Query се извиква List.Max):

Въпреки това, основната характеристика на List.Accumulate е способността да обработва не само прост текст или числови списъци като аргументи, но и по-сложни обекти – например списъци от списъци или списъци от записи (здравей, Директория!)

Нека да разгледаме отново конструкцията, която извърши замяната в нашия проблем:

List.Accumulate(указател, [Адрес], (състояние, текущо) => Text.Replace (състояние, текущо [Намиране], текущо [Замяна]) )

Какво всъщност става тук?

1. Като първоначална стойност (семена) вземаме първия тромав текст от колоната [Адрес] нашата маса: 199034, Санкт Петербург, ул. Беринга, д. 1
2. След това List.Accumulate итерира елементите на списъка един по един – Наръчник. Всеки елемент от този списък е запис, състоящ се от двойка полета „Какво да намеря – с какво да заменя“ или, с други думи, следващия ред в директорията.
3. Функцията акумулатор поставя в променлива са първоначална стойност (първи адрес 199034, Санкт Петербург, ул. Беринга, д. 1) и изпълнява върху него акумулираща функция – операцията за замяна с помощта на стандартната М-функция Text.Replace (аналогично на функцията SUBSTITUTE на Excel). Синтаксисът му е:

   Text.Replace( оригинален текст, какво търсим, с какво заменяме)

   и тук имаме:

• са е нашият мръсен адрес, който се намира в са (стигане до там от семена)
  
• текущо [търсене] – стойност на полето Да намеря от следващия повторен запис в списъка указател, която се намира в променливата ток
• текущ [замени] – стойност на полето заместител от следващия повторен запис в списъка указателлежи в ток

Така за всеки адрес всеки път се изпълнява пълен цикъл на изброяване на всички редове в директорията, като текстът от полето [Find] се заменя със стойността от полето [Replace].

Надявам се, че схванахте идеята 🙂

• Групова замяна на текст в списък с помощта на формули
  
• Регулярни изрази (RegExp) в Power Query