Kako uporabiti Power Query v Excelu?
Excel Power Query se uporablja za iskanje podatkovnih virov, povezovanje z njimi in njihovo oblikovanje v skladu z našo analizo. Ko končamo z oblikovanjem podatkov glede na naše potrebe, lahko svoje ugotovitve tudi delimo in z več poizvedbami ustvarimo različna poročila.
Koraki
V bistvu obstajajo 4 koraki, vrstni red teh 4 korakov v Power Query pa je naslednji:
- Poveži: Najprej se povežemo s podatki, ki so lahko nekje, v oblaku, v storitvi ali lokalno.
- Preoblikovanje: Drugi korak bi bil spremeniti obliko podatkov glede na zahteve uporabnika.
- Združi: V tem koraku izvedemo nekaj korakov preoblikovanja in združevanja ter združimo podatke iz obeh virov, da dobimo kombinirano poročilo.
- Upravljanje: To združi in doda stolpce v poizvedbi z ujemajočimi se stolpci v drugih poizvedbah v delovnem zvezku.
Obstaja veliko izjemno zmogljivih funkcij programa Excel Power Query.
Recimo, da imamo podatke o nakupih za zadnjih 15 let v 180 datotekah. Zdaj bi vodstvo organizacije zahtevalo konsolidacijo številk, preden jih analizira. Vodstvo lahko uporabi katero koli od naslednjih metod:
- Odprli bi vse datoteke in jih kopirali in prilepili v eno datoteko.
- Po drugi strani pa lahko uporabijo pametno rešitev, to je uporabo formul, saj je nagnjena k napakam.
Ne glede na način, ki ga izberejo, vsebuje veliko ročnega dela, po nekaj mesecih pa bodo novi podatki o prodaji dodani. Ponovno bodo morali opraviti isto vajo.
Vendar jim Power Query lahko pomaga, da tega dolgočasnega in ponavljajočega se dela ne opravljajo. Razumimo to poizvedbo excel power na primeru.
Primer
Recimo, da imamo besedilne datoteke v mapi s prodajnimi podatki in jih želimo dobiti v naši datoteki excel.
Kot lahko vidimo na spodnji sliki, da imamo v mapi dve vrsti datotek, želimo pa pridobiti podatke samo besedilnih datotek v datoteki excel.
Če želite narediti enako, bi bili naslednji koraki:
1. korak: Najprej moramo podatke pridobiti v Power Queryju, da bomo lahko podatke spremenili, da jih uvozimo v datoteko excel.
Da storijo enako, bomo izbrali "iz mape" možnost v "From File" menu po kliku na ukaz "Get Data" iz "Get & transformacijo" skupina v "Data" kartici.
2. korak: z brskanjem izberite mesto mape.
Kliknite 'V redu'
3. korak: Odpre se pogovorno okno s seznamom vseh datotek v izbrani mapi z naslovi stolpcev kot »Vsebina«, »Ime«, »Razširitev«, »Datum dostopa«, »Datum spremembe«, »Datum ustvarjanja«. 'Atributi' in 'Pot mape.'
Obstajajo 3 možnosti, in sicer Združitev , Nalaganje in Preoblikovanje podatkov .
- Združi : Ta možnost se uporablja za prehod na zaslon, kjer lahko izberemo, katere podatke bomo združili. Korak urejanja je pri tej možnosti preskočen in nam ne daje nadzora nad datotekami, ki jih želimo združiti. Funkcija združevanja konsolidira vse datoteke v mapi, kar lahko vodi do napak.
- Naloži: Ta možnost bo le naložila tabelo, kot je prikazano zgoraj na sliki, v Excelov delovni list namesto dejanskih podatkov v datotekah.
- Preoblikovanje podatkov: Za razliko od ukaza 'Združi' , če uporabimo ta ukaz, lahko izberemo, katere datoteke bomo kombinirali, torej lahko združimo samo eno vrsto datoteke (enaka pripona).
Kot v našem primeru želimo tudi mi združiti samo besedilne datoteke (.txt); izbrali bomo ukaz »Transform Data« .
Na desni strani okna lahko vidimo “Applied Steps”. Za zdaj je narejen le en korak, ki je, da iz mape vzamemo podrobnosti o datotekah.
4. korak: Obstaja stolpec z imenom »Razširitev«, kjer lahko vidimo, da so vrednosti v stolpcu zapisane v obeh primerih, torej v velikih in malih črkah.
Vse vrednosti pa moramo pretvoriti v male črke, saj filter razlikuje med obema. Da storijo enako, moramo izbrati stolpec in nato izberite "male črke" s strani "Format" meniju ukaza.
5. korak: Podatke bomo filtrirali s pomočjo stolpca »Razširitev« za besedilne datoteke.
6. korak: Zdaj moramo v prvem stolpcu »Vsebina« združiti podatke za obe besedilni datoteki . Kliknili bomo na ikono na desni strani imena stolpca.
Korak 7: Odpre se pogovorno okno z napisom »Združi datoteke«, kjer moramo za besedilne datoteke (datoteke z razširitvijo ».txt«) določiti ločilo kot »Tab« in izbrati osnovo za zaznavanje podatkovnega tipa. In kliknite »V redu«.
Po kliku na »V redu« bomo v oknu »Power Query« dobili združene podatke besedilnih datotek .
Po potrebi lahko spremenimo podatkovni tip stolpcev. V stolpcu »Prihodki« bomo spremenili vrsto podatkov v »Valuta«.
Korake, ki so bili uporabljeni za podatke, lahko vidimo s poizvedbo o napajanju na desni strani okna.
Po vseh zahtevanih spremembah podatkov lahko podatke naložimo v Excelov delovni list z ukazom »Zapri in naloži v« v skupini »Zapri« na zavihku »Domov «.
Izbrati moramo, ali želimo podatke naložiti kot tabelo ali povezavo. Nato kliknite »V redu«.
Zdaj lahko na delovnem listu vidimo podatke kot tabelo.
In podokno 'Poizvedbe v delovnem zvezku' na desni strani, ki ga lahko uporabimo za urejanje, podvajanje, združevanje, dodajanje poizvedb in za številne druge namene.
Excel Power Query je zelo koristen, saj lahko vidimo, da je bilo v nekaj minutah naloženih 601.612 vrstic.
Stvari, ki si jih je treba zapomniti
- Power Query ne spremeni prvotnih izvornih podatkov. Namesto da spremeni izvorne izvorne podatke, zabeleži vsak korak, ki ga uporabnik opravi med povezovanjem ali preoblikovanjem podatkov, in ko uporabnik dokonča oblikovanje podatkov, vzame izpopolnjen nabor podatkov in ga vnese v delovni zvezek.
- Power Query razlikuje med velikimi in malimi črkami.
- Med konsolidacijo datotek v določeni mapi moramo zagotoviti, da uporabljamo stolpec »Razširitev«, začasne datoteke (s pripono ».tmp« in imenom teh datotek pa se začne z znakom »~«) moramo izključiti kot Power Query lahko te datoteke tudi uvozi.
