Ranplasman tèks esansyèl nan Power Query ak fonksyon List.Accumulate

Ki jan yo byen vit ak an gwo ranplase tèks la dapre lis referans la ak fòmil - nou te deja klase li soti. Koulye a, ann eseye fè li nan Power Query.

Kòm souvan rive fè travay sa a pi fasil pase eksplike poukisa li mache, men ann eseye fè tou de 🙂

Se konsa, nou gen de "entelijan" tab dinamik ki te kreye nan seri òdinè ak yon chemen kout klavye Ctrl+T oswa ekip Kay - Fòma kòm yon tab (Kay — Fòma kòm Tablo):

Mwen rele premye tab la Done, dezyèm tab la - Anyèlè l sèvi avèk jaden Non tab la (Non tab la) tab Constructor (Design).

Objektif Travay la: ranplase nan adrès ki nan tablo a Done tout evènman ki soti nan yon kolòn Jwenn Manyèl nan tokay korespondan kòrèk yo nan kolòn nan Ranplase. Rès tèks la nan selil yo ta dwe rete intact.

Etap 1. Chaje anyè a nan Power Query epi vire l nan yon lis

Lè w fin mete selil aktif la nan nenpòt kote nan tablo referans la, klike sou tab la Done (Dat)oswa sou tab la Pouvwa rechèch (si ou gen yon ansyen vèsyon Excel epi ou enstale Power Query kòm yon ajoute nan yon tab separe) sou bouton an Soti nan tab/ranje (Soti nan tab/ranje).

Tablo referans la pral chaje nan editè demann Power Query:

Nan lòd pa entèfere, yon etap otomatikman ajoute kalite modifye (Kanje Kalite) nan panèl dwat la, etap yo aplike yo ka san danje efase, kite sèlman etap la sous (Sous):

Koulye a, pou fè plis transfòmasyon ak ranplasman, nou bezwen vire tab sa a nan yon lis (lis).

Digresyon lirik

• Tablo se yon etalaj ki genyen de dimansyon ki gen plizyè ranje ak kolòn.
• Dosye (Dosye) – yon sèl dimansyon etalaj-string, ki fòme ak plizyè jaden-eleman ak non, pou egzanp [Non = "Masha", Sèks = "f", Laj = 25]
  
• Lis – yon kolòn etalaj ki genyen yon sèl dimansyon, ki gen plizyè eleman, pa egzanp {1, 2, 3, 10, 42} or { "Lafwa Hope Lanmou" }
  

Pou rezoud pwoblèm nou an, nou pral prensipalman enterese nan kalite a Lis.

Trick la isit la se ke atik lis nan Power Query ka pa sèlman nimewo ordinèr oswa tèks, men tou, lòt lis oswa dosye. Se nan yon lis sa a difisil (lis), ki fòme ak dosye (dosye) ke nou bezwen vire anyè nou an. Nan notasyon sentaktik Power Query (antre nan parantèz kare, lis nan parantèz boukle) sa a ta sanble:

{

    [ Jwenn = "St. Petersburg", Ranplase = "St. Petersburg"] ,

    [ Jwenn = "St. Petersburg", Ranplase = "St. Petersburg"] ,

    [ Jwenn = "Pyè", Ranplase = "St. Petersburg"] ,

elatriye.

}

Yon transfòmasyon konsa fèt lè l sèvi avèk yon fonksyon espesyal nan lang M ki bati nan Power Query - Table.ToRecords. Pou aplike li dirèkteman nan ba fòmil la, ajoute fonksyon sa a nan kòd etap la sous.

Se te:

Apre:

Apre yo fin ajoute fonksyon Table.ToRecords, aparans tab nou an ap chanje - li pral tounen yon lis dosye. Ou ka wè sa ki nan dosye endividyèl yo nan pati anba a nan fenèt la lè w klike sou nan background selil akote nenpòt mo. Dosye (men pa nan yon sèl mo!)

