Napravite tabele sa različitim zaglavljima iz više knjiga

Formulacija problema

Imamo nekoliko fajlova (u našem primeru – 4 komada, u opštem slučaju – koliko god želite) u jednoj fascikli Izvještaji:

Unutra, ovi fajlovi izgledaju ovako:

Pri čemu:

• Tehnički list koji nam je potreban uvijek se zove Fotografije, ali može biti bilo gdje u radnoj svesci.
  
• Iza lista Fotografije Svaka knjiga može imati druge listove.
• Tabele sa podacima imaju različit broj redova i mogu početi drugim redom na radnom listu.
• Imena istih kolona u različitim tabelama mogu se razlikovati (na primjer, Količina = Količina = Količina).
• Kolone u tabelama mogu se poredati različitim redosledom.

Zadatak: prikupiti podatke o prodaji iz svih datoteka sa lista Fotografije u jednu zajedničku tabelu kako bi naknadno na njoj izgradili sažetak ili bilo koju drugu analitiku.

Korak 1. Priprema direktorija imena stupaca

Prvo što treba učiniti je pripremiti priručnik sa svim mogućim opcijama za nazive stupaca i njihovu ispravnu interpretaciju:

Konvertujemo ovu listu u dinamičku „pametnu“ tabelu koristeći dugme Formatiraj kao tabelu na kartici Početna (Početna — Format kao tabela) ili prečica na tastaturi Ctrl+T i učitajte ga u Power Query pomoću naredbe Podaci – iz tabele/opseg (Podaci — iz tabele/opseg). U novijim verzijama Excela preimenovan je u Sa lišćem (sa lista).

U prozoru Power Query uređivača upita tradicionalno brišemo korak Promijenjen tip i dodajte novi korak umjesto njega klikom na dugme f_xu traci formule (ako nije vidljivo, možete ga omogućiti na kartici pregled) i tamo unesite formulu u ugrađenom Power Query jeziku M:

=Table.ToRows(Izvor)

Ova komanda će konvertovati onu učitanu u prethodnom koraku izvor referentnu tablicu u listu koja se sastoji od ugniježđenih lista (Lista), od kojih je svaka, pak, par vrijednosti Bilo je-postalo iz jednog reda:

Ova vrsta podataka će nam trebati malo kasnije, kada ćemo masovno preimenovati zaglavlja iz svih učitanih tabela.

Nakon završetka konverzije, odaberite komande Početna — Zatvori i učitaj — Zatvori i učitaj u… i vrstu uvoza Samo stvorite vezu (Početna — Zatvori&Učitaj — Zatvori&Učitaj na… — Samo kreiraj vezu) i vratite se u Excel.

Korak 2. Učitavamo sve iz svih fajlova onako kako jeste

Sada učitajmo sadržaj svih naših fajlova iz foldera – za sada, kakav jeste. Odabir timova Podaci – Uzmi podatke – Iz datoteke – Iz foldera (Podaci — Uzmi podatke — Iz datoteke — Iz foldera) a zatim fasciklu u kojoj se nalaze naše izvorne knjige.

U prozoru za pregled kliknite Pretvoriti (Transformacija) or promjena (Edit):

Zatim proširite sadržaj svih preuzetih datoteka (binarni) dugme sa dvostrukim strelicama u naslovu kolone sadržaj:

Power Query na primjeru prve datoteke (Vostok.xlsx) će nas pitati za naziv lista koji želimo da uzmemo iz svake radne sveske – izaberite Fotografije i pritisnite OK:

Nakon toga će se (zapravo) dogoditi nekoliko događaja koji korisniku nisu očigledni, čije su posljedice jasno vidljive na lijevoj ploči:

1. Power Query će uzeti prvu datoteku iz fascikle (imaćemo je Vostok.xlsx — vidjeti Primjer datoteke) kao primjer i uvozi njegov sadržaj kreiranjem upita Pretvorite uzorak datoteke. Ovaj upit će imati nekoliko jednostavnih koraka kao što su izvor (pristup fajlu) navigacija (izbor lista) i eventualno podizanje naslova. Ovaj zahtjev može učitati podatke samo iz jedne određene datoteke Vostok.xlsx.
   
2. Na osnovu ovog zahtjeva, bit će kreirana funkcija povezana s njim Pretvori datoteku (označeno karakterističnom ikonom f_x), pri čemu izvorni fajl više neće biti konstanta, već promenljiva vrednost – parametar. Dakle, ova funkcija može izdvojiti podatke iz bilo koje knjige koje ubacimo u nju kao argument.
   
3. Funkcija će se naizmjence primijeniti na svaki fajl (binarni) iz stupca sadržaj – Step je odgovoran za ovo Pozovite prilagođenu funkciju u našem upitu koji dodaje kolonu na listu datoteka Pretvori datoteku sa rezultatima uvoza iz svake radne knjige:

   

4. Dodatni stupci su uklonjeni.
5. Sadržaj ugniježđenih tabela se proširuje (korak Proširena kolona tabele) – i vidimo konačne rezultate prikupljanja podataka iz svih knjiga:

   
   

Korak 3. Brušenje

Prethodni snimak ekrana jasno pokazuje da se direktna montaža "kao što je" pokazala loše kvalitete:

• Kolone su obrnute.
• Mnogo dodatnih redova (praznih i ne samo).
• Zaglavlja tabele se ne percipiraju kao zaglavlja i pomešana su sa podacima.
  

Sve ove probleme možete riješiti vrlo lako – samo podesite upit Convert Sample File. Sva podešavanja koja izvršimo automatski će pasti u pridruženu funkciju Pretvori datoteku, što znači da će se kasnije koristiti prilikom uvoza podataka iz svake datoteke.

Otvaranjem zahtjeva Pretvorite uzorak datoteke, dodajte korake za filtriranje nepotrebnih redova (na primjer, po koloni Column2) i podizanje naslova pomoću dugmeta Koristite prvi red kao zaglavlja (Koristite prvi red kao zaglavlja). Stol će izgledati mnogo bolje.

Da bi se stupci iz različitih datoteka kasnije automatski uklopili jedan ispod drugog, moraju se nazvati istim imenom. Takvo masovno preimenovanje možete izvršiti prema prethodno kreiranom direktoriju s jednom linijom M-koda. Pritisnite dugme ponovo f_x u traku formule i dodajte funkciju za promjenu:

= Table.RenameColumns(#”Povišena zaglavlja”, Zaglavlja, MissingField.Ignore)

Ova funkcija preuzima tabelu iz prethodnog koraka Povišena zaglavlja i preimenuje sve kolone u njemu prema ugniježđenoj listi za pretraživanje Headlines. Treći argument MissingField.Ignore je potrebno da na onim naslovima koji su u direktoriju, ali nisu u tabeli, ne dođe do greške.

Zapravo, to je sve.

Vraćamo se na zahtjev Izvještaji videćemo potpuno drugačiju sliku – mnogo lepšu od prethodne:

• Šta je Power Query, Power Pivot, Power BI i zašto su potrebni korisniku programa Excel
  
• Prikupljanje podataka iz svih fajlova u datom folderu
• Prikupljanje podataka sa svih listova knjige u jednu tabelu