sadržaj
Jedan od najgledanijih videa na mom YouTube kanalu je video o Flash Fillu u Microsoft Excel-u. Suština ovog alata je da ako trebate nekako transformirati svoje izvorne podatke, onda samo trebate početi upisivati rezultat koji želite da dobijete u susjednu kolonu. Nakon nekoliko ručno upisanih ćelija (obično su dovoljne 2-3), Excel će "razumjeti" logiku transformacija koje su vam potrebne i automatski će nastaviti ono što ste upisali, dovršavajući sav monoton posao za vas:
Kvintesencija efikasnosti. Čarobno dugme „uradi kako treba“ koje svi toliko volimo, zar ne?
Zapravo, postoji analog takvog alata u Power Queryju - tamo se zove Kolona iz primjera (Kolona iz primjera). Zapravo, ovo je mala umjetna inteligencija ugrađena u Power Query koja može brzo učiti iz vaših podataka i zatim ih transformirati. Pogledajmo bliže njegove mogućnosti u nekoliko praktičnih scenarija kako bismo shvatili gdje nam može biti od koristi u stvarnim zadacima.
Primjer 1. Lijepljenje/rezanje teksta
Recimo da imamo takvu „pametnu“ tabelu u Excelu sa podacima o zaposlenima:
Učitajte ga u Power Query na standardni način – pomoću dugmeta Iz tabele/opseg tabulator podaci (Podaci — iz tabele/opseg).
Pretpostavimo da trebamo dodati kolonu s prezimenima i inicijalima za svakog zaposlenika (Ivanov SV za prvog zaposlenika, itd.). Da biste riješili ovaj problem, možete koristiti jednu od dvije metode:
- kliknite desnim tasterom miša na naslov kolone sa izvornim podacima i izaberite komandu Dodajte kolonu iz primjera (Dodajte kolonu iz primjera);
- odaberite jednu ili više kolona s podacima i na kartici Dodavanje kolone izabrati tim Kolona iz primjera. Ovdje, u padajućoj listi, možete odrediti da li je potrebno analizirati sve ili samo odabrane kolone.
Tada je sve jednostavno – u stupac koji se pojavljuje s desne strane počinjemo unositi primjere željenih rezultata, a umjetna inteligencija ugrađena u Power Query pokušava razumjeti našu logiku transformacije i nastaviti dalje samostalno:
Usput, ispravne opcije možete unijeti u bilo koju ćeliju ove kolone, tj. ne nužno odozgo prema dolje i u nizu. Također, kasnije možete lako dodati ili ukloniti stupce iz analize koristeći potvrdne okvire u naslovnoj traci.
Obratite pažnju na formulu na vrhu prozora – to je ono što pametni Power Query stvara da bi dobili rezultate koji su nam potrebni. Ovo je, inače, osnovna razlika između ovog alata i Instant fill u Excelu. Instant punjenje funkcionira kao “crna kutija” – ne pokazuju nam logiku transformacija, već jednostavno daju gotove rezultate i mi ih uzimamo zdravo za gotovo. Ovdje je sve transparentno i uvijek možete apsolutno jasno razumjeti šta se tačno dešava sa podacima.
Ako vidite da je Power Query „uhvatio ideju“, onda možete bezbedno da pritisnete dugme OK ili prečica na tastaturi Ctrl+ući – kreiraće se prilagođena kolona sa formulom koju je izmislio Power Query. Usput, kasnije se može lako uređivati kao obična ručno kreirana kolona (uz pomoć naredbe Dodavanje kolone – Prilagođena kolona) klikom na ikonu zupčanika desno od naziva koraka:
Primjer 2: Padež kao u rečenicama
Ako desnom tipkom miša kliknete naslov stupca s tekstom i odaberete naredbu transformacija (Transformacija), tada možete vidjeti tri naredbe odgovorne za promjenu registra:
Zgodno i cool, ali na ovoj listi, na primjer, meni lično je uvijek nedostajala još jedna opcija – slučaj kao u rečenicama, kada veliko (veliko) ne postaje prvo slovo u svakoj riječi, već samo prvo slovo u ćeliji, i ostatak teksta kada se ovo prikazuje malim (malim) slovima.
Ova funkcija koja nedostaje lako je implementirati pomoću umjetne inteligencije Kolumne iz primjera – samo unesite nekoliko opcija za Power Query da biste nastavili u istom duhu:
Kao formula ovdje, Power Query koristi gomilu funkcija Text.Upper и Text.Lower, pretvaranje teksta u velika i mala slova, respektivno, i funkcije Text.Start и Text.Mid – analogi Excel funkcija LIJEVO i PSTR, koji mogu izdvojiti podniz iz teksta slijeva i iz sredine.
Primjer 3. Permutacija riječi
Ponekad, prilikom obrade primljenih podataka, postaje potrebno preurediti riječi u ćelijama u datom nizu. Naravno, kolonu možete podijeliti u zasebne stupce riječi pomoću separatora, a zatim ga zalijepiti natrag određenim redoslijedom (ne zaboravite dodati razmake), ali uz pomoć alata Kolona iz primjera sve će biti mnogo lakše:
Primjer 4: Samo brojevi
Još jedan veoma važan zadatak je izvlačenje samo brojeva (brojeva) iz sadržaja ćelije. Kao i prije, nakon učitavanja podataka u Power Query idite na karticu Dodavanje kolone – Kolona iz primjera i ručno popunite nekoliko ćelija kako bi program shvatio šta tačno želimo da dobijemo:
Bingo!
Opet, vrijedi pogledati na vrh prozora kako biste bili sigurni da je Query ispravno generirao formulu – u ovom slučaju sadrži funkciju Tekst. Odaberite, koji, kao što možete pretpostaviti, izdvaja date znakove iz izvornog teksta prema listi. Nakon toga, ova lista se, naravno, može lako urediti u traci formule ako je potrebno.
Primjer 5: Samo tekst
Slično kao u prethodnom primjeru, možete izvući i obrnuto – samo tekst, brisanje svih brojeva, znakova interpunkcije itd.
U ovom slučaju se koristi funkcija koja je već suprotna po značenju – Text.Remove, koja uklanja znakove iz originalnog niza prema datoj listi.
Primjer 6: Ekstrahiranje podataka iz alfanumeričke kaše
Power Query može pomoći i u težim slučajevima, kada trebate izvući korisne informacije iz alfanumeričke kaše u ćeliji, na primjer, dobiti broj računa iz opisa svrhe plaćanja na bankovnom izvodu:
Imajte na umu da formula konverzije koju je generirao Power Query može biti prilično složena:
Radi lakšeg čitanja i razumijevanja, može se pretvoriti u mnogo razumniji oblik pomoću besplatne online usluge. Power Query Formater:
Veoma zgodna stvar - svaka čast kreatorima!
Primjer 7: Konvertiranje datuma
alatka Kolona iz primjera može se primijeniti i na stupce datuma ili vremena. Kada unesete prve znamenke datuma, Power Query će pomoći prikazati listu svih mogućih opcija konverzije:
Tako da možete lako pretvoriti originalni datum u bilo koji egzotičan format, kao što je "godina-mjesec-dan":
Primjer 8: Kategorizacija
Ako koristimo alat Kolona iz primjera na kolonu sa numeričkim podacima, radi drugačije. Pretpostavimo da imamo rezultate testa zaposlenika učitane u Power Query (uslovni rezultati u rasponu od 0-100) i koristimo sljedeću uslovnu gradaciju:
- Masters – oni koji su postigli više od 90 poena
- Stručnjaci – rezultat od 70 do 90
- Korisnici – od 30 do 70
- Početnici – oni koji su postigli manje od 30 bodova
Ako na listu dodamo kolonu iz primjera i počnemo ručno slagati ove gradacije, vrlo brzo će Power Query preuzeti našu ideju i dodati stupac s formulom, gdje su operatori ugniježđeni jedan u drugi if logika će biti implementirana, vrlo slična onome što nam je potrebno:
Opet, ne možete pritisnuti situaciju do kraja, već kliknite na OK a zatim ispravite granične vrijednosti već u formuli - brže je na ovaj način:
zaključci
Svakako alat Kolona iz primjera nije "čarobna pilula" i prije ili kasnije će se u podacima pojaviti nestandardne situacije ili posebno zanemareni slučajevi "kolektivne farme", kada Power Query neće uspjeti i neće moći riješiti ono što želimo ispravno za nas. Međutim, kao pomoćni alat je vrlo dobar. Osim toga, proučavanjem formula koje je on generirao, možete proširiti svoje znanje o funkcijama M jezika, što će vam uvijek dobro doći u budućnosti.
- Parsiranje teksta sa regularnim izrazima (RegExp) u Power Queryju
- Pretraživanje nejasnog teksta u Power Queryju
- Flash Fill u Microsoft Excelu