Power Query može biti od velike pomoći ako želite kombinirati više radnih knjiga u jednu radnu knjigu.
Na primjer, pretpostavimo da imate podatke o prodaji za različite regije (istok, zapad, sjever i jug). Pomoću programa Power Query možete kombinirati te podatke iz različitih radnih knjiga u jedan radni list.
Ako imate ove radne knjige na različitim lokacijama/mapama, bilo bi dobro premjestiti ih u jednu mapu (ili stvoriti kopiju i staviti tu kopiju radne knjige u istu mapu).
Dakle, za početak, imam četiri radne knjige u mapi (kao što je prikazano u nastavku).
Sada u ovom vodiču pokrivam tri scenarija u kojima možete kombinirati podatke iz različitih radnih knjiga pomoću programa Power Query:
- Svaka radna knjiga ima podatke u tablici programa Excel i svi su nazivi tablica isti.
- Svaka radna knjiga ima podatke s istim nazivom radnog lista. To može biti slučaj kada u svim radnim knjigama postoji list pod nazivom „sažetak“ ili „podaci“, a sve to želite kombinirati.
- Svaka radna knjiga ima mnogo listova i tablica, a želite kombinirati određene tablice/listove. Ova metoda također može biti korisna kada želite kombinirati tablicu/listove koji nemaju dosljedan naziv.
Pogledajmo kako u svakom slučaju kombinirati podatke iz ovih radnih knjiga.
Svaka radna knjiga ima podatke u Excel tablici s istom strukturom
Tehnika u nastavku funkcionirala bi kada su vaše Excel tablice strukturirane na isti način (isti nazivi stupaca).
Broj redaka u svakoj tablici može varirati.
Ne brinite ako neke tablice programa Excel imaju dodatne stupce. Možete odabrati jednu od tablica kao predložak (ili kao "ključ" kako ga naziva Power Query), a Power Query bi ga upotrijebio za kombiniranje svih ostalih tablica programa Excel s njim.
U slučaju da u drugim tablicama postoje dodatni stupci, oni će se zanemariti, a kombinirat će se samo oni navedeni u predlošku/ključu. Na primjer, ako predložak/tablica ključeva koji odaberete ima 5 stupaca, a jedna od tablica u nekoj drugoj radnoj knjizi ima 2 dodatna stupca, ti dodatni stupci će se zanemariti.
Sada imam četiri radne knjige u mapi koju želim kombinirati.
Ispod je snimka tablice koju imam u jednoj od radnih knjiga.
Evo koraka za kombiniranje podataka iz ovih radnih knjiga u jednu radnu knjigu (kao jednu tablicu).
- Idite na karticu Podaci.
- U grupi Get & Transform kliknite padajući izbornik New Query.
- Zadržite pokazivač miša na "Iz datoteke" i kliknite na "Iz mape".
- U dijaloškom okviru Mapa unesite put datoteke do mape s datotekama ili kliknite Pregledaj i pronađite mapu.
- Pritisnite U redu.
- U dijaloškom okviru koji se otvori kliknite gumb za kombiniranje.
- Kliknite na "Kombiniraj i učitaj".
- U dijaloškom okviru "Kombiniraj datoteke" koji se otvori odaberite tablicu u lijevom oknu. Imajte na umu da vam Power Query prikazuje tablicu iz prve datoteke. Ova će datoteka djelovati kao predložak (ili ključ) za kombiniranje drugih datoteka. Power Query sada bi tražio 'Tablicu 1' u drugim radnim bilježnicama i kombinirao je s ovom.
- Pritisnite U redu.
Ovo će učitati konačni rezultat (kombinirane podatke) u vaš aktivni radni list.
Napominjemo da zajedno s podacima Power Query automatski dodaje naziv radne knjige kao prvi stupac kombiniranih podataka. To pomaže u praćenju podataka iz koje radne knjige.
U slučaju da želite prvo urediti podatke prije nego što ih učitate u Excel, u 6. koraku odaberite "Kombiniraj i uredi". Ovo će otvoriti konačni rezultat u uređivaču programa Power Query gdje možete urediti podatke.
Nekoliko stvari koje treba znati:
- Ako odaberete Excel tablicu kao predložak (u koraku 7), Power Query će koristiti nazive stupaca u ovoj tablici za kombiniranje podataka iz drugih tablica. Ako druge tablice imaju dodatne stupce, one će se zanemariti. U slučaju da te druge tablice nemaju stupac koji se nalazi u vašoj tablici predložaka, Power Query bi za to stavio "null".
- Stupci ne moraju biti u istom redoslijedu kao što Power Query koristi zaglavlja stupaca za mapiranje stupaca.
- Budući da ste odabrali tablicu1 kao ključ, Power Query će tražiti tablicu1 u svim radnim knjigama i sve to kombinirati. U slučaju da ne pronađe Excel tablicu s istim imenom (tablica1 u ovom primjeru), Power Query će vam dati pogrešku.
Dodavanje novih datoteka u mapu
Sada odvojimo minutu i shvatimo što smo učinili s gornjim koracima (koji su nam oduzeli samo nekoliko sekundi).
Kombinirali smo podatke iz četiri različite radne knjige u jednoj tablici u nekoliko sekundi, a da nismo ni otvorili nijednu radnu knjigu.
Ali to nije sve.
Prava MOĆ Power Queryja je da sada, kada dodate više datoteka u mapu, ne morate ponavljati niti jedan od ovih koraka.
Sve što trebate učiniti je premjestiti novu radnu knjigu u mapu, osvježiti upit i ona će automatski kombinirati podatke iz svih radnih knjiga u toj mapi.
Na primjer, u gornjem primjeru, ako dodam novu radnu knjigu - "Srednji zapad.xlsx" u mapu i osvježi upit, odmah će mi dati novi kombinirani skup podataka.
Evo kako možete osvježiti upit:
- Desnom tipkom miša kliknite Excel tablicu koju ste učitali na radni list i kliknite Osvježi.
- Desnom tipkom miša kliknite upit u oknu ‘Upit radne knjige’ i kliknite Osvježi
- Idite na karticu Podaci i kliknite Osvježi.
Svaka radna knjiga ima podatke s istim nazivom radnog lista
U slučaju da nemate podatke u Excel tablici, ali su svi nazivi listova (iz kojih želite kombinirati podatke) isti, tada možete koristiti metodu prikazanu u ovom odjeljku.
Trebate biti oprezni oko nekoliko stvari kada su to samo tablični podaci, a ne Excel tablica.
- Nazivi radnog lista trebaju biti isti. To će pomoći Power Queryu da pregleda vaše radne knjige i kombinira podatke s radnih listova koji imaju isti naziv u svakoj radnoj knjizi.
- Power Query razlikuje velika i mala slova. To znači da se radni list pod nazivom „podaci“ i „podaci“ smatraju različitim. Slično, stupci s zaglavljem 'Store' i jedan s 'store' smatraju se različitim.
- Iako je važno imati ista zaglavlja stupaca, nije važno imati isti redoslijed. Ako je stupac 2 u "East.xlsx" stupac 4 u "West.xlsx", Power Query će se ispravno podudarati preslikavanjem zaglavlja.
Pogledajmo sada kako brzo kombinirati podatke iz različitih radnih knjiga gdje je naziv radnog lista isti.
U ovom primjeru imam mapu s četiri datoteke.
U svakoj radnoj knjizi imam radni list s imenom ‘Podaci’ koji sadrži podatke u sljedećem formatu (imajte na umu da ovo nije Excel tablica).
Evo koraka za kombiniranje podataka iz više radnih knjiga u jedan radni list:
- Idite na karticu Podaci.
- U grupi Get & Transform kliknite padajući izbornik New Query.
- Zadržite pokazivač miša na "Iz datoteke" i kliknite na "Iz mape".
- U dijaloškom okviru Mapa unesite put datoteke do mape s datotekama ili kliknite Pregledaj i pronađite mapu.
- Pritisnite U redu.
- U dijaloškom okviru koji se otvori kliknite gumb za kombiniranje.
- Kliknite na "Kombiniraj i učitaj".
- U dijaloškom okviru "Kombiniraj datoteke" koji se otvori odaberite "Podaci" u lijevom oknu. Imajte na umu da vam Power Query prikazuje naziv radnog lista iz prve datoteke. Ova će datoteka djelovati kao ključ/predložak za kombiniranje drugih datoteka. Power Query će proći kroz svaku radnu knjigu, pronaći će list pod nazivom „Podaci i kombinirati sve ovo.
- Pritisnite U redu. Sada će Power Query proći kroz svaku radnu knjigu, potražiti radni list pod nazivom "Podaci", a zatim kombinirati sve te skupove podataka.
Ovo će učitati konačni rezultat (kombinirane podatke) u vaš aktivni radni list.
U slučaju da želite prvo urediti podatke prije nego što ih učitate u Excel, u 6. koraku odaberite "Kombiniraj i uredi". Ovo će otvoriti konačni rezultat u uređivaču programa Power Query gdje možete urediti podatke.
Svaka radna knjiga ima podatke s različitim nazivima tablica ili nazivima listova
Ponekad možda nećete dobiti strukturirane i dosljedne podatke (poput tablica s istim imenom ili radnog lista s istim imenom).
Na primjer, pretpostavimo da podatke primate od nekoga tko je stvorio ove skupove podataka, ali je radne listove nazvao Istočni podaci, Zapadni podaci, Sjeverni podaci i Južni podaci.
Ili je osoba možda izradila Excel tablice, ali s različitim imenima.
U takvim slučajevima još uvijek možete koristiti Power Query, ali to morate učiniti s nekoliko dodatnih koraka.
- Idite na karticu Podaci.
- U grupi Get & Transform kliknite padajući izbornik New Query.
- Zadržite pokazivač miša na "Iz datoteke" i kliknite na "Iz mape".
- U dijaloškom okviru Mapa unesite put datoteke do mape s datotekama ili kliknite Pregledaj i pronađite mapu.
- Pritisnite U redu.
- U dijaloškom okviru koji se otvori kliknite gumb Uredi. Ovo će otvoriti uređivač Power Query u kojem ćete vidjeti detalje svih datoteka u mapi.
- Držite tipku Control i odaberite stupce "Sadržaj" i "Naziv", desnom tipkom miša kliknite i odaberite "Ukloni druge stupce". Time ćete ukloniti sve ostale stupce osim odabranih stupaca.
- Na vrpci Uređivač upita kliknite "Dodaj stupac", a zatim kliknite "Prilagođeni stupac".
- U dijaloškom okviru Dodavanje prilagođenog stupca imenujte novi stupac kao "Uvoz podataka" i upotrijebite sljedeću formulu = Excel.Radna knjiga ([SADRŽAJ]). Imajte na umu da ova formula razlikuje velika i mala slova i morate je unijeti točno onako kako sam ovdje prikazao.
- Sada ćete vidjeti novi stupac u kojem je napisana tablica. Dopustite mi da objasnim što se ovdje dogodilo. Power Queryju ste dali nazive radnih knjiga, a Power Query je dohvatio objekte kao što su radni listovi, tablice i imenovani rasponi iz svake radne knjige (koja se od sada nalazi u ćeliji Tablica). Možete kliknuti na bijeli prostor uz tekstualnu tablicu i vidjeti ćete podatke pri dnu. U ovom slučaju, budući da u svakoj radnoj knjizi imamo samo jednu tablicu i jedan radni list, možete vidjeti samo dva retka.
- Kliknite ikonu dvostruke strelice pri vrhu stupca "Uvoz podataka".
- U okviru s podacima o stupcu koji se otvori poništite okvir "Koristi izvorni stupac kao prefiks", a zatim kliknite U redu.
- Sada ćete vidjeti proširenu tablicu u kojoj vidite jedan redak za svaki objekt u tablici. U tom slučaju, za svaku radnu knjigu, objekt lista i objekt tablice navedeni su zasebno.
- U stupcu Vrsta filtrirajte popis tako da prikazuje samo tablicu.
- Držite kontrolnu tipku i odaberite stupac Naziv i podaci. Sada desnom tipkom miša kliknite i uklonite sve ostale stupce.
- U stupcu Podaci kliknite ikonu dvostruke strelice u gornjem desnom kutu zaglavlja podataka.
- U okviru s podacima o stupcu koji se otvori kliknite U redu. To će kombinirati podatke u svim tablicama i prikazati u Power Queryju.
- Sada možete napraviti bilo koju transformaciju koja vam je potrebna, a zatim idite na karticu Početna i kliknite Zatvori i učitaj.
Sada ću pokušati brzo objasniti što smo ovdje učinili. Budući da nije bilo dosljednosti u nazivima listova ili nazivima tablica, koristili smo formulu = Excel.Workbook za dohvaćanje svih objekata radnih knjiga u Power Queryju. Ti objekti mogu uključivati listove, tablice i imenovane raspone. Nakon što smo dobili sve objekte iz svih datoteka, filtrirali smo ih kako bismo uzeli u obzir samo Excel tablice. Zatim smo proširili podatke u tablicama i sve to spojili.
U ovom primjeru filtrirali smo podatke samo za korištenje Excel tablica (u koraku 13). U slučaju da želite kombinirati listove, a ne tablice, možete filtrirati listove.
Napomena - ova tehnika će vam dati kombinirane podatke čak i kada postoji neslaganje u nazivima stupaca. Na primjer, ako u East.xlsx imate stupac koji je pogrešno napisan, završit ćete s 5 stupaca. Power Query ispunit će podatke u stupce ako ih pronađe, a ako ne može pronaći stupac, prijavit će vrijednost kao "null".
Slično, ako imate neke dodatne stupce u bilo kojem radnom listu tablice, oni će biti uključeni u konačni rezultat.
Ako dobijete više radnih knjiga iz kojih trebate kombinirati podatke, jednostavno ih kopirajte i zalijepite u mapu i osvježite Power Query
