Bulktextersättning i Power Query med List.Accumulate-funktion

Hur man snabbt och i bulk byter ut texten enligt referenslistan med formler – vi har redan löst det. Låt oss nu försöka göra det i Power Query.

Som ofta händer utföra denna uppgift är mycket lättare än att förklara varför det fungerar, men låt oss försöka göra båda 🙂

Så vi har två "smarta" dynamiska tabeller skapade från vanliga intervall med en kortkommando ctrl+T eller team Hem – Formatera som en tabell (Hem — Formatera som tabell):

Jag ringde det första bordet Data, det andra bordet – katalogmed fält Tabellnamn (Tabellnamn) fliken Konstruktör (Design).

Uppgift: ersätt i adresser i tabellen Data alla förekomster från en kolumn Att hitta Handbok till deras motsvarande korrekta motsvarigheter från kolumnen Ersättning. Resten av texten i cellerna ska förbli orörd.

Steg 1. Ladda katalogen till Power Query och förvandla den till en lista

Efter att ha ställt in den aktiva cellen på valfri plats i referenstabellen, klicka på fliken Data (Datum)eller på fliken Strömfråga (om du har en gammal version av Excel och du installerade Power Query som ett tillägg på en separat flik) på knappen Från bord/sortiment (Från tabell/intervall).

Referenstabellen kommer att laddas in i Power Query-frågeredigeraren:

För att inte störa, ett automatiskt tillagt steg modifierad typ (Ändrad typ) i den högra panelen kan de tillämpade stegen tas bort på ett säkert sätt och bara steget kvarstår Källa (Källa):

Nu, för att utföra ytterligare transformationer och ersättningar, måste vi förvandla den här tabellen till en lista (lista).

Lyrisk digression

• Bord är en tvådimensionell matris som består av flera rader och kolumner.
• Spela in (Rekord) – endimensionell array-string, bestående av flera fält-element med namn, till exempel [Namn = "Masha", Kön = "f", Ålder = 25]
  
• Lista – en endimensionell array-kolonn bestående av flera element, till exempel {1, 2, 3, 10, 42} or { "Tro hopp kärlek" }
  

För att lösa vårt problem kommer vi i första hand att vara intresserade av typen Lista.

Tricket här är att listobjekt i Power Query inte bara kan vara banala tal eller text, utan även andra listor eller poster. Det är i en så knepig lista (lista), som består av poster (poster) som vi behöver vända vår katalog. I Power Query syntaktisk notation (poster inom hakparenteser, listor inom parentes) skulle detta se ut så här:

{

    [ Hitta = "St. Petersburg", Ersätt = "St. Petersburg” ] ,

    [ Hitta = "St. Petersburg", Ersätt = "St. Petersburg” ] ,

    [ Hitta = "Peter", Ersätt = "St. Petersburg” ] ,

och så vidare

}

En sådan transformation utförs med hjälp av en speciell funktion av M-språket inbyggt i Power Query – Table.ToRecords. För att tillämpa den direkt i formelfältet, lägg till den här funktionen i stegkoden där Källa.

Det var:

Efter:

Efter att ha lagt till funktionen Table.ToRecords kommer utseendet på vår tabell att ändras – den kommer att förvandlas till en lista med poster. Innehållet i enskilda poster kan ses längst ner i vyfönstret genom att klicka i cellbakgrunden bredvid ett ord Spela in (men inte med ett enda ord!)

Utöver ovanstående är det vettigt att lägga till ytterligare en streck – för att cache (buffra) vår skapade lista. Detta kommer att tvinga Power Query att ladda vår uppslagslista en gång i minnet och inte räkna om den igen när vi senare kommer åt den för att ersätta den. För att göra detta, slå in vår formel i en annan funktion - Lista.buffert:

Sådan cachning kommer att ge en mycket märkbar ökning av hastigheten (med flera gånger!) med en stor mängd initial data som ska rensas.

Detta avslutar utarbetandet av handboken.

Det återstår att klicka på Hem – Stäng och ladda – Stäng och ladda till... (Hem — Stäng&Ladda — Stäng&Ladda till..), Välj ett alternativ Skapa bara en anslutning (Skapa bara anslutning) och återgå till Excel.

Steg 2. Laddar datatabellen

Allt är banalt här. Som tidigare med referensboken kommer vi upp till valfri plats i tabellen, klicka på fliken Data Knappen Från tabell/sortiment och vårt bord Data kommer in i Power Query. Automatiskt tillagt steg modifierad typ (Ändrad typ) du kan också ta bort:

Inga speciella förberedande åtgärder krävs för att göras med den, och vi går vidare till det viktigaste.

Steg 3. Utför byten med funktionen List.Accumulate

Låt oss lägga till en beräknad kolumn till vår datatabell med kommandot Lägga till en kolumn – Anpassad kolumn (Lägg till kolumn – Anpassad kolumn): och ange namnet på den tillagda kolumnen i fönstret som öppnas (t.ex. korrigerad adress) och vår magiska funktion Lista. Ackumulera:

Det återstår att klicka på OK – och vi får en kolumn med de ersättningar som gjorts:

Anteckna det:

• Eftersom Power Query är skiftlägeskänslig, fanns det ingen ersättning på den näst sista raden, eftersom vi i katalogen har "SPb", inte "SPb".
• Om det finns flera delsträngar som ska ersättas samtidigt i källdata (till exempel på 7:e raden måste du ersätta både "S-Pb" och "Prospekt"), så skapar detta inga problem (till skillnad från att ersätta med formler från den tidigare metoden).
• Om det inte finns något att ersätta i källtexten (nionde raden), så uppstår inga fel (till skillnad från, återigen, från ersättning med formler).

Hastigheten för en sådan begäran är mycket, mycket anständig. Till exempel, för en tabell med initiala data med en storlek på 5000 rader, uppdaterades denna fråga på mindre än en sekund (utan buffring, förresten, cirka 3 sekunder!)

Hur List.Accumulate-funktionen fungerar

I princip kan detta vara slutet (för mig att skriva, och för dig att läsa) den här artikeln. Om du inte bara vill kunna, utan också förstå hur det fungerar "under huven", måste du dyka lite djupare ner i kaninhålet och ta itu med List.Accumulate-funktionen, som gjorde hela bulkbytet arbeta för oss.

Syntaxen för denna funktion är:

=List.Accumulate(lista, frö, ackumulator)

var

• lista är listan vars element vi itererar över. 
• frö - initialtillstånd
• ackumulator – en funktion som utför någon operation (matematisk, text, etc.) på nästa element i listan och samlar resultatet av bearbetningen i en speciell variabel.
  

Generellt sett ser syntaxen för att skriva funktioner i Power Query ut så här:

(argument1, argument2, … argumentN) => några åtgärder med argument

Till exempel kan summeringsfunktionen representeras som:

(a, b) => a + b

För List.Accumulate har denna ackumulatorfunktion två obligatoriska argument (de kan heta vad som helst, men de vanliga namnen är tillstånd и ström, som i den officiella hjälpen för den här funktionen, där:

• tillstånd – en variabel där resultatet ackumuleras (dess initiala värde är det som nämns ovan frö)
  
• ström – nästa itererade värde från listan lista
  

Låt oss till exempel ta en titt på stegen i logiken för följande konstruktion:

=List.Accumulate({3, 2, 5}, 10, (tillstånd, ström) => tillstånd + ström)

Detta är den senaste ackumulerade tillstånd värdet är vår lista. Ackumulera funktion och utdata som ett resultat:

Om du fantiserar lite kan du med hjälp av List.Accumulate-funktionen simulera till exempel Excel-funktionen CONCATENATE (i Power Query kallas dess analoga Text.Kombinera) med hjälp av uttrycket:

Eller till och med sök efter maxvärdet (imitation av Excels MAX-funktion, som i Power Query kallas Lista.Max):

Men huvudfunktionen i List.Accumulate är förmågan att bearbeta inte bara enkel text eller numeriska listor som argument, utan mer komplexa objekt – till exempel listor-från-listor eller listor-från-poster (hej, Directory!)

Låt oss titta igen på konstruktionen som utförde ersättningen i vårt problem:

List.Accumulate(katalog, [Adress], (tillstånd, nuvarande) => Text.Ersätt(tillstånd, nuvarande[Sök], nuvarande[Ersätt]) )

Vad är det som händer här egentligen?

1. Som initialvärde (frö) tar vi den första klumpiga texten från kolumnen [Adress] vårt bord: 199034, St Petersburg, str. Beringa, d. 1
2. Sedan itererar List.Accumulate över elementen i listan en efter en – Handbok. Varje element i denna lista är en post som består av ett par fält "Vad ska hittas - Vad ska ersättas med" eller, med andra ord, nästa rad i katalogen.
3. Ackumulatorfunktionen lägger in en variabel tillstånd initialt värde (första adressen 199034, St Petersburg, str. Beringa, d. 1) och utför en ackumulatorfunktion på den – utbytesoperationen med standard M-funktion Text.Ersätt (analogt med Excels SUBSTITUTA-funktion). Dess syntax är:

   Text.Replace( originaltext, vad vi letar efter, vad vi ersätter med)

   och här har vi:

• tillstånd är vår smutsiga adress, som ligger i tillstånd (att komma dit därifrån frö)
  
• aktuell[Sök] – fältvärde Att hitta från nästa upprepade post i listan katalog, som ligger i variabeln ström
• nuvarande [Ersätt] – fältvärde Ersättning från nästa upprepade post i listan katalogligger i ström

Således, för varje adress, körs en hel cykel av uppräkning av alla rader i katalogen varje gång, varvid texten från [Sök]-fältet ersätts med värdet från [Ersätt]-fältet.

Hoppas du fick idén 🙂

• Bulk ersätt text i en lista med formler
  
• Reguljära uttryck (RegExp) i Power Query