Bulk nga pag-ilis sa teksto sa Power Query nga adunay List.Accumulate function

Giunsa nga dali ug dinaghan ang pag-ilis sa teksto sumala sa lista sa pakisayran nga adunay mga pormula - naayos na namon kini. Karon atong sulayan nga buhaton kini sa Power Query.

Sama sa kanunay mahitabo himua kini nga buluhaton mas sayon ​​kay sa pagpatin-aw ngano kini molihok, apan atong sulayan nga buhaton ang duha 🙂

Busa, kami adunay duha ka "smart" dinamikong mga lamesa nga gihimo gikan sa ordinaryo nga mga han-ay nga adunay usa ka shortcut sa keyboard Ctrl+T o team Panimalay - Pag-format ingon usa ka lamesa (Balay — Format isip Talaan):

Gitawagan nako ang unang lamesa Data, ang ikaduhang lamesa - Directorynaggamit uma Ngalan sa lamesa (Ngalan sa lamesa) tab Magtutukod (Laraw).

Buluhaton: ilisan ang mga adres sa lamesa Data tanan nga mga panghitabo gikan sa usa ka kolum Pangita-on Handbook sa ilang katugbang nga husto nga mga katugbang gikan sa kolum Kapuli. Ang nahabilin nga teksto sa mga selyula kinahanglan magpabilin nga wala matandog.

Lakang 1. I-load ang direktoryo ngadto sa Power Query ug himoa kini nga listahan

Kung nabutang ang aktibo nga cell sa bisan unsang lugar sa reference table, i-klik ang tab Data (Petsa)o sa tab Kusog nga Pagpangutana (kung aduna kay daan nga bersyon sa Excel ug imong gi-install ang Power Query isip add-in sa lain nga tab) sa buton Gikan sa lamesa / range (Gikan sa Talaan/Sakop).

Ang reference table ikarga sa Power Query query editor:

Aron dili makabalda, usa ka awtomatikong dugang nga lakang giusab nga tipo (Gibag-o nga Uri) sa tuo nga panel, ang gipadapat nga mga lakang mahimong luwas nga mapapas, nga magbilin lamang sa lakang tinubdan (Gigikanan):

Karon, aron mahimo ang dugang nga mga pagbag-o ug pag-ilis, kinahanglan naton nga himuon kini nga lamesa nga usa ka lista (lista).

Lyrical pagkadaot

• Table mao ang usa ka duha-ka-dimensional nga han-ay nga gilangkuban sa daghang mga laray ug mga kolum.
• Rekord (Rekord) – usa ka dimensyon nga array-string, nga naglangkob sa daghang mga field-element nga adunay mga ngalan, pananglitan [Ngalan = "Masha", Kasarian = "f", Edad = 25]
  
• Ilista ang – usa ka one-dimensional array-column, nga naglangkob sa daghang mga elemento, pananglitan {1, 2, 3, 10, 42} or { “Pagtuo Paglaum Gugma” }
  

Aron masulbad ang among problema, mag-una kami nga interesado sa tipo Ilista ang.

Ang lansis dinhi mao nga ang mga butang sa lista sa Power Query mahimong dili lamang mga banal nga numero o teksto, apan uban pang mga lista o mga rekord. Anaa kini sa usa ka malisud nga lista (lista), nga naglangkob sa mga rekord (mga rekord) nga kinahanglan naton ibalik ang among direktoryo. Sa Power Query syntactic notation (mga entry sa square bracket, mga lista sa curly bracket) kini tan-awon:

{

    [ Pangitaa = “St. Petersburg", Ilisan = "St. Petersburg” ] ,

    [ Pangitaa = “St. Petersburg", Ilisan = "St. Petersburg” ] ,

    [ Pangitaa = "Pedro", Ilisan = "St. Petersburg” ] ,

ug uban pa

}

Ang ingon nga pagbag-o gihimo gamit ang usa ka espesyal nga function sa M nga lengguwahe nga gitukod sa Power Query - Talaan.ToRecords. Aron magamit kini direkta sa formula bar, idugang kini nga function sa step code didto tinubdan.

Kini mao ang:

Human sa:

Pagkahuman sa pagdugang sa function sa Table.ToRecords, ang hitsura sa among lamesa mausab - kini mahimong usa ka lista sa mga rekord. Ang sulod sa indibidwal nga mga rekord makita sa ubos sa view pane pinaagi sa pag-klik sa cell background sunod sa bisan unsang pulong Record (apan dili sa usa ka pulong!)

Dugang pa sa naa sa ibabaw, makatarunganon nga magdugang usa pa ka stroke - sa pag-cache (buffer) sa among gibuhat nga lista. Kini mopugos sa Power Query sa pagkarga sa among listahan sa pagpangita sa makausa ngadto sa panumduman ug dili na kalkulahon kini pag-usab sa diha nga sa ulahi ma-access kini aron mapulihan kini. Aron mahimo kini, ibutang ang among pormula sa laing function - Lista.Buffer:

Ang ingon nga pag-cache maghatag usa ka mamatikdan nga pag-uswag sa katulin (sa daghang mga higayon!) Uban ang daghang kantidad sa inisyal nga datos nga ma-clear.

Kini mokompleto sa pag-andam sa handbook.

Kini nagpabilin sa pag-klik sa Panimalay - Pagsira ug Pag-load - Pagsira ug Pag-load sa… (Balay — Close&Load — Close&Load to..), pagpili og opsyon Paghimo lang og koneksyon (Paghimo lang og koneksyon) ug balik sa Excel.

Lakang 2. Pag-load sa lamesa sa datos

Ang tanan naa ra dinhi. Sama sa kaniadto sa reperensiya nga libro, mobangon kami sa bisan unsang lugar sa lamesa, pag-klik sa tab Data button Gikan sa Table/Range ug among lamesa Data mosulod sa Power Query. Awtomatikong gidugang nga lakang giusab nga tipo (Gibag-o nga Uri) mahimo usab nimong tangtangon:

Walay espesyal nga mga aksyon sa pagpangandam ang gikinahanglan nga buhaton niini, ug kita mopadayon ngadto sa labing importante nga butang.

Lakang 3. Buhata ang mga kapuli gamit ang List.Accumulate function

Atong idugang ang usa ka kalkulado nga kolum sa among data table gamit ang command Pagdugang ug Column – Custom Column (Idugang ang kolum — Custom nga kolum): ug isulod ang ngalan sa gidugang nga kolum sa bintana nga magbukas (pananglitan, gitul-id nga adres) ug ang among magic function Ilista.Pagtigom:

Kini nagpabilin sa pag-klik sa OK - ug nakakuha kami usa ka kolum nga adunay mga kapuli nga gihimo:

Timan-i nga:

• Tungod kay ang Power Query sensitibo sa kaso, wala'y kapuli sa penultimate nga linya, tungod kay sa direktoryo kita adunay "SPb", dili "SPb".
• Kung adunay daghang mga substrings nga pulihan sa usa ka higayon sa gigikanan nga datos (pananglitan, sa ika-7 nga linya kinahanglan nimo nga ilisan ang parehas nga "S-Pb" ug "Prospectus"), nan kini wala maghimo bisan unsang mga problema (dili sama sa pag-ilis sa mga pormula gikan sa sa miaging pamaagi).
• Kung walay bisan unsa nga ilisan sa tinubdan nga teksto (ika-9 nga linya), nan walay mga sayup nga mahitabo (dili sama, pag-usab, gikan sa pag-ilis sa mga pormula).

Ang katulin sa ingon nga usa ka hangyo kaayo, desente kaayo. Pananglitan, alang sa usa ka talaan sa inisyal nga datos nga adunay gidak-on nga 5000 ka laray, kini nga pangutana gi-update sa wala'y usa ka segundo (nga walay buffering, sa dalan, mga 3 segundos!)

Giunsa ang List.Accumulate function nagtrabaho

Sa prinsipyo, mahimo’g kini ang katapusan (para akong isulat, ug aron basahon nimo) kini nga artikulo. Kung gusto nimo nga dili lamang mahimo, apan sabton usab kung giunsa kini molihok "sa ilawom sa hood", nan kinahanglan nimo nga mag-dive og gamay sa lungag sa kuneho ug atubangon ang List.Accumulate function, nga naghimo sa tanan nga kadaghanan nga pagpuli pagtrabaho alang kanamo.

Ang syntax alang niini nga function mao ang:

=Listahan.Pagtigom(listahan, binhi, tigtipig)

diin

• listahan mao ang listahan kansang mga elemento atong gisubli. 
• binhi - inisyal nga kahimtang
• tigtipig - usa ka function nga naghimo sa pipila ka operasyon (matematika, teksto, ug uban pa) sa sunod nga elemento sa lista ug nagtigum sa resulta sa pagproseso sa usa ka espesyal nga variable.
  

Sa kinatibuk-an, ang syntax sa pagsulat sa mga gimbuhaton sa Power Query ingon niini:

(argument1, argument2, ... argumentN) => pipila ka aksyon nga adunay argumento

Pananglitan, ang summation function mahimong irepresentar ingon:

(a, b) => a + b

Alang sa List.Accumulate , kini nga function sa accumulator adunay duha ka gikinahanglan nga mga argumento (mahimong tawgon ang bisan unsa, apan ang kasagaran nga mga ngalan kay estado и kasamtangan nga, sama sa opisyal nga tabang alang niini nga function, diin:

• estado – usa ka variable diin ang resulta natipon (ang inisyal nga bili niini mao ang gihisgotan sa ibabaw binhi)
  
• kasamtangan nga – ang sunod nga gibalikbalik nga kantidad gikan sa lista listahan
  

Pananglitan, atong tan-awon ang mga lakang sa lohika sa mosunod nga pagtukod:

=Listahan.Pagtigom({3, 2, 5}, 10, (estado, kasamtangan) => estado + kasamtangan)

Kini ang pinakaulahing natigom estado ang bili mao ang among List.Accumulate function ug outputs isip resulta:

Kung maghanduraw ka gamay, unya gamit ang List.Accumulate function, mahimo nimong i-simulate, pananglitan, ang Excel function CONCATENATE (sa Power Query, ang analogue niini gitawag Text.Paghiusa) gamit ang ekspresyon:

O bisan pagpangita sa labing taas nga kantidad (pagsundog sa MAX function sa Excel, nga gitawag sa Power Query Listahan.Max):

Bisan pa, ang nag-unang bahin sa List.Accumulate mao ang abilidad sa pagproseso dili lamang sa yano nga teksto o mga lista sa numero isip mga argumento, apan mas komplikado nga mga butang - pananglitan, mga lista-gikan sa mga lista o mga lista-gikan sa mga rekord (hello, Directory!)

Atong tan-awon pag-usab ang pagtukod nga naghimo sa pagpuli sa atong problema:

Lista. Pagtigom(Directory, [Pakigsulti], (estado, kasamtangan) => Text.Ilisan(estado, kasamtangan[Pangitaa], kasamtangan[Ilisan]) )

Unsa man gyud ang nahitabo dinhi?

1. Ingon inisyal nga bili (binhi) gikuha namo ang unang clumsy text gikan sa column [Pakigsulti] among lamesa: 199034, St. Petersburg, str. Beringa, d. 1
2. Dayon ang List.Accumulate mag-uli sa mga elemento sa lista sa usa-usa - Handbook. Ang matag elemento niini nga lista usa ka rekord nga gilangkuban sa usa ka parisan sa mga field "Unsa ang pangitaon - Unsa ang ilisan" o, sa laing pagkasulti, ang sunod nga linya sa direktoryo.
3. Ang function sa accumulator gibutang sa usa ka variable estado inisyal nga bili (unang adres 199034, St. Petersburg, str. Beringa, d. 1) ug naghimo sa usa ka accumulator function niini - ang puli nga operasyon gamit ang standard M-function Text.Ilisan (kaamgid sa SUBSTITUTE function sa Excel). Ang syntax niini mao ang:

   Text.Replace(orihinal nga teksto, unsay atong gipangita, unsay atong gipulihan)

   ug ania kami:

• estado mao ang among hugaw nga adres, nga nahimutang sa estado (pag-abot didto gikan binhi)
  
• kasamtangan [Pagpangita] - bili sa uma Pangita-on gikan sa sunod nga gibalikbalik nga entry sa listahan Directory, nga anaa sa variable kasamtangan nga
• kasamtangan [Ilisan] - bili sa uma Kapuli gikan sa sunod nga gibalikbalik nga entry sa listahan Directorynaghigda sa kasamtangan nga

Busa, alang sa matag adres, usa ka bug-os nga siklo sa pag-ihap sa tanan nga mga linya sa direktoryo gipadagan matag higayon, gipulihan ang teksto gikan sa [Pangita] nga uma sa kantidad gikan sa [Ilisan] nga uma.

Hinaot nakuha nimo ang ideya 🙂

• Ilisan ang daghang teksto sa usa ka lista gamit ang mga pormula
  
• Regular nga Ekspresyon (RegExp) sa Power Query