Izgradnja multiformatnih tabela iz jednog lista u Power Queryju

Formulacija problema

Kao ulazne podatke imamo Excel fajl, gde jedan od listova sadrži nekoliko tabela sa podacima o prodaji sledećeg oblika:

Zapiši to:

• Tabele različitih veličina i sa različitim skupovima proizvoda i regija u redovima i kolonama bez ikakvog sortiranja.
• Prazne linije se mogu umetnuti između tabela.
• Broj stolova može biti bilo koji.
  

Dvije važne pretpostavke. Pretpostavlja se da:

• Iznad svake tabele, u prvoj koloni, nalazi se ime menadžera čiju prodaju tabela ilustruje (Ivanov, Petrov, Sidorov itd.)
  
• Nazivi roba i regiona u svim tabelama su napisani na isti način – sa preciznošću.
  

Krajnji cilj je prikupljanje podataka iz svih tabela u jednu ravnu normalizovanu tabelu, pogodnu za naknadnu analizu i pravljenje rezimea, tj. u ovoj:

Korak 1. Povežite se na datoteku

Kreirajmo novu praznu Excel datoteku i odaberite je na kartici podaci naredba Preuzmite podatke – Iz datoteke – Iz knjige (Podaci — Iz datoteke — Iz radne knjige). Odredite lokaciju izvorne datoteke sa podacima o prodaji, a zatim u prozoru navigatora odaberite list koji nam je potreban i kliknite na dugme Pretvori podatke (Transformirajte podatke):

Kao rezultat, svi podaci iz njega bi trebali biti učitani u Power Query editor:

Korak 2. Očistite smeće

Izbrišite automatski generisane korake modificirani tip (promijenjena vrsta) и Povišena zaglavlja (Promovirana zaglavlja) i riješite se praznih linija i redova sa ukupnim vrijednostima koristeći filter null и UKUPNO po prvoj koloni. Kao rezultat, dobijamo sljedeću sliku:

Korak 3. Dodavanje menadžera

Da bismo kasnije shvatili gdje je čija prodaja, potrebno je našoj tabeli dodati kolonu gdje će u svakom redu biti odgovarajuće prezime. Za ovo:

1. Dodajmo pomoćnu kolonu sa brojevima redova pomoću naredbe Dodaj kolonu – Kolona indeksa – Od 0 (Dodaj kolonu — Indeksni stupac — Od 0).

2. Dodajte kolonu s formulom pomoću naredbe Dodavanje kolone – Prilagođena kolona (Dodaj kolonu — Prilagođena kolona) i tamo uvesti sljedeću konstrukciju:

Logika ove formule je jednostavna – ako je vrijednost sljedeće ćelije u prvoj koloni “Proizvod”, onda to znači da smo naišli na početak nove tabele, pa prikazujemo vrijednost prethodne ćelije sa ime menadžera. U suprotnom, ne prikazujemo ništa, tj. null.

Da bismo dobili roditeljsku ćeliju s prezimenom, prvo se pozivamo na tabelu iz prethodnog koraka #”Indeks dodan”, a zatim navedite ime kolone koja nam je potrebna [Kolona1] u uglastim zagradama i broj ćelije u toj koloni u vitičastim zagradama. Broj ćelije će biti za jedan manji od trenutnog, koji uzimamo iz kolone indeks, respektivno.

3. Ostaje popuniti prazne ćelije sa null imena iz viših ćelija sa komandom Transformacija – Ispuna – Dolje (Transformacija — Ispuna — Dolje) i izbrišite više nepotrebnu kolonu sa indeksima i redove sa prezimenima u prvoj koloni. Kao rezultat, dobijamo:

Korak 4. Grupisanje u zasebne tabele po menadžerima

Sljedeći korak je grupiranje redova za svakog menadžera u zasebne tabele. Da biste to učinili, na kartici Transformacija koristite naredbu Grupiraj po (Transform – Group By) i u prozoru koji se otvori odaberite kolonu Upravitelj i operaciju Svi redovi (Svi redovi) da jednostavno prikupite podatke bez primjene bilo kakve funkcije agregiranja na njih (zbir, prosek, itd.). P.):

Kao rezultat, dobijamo zasebne tabele za svakog menadžera:

Korak 5: Transformirajte ugniježđene tablice

Sada dajemo tabele koje leže u svakoj ćeliji rezultirajuće kolone Svi podaci u pristojnom stanju.

Prvo, izbrišite kolonu koja više nije potrebna u svakoj tabeli menadžer. Koristimo ponovo Prilagođena kolona tabulator transformacija (Transformacija — Prilagođena kolona) i sledeća formula:

Zatim, sa drugom izračunatom kolonom, podižemo prvi red u svakoj tabeli na naslove:

I na kraju, izvodimo glavnu transformaciju – otvaramo svaku tablicu pomoću M-funkcije Table.UnpivotOtherColumns:

Imena regiona iz zaglavlja će ići u novu kolonu i dobićemo užu, ali u isto vreme, dužu normalizovanu tabelu. Prazne ćelije sa null se ignorišu.

Oslobađajući se nepotrebnih međukolona, ​​imamo:

Korak 6 Proširite ugniježđene tablice

Ostaje proširiti sve normalizirane ugniježđene tabele u jednu listu pomoću dugmeta sa dvostrukim strelicama u zaglavlju kolone:

… i konačno dobijamo ono što smo želeli:

Dobijenu tabelu možete izvesti nazad u Excel pomoću naredbe Početna — Zatvori i učitaj — Zatvori i učitaj u… (Početna — Zatvori&Učitaj — Zatvori&Učitaj na…).

• Napravite tabele sa različitim zaglavljima iz više knjiga
• Prikupljanje podataka iz svih fajlova u datom folderu
• Prikupljanje podataka sa svih listova knjige u jednu tabelu