Tömeges szövegcsere a Power Queryben a List.Accumulate funkcióval

Hogyan lehet gyorsan és tömegesen helyettesíteni a hivatkozási lista szerinti szöveget képletekkel – azt már rendbe hoztuk. Most próbáljuk meg megtenni a Power Queryben.

Ahogy gyakran megesik teljesít ez a feladat sokkal könnyebb, mint elmagyarázni miért működik, de próbáljuk meg mindkettőt 🙂

Tehát van két „intelligens” dinamikus táblázatunk, amelyeket hagyományos tartományokból hoztunk létre billentyűkóddal Ctrl+T vagy csapat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása):

Felhívtam az első asztalt dátum, a második asztal – Könyvtármező használata Tábla neve (A táblázat neve) lap építész (Tervezés).

Feladat: cserélje ki a címeket a táblázatban dátum minden előfordulás egy oszlopból Megtalálni Kézikönyv oszlopból a megfelelő megfelelőikhez Helyettes. A cellákban lévő szöveg többi részének érintetlennek kell maradnia.

1. lépés: Töltse be a könyvtárat a Power Query programba, és alakítsa át listává

Miután beállította az aktív cellát a referenciatábla tetszőleges helyére, kattintson a fülre dátum (Dátum)vagy a lapon Teljesítmény lekérdezés (ha az Excel régi verziója van, és a Power Query-t bővítményként telepítette egy külön lapon) a gombon Asztalból/tartományból (A táblázatból/Tartományból).

A referenciatábla betöltődik a Power Query lekérdezésszerkesztőbe:

Annak érdekében, hogy ne zavarjon, egy automatikusan hozzáadott lépés módosított típus (Módosított típus) a jobb oldali panelen az alkalmazott lépések biztonságosan törölhetők, csak a lépés marad meg forrás (Forrás):

Most a további átalakítások és cserék végrehajtásához ezt a táblát listává (listává) kell alakítanunk.

Lírai kitérő

• Táblázat egy kétdimenziós tömb, amely több sorból és oszlopból áll.
• Felvétel (Rekord) - egydimenziós tömb-karakterlánc, amely több mezőből-elemből áll, amelyek nevei, pl. [Név = „Masha”, nem = „f”, életkor = 25]
  
• Lista – egydimenziós tömboszlop, amely például több elemből áll {1, 2, 3, 10, 42} or { "Hit Remény Szeretet" }
  

Problémánk megoldásához elsősorban a típusra leszünk kíváncsiak Lista.

A trükk az, hogy a Power Query listaelemei nemcsak banális számok vagy szövegek lehetnek, hanem más listák vagy rekordok is. Egy ilyen trükkös listában (listában), amely rekordokból (rekordokból) áll, meg kell forgatnunk a könyvtárunkat. A Power Query szintaktikai jelölésében (a bejegyzések szögletes zárójelben, a listák zárójelben) így néz ki:

