Masowe zastępowanie tekstu w dodatku Power Query za pomocą funkcji List.Accumulate

Jak szybko i zbiorczo zastąpić tekst według spisu referencyjnego wzorami – już to załatwiliśmy. Teraz spróbujmy to zrobić w dodatku Power Query.

Jak to często bywa wykonać to zadanie jest o wiele łatwiejsze niż wyjaśnianie dlaczego to działa, ale spróbujmy zrobić jedno i drugie 🙂

Mamy więc dwie „inteligentne” dynamiczne tabele utworzone ze zwykłych zakresów za pomocą skrótu klawiaturowego Ctrl+T lub zespół Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę):

Zadzwoniłem do pierwszego stolika Dane, drugi stół – Informatorza pomocą pola Nazwa tabeli (Nazwa tabeli) Zakładka Konstruktor (Projekt).

Zadanie: zamień w adresach w tabeli Dane wszystkie wystąpienia z kolumny Znaleźć Podręcznik do ich odpowiednich poprawnych odpowiedników z kolumny namiastka. Reszta tekstu w komórkach powinna pozostać nietknięta.

Krok 1. Załaduj katalog do Power Query i przekształć go w listę

Po ustawieniu aktywnej komórki w dowolnym miejscu w tabeli odniesienia, kliknij zakładkę Dane (Data)lub na karcie Zapytanie o moc (jeśli masz starą wersję programu Excel i zainstalowałeś dodatek Power Query jako dodatek na osobnej karcie) na przycisku Ze stołu/zakresu (z tabeli/zakresu).

Tabela referencyjna zostanie załadowana do edytora zapytań Power Query:

Aby nie przeszkadzać, automatycznie dodany krok zmodyfikowany typ (Zmieniony typ) w prawym panelu zastosowane kroki można bezpiecznie usunąć, pozostawiając tylko krok Źródło (Źródło):

Teraz, aby wykonać dalsze przekształcenia i zamiany, musimy zamienić tę tabelę w listę (listę).

Dygresja liryczna

• Stół to dwuwymiarowa tablica składająca się z kilku wierszy i kolumn.
• Rekord (Rekord) – jednowymiarowa tablica-łańcuch, składająca się z kilku pól-elementów z nazwami, na przykład [Imię = „Masza”, Płeć = „f”, Wiek = 25]
  
• Lista – jednowymiarowa kolumna-tablica, składająca się z kilku elementów, na przykład {1, 2, 3, 10, 42} or { "Wiara Nadzieja Miłość" }
  

Aby rozwiązać nasz problem, interesuje nas przede wszystkim typ Lista.

Sztuczka polega na tym, że elementy listy w dodatku Power Query mogą być nie tylko banalnymi liczbami lub tekstem, ale także innymi listami lub rekordami. To właśnie na tak zawiłej liście (liście), składającej się z rekordów (rekordów), musimy przewrócić nasz katalog. W notacji składniowej Power Query (wpisy w nawiasach kwadratowych, listy w nawiasach klamrowych) wyglądałoby to tak:

{

    [ Znajdź = „Św. Petersburg”, Zastąp = „Św. Petersburg” ] ,

    [ Znajdź = „Św. Petersburg”, Zastąp = „Św. Petersburg” ] ,

    [ Znajdź = „Piotr”, Zamień = „Św. Petersburg” ] ,

itd.

}

Taka transformacja jest wykonywana za pomocą specjalnej funkcji języka M wbudowanej w Power Query – Tabela.DoRekordów. Aby zastosować ją bezpośrednio na pasku formuły, dodaj tę funkcję do znajdującego się tam kodu kroku Źródło.

To było:

Po:

Po dodaniu funkcji Table.ToRecords zmieni się wygląd naszej tabeli – zmieni się ona w listę rekordów. Zawartość poszczególnych rekordów można zobaczyć na dole okienka widoku, klikając w tle komórki obok dowolnego słowa Rekord (ale nie jednym słowem!)

Oprócz powyższego warto dodać jeszcze jedną kreskę – do cache (bufora) stworzonej przez nas listy. Zmusi to dodatek Power Query do załadowania naszej listy wyszukiwania raz do pamięci i nie będzie ponownie obliczał jej, gdy później uzyskamy do niej dostęp w celu jej zastąpienia. Aby to zrobić, owiń naszą formułę w inną funkcję – Lista.Bufor:

Takie buforowanie da bardzo zauważalny wzrost szybkości (kilkakrotnie!) przy dużej ilości danych początkowych do wyczyszczenia.

Na tym kończy się przygotowanie podręcznika.

Pozostaje kliknąć Strona główna – Zamknij i załaduj – Zamknij i załaduj do… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do...), Wybierz opcję Po prostu stwórz połączenie (Tylko utwórz połączenie) i wróć do Excela.

Krok 2. Ładowanie tabeli danych

Tutaj wszystko jest banalne. Tak jak poprzednio z informatorem, dostaniemy się w dowolne miejsce w tabeli, klikamy w zakładkę Dane przycisk Z tabeli/zakresu i nasz stół Dane dostaje się do dodatku Power Query. Krok dodany automatycznie zmodyfikowany typ (Zmieniony typ) możesz również usunąć:

Nie są wymagane żadne specjalne działania przygotowawcze do wykonania i przechodzimy do rzeczy najważniejszej.

Krok 3. Dokonaj wymiany za pomocą funkcji List.Accumulate

Dodajmy obliczoną kolumnę do naszej tabeli danych za pomocą polecenia Dodawanie kolumny – niestandardowa kolumna (Dodaj kolumnę — Kolumna niestandardowa): i wprowadź nazwę dodanej kolumny w oknie, które się otworzy (np. poprawiony adres) i nasza magiczna funkcja List.Akumuluj:

