Magn textaskipti í Power Query með List.Accumulate aðgerð

Hvernig á að skipta út textanum fljótt og í lausu í samræmi við tilvísunarlistann fyrir formúlur - við höfum þegar reddað því. Nú skulum við reyna að gera það í Power Query.

Eins og oft gerist framkvæma þetta verkefni er miklu auðveldara en að útskýra hvers vegna það virkar, en við skulum reyna að gera bæði 🙂

Þannig að við erum með tvær „snjallar“ kraftmiklar töflur búnar til úr venjulegum sviðum með flýtilykla Ctrl+T eða lið Heim - Snið sem töflu (Heima - Snið sem töflu):

Ég hringdi á fyrsta borðið Gögn, annað borðið - Skránota reit Heiti töflu (Nafn töflu) flipi Framkvæmdaaðili (Hönnun).

Verkefni: skipta um heimilisföng í töflunni Gögn öll tilvik úr dálki Að finna Handbók til samsvarandi réttra hliðstæða þeirra úr dálknum Staðgengill. Afgangurinn af textanum í reitunum ætti að vera ósnert.

Skref 1. Hladdu möppunni í Power Query og breyttu henni í lista

Eftir að hafa stillt virka reitinn á hvaða stað sem er í tilvísunartöflunni, smelltu á flipann Gögn (Dagsetning)eða á flipanum Orkufyrirspurn (ef þú ert með gamla útgáfu af Excel og settir upp Power Query sem viðbót á sérstökum flipa) á hnappinum Frá borði/sviði (Úr töflu/sviði).

Tilvísunartaflan verður hlaðin inn í Power Query fyrirspurnaritilinn:

Til að trufla ekki, sjálfkrafa bætt skref breytt gerð (Breytt gerð) í hægra spjaldinu er hægt að eyða beittum skrefum á öruggan hátt, þannig að aðeins skrefið er eftir Heimild (Heimild):

Nú, til að framkvæma frekari umbreytingar og skipti, þurfum við að breyta þessari töflu í lista (lista).

Ljóðræn útrás

• Tafla er tvívítt fylki sem samanstendur af nokkrum línum og dálkum.
• Record (Record) – einvíddar fylkisstrengur, sem samanstendur af nokkrum sviðsþáttum með nöfnum, til dæmis [Nafn = "Masha", kyn = "f", Aldur = 25]
  
• Listi – einvíddar fylkisdálkur, sem samanstendur af nokkrum þáttum, til dæmis {1, 2, 3, 10, 42} or { "Trú von ást" }
  

Til að leysa vandamál okkar munum við fyrst og fremst hafa áhuga á gerðinni Listi.

Trikkið hér er að listaatriði í Power Query geta ekki aðeins verið banal tölur eða texti, heldur einnig aðrir listar eða færslur. Það er á svo erfiðum lista (lista), sem samanstendur af færslum (skrám) sem við þurfum að breyta skránni okkar. Í Power Query setningafræði (færslur í hornklofa, listar í krulluðum sviga) myndi þetta líta svona út:

{

    [ Finndu = "St. Pétursborg", Skipta út = "St. Pétursborg“] ,

    [ Finndu = "St. Pétursborg", Skipta út = "St. Pétursborg“] ,

    [ Finndu = "Pétur", Skipta út = "St. Pétursborg“] ,

o.fl.

}

Slík umbreyting er framkvæmd með því að nota sérstaka aðgerð M tungumálsins sem er innbyggt í Power Query - Table.ToRecords. Til að nota það beint á formúlustikuna skaltu bæta þessari aðgerð við skrefakóðann þar Heimild.

Það var:

Eftir:

Eftir að Table.ToRecords aðgerðinni hefur verið bætt við mun útlit töflunnar okkar breytast - það mun breytast í lista yfir færslur. Hægt er að sjá innihald einstakra skráa neðst á glugganum með því að smella í bakgrunni hólfsins við hlið hvaða orðs sem er Met (en ekki í einu orði!)

Til viðbótar við ofangreint er skynsamlegt að bæta við einu höggi í viðbót - til að vista (buffa) listann okkar sem búið er til. Þetta mun neyða Power Query til að hlaða uppflettilistanum okkar einu sinni í minnið og ekki endurreikna hann aftur þegar við fáum aðgang að honum síðar til að skipta um hann. Til að gera þetta skaltu vefja formúlunni okkar í aðra aðgerð - Listi.Buffer:

Slík skyndiminni mun gefa mjög áberandi aukningu á hraða (um nokkrum sinnum!) Með miklu magni af upphafsgögnum sem á að hreinsa.

Þar með er gerð handbókarinnar lokið.

Það á eftir að smella á Heim – Loka og hlaða – Loka og hlaða til... (Heima — Loka&hlaða — Loka&hlaða til..), veldu valkost Búðu bara til tengingu (Búa bara til tengingu) og farðu aftur í Excel.

Skref 2. Hleðsla gagnatöflunnar

Hér er allt töff. Eins og áður með uppflettiritið komumst við upp á hvaða stað sem er í töflunni, smelltu á flipann Gögn hnappinn Frá borði/sviði og borðið okkar Gögn kemst inn í Power Query. Skref sjálfkrafa bætt við breytt gerð (Breytt gerð) þú getur líka fjarlægt:

Það þarf ekki að gera sérstakar undirbúningsaðgerðir við það og við höldum áfram að því mikilvægasta.

Skref 3. Framkvæmdu skipti með því að nota List.Accumulate aðgerðina

Við skulum bæta útreiknuðum dálki við gagnatöfluna okkar með því að nota skipunina Að bæta við dálki - Sérsniðinn dálkur (Bæta við dálki - sérsniðinn dálkur): og sláðu inn nafn dálksins sem bætt var við í glugganum sem opnast (til dæmis, leiðrétt heimilisfang) og töfravirkni okkar Listi.Safna:

Það á eftir að smella á OK – og við fáum dálk með skiptingunum:

Athugaðu að:

• Þar sem Power Query er há- og hástafaviðkvæmur kom ekkert í staðinn í næstsíðustu línunni, vegna þess að í möppunni höfum við „SPb“, ekki „SPb“.
• Ef það eru nokkrir undirstrengir sem þarf að skipta út í einu í upprunagögnunum (til dæmis í 7. línu þarftu að skipta út bæði „S-Pb“ og „Lýsingarlýsingu“), þá skapar það engin vandamál (ólíkt því að skipta út með formúlum frá fyrri aðferðin).
• Ef ekkert er til að skipta út í frumtextanum (9. lína), þá koma engar villur (ólíkt því aftur, þegar formúlum er skipt út fyrir).

Hraði slíkrar beiðni er mjög, mjög viðeigandi. Til dæmis, fyrir töflu með upphafsgögnum með stærðinni 5000 línur, var þessi fyrirspurn uppfærð á innan við sekúndu (án biðminni, við the vegur, um 3 sekúndur!)

Hvernig List.Accumulate aðgerðin virkar

Í grundvallaratriðum gæti þetta verið endirinn (fyrir mig að skrifa og fyrir þig að lesa) þessa grein. Ef þú vilt ekki aðeins geta, heldur líka skilja hvernig það virkar „undir hettunni“, þá verðurðu að kafa aðeins dýpra í kanínuholið og takast á við List.Accumulate aðgerðina, sem skipti um allan magn. vinna fyrir okkur.

Setningafræði fyrir þessa aðgerð er:

=List.Safna(lista, fræ, uppsöfnun)

þar sem

• lista er listinn sem við erum að endurtaka yfir. 
• fræ - upphafsástand
• uppsöfnun – fall sem framkvæmir einhverja aðgerð (stærðfræði, texta o.s.frv.) á næsta þætti listans og safnar niðurstöðu úr vinnslu í sérstaka breytu.
  

Almennt séð lítur setningafræði fyrir að skrifa aðgerðir í Power Query svona út:

(rök1, rök2, … rökN) => nokkrar aðgerðir með rökum

Til dæmis gæti samantektarfallið verið táknað sem:

(a, b) => a + b

Fyrir List.Accumulate hefur þessi uppsöfnunarfall tvö nauðsynleg rök (þeim má nefna hvað sem er, en venjuleg nöfn eru voru и núverandi, eins og í opinberu hjálpinni fyrir þessa aðgerð, þar sem:

• voru – breyta þar sem niðurstaðan er safnað saman (upphafsgildi hennar er það sem nefnt er hér að ofan fræ)
  
• núverandi – næsta endurtekna gildi af listanum lista
  

Til dæmis skulum við skoða skrefin í rökfræði eftirfarandi smíði:

=List.Safna({3, 2, 5}, 10, (ástand, straumur) => ástand + straumur)

Þetta er það nýjasta sem hefur safnast voru gildið er Listinn okkar. Safna aðgerð og úttak sem afleiðing:

Ef þú ímyndar þér smá, þá með því að nota List.Accumulate aðgerðina, geturðu líkt eftir, til dæmis, Excel fallinu CONCATENATE (í Power Query er hliðstæða hennar kölluð Texti.Samana) með því að nota orðatiltækið:

Eða jafnvel leitaðu að hámarksgildi (eftirlíkingu af MAX aðgerð Excel, sem í Power Query er kölluð Listi.Max):

Hins vegar er aðaleiginleikinn við List.Accumulate hæfileikinn til að vinna ekki aðeins einfaldan texta eða tölulista sem rök, heldur flóknari hluti - til dæmis lista-af-listum eða listum-frá-skrám (halló, Directory!)

Við skulum líta aftur á smíðina sem kom í staðinn fyrir vandamálið okkar:

Listi.Söfnun(Skrá, [Heimilisfang], (ástand, núverandi) => Texti. Skipta út (ástand, núverandi[Finna], núverandi[Skipta]) )

Hvað er eiginlega í gangi hérna?

1. Sem upphafsgildi (fræ) við tökum fyrsta klaufalega textann úr dálknum [Heimilisfang] borðið okkar: 199034, Pétursborg, str. Beringa, d. 1
2. Síðan endurtekur List.Accumulate yfir þætti listans einn af öðrum – Handbók. Hver þáttur þessa lista er skrá sem samanstendur af par af reitum "Hvað á að finna - Hvað á að skipta út fyrir" eða, með öðrum orðum, næstu línu í möppunni.
3. Uppsöfnunaraðgerðin setur inn breytu voru upphafsgildi (fyrsta heimilisfang 199034, Pétursborg, str. Beringa, d. 1) og framkvæmir rafsöfnunaraðgerð á því - skiptiaðgerðin með því að nota staðlaða M-aðgerðina Texti. Skipta út (svipað og SUBSTITUTE aðgerð Excel). Setningafræði þess er:

   Text.Replace( upprunalega textinn, það sem við erum að leita að, hvað við erum að skipta út fyrir)

   og hér höfum við:

• voru er óhreina heimilisfangið okkar, sem liggur í voru (að komast þangað frá fræ)
  
• núverandi [leit] – reitgildi Að finna frá næstu endurteknu færslu listans Skrá, sem liggur í breyt núverandi
• núverandi[Skipta] – reitgildi Staðgengill frá næstu endurteknu færslu listans Skráliggja í núverandi

Þannig er, fyrir hvert heimilisfang, keyrt heil lotu af upptalningu allra lína í möppunni í hvert skipti, þar sem textinn úr [Finna] reitnum er skipt út fyrir gildið úr [Replace] reitnum.

Vona að þú hafir fengið hugmyndina 🙂

• Skipta út texta í magni á lista með formúlum
  
• Regluleg tjáning (RegExp) í Power Query