{

    [ Find = "St. Petersburg”, Csere = „St. Petersburg” ] ,

    [ Find = "St. Petersburg”, Csere = „St. Petersburg” ] ,

    [ Find = "Péter", Csere = "St. Petersburg” ] ,

elvisszük helyi falvakba ahol megismerkedhet az őslakosok kultúrájával; ...

}

Az ilyen átalakítás a Power Querybe épített M nyelv speciális funkciójával történik – Table.ToRecords. Ha közvetlenül a képletsorban szeretné alkalmazni, adja hozzá ezt a függvényt a lépéskódhoz forrás.

Ez volt:

Után:

A Table.ToRecords funkció hozzáadása után táblázatunk megjelenése megváltozik – rekordlistává változik. Az egyes rekordok tartalma a nézetablak alján látható, ha a cella hátterében bármelyik szó mellett kattint. Rekord (de nem egy szóval!)

A fentieken kívül érdemes még egy vonást hozzáadni – a létrehozott listánk gyorsítótárazásához (puffereléséhez). Ez arra kényszeríti a Power Queryt, hogy a keresési listánkat egyszer betöltse a memóriába, és ne számolja újra, amikor később hozzáférünk, hogy lecseréljük. Ehhez csomagolja a képletünket egy másik függvénybe - List.Puffer:

Az ilyen gyorsítótárazás nagyon észrevehető sebességnövekedést eredményez (többször!), és nagy mennyiségű kezdeti adatot kell törölni.

Ezzel befejeződik a kézikönyv elkészítése.

Már csak rá kell kattintani Kezdőlap – Bezárás és betöltés – Bezárás és betöltés… (Kezdőlap — Bezárás&Betöltés — Bezárás&Betöltés ide...), Válassz egy lehetőséget Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása) és térjen vissza az Excelhez.

2. lépés: Az adattábla betöltése

Itt minden elcsépelt. A referenciakönyvhöz hasonlóan most is feljutunk a táblázat bármely pontjára, kattintson a fülre dátum gomb Táblázatból/Tartományból és az asztalunk dátum bekerül a Power Querybe. Automatikusan hozzáadott lépés módosított típus (Módosított típus) a következőket is eltávolíthatja:

Nincs szükség különleges előkészítő műveletekre, és továbblépünk a legfontosabbra.

3. lépés: Végezze el a cseréket a List.Accumulate funkcióval

Adjunk hozzá egy számított oszlopot az adattáblázatunkhoz a paranccsal Oszlop hozzáadása – Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop): és a megnyíló ablakban írja be a hozzáadott oszlop nevét (pl. javított cím) és a mágikus funkciónk List.Felhalmoz:

Már csak rá kell kattintani OK – és kapunk egy oszlopot a végrehajtott cserékkel:

Vegye figyelembe, hogy:

• Mivel a Power Query megkülönbözteti a kis- és nagybetűket, az utolsó előtti sorban nem volt csere, mert a könyvtárban „SPb” van, nem „SPb”.
• Ha a forrásadatokban egyszerre több részkarakterláncot kell cserélni (például a 7. sorban az „S-Pb” és a „Prospektus” kifejezést is ki kell cserélni), akkor ez nem okoz problémát (ellentétben azzal, ha képletekkel helyettesítjük az előző módszer).
• Ha nincs mit cserélni a forrásszövegben (9. sor), akkor nem történik hiba (ellentétben a képletekkel való helyettesítéstől).

Egy ilyen kérés sebessége nagyon-nagyon tisztességes. Például egy 5000 soros kezdeti adatokat tartalmazó táblázat esetében ez a lekérdezés kevesebb mint egy másodperc alatt frissült (mellesleg, pufferelés nélkül, körülbelül 3 másodperc alatt!)

Hogyan működik a List.Accumulate funkció

Elvileg ezzel a cikknek a vége is lehetne (nekem írnom, te pedig elolvasni). Ha nem csak tudni akarod, hanem meg is akarod érteni, hogyan működik „a motorháztető alatt”, akkor egy kicsit mélyebbre kell merülned a nyúlüregben, és foglalkoznod kell a List.Accumulate funkcióval, amely az összes tömeges cserét elvégezte. dolgozzon nekünk.

Ennek a függvénynek a szintaxisa a következő:

=List.Accumulate(lista, mag, akkumulátor)

ahol

• lista az a lista, amelynek elemeit iteráljuk. 
• mag - kezdeti állapot
• akkumulátor – olyan függvény, amely a lista következő elemén valamilyen műveletet (matematikai, szöveges stb.) hajt végre, és a feldolgozás eredményét egy speciális változóban halmozza fel.
  

Általánosságban elmondható, hogy a Power Queryben a függvények írásának szintaxisa így néz ki:

(argumentum1, argumentum2, … argumentN) => néhány művelet érvekkel

Például az összegzési függvény a következőképpen ábrázolható:

(a, b) => a + b

A List.Accumulate esetén ennek az akkumulátor függvénynek két kötelező argumentuma van (bármilyen elnevezést kaphat, de a szokásos nevek voltak и jelenlegi, mint a funkció hivatalos súgójában, ahol:

• voltak – egy változó, ahol az eredmény halmozódik (a kezdeti értéke a fent említett mag)
  
• jelenlegi – a következő iterált érték a listából lista
  

Nézzük például a következő konstrukció logikájának lépéseit:

=List.Accumulate({3, 2, 5}, 10, (állapot, áram) => állapot + áram)

Ez a legújabb felhalmozott voltak az érték a List.Accumulate függvényünk, és ennek eredményeként adjuk ki:

Ha kicsit fantáziálunk, akkor a List.Accumulate függvény segítségével szimulálhatjuk például az Excel CONCATENATE függvényét (a Power Queryben ennek analógja az ún. Szöveg.Össze) a következő kifejezéssel:

Vagy akár keresse meg a maximális értéket (az Excel MAX függvényének utánzata, amelyet a Power Queryben hívnak List.Max):

A List.Accumulate fő jellemzője azonban, hogy nem csak egyszerű szöveges vagy numerikus listákat tud argumentumként feldolgozni, hanem összetettebb objektumokat is – például listákat a listákból vagy listákat a rekordokból (hello, Directory!)

Nézzük még egyszer azt a konstrukciót, amely a problémánkban a cserét végrehajtotta:

List.Accumulate(Könyvtár, [Cím], (állapot,aktuális) => Szöveg.Csere(állapot, aktuális[Keresés], aktuális[Csere]) )

Mi folyik itt valójában?

1. Kezdő értékként (mag) vesszük az első ügyetlen szöveget az oszlopból [Cím] asztalunk: 199034, Szentpétervár, str. Beringa, d. 1
2. Ezután a List.Accumulate egyenként iterálja a lista elemeit – Kézikönyv. A lista minden eleme egy rekord, amely egy pár mezőből áll: „Mit kell találni – Mit kell cserélni” vagy más szóval a könyvtár következő sorából.
3. Az akkumulátor függvény változóba helyezi voltak kezdeti érték (első cím 199034, Szentpétervár, str. Beringa, d. 1) és egy akkumulátor funkciót hajt végre rajta – a csereműveletet a szabványos M-függvény segítségével Szöveg.Csere (Analóg az Excel SUBSTITUTE függvényével). A szintaxisa a következő:

   Szöveg.Csere( eredeti szöveg, mit keresünk, mit cserélünk le )

   és itt van:

• voltak a piszkos címünk, ami benne rejlik voltak (ahonnan eljutunk mag)
  
• jelenlegi[Keresés] – mező értéke Megtalálni a lista következő iterált bejegyzésétől Könyvtár, ami a változóban rejlik jelenlegi
• jelenlegi[Csere] – mező értéke Helyettes a lista következő iterált bejegyzésétől Könyvtárfekve jelenlegi

Így minden címre a könyvtár összes sorának teljes felsorolási ciklusa fut le, és a [Keresés] mező szövegét a [Csere] mező értékére cseréli.

Remélem értitek az ötletet 🙂

• A lista szövegének tömeges cseréje képletekkel
  
• Reguláris kifejezések (RegExp) a Power Queryben