Masinis teksto pakeitimas Power Query naudojant funkciją List.Accumulate

Kaip greitai ir masiškai pakeisti tekstą pagal nuorodų sąrašą formulėmis – jau sutvarkėme. Dabar pabandykime tai padaryti naudodami „Power Query“.

Kaip dažnai nutinka atlikti ši užduotis yra daug lengviau nei paaiškinti kodėl tai veikia, bet pabandykime padaryti abu 🙂

Taigi, turime dvi „protingas“ dinamines lenteles, sukurtas iš įprastų diapazonų su sparčiuoju klavišu "Ctrl"+T arba komanda Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę):

Paskambinau į pirmą stalą Duomenys, antra lentelė - katalogasnaudojant lauką Lentelės pavadinimas (Lentelės pavadinimas) kortelė Konstruktorius (Dizainas).

Užduotis: pakeiskite adresus lentelėje Duomenys visi įvykiai iš stulpelio Rasti Vadovas į atitinkamus teisingus atitikmenis iš stulpelio Pavaduojantis narys. Likusi teksto dalis langeliuose turėtų likti nepaliesta.

1 veiksmas. Įkelkite katalogą į Power Query ir paverskite jį sąrašu

Nustatę aktyvų langelį į bet kurią nuorodos lentelės vietą, spustelėkite skirtuką Duomenys (Data)arba skirtuke „Power Query“ (jei turite seną „Excel“ versiją ir „Power Query“ įdiegėte kaip priedą atskirame skirtuke) ant mygtuko Iš stalo/asortimento (Iš lentelės / diapazono).

Nuorodų lentelė bus įkelta į Power Query užklausų rengyklę:

Kad netrukdytų, automatiškai pridedamas žingsnis modifikuotas tipas (Pakeistas tipas) dešiniajame skydelyje pritaikytus veiksmus galima saugiai ištrinti, paliekant tik žingsnį Šaltinis (Šaltinis):

Dabar, norėdami atlikti tolesnius pakeitimus ir pakeitimus, turime paversti šią lentelę sąrašu (sąrašu).

Lyrinis nukrypimas

• Lentelė yra dvimatis masyvas, susidedantis iš kelių eilučių ir stulpelių.
• Įrašas (įrašas) – vienmatė masyvo eilutė, susidedanti iš kelių laukų-elementų su pavadinimais, pvz. [Vardas = "Maša", lytis = "f", amžius = 25]
  
• sąrašas – vienmatis masyvas-stulpelis, susidedantis iš kelių elementų, pavyzdžiui {1, 2, 3, 10, 42} or { "Tikėjimas Viltis Meilė" }
  

Norėdami išspręsti savo problemą, pirmiausia domėsimės tipu sąrašas.

Apgaulė čia ta, kad Power Query sąrašo elementai gali būti ne tik banalūs skaičiai ar tekstas, bet ir kiti sąrašai ar įrašai. Būtent tokiame sudėtingame sąraše (sąraše), kurį sudaro įrašai (įrašai), turime paversti savo katalogą. „Power Query“ sintaksėje (įrašai laužtiniuose skliaustuose, sąrašai lenktuose skliaustuose) atrodytų taip:

{

    [ Rasti = „Šv. Sankt Peterburgas“, Pakeisti = „Šv. Sankt Peterburgas“] ,

    [ Rasti = „Šv. Sankt Peterburgas“, Pakeisti = „Šv. Sankt Peterburgas“] ,

    [ Rasti = "Petras", Pakeisti = "Šv. Sankt Peterburgas“] ,

ir taip toliau

}

Tokia transformacija atliekama naudojant specialią „Power Query“ integruotos M kalbos funkciją – Lentelė.ToRecords. Norėdami pritaikyti ją tiesiai formulės juostoje, pridėkite šią funkciją prie ten esančio veiksmo kodo Šaltinis.

Tai buvo:

Po:

Pridėjus funkciją Table.ToRecords, pasikeis mūsų lentelės išvaizda – ji virs įrašų sąrašu. Atskirų įrašų turinį galima pamatyti peržiūros srities apačioje spustelėjus langelio fone šalia bet kurio žodžio Įrašas (bet ne vienu žodžiu!)

Be to, kas išdėstyta aukščiau, prasminga pridėti dar vieną potėpį – į talpyklą (buferį) mūsų sukurtą sąrašą. Tai privers „Power Query“ vieną kartą įkelti mūsų paieškos sąrašą į atmintį ir neperskaičiuoti jo dar kartą, kai vėliau pasieksime jį, kad jį pakeistume. Norėdami tai padaryti, apvyniokite mūsų formulę į kitą funkciją - Sąrašas.Buferis:

Toks kaupimas talpykloje labai pastebimai padidins greitį (kelis kartus!), kai bus išvalytas didelis pradinių duomenų kiekis.

Tai baigia parengti vadovą.

Belieka spustelėti Pagrindinis – uždaryti ir įkelti – uždaryti ir įkelti… (Pagrindinis – Uždaryti&Įkelti – Uždaryti&Įkelti į...), pasirinkite parinktį Tiesiog sukurkite ryšį (Tik sukurti ryšį) ir grįžkite į „Excel“.

2 veiksmas. Duomenų lentelės įkėlimas

Viskas čia banalu. Kaip ir anksčiau su žinynu, mes patenkame į bet kurią lentelės vietą, spustelėkite skirtuką Duomenys mygtukas Iš lentelės/diapazono ir mūsų stalas Duomenys patenka į „Power Query“. Automatiškai pridėtas veiksmas modifikuotas tipas (Pakeistas tipas) taip pat galite pašalinti:

Jokių specialių parengiamųjų veiksmų su juo atlikti nereikia, o pereiname prie svarbiausio.

3 veiksmas. Atlikite pakeitimus naudodami funkciją List.Accumulate

Naudodami komandą įtraukime apskaičiuotą stulpelį į duomenų lentelę Stulpelio pridėjimas – pasirinktinis stulpelis (Pridėti stulpelį – tinkintas stulpelis): ir atsidariusiame lange įveskite pridėto stulpelio pavadinimą (pvz., pataisytas adresas) ir mūsų magiškoji funkcija Sąrašas.kaupti:

Belieka spustelėti OK – ir gauname stulpelį su atliktais pakeitimais:

Prisimink tai:

• Kadangi „Power Query“ yra didžiosios ir mažosios raidės, priešpaskutinėje eilutėje pakeitimo nebuvo, nes kataloge turime „SPb“, o ne „SPb“.
• Jei šaltinio duomenyse vienu metu reikia pakeisti kelias eilutes (pvz., 7 eilutėje reikia pakeisti ir „S-Pb“, ir „Prospektas“), tai nesukelia jokių problemų (skirtingai nei pakeitus formulėmis iš ankstesnis metodas).
• Jei šaltinio tekste nėra ką pakeisti (9 eilutė), tada klaidų neįvyksta (kitaip nei pakeitus formulėmis).

Tokio prašymo greitis yra labai, labai padorus. Pavyzdžiui, 5000 eilučių pradinių duomenų lentelės užklausa buvo atnaujinta greičiau nei per sekundę (be buferio, beje, apie 3 sekundes!)

Kaip veikia funkcija List.Accumulate

Iš principo tai galėtų būti šio straipsnio pabaiga (man parašyti, o jums – skaityti). Jei norite ne tik sugebėti, bet ir suprasti, kaip tai veikia „po gaubtu“, tuomet turėsite pasinerti šiek tiek giliau į triušio duobę ir susitvarkyti su List.Accumulate funkcija, kuri atliko visą masinį pakeitimą. dirbk pas mus.

Šios funkcijos sintaksė yra tokia:

=List.Accumulate(sąrašas, sėklos, Akumuliatorius)

kur

• sąrašas yra sąrašas, kurio elementus kartojame. 
• sėklos – pradinė būsena
• Akumuliatorius – funkcija, kuri atlieka tam tikrą operaciją (matematinę, tekstinę ir pan.) kitame sąrašo elemente ir kaupia apdorojimo rezultatą specialiame kintamajame.
  

Apskritai „Power Query“ funkcijų rašymo sintaksė atrodo taip:

(argumentas1, argumentas2, … argumentasN) => kai kurie veiksmai su argumentais

Pavyzdžiui, sumavimo funkcija gali būti pavaizduota taip:

(a, b) => a + b

List.Accumulate ši kaupimo funkcija turi du būtinus argumentus (juos galima pavadinti bet kuo, bet įprasti pavadinimai yra valstybės и dabartinis, kaip ir oficialioje šios funkcijos žinyne, kur:

• valstybės – kintamasis, kuriame kaupiamas rezultatas (jo pradinė reikšmė yra tokia, kaip minėta aukščiau sėklos)
  
• dabartinis – kita pasikartojanti reikšmė iš sąrašo sąrašas
  

Pavyzdžiui, pažvelkime į šios konstrukcijos logikos žingsnius:

=List.Accumulate({3, 2, 5}, 10, (būsena, srovė) => būsena + srovė)

Tai naujausias sukauptas valstybės reikšmė yra mūsų List.Accumulate funkcija ir išveda kaip rezultatas:

Jei šiek tiek fantazuojate, tada naudodami funkciją List.Accumulate galite imituoti, pavyzdžiui, Excel funkciją CONCATENATE (Power Query jos analogas vadinamas Tekstas.Sujungti) naudojant posakį:

Ar net ieškokite didžiausios vertės ("Excel" MAX funkcijos imitacija, kuri "Power Query" vadinama Sąrašas.Maks):

Tačiau pagrindinė List.Accumulate savybė yra galimybė kaip argumentus apdoroti ne tik paprastą tekstą ar skaitinius sąrašus, bet ir sudėtingesnius objektus – pavyzdžiui, sąrašus iš sąrašų arba sąrašus iš įrašų (labas, katalogas!)

Dar kartą pažvelkime į konstrukciją, kuri atliko mūsų problemos pakeitimą:

List.Accumulate(katalogas, [Adresas], (būsena,dabartinė) => Tekstas.Pakeisti(būsena, dabartinė[Rasti], dabartinė[Pakeisti]) )

Kas čia iš tikrųjų vyksta?

1. Kaip pradinė vertė (sėklos) paimame pirmą gremėzdišką tekstą iš stulpelio [Adresas] mūsų stalas: 199034, Sankt Peterburgas, g. Beringa, gyv. 1
2. Tada List.Accumulate kartoja sąrašo elementus po vieną – Vadovas. Kiekvienas šio sąrašo elementas yra įrašas, susidedantis iš poros laukelių „Ką rasti – kuo pakeisti“ arba, kitaip tariant, kitos katalogo eilutės.
3. Akumuliatoriaus funkcija įdeda į kintamąjį valstybės pradinė vertė (pirmasis adresas 199034, Sankt Peterburgas, g. Beringa, gyv. 1) ir jame atlieka akumuliatoriaus funkciją – pakeitimo operaciją naudojant standartinę M funkciją Tekstas.Pakeisti (analogiškai Excel funkcijai SUBSTITUTE). Jo sintaksė yra tokia:

   Text.Replace( originalus tekstas, ko ieškome, kuo keičiame )

   o štai mes turime:

• valstybės yra mūsų nešvarus adresas, kuris slypi valstybės (atvykstant iš sėklos)
  
• dabartinė [paieška] – lauko reikšmė Rasti nuo kito kartojamo sąrašo įrašo katalogas, kuris yra kintamajame dabartinis
• dabartinis[Pakeisti] – lauko reikšmė Pavaduojantis narys nuo kito kartojamo sąrašo įrašo katalogasgulėdamas dabartinis

Taigi kiekvienam adresui kiekvieną kartą paleidžiamas visas visų katalogo eilučių surašymo ciklas, pakeičiant lauke [Rasti] esantį tekstą reikšme iš lauko [Replace].

Tikiuosi supratote idėją 🙂

• Masinis teksto keitimas sąraše naudojant formules
  
• Reguliarūs reiškiniai (RegExp) programoje Power Query