Anplis de sa ki pi wo a, li fè sans pou ajoute yon sèl plis konjesyon serebral - nan kachèt (tanpon) lis nou an kreye. Sa a pral fòse Power Query chaje lis rechèch nou an yon fwa nan memwa epi yo pa rekalkile li ankò lè nou pita jwenn aksè nan li ranplase li. Pou fè sa, vlope fòmil nou an nan yon lòt fonksyon - Lis.Tanpon:

Cache sa yo pral bay yon ogmantasyon trè aparan nan vitès (pa plizyè fwa!) Ak yon gwo kantite done inisyal yo dwe otorize.

Sa a konplete preparasyon manyèl la.

Li rete pou klike sou Kay – Fèmen epi chaje – Fèmen epi chaje pou… (Kay — Fèmen&Chaje — Fèmen&Chaje sou..), chwazi yon opsyon Jis kreye yon koneksyon (Sèlman kreye koneksyon) epi retounen nan Excel.

Etap 2. Chaje tab la done

Tout bagay se banal isit la. Kòm anvan ak liv referans la, nou leve nan nenpòt kote nan tablo a, klike sou tab la Done bouton Soti nan Tablo / Ranje ak tab nou an Done antre nan Power Query. Otomatikman te ajoute etap kalite modifye (Kanje Kalite) ou ka retire tou:

Pa gen okenn aksyon preparasyon espesyal yo oblije fè ak li, epi nou deplase sou bagay ki pi enpòtan an.

Etap 3. Fè ranplasman lè l sèvi avèk fonksyon List.Accumulate

Ann ajoute yon kolòn kalkile nan tab done nou an lè l sèvi avèk lòd la Ajoute yon kolòn - Custom kolòn (Ajoute kolòn - kolòn Custom): epi antre non kolòn ki te ajoute a nan fenèt ki ouvè a (pa egzanp, adrès korije) ak fonksyon majik nou an Lis.Akimile:

Li rete pou klike sou OK – epi nou jwenn yon kolòn ak ranplasman yo te fè:

Sonje ke:

• Piske Power Query se ka sansib, pa te gen okenn ranplasman nan penultyèm liy lan, paske nan anyè a nou gen "SPb", pa "SPb".
• Si gen plizyè substrings pou ranplase nan yon fwa nan done sous yo (pa egzanp, nan 7yèm liy lan ou bezwen ranplase tou de "S-Pb" ak "Prospectus"), Lè sa a, sa a pa kreye okenn pwoblèm (kontrèman ak ranplase ak fòmil soti nan metòd anvan an).
• Si pa gen anyen pou ranplase nan tèks sous la (9yèm liy), Lè sa a, pa gen okenn erè rive (kontrèman ak, ankò, nan ranplasman pa fòmil).

Vitès la nan yon demann sa a se trè, trè desan. Pou egzanp, pou yon tablo done inisyal ak yon gwosè 5000 ranje, rechèch sa a te mete ajou nan mwens pase yon segonn (san yo pa tampon, nan chemen an, apeprè 3 segonn!)

Ki jan fonksyon List.Accumulate fonksyone

Nan prensip, sa a ta ka fini (pou mwen ekri, epi pou ou li) atik sa a. Si ou vle pa sèlman kapab, men tou, konprann ki jan li fonksyone "anba kapo a", Lè sa a, ou pral gen plonje yon ti kras pi fon nan twou a lapen ak fè fas ak fonksyon an List.Accumulate, ki te fè tout ranplasman an esansyèl. travay pou nou.

Sentaks pou fonksyon sa a se:

=Lis.Akimile(lis, pitit pitit, akimilatè)

ki kote

• lis se lis ki gen eleman nou ap iterasyon sou. 
• pitit pitit - premye eta
• akimilatè – yon fonksyon ki fè kèk operasyon (matematik, tèks, elatriye) sou pwochen eleman nan lis la ak akimile rezilta a nan pwosesis nan yon varyab espesyal.
  

