Sestavljanje tabel iz različnih Excelovih datotek s Power Queryjem

Formulacija problema

Oglejmo si čudovito rešitev za eno od zelo standardnih situacij, s katerimi se prej ali slej sooči večina uporabnikov Excela: hitro in samodejno morate zbrati podatke iz velikega števila datotek v eno končno tabelo. 

Recimo, da imamo naslednjo mapo, ki vsebuje več datotek s podatki iz podružnic:

Število datotek ni pomembno in se lahko v prihodnosti spremeni. Vsaka datoteka ima list z imenom Prodajakjer se nahaja podatkovna tabela:

Število vrstic (naročil) v tabelah je seveda drugačno, vendar je nabor stolpcev povsod standarden.

Naloga: zbrati podatke iz vseh datotek v eno knjigo z naknadnim samodejnim posodabljanjem pri dodajanju ali brisanju mestnih datotek ali vrstic v tabelah. Glede na končno konsolidirano tabelo bo potem mogoče zgraditi poljubna poročila, vrtilne tabele, podatke za filtriranje, itd. Glavna stvar je, da lahko zbirate.

Izberemo orožje

Za rešitev potrebujemo najnovejšo različico Excela 2016 (potrebna funkcionalnost je vanj že privzeto vgrajena) ali prejšnje različice Excela 2010-2013 z nameščenim brezplačnim dodatkom poizvedba o moči od Microsofta (prenesite ga tukaj). Power Query je izjemno prilagodljivo in izjemno zmogljivo orodje za nalaganje podatkov v Excel iz zunanjega sveta, nato njihovo odstranjevanje in obdelavo. Power Query podpira skoraj vse obstoječe vire podatkov – od besedilnih datotek do SQL in celo Facebooka 🙂

Če nimate Excela 2013 ali 2016, potem ne morete brati naprej (šalim se). V starejših različicah Excela je takšno nalogo mogoče opraviti le s programiranjem makra v Visual Basicu (kar je za začetnike zelo težko) ali z monotonim ročnim kopiranjem (kar traja dolgo in povzroča napake).

Korak 1. Uvozite eno datoteko kot vzorec

Najprej uvozimo podatke iz enega delovnega zvezka kot primer, da Excel »pobere idejo«. Če želite to narediti, ustvarite nov prazen delovni zvezek in ...

• če imate Excel 2016, odprite zavihek datum in nato Ustvari poizvedbo – Iz datoteke – Iz knjige (Podatki — Nova poizvedba — Iz datoteke — Iz Excela)
• če imate nameščen Excel 2010-2013 z dodatkom Power Query, odprite zavihek poizvedba o moči in izberite na njem Iz datoteke – iz knjige (Iz datoteke — Iz Excela)

Nato v oknu, ki se odpre, pojdite v našo mapo s poročili in izberite katero koli mestno datoteko (ni važno katero, ker so vse tipične). Po nekaj sekundah se mora prikazati okno Navigator, kjer morate na levi strani izbrati list, ki ga potrebujemo (Prodaja), njegova vsebina pa bo prikazana na desni strani:

Če kliknete na gumb v spodnjem desnem kotu tega okna Prenos (Nalaganje), potem bo tabela takoj uvožena na list v izvirni obliki. Za eno datoteko je to dobro, vendar moramo naložiti veliko takih datotek, zato bomo šli malo drugače in kliknili gumb Popravek (Uredi). Po tem bi moral biti urejevalnik poizvedb Power Query prikazan v ločenem oknu z našimi podatki iz knjige:

To je zelo zmogljivo orodje, ki vam omogoča, da tabelo "dokončate" do pogleda, ki ga potrebujemo. Celo površen opis vseh njegovih funkcij bi vzel približno sto strani, a če je zelo na kratko, lahko s tem oknom:

• filtrirajte nepotrebne podatke, prazne vrstice, vrstice z napakami
• razvrstite podatke po enem ali več stolpcih
• znebite se ponavljanja
• razdeli lepljivo besedilo po stolpcih (po ločilih, številu znakov itd.)
• uredite besedilo (odstranite odvečne presledke, popravite velike in male črke itd.)
• pretvarjanje podatkovnih vrst na vse možne načine (številke, kot je besedilo, pretvori v običajne številke in obratno)
• transponirati (zasukati) tabele in razširiti dvodimenzionalne križne tabele v ravne
• dodajte dodatne stolpce v tabelo in v njih uporabite formule in funkcije z uporabo jezika M, vgrajenega v Power Query.
• ...

V našo tabelo na primer dodamo stolpec z besedilnim imenom meseca, da bomo kasneje lažje gradili poročila vrtilne tabele. Če želite to narediti, z desno miškino tipko kliknite naslov stolpca Datumin izberite ukaz Podvojen stolpec (Podvojeni stolpec), nato pa z desno miškino tipko kliknite glavo podvojenega stolpca, ki se prikaže, in izberite Ukazi Transformacija – Mesec – Ime meseca:

Za vsako vrstico je treba oblikovati nov stolpec z besedilnimi imeni mesecev. Z dvojnim klikom na naslov stolpca ga lahko preimenujete iz Kopiraj datum na bolj udobno mesec, npr.

Če v nekaterih stolpcih program ni povsem pravilno prepoznal vrste podatkov, si lahko pomagate s klikom na ikono za format na levi strani posameznega stolpca:

S preprostim filtrom lahko izključite vrstice z napakami ali prazne vrstice, pa tudi nepotrebne upravitelje ali stranke:

Poleg tega so vse izvedene transformacije pritrjene na desni plošči, kjer jih je vedno mogoče vrniti nazaj (križ) ali spremeniti njihove parametre (prestava):

Lahka in elegantna, kajne?

2. korak. Našo zahtevo pretvorimo v funkcijo

Da bi pozneje ponovili vse transformacije podatkov, opravljene za vsako uvoženo knjigo, moramo našo ustvarjeno zahtevo pretvoriti v funkcijo, ki bo nato uporabljena za vse naše datoteke. To storiti je pravzaprav zelo preprosto.

V urejevalniku poizvedb pojdite na zavihek Pogled in kliknite gumb Napredni urejevalnik (Pogled — napredni urejevalnik). Odpreti se mora okno, kjer bodo zapisana vsa naša prejšnja dejanja v obliki kode v jeziku M. Upoštevajte, da je pot do datoteke, ki smo jo uvozili za primer, trdo kodirana v kodi:

Zdaj pa naredimo nekaj prilagoditev:

Njihov pomen je preprost: prva vrstica (pot datoteke)=> spremeni našo proceduro v funkcijo z argumentom pot datoteke, spodaj pa spremenimo fiksno pot do vrednosti te spremenljivke. 

Vse. Kliknite na Konec in bi moral videti tole:

Ne bojte se, da so podatki izginili – pravzaprav je vse v redu, vse bi moralo izgledati takole 🙂 Uspešno smo ustvarili našo funkcijo po meri, kjer si zapomnimo celoten algoritem za uvoz in obdelavo podatkov brez vezave na določeno datoteko . Ostaja, da mu damo bolj razumljivo ime (npr getData) v plošči na desni v polju Ime in lahko žanješ Domov — Zaprite in prenesite (Domov — Zapri in naloži). Upoštevajte, da je pot do datoteke, ki smo jo uvozili za primer, trdo kodirana v kodi. Vrnili se boste v glavno okno programa Microsoft Excel, vendar bi se morala na desni prikazati plošča z ustvarjeno povezavo do naše funkcije:

Korak 3. Zbiranje vseh datotek

