Kako koristiti Power Query u programu Excel?
Excel Power Query koristi se za pretraživanje izvora podataka, povezivanje s izvorima podataka, a zatim oblikovanje podataka prema našim zahtjevima za analizom. Nakon što završimo s oblikovanjem podataka prema našim potrebama, također možemo podijeliti svoja otkrića i stvoriti različita izvješća koristeći više upita.
Koraci
U osnovi postoje 4 koraka, a redoslijed tih 4 koraka u programu Power Query je sljedeći:
- Povezivanje: Prvo se povezujemo s podacima koji mogu biti negdje, u oblaku, u službi ili lokalno.
- Transformacija: Drugi bi korak bio promjena oblika podataka prema zahtjevu korisnika.
- Kombiniranje: U ovom koraku izvodimo neke korake transformacije i agregiranja i kombiniramo podatke iz oba izvora kako bismo stvorili kombinirano izvješće.
- Upravljanje: Ovo spaja i dodaje stupce u upitu s odgovarajućim stupcima u drugim upitima u radnoj knjizi.
Mnogo je moćnih značajki programa Excel Power Query.
Pretpostavimo da podatke o kupnji za posljednjih 15 godina imamo u 180 datoteka. Sada bi menadžment organizacije trebao konsolidirati brojeve prije nego što ih analizira. Uprava može poduzeti bilo koju od sljedećih metoda:
- Otvorili bi sve datoteke i kopirali ih u jednu datoteku.
- S druge strane, oni mogu koristiti mudro rješenje, a to je primjena formula jer je sklono pogreškama.
Koju god metodu odabrali, ona sadrži puno ručnog rada, a nakon nekoliko mjeseci bi se pojavili novi podaci o prodaji za dodatno trajanje. Morat će ponoviti istu vježbu.
Međutim, Power Query može im pomoći da ne rade ovaj dosadan i ponavljajući posao. Razumijemo ovaj excel power upit na primjeru.
Primjer
Pretpostavimo da imamo tekstualne datoteke u mapi s podacima o prodaji, a te podatke želimo dobiti u našoj excel datoteci.
Kao što možemo vidjeti na donjoj slici da u mapi imamo dvije vrste datoteka, ali želimo dobiti podatke samo tekstualnih datoteka u datoteci excel.
Da biste učinili isto, koraci bi bili:
Korak 1: Prvo moramo dobiti podatke u Power Query-u kako bismo mogli izvršiti potrebne promjene u podacima da bismo ih uvezli u excel datoteku.
Da bismo učinili isto, odabrat ćemo opciju "Iz mape" na izborniku "Iz datoteke" nakon klika na naredbu "Dohvati podatke" iz grupe "Dohvati i transformiraj" na kartici "Podaci" .
Korak 2: Pregledavanjem odaberite mjesto mape.
Kliknite "U redu"
Korak 3: Otvorit će se dijaloški okvir koji sadrži popis svih datoteka u odabranoj mapi s zaglavljima stupaca kao "Sadržaj", "Ime", "Proširenje", "Pristup datumu", "Datum izmjene", "Datum kreiranja" 'Atributi' i 'Put mape'.
Postoje 3 mogućnosti, tj. Kombiniraj , učitaj i transformiraj podatke .
- Kombiniraj : Ova se opcija koristi za odlazak na zaslon na kojem možemo odabrati koje podatke kombinirati. Korak uređivanja preskače se za ovu opciju i ne daje kontrolu nad datotekama koje ćemo kombinirati. Funkcija kombiniranja uzima za konsolidaciju svaku datoteku u mapi, što može dovesti do pogrešaka.
- Učitavanje: Ova će opcija samo učitati tablicu kako je gore prikazano na slici u Excel radni list umjesto stvarnih podataka u datotekama.
- Transformacija podataka: Za razliku od naredbe 'Kombiniraj' , ako koristimo ovu naredbu, tada možemo odabrati koje ćemo datoteke kombinirati, tj. Možemo kombinirati samo jednu vrstu datoteke (isto proširenje).
Kao i u našem slučaju, i mi želimo kombinirati samo tekstualne datoteke (.txt); izabrat ćemo naredbu "Transform Data" .
Na desnoj strani prozora možemo vidjeti "Primijenjene korake". Za sada je napravljen samo jedan korak koji je preuzimanje podataka o datotekama iz mape.
Korak 4: Postoji stupac nazvan 'Proširenje' u kojem možemo vidjeti da su vrijednosti u stupcu zapisane u oba slučaja, tj. Velikim i malim slovima.
Međutim, moramo pretvoriti sve vrijednosti u mala slova jer filtar razlikuje obje. Da učine isto, moramo odabrati stupac i zatim odaberite „malim slovima” iz „Format” izbornika naredba je.
Korak 5: Podatke ćemo filtrirati pomoću stupca 'Proširenje' za tekstualne datoteke.
Korak 6: Moramo kombinirati podatke za obje tekstualne datoteke sada koristeći prvi stupac "Sadržaj". Kliknut ćemo na ikonu s desne strane naziva stupca.
Korak 7: Otvorit će se dijaloški okvir s nazivom "Kombiniraj datoteke" u kojem trebamo odabrati graničnik kao "Tab" za tekstualne datoteke (datoteke s nastavkom ".txt") i možemo odabrati bazu za otkrivanje vrste podataka. I kliknite "U redu".
Nakon klika na "U redu" dobit ćemo kombinirane podatke tekstualnih datoteka u prozoru "Power Query" .
Tip podataka stupaca možemo mijenjati prema potrebi. Za stupac "Prihod" promijenit ćemo vrstu podataka u "Valuta".
Korake primijenjene na podatke možemo vidjeti pomoću upita za napajanje s desne strane prozora.
Nakon što izvršimo sve potrebne promjene u podacima, podatke možemo učitati u excel radni list pomoću naredbe „Zatvori i učitaj u“ u grupi „Zatvori“ na kartici „Početna“ .
Moramo odabrati želimo li podatke učitati kao tablicu ili vezu. Zatim kliknite "U redu".
Sada podatke možemo vidjeti kao tablicu na radnom listu.
I okno 'Upiti radne knjige' s desne strane, koje možemo koristiti za uređivanje, umnožavanje, spajanje, dodavanje upita i u mnoge druge svrhe.
Excel Power Query vrlo je koristan jer vidimo da je u roku od nekoliko minuta učitano 601.612 redaka.
Stvari koje treba zapamtiti
- Power Query ne mijenja izvorne izvorne podatke. Umjesto da mijenja izvorne izvorne podatke, bilježi svaki korak koji korisnik poduzima tijekom povezivanja ili transformiranja podataka, a nakon što korisnik dovrši oblikovanje podataka, uzima pročišćeni skup podataka i unosi ga u radnu knjigu.
- Power Query razlikuje velika i mala slova.
- Tijekom konsolidacije datoteka u navedenoj mapi, moramo biti sigurni da koristimo stupac 'Proširenje' i moramo izuzeti privremene datoteke (s nastavkom '.tmp' i naziv tih datoteka započinje znakom '~') kao Power Query može uvoziti i ove datoteke.
