Halimbawang Column – Artificial Intelligence sa Power Query

Isa sa mga pinakapinapanood na video sa aking channel sa YouTube ay isang video tungkol sa Flash Fill sa Microsoft Excel. Ang kakanyahan ng tool na ito ay kung kailangan mong baguhin ang iyong source data, kailangan mo lang simulan ang pag-type ng resulta na gusto mong makuha sa katabing column. Pagkatapos ng ilang manu-manong nai-type na mga cell (karaniwan ay sapat na ang 2-3), "maiintindihan" ng Excel ang lohika ng mga pagbabagong kailangan mo at awtomatikong ipagpapatuloy ang iyong na-type, na kinukumpleto ang lahat ng walang pagbabago na gawain para sa iyo:

Ang quintessence ng kahusayan. Ang magic na "do it right" na buton na mahal na mahal nating lahat, tama ba?

Sa katunayan, mayroong isang analogue ng naturang tool sa Power Query - doon ito tinatawag Kolum mula sa mga halimbawa (Haligi mula sa Mga Halimbawa). Sa katunayan, ito ay isang maliit na artificial intelligence na binuo sa Power Query na maaaring mabilis na matuto mula sa iyong data at pagkatapos ay baguhin ito. Tingnan natin ang mga kakayahan nito sa ilang praktikal na sitwasyon upang maunawaan kung saan ito maaaring maging kapaki-pakinabang sa atin sa mga totoong gawain.

Halimbawa 1. Pagdikit/pagputol ng teksto

Sabihin nating mayroon kaming ganoong "matalinong" talahanayan sa Excel na may data sa mga empleyado:

I-load ito sa Power Query sa karaniwang paraan – gamit ang button Mula sa Table/Range tab data (Data — Mula sa Talahanayan/Saklaw).

Ipagpalagay na kailangan nating magdagdag ng column na may mga apelyido at inisyal para sa bawat empleyado (Ivanov SV para sa unang empleyado, atbp.). Upang malutas ang problemang ito, maaari mong gamitin ang isa sa dalawang pamamaraan:

• i-right-click sa heading ng column na may source data at piliin ang command Magdagdag ng column mula sa mga halimbawa (Magdagdag ng column mula sa mga halimbawa);

• pumili ng isa o higit pang column na may data at sa tab Pagdaragdag ng column pumili ng isang pangkat Kolum mula sa mga halimbawa. Dito, sa drop-down na listahan, maaari mong tukuyin kung lahat o mga napiling column lang ang kailangang suriin.

Pagkatapos ang lahat ay simple – sa column na lumilitaw sa kanan, magsisimula kaming maglagay ng mga halimbawa ng mga nais na resulta, at ang artificial intelligence na binuo sa Power Query ay sumusubok na maunawaan ang aming lohika ng pagbabagong-anyo at magpatuloy nang higit pa sa sarili nitong:

Sa pamamagitan ng paraan, maaari mong ipasok ang mga tamang pagpipilian sa anumang mga cell ng column na ito, ibig sabihin, hindi kinakailangang top-down at sa isang hilera. Gayundin, maaari mong madaling magdagdag o mag-alis ng mga column mula sa pagsusuri sa ibang pagkakataon gamit ang mga checkbox sa title bar.

Bigyang-pansin ang formula sa itaas ng window – ito ang ginagawa ng matalinong Power Query para makuha ang mga resultang kailangan namin. Ito, sa pamamagitan ng paraan, ay ang pangunahing pagkakaiba sa pagitan ng tool na ito at Instant fill sa Excel. Gumagana ang instant filling tulad ng isang "itim na kahon" - hindi nila ipinapakita sa amin ang lohika ng mga pagbabago, ngunit nagbibigay lamang ng mga handa na resulta at tinatanggap namin ang mga ito para sa ipinagkaloob. Narito ang lahat ay transparent at maaari mong palaging ganap na malinaw na maunawaan kung ano ang eksaktong nangyayari sa data.

Kung nakita mong "nakuha ng Power Query ang ideya", maaari mong ligtas na pindutin ang button OK o keyboard shortcut Ctrl+Magpasok – gagawa ng custom na column na may formula na naimbento ng Power Query. Sa pamamagitan ng paraan, maaari itong madaling i-edit sa ibang pagkakataon bilang isang regular na manu-manong nilikha na column (na may command Pagdaragdag ng Column – Custom Column) sa pamamagitan ng pag-click sa icon na gear sa kanan ng pangalan ng hakbang:

Halimbawa 2: Kaso tulad ng sa mga pangungusap

Kung nag-right-click ka sa heading ng column na may teksto at piliin ang command Pagbabagong-anyo (Pagbabago), pagkatapos ay makikita mo ang tatlong utos na responsable para sa pagbabago ng rehistro:

Maginhawa at cool, ngunit sa listahang ito, halimbawa, ako ay personal na palaging kulang ng isa pang pagpipilian - kaso tulad ng sa mga pangungusap, kapag ang capitalization (kapital) ay hindi ang unang titik sa bawat salita, ngunit ang unang titik lamang sa cell, at ang natitirang bahagi ng teksto kapag ito ay ipinapakita sa maliliit na titik (maliit).

Ang nawawalang feature na ito ay madaling ipatupad gamit ang artificial intelligence Mga hanay mula sa mga halimbawa – magpasok lamang ng ilang opsyon para sa Power Query upang magpatuloy sa parehong diwa:

Bilang isang formula dito, ang Power Query ay gumagamit ng isang grupo ng mga function Text.Upper и Text.Lower, pag-convert ng text sa upper at lower case, ayon sa pagkakabanggit, at mga function Text.Start и Text.Mid – mga analogue ng Excel functions LEFT at PSTR, na nakakakuha ng substring mula sa text mula sa kaliwa at mula sa gitna.

Halimbawa 3. Permutasyon ng mga salita

Minsan, kapag pinoproseso ang natanggap na data, kinakailangan na muling ayusin ang mga salita sa mga cell sa isang naibigay na pagkakasunud-sunod. Siyempre, maaari mong hatiin ang hanay sa magkahiwalay na mga hanay ng salita ng separator at pagkatapos ay idikit ito pabalik sa tinukoy na pagkakasunud-sunod (huwag kalimutang magdagdag ng mga puwang), ngunit sa tulong ng tool Kolum mula sa mga halimbawa magiging mas madali ang lahat:

Halimbawa 4: Mga numero lamang

Ang isa pang napakahalagang gawain ay ang paglabas lamang ng mga numero (mga numero) mula sa mga nilalaman ng cell. Gaya ng dati, pagkatapos mag-load ng data sa Power Query, pumunta sa tab Pagdaragdag ng column – Column mula sa mga halimbawa at manu-manong punan ang isang pares ng mga cell upang maunawaan ng programa kung ano ang eksaktong gusto nating makuha:

Bingo!

Muli, ito ay nagkakahalaga ng pagtingin sa tuktok ng window upang matiyak na nabuo ng Query ang formula nang tama - sa kasong ito, naglalaman ito ng isang function Text. Pumili, na, gaya ng maaari mong hulaan, ay kinukuha ang ibinigay na mga character mula sa pinagmulang teksto ayon sa listahan. Sa dakong huli, ang listahang ito, siyempre, ay madaling ma-edit sa formula bar kung kinakailangan.

Halimbawa 5: Text lang

Katulad ng nakaraang halimbawa, maaari mong i-pull out at vice versa – ang text lang, pagtanggal ng lahat ng numero, mga bantas, atbp.

Sa kasong ito, ginagamit ang isang function na kabaligtaran na ang kahulugan - Text.Remove, na nag-aalis ng mga character mula sa orihinal na string ayon sa isang ibinigay na listahan.

Halimbawa 6: Pagkuha ng data mula sa isang alphanumeric na sinigang

Makakatulong din ang Power Query sa mas mahirap na mga kaso, kapag kailangan mong kunin ang kapaki-pakinabang na impormasyon mula sa alphanumeric na lugaw sa isang cell, halimbawa, kunin ang account number mula sa paglalarawan ng layunin ng pagbabayad sa isang bank statement:

Tandaan na ang formula ng conversion na nabuo ng Power Query ay maaaring maging kumplikado:

Para sa kadalian ng pagbabasa at pag-unawa, maaari itong ma-convert sa isang mas matino na anyo gamit ang isang libreng online na serbisyo. Power Query Formatter:

Napakadaling bagay - paggalang sa mga tagalikha!

Halimbawa 7: Pag-convert ng mga petsa

Kasangkapan Kolum mula sa mga halimbawa maaaring ilapat sa mga column ng date o datetime. Kapag inilagay mo ang mga unang digit ng isang petsa, makakatulong ang Power Query na magpapakita ng listahan ng lahat ng posibleng opsyon sa conversion:

Kaya madali mong mai-convert ang orihinal na petsa sa anumang kakaibang format, gaya ng “year-month-day”:

Halimbawa 8: Pagkakategorya

Kung gagamitin natin ang tool Kolum mula sa mga halimbawa sa isang column na may numeric na data, ito ay gumagana nang iba. Ipagpalagay na mayroon kaming mga resulta ng pagsusulit ng empleyado na na-load sa Power Query (mga kondisyong marka sa hanay na 0-100) at ginagamit namin ang sumusunod na kondisyonal na gradasyon:

• Masters – ang mga nakakuha ng higit sa 90
• Mga Eksperto – nakapuntos mula 70 hanggang 90
• Mga gumagamit - mula 30 hanggang 70
• Mga Beginners – ang mga nakakuha ng mas mababa sa 30

Kung magdaragdag kami ng column mula sa mga halimbawa sa listahan at sisimulan naming ayusin ang mga gradasyong ito nang manu-mano, sa lalong madaling panahon kukunin ng Power Query ang aming ideya at magdagdag ng column na may formula, kung saan ang mga operator ay nakipag-nested sa isa't isa. if ipapatupad ang lohika, halos kapareho sa kailangan natin:

Muli, hindi mo maaaring pindutin ang sitwasyon hanggang sa dulo, ngunit mag-click sa OK at pagkatapos ay itama ang mga halaga ng threshold na nasa formula - mas mabilis ito sa ganitong paraan:

Konklusyon

Tiyak na isang kasangkapan Kolum mula sa mga halimbawa ay hindi isang "magic pill" at, maaga o huli, magkakaroon ng mga hindi pamantayang sitwasyon o lalo na ang napapabayaang mga kaso ng isang "collective farm" sa data, kapag ang Power Query ay mabibigo at hindi magagawa kung ano ang gusto natin tama para sa atin. Gayunpaman, bilang isang pantulong na tool, ito ay napakahusay. Dagdag pa, sa pamamagitan ng pag-aaral ng mga formula na kanyang nabuo, maaari mong palawakin ang iyong kaalaman sa mga function ng wikang M, na palaging magiging kapaki-pakinabang sa hinaharap.

• Pag-parse ng Text gamit ang Regular Expressions (RegExp) sa Power Query
• Malabo na paghahanap ng teksto sa Power Query
• Flash Fill sa Microsoft Excel