An jeneral, sentaks pou ekri fonksyon nan Power Query sanble sa a:

( agiman1, agiman2, … agimanN) => kèk aksyon ak agiman

Pou egzanp, fonksyon somasyon an ka reprezante kòm:

(a, b) => a + b

Pou List.Accumulate , fonksyon akimilatè sa a gen de agiman obligatwa (yo ka rele nenpòt bagay, men non abityèl yo se te и aktyèl, tankou nan èd ofisyèl la pou fonksyon sa a, kote:

• te – yon varyab kote rezilta a akimile (valè inisyal li se youn ki mansyone pi wo a pitit pitit)
  
• aktyèl – pwochen valè repete nan lis la lis
  

Pou egzanp, ann pran yon gade nan etap sa yo nan lojik konstriksyon sa a:

=Lis.Akimile({3, 2, 5}, 10, (eta, aktyèl) => eta + kouran)

Sa a se dènye akimile te valè a se fonksyon List.Akumule nou an ak rezilta kòm yon rezilta:

Si ou fantasme yon ti kras, Lè sa a, lè l sèvi avèk fonksyon an List.Accumulate, ou ka simulation, pou egzanp, fonksyon Excel CONCATENATE (nan Power Query, yo rele analòg li yo). Tèks.Konbine) itilize ekspresyon an:

Oswa menm rechèch pou valè maksimòm lan (imitasyon fonksyon MAX Excel a, ki nan Power Query yo rele List.Max):

Sepandan, karakteristik prensipal List.Accumulate se kapasite pou trete non sèlman tèks senp oswa lis nimerik kòm agiman, men objè ki pi konplèks - pou egzanp, lis-soti nan lis oswa lis-soti nan-dosye (alo, Anyè!)

Ann gade ankò nan konstriksyon an ki te fè ranplasman nan pwoblèm nou an:

List.Akumule(Anyè, [Adrès], (eta,aktyèl) => Tèks.Ranplase(eta, aktyèl[Jwenn], aktyèl[Ranplase]) )

Ki sa ki vrèman pase isit la?

1. Kòm valè inisyal (pitit pitit) nou pran premye tèks maladwa nan kolòn nan [Adrès] tab nou an: 199034, Saint Petersburg, str. Beringa, d. 1
2. Lè sa a, List.Accumulate itera sou eleman yo nan lis la youn pa youn - Manyèl. Chak eleman nan lis sa a se yon dosye ki fòme ak yon pè nan jaden "Kisa pou jwenn - Ki sa pou ranplase ak" oswa, nan lòt mo, liy kap vini an nan anyè a.
3. Fonksyon akimilatè a mete nan yon varyab te valè inisyal (premye adrès 199034, Saint Petersburg, str. Beringa, d. 1) ak fè yon fonksyon akimilatè sou li - operasyon an ranplasman lè l sèvi avèk estanda M-fonksyon an Tèks.Ranplase (analòg ak fonksyon SUBSTITUTE Excel la). Sentaks li se:

   Text.Replace (tèks orijinal, sa n ap chèche, sa n ap ranplase ak)

   e la nou genyen:

• te se adrès sal nou an, ki se nan te (ap resevwa la depi pitit pitit)
  
• aktyèl[Rechèch] - valè jaden Jwenn soti nan pwochen itere antre nan lis la Anyè, ki bay manti nan varyab la aktyèl
• aktyèl[Ranplase] - valè jaden Ranplase soti nan pwochen itere antre nan lis la Anyèkouche nan aktyèl

Kidonk, pou chak adrès, yon sik konplè enimerasyon nan tout liy nan anyè a ap kouri chak fwa, ranplase tèks ki soti nan jaden an [Jwenn] ak valè a nan jaden an [Ranplase].

Espere ou gen lide a 🙂

• Bulk ranplase tèks nan yon lis lè l sèvi avèk fòmil
  
• Ekspresyon regilye (RegExp) nan Power Query