Amnewid testun swmp yn Power Query gyda swyddogaeth List.Accumulate

Sut i newid y testun yn gyflym ac mewn swmp yn ôl y rhestr gyfeirio gyda fformiwlâu - rydym eisoes wedi ei ddatrys. Nawr, gadewch i ni geisio ei wneud yn Power Query.

Fel sy'n digwydd yn aml perfformio mae'r dasg hon yn llawer haws nag egluro pam mae'n gweithio, ond gadewch i ni geisio gwneud y ddau 🙂

Felly, mae gennym ddau dabl deinamig “smart” wedi'u creu o ystodau cyffredin gyda llwybr byr bysellfwrdd Ctrl+T neu dîm Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl):

Gelwais y bwrdd cyntaf Dyddiad, yr ail dabl - Cyfeiriadurgan ddefnyddio maes Enw'r tabl (Enw tabl) tab Constructor (Dylunio).

Tasg: disodli mewn cyfeiriadau yn y tabl Dyddiad pob digwyddiad o golofn i ddod o hyd Llawlyfr i'w gwyr cyfatebol cywir o'r golofn Dirprwy. Dylai gweddill y testun yn y celloedd aros heb ei gyffwrdd.

Cam 1. Llwythwch y cyfeiriadur i mewn i Power Query a'i droi'n rhestr

