Joukkotekstin korvaaminen Power Queryssä List.Accumulate-toiminnolla

Kuinka nopeasti ja joukkona korvata viiteluettelon mukainen teksti kaavoilla – olemme jo selvittäneet sen. Yritetään nyt tehdä se Power Queryssä.

Kuten usein tapahtuu suorittaa tämä tehtävä on paljon helpompi kuin selittää miksi se toimii, mutta yritetään tehdä molemmat 🙂

Meillä on siis kaksi "älykästä" dynaamista taulukkoa, jotka on luotu tavallisista alueista pikanäppäimellä Ctrl+T tai joukkue Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona):

Soitin ensimmäiseen pöytään Päiväys, toinen pöytä - hakemistokäyttämällä kenttää Taulukon nimi (taulukon nimi) kieleke Rakentaja (Design).

Tehtävä: korvaa osoitteet taulukossa Päiväys kaikki esiintymät sarakkeesta Löytää Käsikirja vastaaviin oikeisiin kollegoihinsa sarakkeesta korvike. Solujen muun tekstin tulee pysyä koskemattomana.

Vaihe 1. Lataa hakemisto Power Queryyn ja muuta se luetteloksi

Kun olet asettanut aktiivisen solun mihin tahansa kohtaan viitetaulukossa, napsauta välilehteä Päiväys (Päivämäärä)tai välilehdellä Tehokysely (jos sinulla on vanha Excel-versio ja olet asentanut Power Queryn apuohjelmana erilliselle välilehdelle) Pöydästä/valikoimasta (taulukosta/alueelta).

Viitetaulukko ladataan Power Queryn kyselyeditoriin:

Jotta ei häiritse, automaattisesti lisätty vaihe muokattu tyyppi (Muutettu tyyppi) oikeanpuoleisessa paneelissa käytetyt vaiheet voidaan poistaa turvallisesti jättäen vain vaiheen lähde (Lähde):

Nyt, jotta voimme suorittaa lisämuunnoksia ja korvauksia, meidän on muutettava tämä taulukko luetteloksi (luetteloksi).

Lyyrinen poikkeama

• Pöytä on kaksiulotteinen taulukko, joka koostuu useista riveistä ja sarakkeista.
• Record (ennätys) – yksiulotteinen taulukkomerkkijono, joka koostuu useista kentistä-elementeistä, joissa on nimiä, esim. [Nimi = "Masha", sukupuoli = "f", ikä = 25]
  
• Lista – yksiulotteinen taulukkosarake, joka koostuu esimerkiksi useista elementeistä {1, 2, 3, 10, 42} or { "Usko toivo rakkaus" }
  

Ongelmamme ratkaisemiseksi olemme ensisijaisesti kiinnostuneita tyypistä Lista.

Temppu tässä on se, että Power Queryn luettelokohteet voivat olla paitsi banaalisia numeroita tai tekstiä, myös muita luetteloita tai tietueita. Meidän on käännettävä hakemistomme niin hankalassa luettelossa (luettelossa), joka koostuu tietueista (tietueista). Power Queryn syntaktisessa merkinnässä (merkinnät hakasulkeissa, luettelot hakasulkeissa) tämä näyttäisi tältä:

{

    [ Etsi = "St. Pietari", Korvaa = "St. Pietari"] ,

    [ Etsi = "St. Pietari", Korvaa = "St. Pietari"] ,

    [ Etsi = "Pietari", Korvaa = "St. Pietari"] ,

ja niin edelleen

}

Tällainen muunnos suoritetaan Power Queryyn sisäänrakennetun M-kielen erikoistoiminnolla – Table.ToRecords. Jos haluat käyttää sitä suoraan kaavapalkissa, lisää tämä funktio siellä olevaan vaihekoodiin lähde.

Se oli:

Jälkeen:

Kun Table.ToRecords-toiminto on lisätty, taulukkomme ulkoasu muuttuu – se muuttuu tietueluetteloksi. Yksittäisten tietueiden sisältö näkyy näkymäruudun alareunassa napsauttamalla solun taustaa minkä tahansa sanan vieressä ennätys (mutta ei yhdellä sanalla!)

Yllämainittujen lisäksi on järkevää lisätä vielä yksi veto – luodun listamme välimuistiin (puskurointiin). Tämä pakottaa Power Queryn lataamaan hakuluettelomme kerran muistiin eikä laskemaan sitä uudelleen, kun käytämme sitä myöhemmin korvataksemme sen. Tee tämä käärimällä kaavamme toiseen funktioon - List.Buffer:

Tällainen välimuisti lisää nopeutta huomattavasti (useita kertoja!) ja tyhjentää suuren määrän alkutietoja.

Tämä päättää käsikirjan valmistelun.

Jäljelle jää klikata Etusivu – Sulje ja lataa – Sulje ja lataa… (Etusivu — Sulje&Lataa — Sulje&Lataa kohteeseen...), Valitse vaihtoehto Luo vain yhteys (Luo vain yhteys) ja palaa Exceliin.

Vaihe 2. Tietotaulukon lataaminen

Täällä kaikki on tylsää. Kuten aiemmin hakuteoksen kanssa, pääsemme mihin tahansa kohtaan taulukossa, napsauta välilehteä Päiväys nappia Taulukosta/alueesta ja meidän pöytään Päiväys pääsee Power Queryyn. Automaattisesti lisätty vaihe muokattu tyyppi (Muutettu tyyppi) voit myös poistaa:

Sen kanssa ei vaadita erityisiä valmistelutoimia, ja siirrymme tärkeimpään.

Vaihe 3. Suorita vaihdot List.Accumulate-toiminnolla

Lisätään laskettu sarake tietotaulukkoomme komennolla Sarakkeen lisääminen – Mukautettu sarake (Lisää sarake — Muokattu sarake): ja kirjoita lisätyn sarakkeen nimi avautuvaan ikkunaan (esim. korjattu osoite) ja maaginen toimintomme Listaa. Kerää:

Jäljelle jää klikata OK – ja saamme sarakkeen, jossa on tehdyt vaihdot:

Ota huomioon, että:

• Koska Power Query on isojen ja pienten kirjainten välinen ero, toiseksi viimeisellä rivillä ei ollut korvausta, koska hakemistossa on "SPb", ei "SPb".
• Jos lähdetiedoissa on useita korvattavia alimerkkijonoja kerralla (esimerkiksi 7. rivillä sinun on korvattava sekä "S-Pb" että "Esite"), tämä ei aiheuta ongelmia (toisin kuin korvaaminen kaavoilla edellinen menetelmä).
• Jos lähdetekstissä (9. rivi) ei ole mitään korvattavaa, virheitä ei tapahdu (toisin kuin korvaaminen kaavoilla).

Tällaisen pyynnön nopeus on erittäin, erittäin kohtuullinen. Esimerkiksi 5000 rivin alkutietojen taulukossa tämä kysely päivitettiin alle sekunnissa (ilman puskurointia, muuten noin 3 sekunnissa!)

Kuinka List.Accumulate-toiminto toimii

Periaatteessa tämä voisi olla tämän artikkelin loppu (minä kirjoitan ja sinä luet). Jos haluat paitsi pystyä, myös ymmärtää, kuinka se toimii "konepellin alla", sinun täytyy sukeltaa hieman syvemmälle kanin reikään ja käsitellä List.Accumulate-toimintoa, joka teki kaiken bulkkivaihdon. työskentele meille.

Tämän funktion syntaksi on:

=List.Accumulate(lista, siemenet, akku)

jossa

• lista on luettelo, jonka elementtejä toistamme. 
• siemenet -alkutila
• akku – funktio, joka suorittaa jonkin toiminnon (matemaattinen, teksti jne.) listan seuraavalle elementille ja kerää käsittelyn tuloksen erityiseen muuttujaan.
  

