Zëvendësimi i tekstit në masë në Power Query me funksionin List.Acumulate

Si të zëvendësoni shpejt dhe në masë tekstin sipas listës së referencës me formula - ne e kemi renditur tashmë atë. Tani le të përpiqemi ta bëjmë atë në Power Query.

Siç ndodh shpesh kryej kjo detyrë është shumë më e lehtë sesa të shpjegohet pse funksionon, por le të përpiqemi t'i bëjmë të dyja 🙂

Pra, ne kemi dy tabela dinamike "të zgjuara" të krijuara nga diapazoni i zakonshëm me një shkurtore të tastierës Ctrl+T ose ekipi Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë):

Unë thirra tryezën e parë Data, tabela e dytë - drejtoriduke përdorur fushën Emri i tabelës (Emri i tabelës) tab konstruktor (Dizajn).

Detyrë: zëvendësoni adresat në tabelë Data të gjitha dukuritë nga një kolonë Per te gjetur Manual ndaj homologëve të tyre të saktë nga kolona zëvendësim. Pjesa tjetër e tekstit në qeliza duhet të mbetet e paprekur.

Hapi 1. Ngarkoni direktorinë në Power Query dhe kthejeni atë në një listë

Pasi të keni vendosur qelizën aktive në çdo vend në tabelën e referencës, klikoni në skedën Data (Data)ose në skedën Kërkesa e energjisë (nëse keni një version të vjetër të Excel dhe keni instaluar Power Query si një shtesë në një skedë të veçantë) në butonin Nga tabela/gama (Nga tabela/gama).

Tabela e referencës do të ngarkohet në redaktuesin e pyetjeve të Power Query:

Për të mos ndërhyrë, një hap i shtuar automatikisht lloji i modifikuar (Lloji i ndryshuar) në panelin e djathtë, hapat e aplikuar mund të fshihen në mënyrë të sigurt, duke lënë vetëm hapin Burim (Burimi):

Tani, për të kryer transformime dhe zëvendësime të mëtejshme, duhet ta kthejmë këtë tabelë në një listë (listë).

Digresion lirik

• Tryezë është një grup dydimensional i përbërë nga disa rreshta dhe kolona.
• Regjistro (Regjistro) – varg grupi njëdimensional, i përbërë nga disa fusha-elemente me emra, p.sh. [Emri = "Masha", Gjinia = "f", Mosha = 25]
  
• Listë – një varg-kolona njëdimensionale, e përbërë nga disa elementë, për shembull {1, 2, 3, 10, 42} or { "Besimi shpresa Dashuria" }
  

Për të zgjidhur problemin tonë, ne do të jemi të interesuar kryesisht për llojin Listë.

Truku këtu është se artikujt e listës në Power Query mund të jenë jo vetëm numra ose tekst banal, por edhe lista ose regjistrime të tjera. Është në një listë (listë) kaq të ndërlikuar, e përbërë nga regjistrime (rekorde) që ne duhet të kthejmë direktorinë tonë. Në shënimin sintaksor të Power Query (hyrjet në kllapa katrore, listat në kllapa kaçurrelë) do të duket kështu:

{

    [ Gjeni = “Shën. Petersburg”, Replace = “Shën. Petersburg”] ,

    [ Gjeni = “Shën. Petersburg”, Replace = “Shën. Petersburg”] ,

    [ Gjeni = “Pjetri”, Replace = “Shën. Petersburg”] ,

etj.

}

Një transformim i tillë kryhet duke përdorur një funksion të veçantë të gjuhës M të integruar në Power Query - Tabela.ToRecords. Për ta aplikuar atë drejtpërdrejt në shiritin e formulës, shtoni këtë funksion në kodin e hapit atje Burim.

Ishte:

Pas:

Pas shtimit të funksionit Table.ToRecords, pamja e tabelës sonë do të ndryshojë - ajo do të kthehet në një listë të rekordeve. Përmbajtja e regjistrimeve individuale mund të shihet në fund të panelit të pamjes duke klikuar në sfondin e qelizës pranë çdo fjale rekord (por jo me një fjalë të vetme!)

