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

Як хутка і масава замяніць тэкст па спасылцы на формулы - мы ўжо разабраліся. Зараз давайце паспрабуем зрабіць гэта ў Power Query.

Як часта бывае выконваць гэтая задача нашмат лягчэй, чым тлумачыць чаму гэта працуе, але давайце паспрабуем зрабіць абодва 🙂

Такім чынам, мы маем дзве «разумныя» дынамічныя табліцы, створаныя з звычайных дыяпазонаў спалучэннем клавіш Ctrl+T або каманда Галоўная - фармат у выглядзе табліцы (Галоўная — фармат у табліцу):

Я назваў першы стол Дата, другі стол – каталогз выкарыстаннем поля Назва табліцы (Назва табліцы) таб Канструктар (Дызайн).

Заданне: замяніць адрасы ў табліцы Дата усе выпадкі са слупка Для таго, каб знайсці Даведнік іх адпаведным правільным адпаведнікам са слупка сурагат. Астатні тэкст у вочках павінен заставацца некранутым.

Крок 1. Загрузіце каталог у Power Query і ператварыце яго ў спіс

Усталяваўшы актыўную ячэйку ў любое месца даведачнай табліцы, пстрыкніце па ўкладцы Дата (Дата)або на ўкладцы Запыт на харчаванне (калі ў вас старая версія Excel і вы ўсталявалі Power Query як надбудову на асобнай укладцы) на кнопку З табліцы/дыяпазону (З табліцы/дыяпазону).

Даведачная табліца будзе загружана ў рэдактар ​​запытаў Power Query:

Каб не перашкаджаць, аўтаматычна дададзены крок мадыфікаваны тып (Зменены тып) на правай панэлі ўжытыя крокі можна бяспечна выдаліць, пакінуўшы толькі крок крыніца (Крыніца):

Цяпер, каб выканаць далейшыя пераўтварэнні і замены, нам неабходна ператварыць гэтую табліцу ў спіс (list).

Лірычнае адступленне

• табліца - гэта двухмерны масіў, які складаецца з некалькіх радкоў і слупкоў.
• Запіс (Запіс) – аднамерны масіў-радок, які складаецца з некалькіх палёў-элементаў з назвамі, напр [Імя = “Маша”, Пол = “ж”, Узрост = 25]
  
• спіс – аднамерны масіў-слупок, які складаецца з некалькіх элементаў, напр {1, 2, 3, 10, 42} or { "Вера, Надзея, Любоў" }
  

Каб вырашыць нашу праблему, нас у першую чаргу будзе цікавіць тып спіс.

Фішка тут у тым, што элементамі спісу ў Power Query могуць быць не толькі банальныя лічбы або тэкст, але і іншыя спісы або запісы. Вось у такі хітры спіс (list), які складаецца з запісаў (records), і трэба згарнуць наш каталог. У сінтаксічнай натацыі Power Query (запісы ў квадратных дужках, спісы ў фігурных дужках) гэта будзе выглядаць так:

{

    [ Знайсці = “Св. Пецярбург», Замяніць = «Санкт-Пецярбург». Пецярбург”] ,

    [ Знайсці = “Св. Пецярбург», Замяніць = «Санкт-Пецярбург». Пецярбург”] ,

    [ Find = “Пётр”, Replace = “St. Пецярбург”] ,

і г.д.

}

Такое пераўтварэнне выконваецца з дапамогай спецыяльнай функцыі мовы М, убудаванай у Power Query – Table.ToRecords. Каб прымяніць яе непасрэдна ў радку формул, дадайце гэту функцыю ў код кроку там крыніца.

Гэта было:

Пасля:

Пасля дадання функцыі Table.ToRecords знешні выгляд нашай табліцы зменіцца - яна ператворыцца ў спіс запісаў. Змесціва асобных запісаў можна ўбачыць унізе панэлі прагляду, пстрыкнуўшы на фоне ячэйкі побач з любым словам запіс (але ні адным словам!)

У дадатак да вышэйсказанага ёсць сэнс дадаць яшчэ адзін штрых - кэшаваць (буферизировать) створаны намі спіс. Гэта прымусіць Power Query загрузіць наш спіс пошуку адзін раз у памяць і не пералічваць яго зноў, калі мы пазней будзем звяртацца да яго, каб замяніць яго. Каб зрабіць гэта, загарніце нашу формулу ў іншую функцыю - Спіс.Буфер:

Такое кэшаванне дасць вельмі прыкметны прырост хуткасці (у некалькі разоў!) Пры вялікім аб'ёме зыходных дадзеных, якія трэба ачысціць.

На гэтым падрыхтоўка даведніка завершана.

Засталося націснуць Дадому – Зачыніць і загрузіць – Зачыніць і загрузіць у… (Галоўная — Зачыніць&Загрузіць — Зачыніць&Загрузіць у..), абярыце варыянт Проста стварыце злучэнне (Толькі стварыць злучэнне) і вярнуцца ў 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.Accumulate з'яўляецца магчымасць апрацоўваць не толькі просты тэкст або лікавыя спісы ў якасці аргументаў, але і больш складаныя аб'екты - напрыклад, спісы-са-спісаў або спісы-з-запісаў (прывітанне, Каталог!)

Давайце яшчэ раз паглядзім на канструкцыю, якая выканала замену ў нашай задачы:

List.Accumulate(каталог, [Адрас], (стан,бягучы) => Text.Replace(стан, бягучы[Знайсці], бягучы[Замяніць]) )

Што тут насамрэч адбываецца?

1. У якасці пачатковага значэння (насенне) бярэм першы нязграбны тэкст са слупка [Адрас] наш стол: 199034, г. Санкт-Пецярбург, вул. Берынга, д. 1
2. Затым List.Accumulate перабірае элементы спісу адзін за адным - Даведнік. Кожны элемент гэтага спісу ўяўляе сабой запіс, які складаецца з пары палёў «Што знайсці - Чым замяніць» або, іншымі словамі, наступны радок у каталогу.
3. Функцыя акумулятара змяшчае ў зменную былі пачатковае значэнне (першы адрас 199034, г. Санкт-Пецярбург, вул. Берынга, д. 1) і выконвае над ім акумулятарную функцыю – аперацыю замяшчэння з дапамогай стандартнай М-функцыі Тэкст.Замяніць (аналаг функцыі SUBSTITUTE у Excel). Яго сінтаксіс:

   Text.Replace( зыходны тэкст, што мы шукаем, чым мы замяняем)

   і вось мы маем:

• былі гэта наш брудны адрас, які знаходзіцца ў былі (дабіраюся адкуль насенне)
  
• бягучы [Пошук] – значэнне поля Для таго, каб знайсці ад наступнага паўторнага запісу спісу каталог, які ляжыць у зменнай ток
• бягучы[Замяніць] – значэнне поля сурагат ад наступнага паўторнага запісу спісу каталоглежачы ў ток

Такім чынам, для кожнага адрасу кожны раз запускаецца поўны цыкл пераліку ўсіх радкоў у каталогу, замяняючы тэкст з поля [Знайсці] на значэнне з поля [Замяніць].

Спадзяюся, вы зразумелі ідэю 🙂

• Масавая замена тэксту ў спісе з дапамогай формул
  
• Рэгулярныя выразы (RegExp) у Power Query