Yleisesti ottaen Power Queryn kirjoitusfunktioiden syntaksi näyttää tältä:

(argumentti1, argumentti2, … argumenttiN) => jotkin toiminnot argumenteilla

Summafunktio voitaisiin esittää esimerkiksi seuraavasti:

(a, b) => a + b

List.Accumulate :ssä tässä akkufunktiossa on kaksi vaadittua argumenttia (niille voidaan nimetä mikä tahansa, mutta tavalliset nimet ovat olivat и nykyinen, kuten tämän toiminnon virallisessa ohjeessa, jossa:

• olivat – muuttuja, johon tulos kerätään (sen alkuarvo on edellä mainittu siemenet)
  
• nykyinen – seuraava iteroitu arvo luettelosta lista
  

Katsotaanpa esimerkiksi seuraavan konstruktion logiikan vaiheita:

=List.Accumulate({3, 2, 5}, 10, (tila, virta) => tila + virta)

Tämä on viimeisin kertynyt olivat arvo on List.Accumulate-funktiomme ja tulostaa sen seurauksena:

Jos vähän fantasioi, niin List.Accumulate-funktiolla voit simuloida esimerkiksi Excelin CONCATENATE-funktiota (Power Queryssä sen analogia kutsutaan ns. Teksti.Yhdistä) käyttämällä lauseketta:

Tai jopa etsi maksimiarvoa (Excelin MAX-funktion jäljitelmä, jota kutsutaan Power Queryssä List.Max):

List.Accumulate-sovelluksen pääominaisuus on kuitenkin kyky käsitellä yksinkertaisen tekstin tai numeeristen luetteloiden lisäksi argumentteina monimutkaisempia objekteja - esimerkiksi listoja luetteloista tai luetteloita tietueista (hei, hakemisto!)

Katsotaanpa uudelleen rakennetta, joka suoritti vaihdon ongelmassamme:

List.Accumulate(hakemisto, [Osoite], (tila,nykyinen) => Teksti.Korvaa(tila, nykyinen[Etsi], nykyinen[Korvaa]) )

Mitä täällä oikein tapahtuu?

1. Alkuarvona (siemenet) otamme sarakkeen ensimmäisen kömpelön tekstin [Osoite] meidän pöytä: 199034, Pietari, str. Beringa, d. 1
2. Sitten List.Accumulate iteroi luettelon elementit yksitellen – Käsikirja. Jokainen tämän luettelon elementti on tietue, joka koostuu parista kentistä "Mitä löytää – Millä korvataan" tai toisin sanoen hakemiston seuraavasta rivistä.
3. Akkutoiminto asettaa muuttujan olivat alkuarvo (ensimmäinen osoite 199034, Pietari, str. Beringa, d. 1) ja suorittaa siinä akkutoiminnon – vaihtotoiminnon käyttämällä standardia M-toimintoa Teksti.Korvaa (analogisesti Excelin SUBSTITUTE-funktion kanssa). Sen syntaksi on:

   Text.Replace(alkuperäinen teksti, mitä etsimme, millä korvaamme )

   ja tässä meillä:

• olivat on likainen osoitteemme, joka sijaitsee olivat (päästä sinne siemenet)
  
• nykyinen[haku] – kentän arvo Löytää luettelon seuraavasta iteroidusta merkinnästä hakemisto, joka sijaitsee muuttujassa nykyinen
• nykyinen[Korvaa] – kentän arvo korvike luettelon seuraavasta iteroidusta merkinnästä hakemistomakaa nykyinen

Siten jokaiselle osoitteelle suoritetaan joka kerta hakemiston kaikkien rivien luettelointisykli, jossa [Etsi]-kentän teksti korvataan [Korvaa]-kentän arvolla.

Toivottavasti sait idean 🙂

• Joukkokorvaa luettelon tekstiä kaavoilla
  
• Säännölliset lausekkeet (RegExp) Power Queryssa