Përveç sa më sipër, ka kuptim të shtoni një goditje më shumë - në cache (bufer) listën tonë të krijuar. Kjo do ta detyrojë Power Query të ngarkojë listën tonë të kërkimit një herë në memorie dhe të mos e rillogarisë përsëri kur ne më vonë t'i qasemi asaj për ta zëvendësuar. Për ta bërë këtë, mbështillni formulën tonë në një funksion tjetër - Lista.Bufer:

Një memorie e tillë do të japë një rritje shumë të dukshme të shpejtësisë (me disa herë!) me një sasi të madhe të të dhënave fillestare që do të pastrohen.

Kjo përfundon përgatitjen e manualit.

Mbetet për të klikuar Faqja kryesore – Mbyll dhe ngarko – Mbylle dhe ngarko në… (Shtëpia — Mbyll&Ngarko — Mbylle&Ngarko te..), zgjidhni një opsion Thjesht krijoni një lidhje (Vetëm krijoni lidhje) dhe kthehuni në Excel.

Hapi 2. Ngarkimi i tabelës së të dhënave

Gjithçka është e zakonshme këtu. Si më parë me librin e referencës, ngjitemi në çdo vend të tabelës, klikojmë në skedën Data buton Nga tabela/gama dhe tavolina jonë Data futet në Power Query. Hapi i shtuar automatikisht lloji i modifikuar (Lloji i ndryshuar) ju gjithashtu mund të hiqni:

Asnjë veprim i veçantë përgatitor nuk kërkohet të bëhet me të dhe kalojmë në gjënë më të rëndësishme.

Hapi 3. Kryeni zëvendësime duke përdorur funksionin List.Acumulate

Le të shtojmë një kolonë të llogaritur në tabelën tonë të të dhënave duke përdorur komandën Shtimi i një kolone - Kolona e personalizuar (Shto kolonë - kolonë e personalizuar): dhe shkruani emrin e kolonës së shtuar në dritaren që hapet (për shembull, adresa e korrigjuar) dhe funksionin tonë magjik Lista.Akumuloj:

Mbetet për të klikuar OK – dhe marrim një kolonë me zëvendësimet e bëra:

Vini re se:

• Meqenëse Power Query është sensitive, nuk ka pasur zëvendësim në rreshtin e parafundit, sepse në direktorium kemi “SPb”, jo “SPb”.
• Nëse ka disa nënvargje për t'u zëvendësuar menjëherë në të dhënat e burimit (për shembull, në rreshtin e 7-të duhet të zëvendësoni të dy "S-Pb" dhe "Prospekt"), atëherë kjo nuk krijon asnjë problem (ndryshe nga zëvendësimi me formula nga metoda e mëparshme).
• Nëse nuk ka asgjë për të zëvendësuar në tekstin burimor (rreshti i 9-të), atëherë nuk ndodhin gabime (ndryshe nga, përsëri, nga zëvendësimi me formula).

Shpejtësia e një kërkese të tillë është shumë, shumë e mirë. Për shembull, për një tabelë të të dhënave fillestare me një madhësi prej 5000 rreshtash, kjo pyetje u përditësua në më pak se një sekondë (pa buferuar, meqë ra fjala, rreth 3 sekonda!)

Si funksionon funksioni List.Acumulate

Në parim, ky mund të jetë fundi (që unë të shkruaj, dhe që ju të lexoni) këtë artikull. Nëse dëshironi që jo vetëm të jeni në gjendje, por edhe të kuptoni se si funksionon "nën kapuç", atëherë do t'ju duhet të zhyteni pak më thellë në vrimën e lepurit dhe të merreni me funksionin List.Acumulate, i cili bëri të gjithë zëvendësimin në masë. punoni për ne.

Sintaksa për këtë funksion është:

=Lista.Akumulo(listë, farë, akumulator)

ku

• listë është lista mbi elementet e së cilës po i përsërisim. 
• farë – gjendja fillestare
• akumulator – një funksion që kryen një veprim (matematikor, tekst, etj.) në elementin tjetër të listës dhe grumbullon rezultatin e përpunimit në një variabël të veçantë.
  