Vse najtežje je zadaj, ostalo je prijetno in enostavno. Pojdite na zavihek Podatki – Ustvari poizvedbo – Iz datoteke – Iz mape (Podatki — Nova poizvedba — Iz datoteke — Iz mape) ali, če imate Excel 2010-2013, podobno kot zavihek poizvedba o moči. V oknu, ki se prikaže, določite mapo, v kateri se nahajajo vse naše izvorne mestne datoteke, in kliknite OK. Naslednji korak bi moral odpreti okno, v katerem bodo navedene vse Excelove datoteke, najdene v tej mapi (in njenih podmapah), in podrobnosti za vsako od njih:

klik Spreminjanje (Uredi) in spet pridemo v znano okno urejevalnika poizvedb.

Zdaj moramo v našo tabelo dodati še en stolpec z našo ustvarjeno funkcijo, ki bo "potegnila" podatke iz vsake datoteke. Če želite to narediti, pojdite na zavihek Dodaj stolpec – stolpec po meri (Dodaj stolpec — Dodaj stolpec po meri) in v oknu, ki se prikaže, vnesite našo funkcijo getData, pri čemer mu kot argument podate celotno pot do vsake datoteke:

Po kliku OK ustvarjeni stolpec je treba dodati v našo tabelo na desni.

Zdaj izbrišite vse nepotrebne stolpce (kot v Excelu, z desnim gumbom miške – odstrani), pustimo le dodani stolpec in stolpec z imenom datoteke, ker bo to ime (natančneje mesto) koristno imeti v skupnih podatkih za vsako vrstico.

In zdaj "vau trenutek" - kliknite na ikono z lastnimi puščicami v zgornjem desnem kotu dodanega stolpca z našo funkcijo:

… odkljukajte Kot predpono uporabite izvirno ime stolpca (Uporabi izvirno ime stolpca kot predpono)in kliknite OK. In naša funkcija bo naložila in obdelala podatke iz vsake datoteke po zabeleženem algoritmu in zbrala vse v skupni tabeli:

Za popolno lepoto lahko tudi odstranite končnice .xlsx iz prvega stolpca z imeni datotek – s standardno zamenjavo z “nič” (z desnim klikom na glavo stolpca – Namestnik) in preimenujte ta stolpec v mesto. In tudi popravite obliko podatkov v stolpcu z datumom.

Vse! Kliknite na Domov – Zapri in naloži (Domov — Zapri in naloži). Vsi podatki, zbrani s poizvedbo za vsa mesta, bodo naloženi na trenutni Excelov list v obliki »pametne tabele«:

Ustvarjene povezave in naše funkcije sestavljanja ni treba na noben način posebej shranjevati – shranita se skupaj s trenutno datoteko na običajen način.

V prihodnje bo pri kakršnih koli spremembah v mapi (dodajanje ali odstranjevanje mest) ali v datotekah (spreminjanje števila vrstic) dovolj, da desno kliknete neposredno na tabelo ali na poizvedbo v desni plošči in izberete ukaz Posodobi in shrani (Osveži) – Power Query bo v nekaj sekundah znova »zgradil« vse podatke.

PS

Dopolnitev. Po posodobitvah januarja 2017 se je Power Query naučil, kako sam zbirati Excelove delovne zvezke, tj. ni več treba narediti ločene funkcije – to se zgodi samodejno. Tako drugi korak iz tega članka ni več potreben in celoten postopek postane opazno enostavnejši:

1. Izberite Ustvari zahtevo – Iz datoteke – Iz mape – Izberite mapo – V redu
2. Ko se prikaže seznam datotek, pritisnite Spreminjanje
3. V oknu urejevalnika poizvedb razširite stolpec Binary z dvojno puščico in izberite ime lista, ki ga želite vzeti iz vsake datoteke

In to je vse! Pesem!

• Preoblikovanje križne tabele v ravno, primerno za izdelavo vrtilnih tabel
• Izdelava animiranega mehurčkastega grafikona v Power Viewu
• Makro za sestavljanje listov iz različnih Excelovih datotek v eno