Pozostaje kliknąć OK – i otrzymujemy kolumnę z wykonanymi zamiennikami:

Zauważ, że:

• Ponieważ w dodatku Power Query rozróżniana jest wielkość liter, w przedostatnim wierszu nie było zamiany, ponieważ w katalogu mamy „SPb”, a nie „SPb”.
• Jeśli w danych źródłowych jest kilka podciągów do zastąpienia naraz (na przykład w 7 wierszu trzeba zamienić zarówno „S-Pb”, jak i „Prospekt”), nie stwarza to żadnych problemów (w przeciwieństwie do zastępowania formułami z poprzednia metoda).
• Jeśli nie ma nic do zastąpienia w tekście źródłowym (9 wiersz), to nie występują błędy (w przeciwieństwie do zastąpienia przez formuły).

Szybkość takiej prośby jest bardzo, bardzo przyzwoita. Na przykład dla tabeli danych początkowych o rozmiarze 5000 wierszy to zapytanie zostało zaktualizowane w mniej niż sekundę (bez buforowania notabene około 3 sekund!)

Jak działa funkcja List.Accumulate

W zasadzie mógłby to być koniec (dla mnie pisać, a dla ciebie czytać) ten artykuł. Jeśli chcesz nie tylko móc, ale także zrozumieć, jak to działa „pod maską”, będziesz musiał zanurkować nieco głębiej w króliczą norę i poradzić sobie z funkcją List.Accumulate, która wykonała całą wymianę masową Pracuj dla nas.

Składnia tej funkcji to:

=List.Akumuluj(podstęp, nasienie, akumulator)

gdzie

• podstęp to lista, po której elementach iterujemy. 
• nasienie - stan początkowy
• akumulator – funkcja, która wykonuje jakąś operację (matematyczną, tekstową itp.) na kolejnym elemencie listy i akumuluje wynik przetwarzania w specjalnej zmiennej.
  

Ogólnie składnia pisania funkcji w dodatku Power Query wygląda następująco:

(argument1, argument2, … argumentN) => niektóre akcje z argumentami

Na przykład funkcja sumująca może być reprezentowana jako:

(a, b) => a + b

W przypadku List.Accumulate , ta funkcja akumulatora ma dwa wymagane argumenty (mogą mieć dowolną nazwę, ale zwykłe nazwy to były и aktualne, jak w oficjalnej pomocy do tej funkcji, gdzie:

• były – zmienna, w której akumulowany jest wynik (jej początkową wartością jest ta, o której mowa powyżej) nasienie)
  
• aktualne – kolejna iterowana wartość z listy podstęp
  

Przyjrzyjmy się na przykład krokom logiki następującej konstrukcji:

=List.Akumuluj({3, 2, 5}, 10, (stan, prąd) => stan + prąd)

To jest ostatnia zgromadzona były wartością jest nasza funkcja List.Accumulate i wyjścia w wyniku:

Jeśli trochę fantazjujesz, to używając funkcji List.Accumulate, możesz symulować na przykład funkcję Excel CONCATENATE (w Power Query jej analog nazywa się Tekst.Połącz) używając wyrażenia:

Lub nawet wyszukaj maksymalną wartość (imitacja funkcji MAX programu Excel, która w dodatku Power Query nazywa się Lista.Maks.):

Jednak główną cechą List.Accumulate jest możliwość przetwarzania nie tylko prostych list tekstowych lub liczbowych jako argumentów, ale także bardziej złożonych obiektów – na przykład list-z-list lub list-z-rekordów (Witaj Katalog!)

Przyjrzyjmy się jeszcze raz konstrukcji, która wykonała wymianę w naszym zadaniu:

Lista.Akumuluj(Informator, [Adres], (stan,bieżący) => Tekst.Zastąp(stan, bieżący[Znajdź], bieżący[Zamień]) )

Co się tu naprawdę dzieje?

1. Jako wartość początkowa (nasienie) bierzemy pierwszy niezgrabny tekst z kolumny [Adres] nasz stół: 199034, Petersburg, ul. Beringa, zm. 1
2. Następnie List.Accumulate iteruje po elementach listy jeden po drugim – Podręcznik. Każdy element tej listy to rekord składający się z pary pól „Co znaleźć – czym zastąpić” lub innymi słowy kolejnego wiersza w katalogu.
3. Funkcja akumulatora umieszcza w zmiennej były wartość początkowa (pierwszy adres 199034, Petersburg, ul. Beringa, zm. 1) i wykonuje na nim funkcję akumulatora – operację wymiany przy użyciu standardowej funkcji M Tekst.Zamień (analogicznie do funkcji SUBSTITUTE programu Excel). Jego składnia to:

   Text.Replace( oryginalny tekst, czego szukamy, czym zastępujemy )

   a tu mamy:

• były to nasz brudny adres, który leży w były (dostaję się stamtąd nasienie)
  
• bieżące[Szukaj] - wartość pola Znaleźć od następnego iterowanego wpisu na liście Informator, który leży w zmiennej aktualne
• obecny[Zamień] - wartość pola namiastka od następnego iterowanego wpisu na liście Informatorleżeć w aktualne

Tym samym dla każdego adresu każdorazowo uruchamiany jest pełny cykl wyliczania wszystkich wierszy w katalogu, zastępując tekst z pola [Znajdź] wartością z pola [Zamień].

Mam nadzieję, że masz pomysł 🙂

• Masowe zastępowanie tekstu na liście za pomocą formuł
  
• Wyrażenia regularne (RegExp) w dodatku Power Query