Në përgjithësi, sintaksa për shkrimin e funksioneve në Power Query duket si kjo:

(argument1, argument2, … argumentN) => disa veprime me argumente

Për shembull, funksioni i mbledhjes mund të përfaqësohet si:

(a, b) => a + b

Për List.Accumulate, ky funksion akumulator ka dy argumente të kërkuara (ata mund të emërtohen çdo gjë, por emrat e zakonshëm janë ishin и aktual, si në ndihmën zyrtare për këtë funksion, ku:

• ishin – një variabël ku akumulohet rezultati (vlera e tij fillestare është ajo e përmendur më sipër farë)
  
• aktual – vlera tjetër e përsëritur nga lista listë
  

Për shembull, le të hedhim një vështrim në hapat e logjikës së ndërtimit të mëposhtëm:

=Lista.Akumulo({3, 2, 5}, 10, (gjendje, rrymë) => gjendje + rrymë)

Ky është akumulimi i fundit ishin vlera është Lista jonë. Akumuloni funksionin dhe rezultatet si rezultat:

Nëse fantazoni pak, atëherë duke përdorur funksionin List.Acumulate, mund të simuloni, për shembull, funksionin Excel CONCATENATE (në Power Query, analogu i tij quhet Teksti.Kombinoje) duke përdorur shprehjen:

Ose edhe kërkoni për vlerën maksimale (imitim i funksionit MAX të Excel, i cili në Power Query quhet Lista.Maks):

Sidoqoftë, veçoria kryesore e List.Acumulate është aftësia për të përpunuar jo vetëm tekste të thjeshta ose lista numerike si argumente, por objekte më komplekse - për shembull, listat-nga-listat ose listat-nga-rekordet (përshëndetje, Drejtori!)

Le të shohim përsëri konstruksionin që ka kryer zëvendësimin në problemin tonë:

List.Acumulate(drejtori, [Adresë], (gjendja, aktuale) => Teksti. Zëvendësoni (gjendja, aktuale[Gjeni], aktuale[Zëvendëso]) )

Çfarë po ndodh vërtet këtu?

1. Si vlerë fillestare (farë) marrim tekstin e parë të ngathët nga kolona [Adresë] tabela jonë: 199034, Shën Petersburg, rr. Beringa, d. 1
2. Pastaj Listoni. Akumuloni përsëritjet mbi elementët e listës një nga një - Manual. Çdo element i kësaj liste është një rekord i përbërë nga një palë fushash "Çfarë të gjesh – Me çfarë të zëvendësosh" ose, me fjalë të tjera, rreshti tjetër në drejtori.
3. Funksioni akumulator vendos në një ndryshore ishin vlera fillestare (adresa e parë 199034, Shën Petersburg, rr. Beringa, d. 1) dhe kryen një funksion akumulator mbi të - funksionin e zëvendësimit duke përdorur funksionin standard M Teksti.Zëvendëso (analog me funksionin SUBSTITUTE të Excel). Sintaksa e tij është:

   Text.Replace (teksti origjinal, çfarë po kërkojmë, me çfarë po zëvendësojmë)

   dhe ja ku kemi:

• ishin është adresa jonë e pistë, në të cilën ndodhet ishin (arritja atje nga farë)
  
• aktuale[Kërko] – vlera e fushës Per te gjetur nga hyrja tjetër e përsëritur e listës drejtori, e cila shtrihet në ndryshoren aktual
• aktuale[Zëvendëso] – vlera e fushës zëvendësim nga hyrja tjetër e përsëritur e listës drejtorii shtrirë në aktual

Kështu, për çdo adresë, çdo herë ekzekutohet një cikël i plotë i numërimit të të gjitha rreshtave në drejtori, duke zëvendësuar tekstin nga fusha [Find] me vlerën nga fusha [Replace].

Shpresoj ta keni marrë idenë 🙂

• Zëvendësoni në masë tekstin në një listë duke përdorur formula
  
• Shprehjet e rregullta (RegExp) në Power Query