Kusintha mawu ambiri mu Power Query ndi List.Accumulate ntchito

Momwe mungasinthire mawuwo mwachangu komanso mochulukira molingana ndi mndandanda wamawu ndi ma formula - tazikonza kale. Tsopano tiyeni tiyese kuchita mu Power Query.

Nthawi zambiri zimachitika chitani ntchito imeneyi ndi yosavuta kuposa kufotokoza chifukwa zimagwira ntchito, koma tiyeni tiyese kuchita zonsezi 🙂

Chifukwa chake, tili ndi matebulo awiri osinthika "anzeru" opangidwa kuchokera kumagulu wamba okhala ndi njira yachidule ya kiyibodi Ctrl+T kapena timu Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table):

Ndinaitana tebulo loyamba Deta, tebulo lachiwiri - Directorykugwiritsa ntchito munda Dzina latebulo (Dzina la tebulo) tsamba Constructor (Kapangidwe).

Ntchito: sinthani ma adilesi omwe ali patebulo Deta zochitika zonse kuchokera pagawo Kuti mupeze Bukuli kwa anzawo oyenerera omwe ali pagawoli M'malo. Malemba ena onse m'maselo azikhala osakhudzidwa.

Gawo 1. Kwezani chikwatu mu Mphamvu Query ndi kusandutsa mndandanda

Mukakhazikitsa selo logwira ntchito pamalo aliwonse pagulu lolozera, dinani tabu Deta (Tsiku)kapena pa tabu Kufunsa Mphamvu (ngati muli ndi mtundu wakale wa Excel ndipo mudayika Power Query ngati chowonjezera pa tabu yosiyana) pa batani Kuchokera pa tebulo/ranji (Kuchokera pa Table/Range).

Gome lolozera lidzakwezedwa mu Power Query query editor:

Pofuna kuti asasokoneze, sitepe yowonjezeredwa yokha mtundu wosinthidwa (Mtundu Wosinthidwa) mu gulu lamanja, masitepe ntchito akhoza bwinobwino zichotsedwa, kusiya sitepe yokha gwero (Chitsime):

Tsopano, kuti tisinthe zina ndi zina, tiyenera kusintha tebulo ili kukhala mndandanda (mndandanda).

Kuchotsa kwachinyengo

• Table ndi mbali ziwiri zokhala ndi mizere ingapo ndi mizati.
• Record (Rekodi) - Chingwe cha mbali imodzi, chokhala ndi magawo angapo okhala ndi mayina, mwachitsanzo [Dzina = “Masha”, Jenda = “f”, Zaka = 25]
  
• List - mzere wamtundu umodzi, wokhala ndi zinthu zingapo, mwachitsanzo {1, 2, 3, 10, 42} or {"Chikhulupiriro Chiyembekezo Chikondi"}
  

Kuthetsa vuto lathu, tidzakhala makamaka chidwi mtundu List.

Chinyengo apa ndikuti mndandanda wazinthu mu Power Query sungakhale manambala a banal kapena zolemba, komanso mindandanda ina kapena zolemba. Zili pamndandanda wovuta kwambiri (mndandanda), wokhala ndi zolemba (zolemba) zomwe tifunika kutembenuza chikwatu chathu. Mu Power Query syntactic notation (zolemba m'mabulaketi apakati, mindandanda m'mabulaketi opindika) izi zitha kuwoneka motere:

{

    [Pezani = "St. Petersburg", M'malo = "St. Petersburg”] ,

    [Pezani = "St. Petersburg", M'malo = "St. Petersburg”] ,

    [ Pezani = “Peter”, M’malo = “St. Petersburg”] ,

etc.

}

Kusintha kotereku kumachitika pogwiritsa ntchito ntchito yapadera ya chilankhulo cha M chomangidwa mu Power Query - Table.ToRecords. Kuti mugwiritse ntchito mwachindunji mu bar ya formula, yonjezerani ntchitoyi ku code code pamenepo gwero.

Zinali:

Pambuyo pake:

Pambuyo powonjezera ntchito ya Table.ToRecords, maonekedwe a tebulo lathu adzasintha - lidzasanduka mndandanda wa zolemba. Zomwe zili m'marekodi amodzi zitha kuwoneka pansi pagawo lowonera podina kumbuyo kwa cell pafupi ndi mawu aliwonse. mbiri (koma osati m'mawu amodzi!)

Kuphatikiza pa zomwe tafotokozazi, ndizomveka kuwonjezera sitiroko imodzi - ku cache (buffer) mndandanda wathu wopangidwa. Izi zidzakakamiza Power Query kukweza mndandanda wathu wowunika kamodzi m'mtima ndipo osawerengeranso pomwe tidzalowanso m'malo mwake. Kuti muchite izi, sungani fomula yathu mu ntchito ina - List.Buffer:

Kusungirako kotereku kudzapereka kuwonjezeka kwakukulu kwa liwiro (ndi kangapo!)

Izi zimamaliza kukonza bukhuli.

Zimakhalabe kuti alemba pa Kwanyumba - Tsekani ndikunyamula - Tsekani ndikunyamula ku... (Kunyumba — Tsekani & Katundu — Tsekani & Kwezani ku..), sankhani njira Ingopangani kulumikizana (Pangani kulumikizana kokha) ndi kubwerera ku Excel.

Khwerero 2. Kuyika tebulo la deta

Chilichonse ndichabwino apa. Monga kale ndi bukhu lofotokozera, timafika pamalo aliwonse patebulo, dinani pa tabu Deta batani Kuchokera pa Table/Range ndi tebulo lathu Deta amalowa mu Power Query. Zongowonjezera sitepe mtundu wosinthidwa (Mtundu Wosinthidwa) mutha kuchotsanso:

Palibe zochitika zapadera zokonzekera zomwe zimafunika kuti tichite nazo, ndipo timapita ku chinthu chofunikira kwambiri.

Gawo 3. Pangani zosintha pogwiritsa ntchito List.Accumulate ntchito

Tiyeni tiwonjezere gawo lowerengeka pa tebulo lathu la data pogwiritsa ntchito lamulo Kuwonjezera Mzere - Mzere Wamakonda (Onjezani gawo - Mzere Wamakonda): ndipo lowetsani dzina lagawo lowonjezera pawindo lomwe limatsegulidwa (mwachitsanzo, adilesi yokonzedwa) ndi ntchito yathu yamatsenga List.Wunjikana:

Zimakhalabe kuti alemba pa OK - ndipo timapeza ndime yokhala ndi zosintha zomwe zasinthidwa:

Zindikirani kuti:

• Popeza Power Query ndizovuta kwambiri, panalibe choloweza m'malo mwa mzere woyamba, chifukwa mu bukhuli tili ndi "SPb", osati "SPb".
• Ngati pali zingwe zingapo zomwe zingasinthidwe nthawi imodzi muzochokera (mwachitsanzo, pamzere wa 7 muyenera kusintha onse "S-Pb" ndi "Prospectus"), ndiye kuti izi sizimayambitsa vuto lililonse (mosiyana ndi kusintha ma formula kuchokera njira yakale).
• Ngati palibe chomwe chingasinthidwe m'mawu oyambira (mzere wa 9), ndiye kuti palibe zolakwika zomwe zimachitika (mosiyana, kachiwiri, kuchokera m'malo mwa mafomu).

Liwiro la pempho lotere ndilobwino kwambiri. Mwachitsanzo, pa tebulo la deta yoyambirira yokhala ndi mizere 5000, funsoli linasinthidwa pasanathe mphindi imodzi (popanda kusokoneza, mwa njira, pafupifupi masekondi atatu!)

Momwe ntchito ya List.Accumulate imagwirira ntchito

M'malo mwake, awa akhoza kukhala mathero (kuti ndilembe, komanso kuti muwerenge) nkhaniyi. Ngati mukufuna kuti musamangokhalira kutero, komanso kumvetsetsa momwe zimagwirira ntchito "pansi pa hood", ndiye kuti muyenera kulowa pansi pang'ono mu dzenje la kalulu ndikuthana ndi List.Accumulate ntchito, yomwe idachita zonse zambiri. ntchito kwa ife.

Syntax ya ntchitoyi ndi:

=Mndandanda.Wunjikana(mndandanda, mbewu, chosungira)

kumene

• mndandanda ndi mndandanda wazinthu zomwe tikubwereza. 
• mbewu - chikhalidwe choyambirira
• chosungira - ntchito yomwe imagwira ntchito zina (masamu, malemba, ndi zina zotero) pa chinthu chotsatira cha mndandanda ndikusonkhanitsa zotsatira za processing mu kusintha kwapadera.
  

Mwambiri, mawu ofotokozera polemba ntchito mu Power Query amawoneka motere:

(mkangano1, mkangano2, … mkanganoN) => zochita zina ndi mikangano

Mwachitsanzo, ntchito yomasulira ikhoza kuyimiridwa motere:

(a, b) => a + b

Kwa List.Accumulate , ntchitoyi ili ndi mfundo ziwiri zofunika (ikhoza kutchulidwa chirichonse, koma mayina achizolowezi ndi Boma и panopa, monga mu chithandizo chovomerezeka cha ntchitoyi, pamene:

• Boma - kusintha komwe zotsatira zake zimasonkhanitsidwa (mtengo wake woyamba ndi womwe watchulidwa pamwambapa mbewu)
  
• panopa - mtengo wobwerezabwereza wotsatira pamndandanda mndandanda
  

Mwachitsanzo, tiyeni tione masitepe a logic ya zomangamanga zotsatirazi:

=Mndandanda.Wunjikana({3, 2, 5}, 10, (dziko, panopa) => dziko + panopa)

Izi ndi zaposachedwa zomwe zasonkhanitsidwa Boma mtengo ndi List.Accumulate ntchito ndi zotsatira zake:

Ngati mumaganizira pang'ono, ndiye kugwiritsa ntchito List.Accumulate function, mukhoza kuyerekezera, mwachitsanzo, ntchito ya Excel CONCATENATE (mu Power Query, analogue yake imatchedwa. Zolemba.Phatikizani) pogwiritsa ntchito mawu akuti:

Kapena fufuzani mtengo wapamwamba (kutsanzira ntchito ya Excel's MAX, yomwe mu Power Query imatchedwa List.Max):

Komabe, mbali yaikulu ya List.Accumulate ndi luso lokonza osati zolemba zosavuta kapena manambala monga mitsutso, koma zinthu zovuta kwambiri - mwachitsanzo, mindandanda-kuchokera-mndandanda kapena mindandanda-kuchokera-zolemba (hello, Directory!)

Tiyeni tiwonenso zomanga zomwe zidasinthanso vuto lathu:

List.Wunjikira(Directory, [Adilesi], (dziko, panopa) => Mawu.Bwezerani(dziko, panopa[Pezani], panopa[Bwerani]) )

Kodi kwenikweni chikuchitika ndi chiyani pano?

1. Monga mtengo woyamba (mbewu) timatenga mawu oyamba osamveka bwino pagawo [Adilesi] tebulo lathu: 199034, St. Petersburg, St. Benga, d. 1
2. Kenako List.Accumulate ibwerezabwereza zinthu za mndandanda umodzi ndi umodzi - Bukuli. Chilichonse chomwe chili pamndandandawu ndi zolemba zomwe zili ndi magawo awiri "Zomwe mungapeze - Zomwe mungasinthe" kapena, mwa kuyankhula kwina, mzere wotsatira mu bukhuli.
3. Ntchito ya accumulator imayika mu variable Boma mtengo woyamba (adilesi yoyamba 199034, St. Petersburg, St. Benga, d. 1) ndipo imagwira ntchito yolimbikitsira pa iyo - ntchito yosinthira pogwiritsa ntchito muyezo wa M-function Lembani.Bwezerani (zofanana ndi ntchito ya Excel SUBSTITUTE). Mawu ake ndi awa:

   Text.Replace(zolemba zoyambirira, zomwe tikuyang'ana, zomwe tikulowetsamo)

   ndipo tili ndi:

• Boma ndi adilesi yathu yonyansa, yomwe ili mkati Boma (kuchokera mbewu)
  
• panopa[Sakani] - mtengo wamunda Kuti mupeze kuchokera pamndandanda wobwerezabwereza Directory, yomwe ili mu variable panopa
• zamakono[Bwezerani] - mtengo wamunda M'malo kuchokera pamndandanda wobwerezabwereza Directoryatagona mkati panopa

Chifukwa chake, pa adilesi iliyonse, kuwerengera kwathunthu kwa mizere yonse mu bukhuli kumayendetsedwa nthawi iliyonse, m'malo mwake mawu kuchokera pagawo la [Pezani] ndi mtengo wochokera pagawo la [Replace].

Ndikukhulupirira kuti mwapeza lingaliro 🙂

• Zochulukira m'malo mwa mawu pamndandanda pogwiritsa ntchito ma fomula
  
• Mawu Okhazikika (RegExp) mu Power Query