Power Query je lahko v veliko pomoč, če želite združiti več delovnih zvezkov v en sam delovni zvezek.
Recimo, da imate podatke o prodaji za različne regije (vzhod, zahod, sever in jug). S pomočjo Power Query lahko te podatke iz različnih delovnih zvezkov združite v en sam delovni list.
Če imate te delovne zvezke na različnih lokacijah/mapah, je dobro, da jih vse premaknete v eno mapo (ali ustvarite kopijo in kopijo tega delovnega zvezka postavite v isto mapo).
Za začetek imam v mapi štiri delovne zvezke (kot je prikazano spodaj).
Zdaj v tej vadnici obravnavam tri scenarije, v katerih lahko kombinirate podatke iz različnih delovnih zvezkov z uporabo Power Query:
- Vsak delovni zvezek ima podatke v Excelovi tabeli in vsa imena tabel so enaka.
- Vsak delovni zvezek ima podatke z istim imenom delovnega lista. To se lahko zgodi, če je v vseh delovnih zvezkih list z imenom »povzetek« ali »podatek« in želite vse to združiti.
- Vsak delovni zvezek ima veliko listov in tabel, zato želite združiti posebne tabele/liste. Ta metoda je lahko v pomoč tudi, če želite združiti tabelo/liste, ki nimajo doslednega imena.
Poglejmo, kako v vsakem primeru združiti podatke iz teh delovnih zvezkov.
Vsak delovni zvezek ima podatke v Excelovi tabeli z enako strukturo
Spodnja tehnika bi delovala, če bi bile vaše Excelove tabele strukturirane na enak način (ista imena stolpcev).
Število vrstic v vsaki tabeli se lahko razlikuje.
Ne skrbite, če imajo nekatere Excelove tabele dodatne stolpce. Kot predlogo lahko izberete eno od tabel (ali kot "ključ", kot jo pokliče Power Query), Power Query pa bi jo uporabil za združevanje vseh drugih tabel Excel z njo.
Če so v drugih tabelah dodatni stolpci, bodo ti prezrti in združeni bodo le tisti, ki so navedeni v predlogi/ključu. Na primer, če ima izbrana tabela predloge/ključa 5 stolpcev, ena od tabel v drugem delovnem zvezku pa 2 dodatna stolpca, se ti dodatni stolpci ne upoštevajo.
Zdaj imam v mapi štiri delovne zvezke, ki jih želim združiti.
Spodaj je posnetek tabele, ki jo imam v enem od delovnih zvezkov.
Tu so koraki za združevanje podatkov iz teh delovnih zvezkov v en sam delovni zvezek (kot enotno tabelo).
- Pojdite na zavihek Podatki.
- V skupini Get & Transform kliknite spustni meni New Query.
- Premaknite kazalec miške na »Iz datoteke« in kliknite na »Iz mape«.
- V pogovornem oknu Mapa vnesite pot do mape z datotekami ali kliknite Prebrskaj in poiščite mapo.
- Kliknite V redu.
- V pogovornem oknu, ki se odpre, kliknite gumb za združevanje.
- Kliknite »Združi in naloži«.
- V pogovornem oknu »Združi datoteke«, ki se odpre, izberite tabelo v levem podoknu. Upoštevajte, da vam Power Query prikaže tabelo iz prve datoteke. Ta datoteka bi delovala kot predloga (ali ključ) za združevanje drugih datotek. Power Query bi zdaj iskal "Tabelo 1" v drugih delovnih zvezkih in jo združil s tem.
- Kliknite V redu.
To bo naložilo končni rezultat (združene podatke) v vaš aktivni delovni list.
Upoštevajte, da Power Query skupaj s podatki samodejno doda ime delovnega zvezka kot prvi stolpec združenih podatkov. To pomaga spremljati, kateri podatki so prišli iz katerega delovnega zvezka.
Če želite podatke najprej naložiti, preden jih naložite v Excel, v 6. koraku izberite »Združi in uredi«. To bo odprlo končni rezultat v urejevalniku Power Query, kjer lahko uredite podatke.
Nekaj stvari morate vedeti:
- Če za predlogo izberete Excelovo tabelo (v 7. koraku), bo Power Query uporabil imena stolpcev v tej tabeli za združevanje podatkov iz drugih tabel. Če imajo druge tabele dodatne stolpce, se te ne upoštevajo. V primeru, da te druge tabele nimajo stolpca, ki je v vaši tabeli predlog, bi Power Query zanj postavil »nič«.
- Ni treba, da so stolpci v istem vrstnem redu, saj Power Query uporablja naslove stolpcev za preslikavo stolpcev.
- Ker ste za ključ izbrali Tabelo1, bo Power Query poiskal Tabelo1 v vseh delovnih zvezkih in vse to združil. Če ne najde Excelove tabele z istim imenom (tabela 1 v tem primeru), vam bo Power Query prikazal napako.
Dodajanje novih datotek v mapo
Zdaj si vzemimo minuto in razumejmo, kaj smo naredili z zgornjimi koraki (ki so nam vzeli le nekaj sekund).
Podatke iz štirih različnih delovnih zvezkov smo v nekaj sekundah združili v eno samo tabelo, ne da bi sploh odprli katerega od delovnih zvezkov.
A to še ni vse.
Resnična moč Power Queryja je, da vam zdaj, ko dodate več datotek v mapo, ni treba ponavljati nobenega od teh korakov.
Vse, kar morate narediti, premaknite nov delovni zvezek v mapo, osvežite poizvedbo in samodejno bo združil podatke iz vseh delovnih zvezkov v tej mapi.
Na primer, v zgornjem primeru, če dodam nov delovni zvezek - "Srednji zahod.xlsx" v mapo in osveži poizvedbo, mi bo takoj dala nov kombinirani nabor podatkov.
Tako osvežite poizvedbo:
- Z desno tipko miške kliknite Excelovo tabelo, ki ste jo naložili na delovni list, in kliknite Osveži.
- Z desno tipko miške kliknite poizvedbo v podoknu »Poizvedba v delovnem zvezku« in kliknite Osveži
- Pojdite na zavihek Podatki in kliknite Osveži.
Vsak delovni zvezek ima podatke z istim imenom delovnega lista
Če podatkov nimate v Excelovi tabeli, vendar so vsa imena listov (iz katerih želite združiti podatke) enaka, lahko uporabite metodo, prikazano v tem razdelku.
Nekaj stvari, na katere morate biti previdni, ko gre le za tabelarne podatke in ne za Excelovo tabelo.
- Imena delovnih listov morajo biti enaka. To bo Power Queryju pomagalo pregledati vaše delovne zvezke in združiti podatke iz delovnih listov z istim imenom v vsakem delovnem zvezku.
- Power Query razlikuje velike in male črke. To pomeni, da se delovni list z imenom „podatki“ in „podatki“ štejeta za različna. Podobno se stolpca z naslovom »Trgovina« in ena z »trgovina« štejeta za različna.
- Čeprav je pomembno, da imate enake glave stolpcev, ni pomembno, da imate enako zaporedje. Če je stolpec 2 v "East.xlsx" stolpec 4 v "West.xlsx", se bo Power Query pravilno ujemal s preslikavo glav.
Zdaj pa poglejmo, kako hitro združiti podatke iz različnih delovnih zvezkov, kjer je ime delovnega lista enako.
V tem primeru imam mapo s štirimi datotekami.
V vsakem delovnem zvezku imam delovni list z imenom »Podatki«, ki vsebuje podatke v naslednji obliki (upoštevajte, da to ni Excelova tabela).
Tu so koraki za združevanje podatkov iz več delovnih zvezkov v en sam delovni list:
- Pojdite na zavihek Podatki.
- V skupini Get & Transform kliknite spustni meni New Query.
- Premaknite kazalec miške na »Iz datoteke« in kliknite na »Iz mape«.
- V pogovornem oknu Mapa vnesite pot do mape z datotekami ali kliknite Prebrskaj in poiščite mapo.
- Kliknite V redu.
- V pogovornem oknu, ki se odpre, kliknite gumb za združevanje.
- Kliknite »Združi in naloži«.
- V pogovornem oknu »Združi datoteke«, ki se odpre, v levem podoknu izberite »Podatki«. Upoštevajte, da vam Power Query prikaže ime delovnega lista iz prve datoteke. Ta datoteka bi delovala kot ključ/predloga za združevanje drugih datotek. Power Query bo pregledal vsak delovni zvezek, poiskal list z imenom »Podatki« in združil vse to.
- Kliknite V redu. Zdaj bo Power Query pregledal vsak delovni zvezek, v njem poiskal delovni list z imenom »Podatki« in nato združil vse te nabore podatkov.
To bo naložilo končni rezultat (združene podatke) v vaš aktivni delovni list.
Če želite podatke najprej naložiti, preden jih naložite v Excel, v 6. koraku izberite »Združi in uredi«. To bo odprlo končni rezultat v urejevalniku Power Query, kjer lahko uredite podatke.
Vsak delovni zvezek ima podatke z različnimi imeni tabel ali imeni listov
Včasih morda ne boste dobili strukturiranih in doslednih podatkov (na primer tabel z istim imenom ali delovnega lista z istim imenom).
Recimo, da podatke dobite od nekoga, ki je ustvaril te nabore podatkov, vendar je delovne liste poimenoval vzhodni podatki, zahodni podatki, severni podatki in južni podatki.
Ali pa je oseba morda ustvarila Excelove tabele, vendar z različnimi imeni.
V takih primerih lahko še vedno uporabite Power Query, vendar morate to narediti z nekaj dodatnimi koraki.
- Pojdite na zavihek Podatki.
- V skupini Get & Transform kliknite spustni meni New Query.
- Premaknite kazalec miške na »Iz datoteke« in kliknite na »Iz mape«.
- V pogovornem oknu Mapa vnesite pot do mape z datotekami ali kliknite Prebrskaj in poiščite mapo.
- Kliknite V redu.
- V pogovornem oknu, ki se odpre, kliknite gumb Uredi. S tem se odpre urejevalnik Power Query, kjer boste videli podrobnosti vseh datotek v mapi.
- Držite tipko Control in izberite stolpca »Vsebina« in »Ime«, z desno tipko miške kliknite in izberite »Odstrani druge stolpce«. S tem boste odstranili vse druge stolpce, razen izbranih.
- Na traku urejevalnika poizvedb kliknite »Dodaj stolpec« in nato »Stolpec po meri«.
- V pogovornem oknu Dodaj stolpec po meri poimenujte nov stolpec kot "Uvoz podatkov" in uporabite naslednjo formulo = Excel.Delovni zvezek ([VSEBINA]). Upoštevajte, da ta formula razlikuje velike in male črke in jo morate vnesti točno tako, kot sem prikazal tukaj.
- Zdaj boste videli nov stolpec, v katerem je zapisana tabela. Naj zdaj razložim, kaj se je tukaj zgodilo. Power Query ste posredovali imena delovnih zvezkov, Power Query pa je iz vsakega delovnega zvezka (ki je trenutno v celici tabele) prenašal predmete, kot so delovni listi, tabele in poimenovani obsegi. Lahko kliknete presledek poleg besedilne tabele in na dnu boste videli podatke. V tem primeru, ker imamo v vsakem delovnem zvezku le eno tabelo in en delovni list, lahko vidite le dve vrstici.
- Kliknite ikono z dvojno puščico na vrhu stolpca »Uvoz podatkov«.
- V polju s podatki stolpca, ki se odpre, počistite polje »Uporabi izvirni stolpec kot predpono« in kliknite V redu.
- Zdaj boste videli razširjeno tabelo, kjer vidite eno vrstico za vsak predmet v tabeli. V tem primeru sta za vsak delovni zvezek predmet lista in predmet tabele navedena ločeno.
- V stolpcu Vrsta filtrirajte seznam, da prikaže le tabelo.
- Držite kontrolno tipko in izberite stolpec Ime in podatki. Zdaj z desno tipko miške kliknite in odstranite vse druge stolpce.
- V stolpcu Podatki kliknite ikono z dvojno puščico v zgornjem desnem kotu glave podatkov.
- V polju s podatki stolpca, ki se odpre, kliknite V redu. To bo združilo podatke v vseh tabelah in jih prikazalo v Power Query.
- Zdaj lahko naredite kakršno koli preoblikovanje, nato pa pojdite na zavihek Domov in kliknite Zapri in naloži.
Zdaj pa naj hitro poskusim razložiti, kaj smo tukaj počeli. Ker ni bilo doslednosti v imenih listov ali imenih tabel, smo uporabili formulo = Excel.Workbook za pridobivanje vseh predmetov delovnih zvezkov v Power Query. Ti predmeti lahko vključujejo liste, tabele in poimenovane obsege. Ko smo dobili vse predmete iz vseh datotek, smo jih filtrirali, da smo upoštevali le Excelove tabele. Nato smo razširili podatke v tabelah in vse to združili.
V tem primeru smo podatke filtrirali samo za uporabo Excelovih tabel (v 13. koraku). Če želite združiti liste in ne tabel, lahko liste filtrirate.
Opomba - ta tehnika vam bo dala združene podatke, tudi če se imena stolpcev ne ujemajo. Na primer, če imate v East.xlsx stolpec, ki je bil napačno črkovan, boste na koncu dobili 5 stolpcev. Power Query bo podatke napolnil v stolpce, če jih bo našel, če pa ne najde stolpca, bo vrednost poročal kot »ničelno«.
Podobno, če imate v katerem koli od delovnih listov tabel nekaj dodatnih stolpcev, bodo ti vključeni v končni rezultat.
Če dobite več delovnih zvezkov, iz katerih morate združiti podatke, jih preprosto kopirajte in prilepite v mapo in osvežite Power Query