Ar ôl gosod y gell weithredol i unrhyw le yn y tabl cyfeirio, cliciwch ar y tab Dyddiad (Dyddiad)neu ar y tab Ymholiad Pwer (os oes gennych hen fersiwn o Excel a'ch bod wedi gosod Power Query fel ychwanegiad ar dab ar wahân) ar y botwm O'r bwrdd/ystod (O'r Tabl / Ystod).

Bydd y tabl cyfeirio yn cael ei lwytho i mewn i olygydd ymholiad Power Query:

Er mwyn peidio ag ymyrryd, cam a ychwanegir yn awtomatig math wedi'i addasu (Math wedi'i Newid) yn y panel cywir, gellir dileu'r camau cymhwysol yn ddiogel, gan adael y cam yn unig ffynhonnell (Ffynhonnell):

Nawr, i berfformio trawsnewidiadau ac amnewidiadau pellach, mae angen i ni droi'r tabl hwn yn rhestr (rhestr).

Treuliad telynegol

• Tabl yn arae dau ddimensiwn sy'n cynnwys nifer o resi a cholofnau.
• Cofnod (Record) – llinyn arae un-dimensiwn, yn cynnwys sawl maes-elfen gydag enwau, er enghraifft [Enw = “Masha”, Rhyw = “f”, Oedran = 25]
  
• rhestr – colofn arae un-dimensiwn, sy'n cynnwys sawl elfen, er enghraifft {1, 2, 3, 10, 42} or { “Ffydd Gobaith Cariad” }
  

I ddatrys ein problem, bydd gennym ddiddordeb yn bennaf yn y math rhestr.

Y tric yma yw y gall eitemau rhestr yn Power Query fod nid yn unig yn rhifau banal neu'n destun, ond hefyd yn rhestrau neu'n gofnodion eraill. Mae mewn rhestr mor anodd (rhestr), sy'n cynnwys cofnodion (cofnodion) y mae angen i ni droi ein cyfeiriadur. Yn nodiant cystrawenol Power Query (cofnod mewn cromfachau sgwâr, rhestrau mewn cromfachau cyrliog) byddai hyn yn edrych fel:

{

    [ Darganfod = “St. Petersburg”, Replace = “St. Petersburg"] ,

    [ Darganfod = “St. Petersburg”, Replace = “St. Petersburg"] ,

    [ Darganfod = “Pedr”, Disodli = “St. Petersburg"] ,

ac ati

}

Mae trawsnewidiad o'r fath yn cael ei berfformio gan ddefnyddio swyddogaeth arbennig o'r iaith M sydd wedi'i chynnwys yn Power Query - Tabl.ToRecords. Er mwyn ei gymhwyso'n uniongyrchol yn y bar fformiwla, ychwanegwch y swyddogaeth hon at y cod cam yno ffynhonnell.

Yr oedd:

Ar ôl:

Ar ôl ychwanegu'r swyddogaeth Table.ToRecords, bydd ymddangosiad ein tabl yn newid - bydd yn troi'n rhestr o gofnodion. Mae cynnwys cofnodion unigol i'w weld ar waelod y cwarel gweld trwy glicio yng nghefndir y gell wrth ymyl unrhyw air cofnod (ond nid mewn un gair!)

Yn ogystal â'r uchod, mae'n gwneud synnwyr ychwanegu un strôc arall - i storio (byffer) ein rhestr a grëwyd. Bydd hyn yn gorfodi Power Query i lwytho ein rhestr chwilio unwaith i'r cof a pheidio â'i hailgyfrifo eto pan fyddwn yn ei chyrchu yn ddiweddarach i'w disodli. I wneud hyn, lapiwch ein fformiwla mewn swyddogaeth arall - Rhestr.Buffer:

Bydd caching o'r fath yn rhoi cynnydd amlwg iawn mewn cyflymder (o sawl gwaith!) gyda llawer iawn o ddata cychwynnol i'w glirio.

Mae hyn yn cwblhau'r gwaith o baratoi'r llawlyfr.

Mae'n parhau i fod i glicio ar Cartref - Cau a Llwythwch - Cau a Llwythwch i… (Cartref - Cau a Llwyth - Cau a Llwythwch i..), dewiswch opsiwn Dim ond creu cysylltiad (Dim ond creu cysylltiad) a dychwelyd i Excel.

Cam 2. Llwytho'r tabl data

Mae popeth yn drite yma. Fel o'r blaen gyda'r cyfeirlyfr, rydyn ni'n cyrraedd unrhyw le yn y tabl, cliciwch ar y tab Dyddiad botwm O'r Tabl / Ystod a'n bwrdd Dyddiad yn mynd i mewn i Power Query. Ychwanegwyd cam yn awtomatig math wedi'i addasu (Math wedi'i Newid) gallwch hefyd gael gwared ar:

Nid oes angen unrhyw gamau paratoadol arbennig gydag ef, a symudwn ymlaen at y peth pwysicaf.

Cam 3. Perfformio amnewidiadau gan ddefnyddio'r swyddogaeth List.Accumulate

Gadewch i ni ychwanegu colofn wedi'i chyfrifo at ein tabl data gan ddefnyddio'r gorchymyn Ychwanegu Colofn - Colofn Custom (Ychwanegu colofn - colofn personol): a rhowch enw'r golofn ychwanegol yn y ffenestr sy'n agor (er enghraifft, cyfeiriad wedi'i gywiro) a'n swyddogaeth hud Rhestr.Accumulate:

Mae'n parhau i fod i glicio ar OK – ac rydym yn cael colofn gyda'r rhai newydd wedi'u gwneud:

Noder:

• Gan fod Power Query yn sensitif i achosion, nid oedd unrhyw ddisodli yn y llinell olaf ond un, oherwydd yn y cyfeiriadur mae gennym “SPb”, nid “SPb”.
• Os oes sawl is-linyn i'w disodli ar unwaith yn y data ffynhonnell (er enghraifft, yn y 7fed llinell mae angen i chi ddisodli "S-Pb" a "Prospectus"), yna nid yw hyn yn creu unrhyw broblemau (yn wahanol i ddisodli gyda fformiwlâu o y dull blaenorol).
• Os nad oes unrhyw beth i'w ddisodli yn y testun ffynhonnell (9fed llinell), yna nid oes unrhyw wallau (yn wahanol i, unwaith eto, o ddisodli gan fformiwlâu).

Mae cyflymder cais o'r fath yn weddus iawn, iawn. Er enghraifft, ar gyfer tabl o ddata cychwynnol gyda maint o 5000 rhesi, diweddarwyd yr ymholiad hwn mewn llai nag eiliad (heb glustogi, gyda llaw, tua 3 eiliad!)

Sut mae swyddogaeth List.Accumulate yn gweithio

Mewn egwyddor, gallai hyn fod yn ddiwedd (i mi ysgrifennu, ac i chi ei ddarllen) yr erthygl hon. Os ydych chi eisiau nid yn unig allu, ond hefyd ddeall sut mae'n gweithio “o dan y cwfl”, yna bydd yn rhaid i chi blymio ychydig yn ddyfnach i mewn i'r twll cwningen a delio â'r swyddogaeth List.Accumulate, a wnaeth yr holl ailosod swmp gweithio i ni.

Y gystrawen ar gyfer y swyddogaeth hon yw:

=Rhestr.Cronni(rhestr, hadau, cronnwr)

lle

• rhestr yw'r rhestr yr ydym yn ailadrodd ei elfennau. 
• hadau - cyflwr cychwynnol
• cronnwr – swyddogaeth sy'n cyflawni rhywfaint o weithrediad (mathemategol, testun, ac ati) ar elfen nesaf y rhestr ac sy'n cronni canlyniad prosesu mewn newidyn arbennig.
  

Yn gyffredinol, mae'r gystrawen ar gyfer ysgrifennu swyddogaethau yn Power Query yn edrych fel hyn:

(dadl1, dadl2, …argymhelliadN) => rhai gweithredoedd gyda dadleuon

Er enghraifft, gellid cynrychioli'r swyddogaeth grynhoi fel:

(a, b) => a + b

Ar gyfer List.Accumulate , mae gan y ffwythiant cronadur hwn ddwy ddadl ofynnol (gellir eu henwi'n unrhyw beth, ond yr enwau arferol yw Roedd и ar hyn o bryd, fel yn y cymorth swyddogol ar gyfer y swyddogaeth hon, lle:

• Roedd – newidyn lle mae'r canlyniad wedi'i gronni (ei werth cychwynnol yw'r un a grybwyllir uchod hadau)
  
• ar hyn o bryd – y gwerth ailadroddol nesaf o'r rhestr rhestr
  

Er enghraifft, gadewch i ni edrych ar gamau rhesymeg y lluniad canlynol:

=Rhestr.Cronni({3, 2, 5}, 10, (cyflwr, cerrynt) => cyflwr + cerrynt)

Dyma'r diweddaraf cronedig Roedd y gwerth yw ein swyddogaeth List.Accumulate ac allbynnau o ganlyniad:

Os ydych chi'n ffantasi ychydig, yna gan ddefnyddio'r swyddogaeth List.Accumulate, gallwch chi efelychu, er enghraifft, y swyddogaeth Excel CONCATENATE (yn Power Query, gelwir ei analog Testun.Combine) gan ddefnyddio'r ymadrodd:

Neu hyd yn oed chwilio am y gwerth mwyaf (dynwared swyddogaeth MAX Excel, a elwir yn Power Query Rhestr.Max):

Fodd bynnag, prif nodwedd List.Accumulate yw'r gallu i brosesu nid yn unig destun syml neu restrau rhifol fel dadleuon, ond gwrthrychau mwy cymhleth - er enghraifft, rhestrau-o-restrau neu restrau-o-gofnodion (helo, Cyfeiriadur!)

Gadewch i ni edrych eto ar y gwaith adeiladu a berfformiodd y disodli yn ein problem:

Rhestr.Cronni(Cyfeiriadur, [Cyfeiriad], (cyflwr, cyfredol) => Text.Replace(state, current[Canfod], current[Replace]) )

Beth sy'n digwydd yma mewn gwirionedd?

1. Fel gwerth cychwynnol (hadau) cymerwn y testun trwsgl cyntaf o'r golofn [Cyfeiriad] ein bwrdd: 199034, St Petersburg, str. Beringa, d. 1
2. Yna mae List.Acronulate yn ailadrodd dros elfennau'r rhestr fesul un - Llawlyfr. Mae pob elfen o'r rhestr hon yn gofnod sy'n cynnwys pâr o feysydd “Beth i'w ddarganfod - Beth i'w ddisodli” neu, mewn geiriau eraill, y llinell nesaf yn y cyfeiriadur.
3. Mae'r swyddogaeth cronadur yn rhoi i mewn i newidyn Roedd gwerth cychwynnol (cyfeiriad cyntaf 199034, St Petersburg, str. Beringa, d. 1) ac yn cyflawni swyddogaeth cronadur arno - y gweithrediad amnewid gan ddefnyddio'r swyddogaeth M safonol Testun.Replace (yn cyfateb i swyddogaeth SUBSTITUTE Excel). Ei gystrawen yw:

   Text.Replace (testun gwreiddiol, yr hyn yr ydym yn edrych amdano, yr hyn yr ydym yn ei ddisodli)

   a dyma ni:

• Roedd yw ein cyfeiriad budr, sydd yn gorwedd yn Roedd (cyrraedd o hadau)
  
• cyfredol[Chwilio] - gwerth maes i ddod o hyd o'r cofnod ailadroddus nesaf o'r rhestr Cyfeiriadur, sy'n gorwedd yn y newidyn ar hyn o bryd
• cyfredol[Amnewid] - gwerth maes Dirprwy o'r cofnod ailadroddus nesaf o'r rhestr Cyfeiriadurgorwedd i mewn ar hyn o bryd

Felly, ar gyfer pob cyfeiriad, rhedir cylchred llawn o gyfrifo'r holl linellau yn y cyfeiriadur bob tro, gan ddisodli'r testun o'r maes [Find] gyda'r gwerth o'r maes [Replace].

Gobeithio cawsoch chi'r syniad 🙂

• Disodli testun swmp mewn rhestr gan ddefnyddio fformiwlâu
  
• Mynegiadau Rheolaidd (RegExp) mewn Pŵer Ymholiad