Масовна замена на текст во Power Query со функција List.Acumulate

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

Како што често се случува вршат оваа задача е многу полесна од објаснувањето зошто функционира, но ајде да се обидеме да ги направиме и двете 🙂

Значи, имаме две „паметни“ динамични табели создадени од обични опсези со кратенка на тастатурата Ctrl+T или тим Дома – Форматирајте како табела (Дома - Формат како табела):

Ја повикав првата маса податоци, втората табела - директориумкористејќи поле Име на табелата (Име на табелата) табот Изведувач (Дизајн).

Задача: заменете ги адресите во табелата податоци сите појави од колона Да најде Прирачник на нивните соодветни точни колеги од колоната Заменик. Остатокот од текстот во ќелиите треба да остане недопрен.

Чекор 1. Вчитајте го директориумот во Power Query и претворете го во листа

Откако ќе ја поставите активната ќелија на кое било место во референтната табела, кликнете на јазичето податоци (Датум)или на јазичето Барање за напојување (ако имате стара верзија на Excel и сте инсталирале Power Query како додаток на посебен таб) на копчето Од маса/опсег (Од табела/опсег).

Референтната табела ќе биде вчитана во уредувачот за прашања Power Query:

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

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

Лирска дигресија

• Табела е дводимензионална низа која се состои од неколку редови и колони.
• Запис (рекорд) – еднодимензионална низа-низа, која се состои од неколку полиња-елементи со имиња, на пример [Име = „Маша“, Пол = „ѓ“, Возраст = 25]
  
• листа – еднодимензионална низа-колона, составена од неколку елементи, на пример {1, 2, 3, 10, 42} or { "Вера, Надеж љубов" }
  

За да го решиме нашиот проблем, првенствено ќе не интересира типот листа.

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

{

    [ Најдете = „Св. Петербург“, Replace = „Св. Петербург“] ,

    [ Најдете = „Св. Петербург“, Replace = „Св. Петербург“] ,

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

итн

}

Таквата трансформација се врши со помош на специјална функција на јазикот М вградена во Power Query - Табела.ToRecords. За да ја примените директно во лентата со формули, додајте ја оваа функција во кодот за чекор таму извор.

Беше:

По:

По додавањето на функцијата Table.ToRecords, изгледот на нашата табела ќе се промени – ќе се претвори во листа на записи. Содржината на поединечните записи може да се види на дното на прозорецот за преглед со кликнување во позадината на ќелијата до кој било збор рекорд (но не со еден збор!)

Покрај горенаведеното, има смисла да се додаде уште еден удар - во кешот (баферот) на нашата креирана листа. Ова ќе го принуди Power Query да ја вчита нашата листа за пребарување еднаш во меморијата и да не ја пресметува повторно кога подоцна ќе пристапиме до неа за да ја замениме. За да го направите ова, завиткајте ја нашата формула во друга функција - Листа.Бафер:

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

Со ова се комплетира подготовката на прирачникот.

Останува да кликнете на Дома – Затвори и вчитај – Затвори и вчитај во… (Дома - Затвори&Вчитај - Затвори&Вчитај до..), изберете опција Само создадете врска (Само креирајте врска) и вратете се во Excel.

Чекор 2. Вчитување на табелата со податоци

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

Не се потребни посебни подготвителни дејствија да се направат со него и продолжуваме на најважното.

Чекор 3. Извршете замени користејќи ја функцијата List.Acumulate

Ајде да додадеме пресметана колона во нашата табела со податоци користејќи ја командата Додавање колона – Прилагодена колона (Додај колона - Прилагодена колона): и внесете го името на додадената колона во прозорецот што се отвора (на пример, поправена адреса) и нашата магична функција Листа.Акумулирај:

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

Забележи го тоа:

• Бидејќи Power Query е осетлив на букви, немаше замена во претпоследната линија, бидејќи во директориумот имаме „SPb“, а не „SPb“.
• Ако има неколку поднизи што треба да се заменат одеднаш во изворните податоци (на пример, во 7-от ред треба да ги замените и „S-Pb“ и „Prospectus“), тогаш тоа не создава никакви проблеми (за разлика од заменувањето со формули од претходниот метод).
• Ако нема што да се замени во изворниот текст (9-ти ред), тогаш не се појавуваат грешки (за разлика од, повторно, од замена со формули).

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

Како функционира функцијата List.Acumulate

Во принцип, ова може да биде крајот (јас да пишувам, а вие да прочитате) овој напис. Ако сакате не само да можете, туку и да разберете како функционира „под хаубата“, тогаш ќе мора да нурнете малку подлабоко во зајачката дупка и да се справите со функцијата List.Acumulate, која ја изврши целата замена работи за нас.

Синтаксата за оваа функција е:

=List.Acumulate(листа, семе, акумулатор)

каде

• листа е списокот чии елементи ги повторуваме. 
• семе – почетна состојба
• акумулатор – функција која врши некоја операција (математичка, текстуална итн.) на следниот елемент од списокот и го акумулира резултатот од обработката во посебна променлива.
  

Генерално, синтаксата за пишување функции во Power Query изгледа вака:

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

На пример, функцијата за сумирање може да се претстави како:

(а, б) => a + b

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

• беа – променлива каде што се акумулира резултатот (неговата почетна вредност е онаа споменатата погоре семе)
  
• тековната – следната повторена вредност од списокот листа
  

На пример, да ги погледнеме чекорите на логиката на следната конструкција:

=List.Acumulate({3, 2, 5}, 10, (состојба, струја) => состојба + струја)

Ова е најновото акумулирано беа вредноста е нашата листа. Акумулирај ја функцијата и излезите како резултат:

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

Или дури и да ја барате максималната вредност (имитација на функцијата MAX на Excel, која во Power Query се нарекува Список.Max):

Сепак, главната карактеристика на List.Acumulate е способноста да се обработуваат не само едноставни текстови или нумерички листи како аргументи, туку и посложени објекти - на пример, списоци-од-листи или списоци-од-записи (здраво, Директориум!)

Ајде повторно да ја погледнеме конструкцијата што ја изврши замената во нашиот проблем:

List.Acumulate(директориум, [Адреса], (состојба, тековна) => Текст. Замени (состојба, тековно[Најди], тековно[Замени]) )

Што навистина се случува овде?

1. Како почетна вредност (семе) го земаме првиот невешт текст од колоната [Адреса] нашата маса: 199034, Санкт Петербург, ул. Беринга, г. 1
2. Потоа List.Acumulate ги повторува елементите на листата еден по еден - Прирачник. Секој елемент од оваа листа е запис кој се состои од пар полиња „Што да се најде – со што да се замени“ или, со други зборови, следната линија во директориумот.
3. Функцијата акумулатор става во променлива беа почетна вредност (прва адреса 199034, Санкт Петербург, ул. Беринга, г. 1) и врши функција на акумулатор на него – операција за замена со помош на стандардната M-функција Текст.Замени (аналогно на функцијата SUBSTITUTE на Excel). Неговата синтакса е:

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

   и тука имаме:

• беа е нашата валкана адреса, која лежи во беа (стигнување таму од семе)
  
• тековно[Пребарување] – вредност на полето Да најде од следниот повторен запис на листата директориум, која лежи во променливата тековната
• тековно[Замени] – вредност на полето Заменик од следниот повторен запис на листата директориумлежи во тековната

Така, за секоја адреса, секој пат се извршува целосен циклус на набројување на сите линии во директориумот, заменувајќи го текстот од полето [Find] со вредноста од полето [Replace].

Се надевам дека ја сфативте идејата 🙂

• Масовно заменете го текстот во список користејќи формули
  
• Редовни изрази (RegExp) во Power Query