Sestavite tabele z različnimi glavami iz več knjig

Formulacija problema

V eni mapi imamo več datotek (v našem primeru - 4 kose, v splošnem primeru - kolikor želite). Poročila:

V notranjosti te datoteke izgledajo takole:

Pri tem:

• Podatkovni list, ki ga potrebujemo, se vedno kliče slike, vendar je lahko kjer koli v delovnem zvezku.
  
• Onkraj lista slike Vsaka knjiga ima lahko še druge liste.
• Tabele s podatki imajo različno število vrstic in se lahko začnejo z drugo vrstico na delovnem listu.
• Imena istih stolpcev v različnih tabelah se lahko razlikujejo (npr. Količina = Količina = Količina).
• Stolpci v tabelah so lahko razporejeni v drugačnem vrstnem redu.

Naloga: zbrati podatke o prodaji iz vseh datotek na listu slike v eno skupno tabelo, da bi kasneje na njej zgradili povzetek ali kakšno drugo analitiko.

Korak 1. Priprava imenika imen stolpcev

Najprej morate pripraviti referenčno knjigo z vsemi možnimi možnostmi za imena stolpcev in njihovo pravilno razlago:

Ta seznam pretvorimo v dinamično »pametno« tabelo z gumbom Oblikuj kot tabelo na zavihku Domov (Domov — Oblikuj kot tabelo) ali bližnjico na tipkovnici Ctrl+T in ga naložite v Power Query z ukazom Podatki – iz tabele/razpona (Podatki — iz tabele/razpona). V zadnjih različicah Excela je bil preimenovan v Z listi (Iz lista).

V oknu urejevalnika poizvedbe Power Query tradicionalno izbrišemo korak Spremenjena vrsta in namesto njega s klikom na gumb dodajte nov korak f_xv vrstici s formulami (če ni viden, ga lahko omogočite na zavihku pregled) in tam vnesite formulo v vgrajenem jeziku Power Query M:

=Table.ToRows(Vir)

Ta ukaz bo pretvoril tistega, ki je bil naložen v prejšnjem koraku vir referenčno tabelo v seznam, sestavljen iz ugnezdenih seznamov (Seznam), od katerih je vsak po vrsti par vrednosti Bilo je-postalo iz ene vrstice:

To vrsto podatkov bomo potrebovali nekoliko kasneje, ko bomo množično preimenovali glave iz vseh naloženih tabel.

Po končani pretvorbi izberite ukaze Domov — Zapri in naloži — Zapri in naloži v… in vrsto uvoza Samo ustvarite povezavo (Domov — Zapri&Naloži — Zapri&Naloži na… — Ustvari samo povezavo) in se vrnite v Excel.

Korak 2. Naložimo vse iz vseh datotek, kot je

Zdaj pa naložimo vsebino vseh naših datotek iz mape – za zdaj, kot je. Izbira ekip Podatki – Pridobi podatke – Iz datoteke – Iz mape (Podatki — Pridobi podatke — Iz datoteke — Iz mape) in nato mapo, kjer so naše izvorne knjige.

V oknu za predogled kliknite Pretvarjanje (preobrazba) or Spreminjanje (Uredi):

In nato razširite vsebino vseh prenesenih datotek (binarni) gumb z dvojnimi puščicami v naslovu stolpca vsebina:

Power Query na primeru prve datoteke (Vostok.xlsx) nas bo vprašal za ime lista, ki ga želimo vzeti iz posameznega delovnega zvezka – izberite slike in pritisnite OK:

Po tem (pravzaprav) se bo zgodilo več dogodkov, ki uporabniku niso očitni, katerih posledice so jasno vidne na levi plošči:

1. Power Query bo vzel prvo datoteko iz mape (imeli jo bomo Vostok.xlsx — glej Primer datoteke) kot primer in uvozi njegovo vsebino z ustvarjanjem poizvedbe Pretvori vzorčno datoteko. Ta poizvedba bo imela nekaj preprostih korakov, kot je vir (dostop do datoteke) ostalo (izbor listov) in morebiti dvig naslovov. Ta zahteva lahko naloži samo podatke iz ene določene datoteke Vostok.xlsx.
   
2. Na podlagi te zahteve bo ustvarjena z njo povezana funkcija Pretvori datoteko (označeno z značilno ikono f_x), kjer izvorna datoteka ne bo več konstanta, temveč spremenljiva vrednost – parameter. Tako lahko ta funkcija izvleče podatke iz katere koli knjige, ki ji jih vstavimo kot argument.
   
3. Funkcija bo po vrsti uporabljena za vsako datoteko (Binary) iz stolpca vsebina – za to je odgovoren korak Pokličite funkcijo po meri v naši poizvedbi, ki doda stolpec na seznam datotek Pretvori datoteko z rezultati uvoza iz vsakega delovnega zvezka:

   

4. Dodatni stolpci so odstranjeni.
5. Vsebina ugnezdenih tabel se razširi (korak Razširjen stolpec tabele) – in vidimo končne rezultate zbiranja podatkov iz vseh knjig:

   
   

Korak 3. Brušenje

Prejšnji posnetek zaslona jasno kaže, da se je neposredna montaža "kot je" izkazala za slabe kakovosti:

• Stolpci so obrnjeni.
• Veliko dodatnih vrstic (praznih in ne samo).
• Glave tabel niso zaznane kot glave in so pomešane s podatki.
  

Vse te težave lahko zelo enostavno odpravite – samo prilagodite poizvedbo Pretvori vzorčno datoteko. Vse prilagoditve, ki jih naredimo, bodo samodejno padle v povezano funkcijo Pretvori datoteko, kar pomeni, da bodo uporabljene pozneje pri uvozu podatkov iz posamezne datoteke.

Z odpiranjem zahteve Pretvori vzorčno datoteko, dodajte korake za filtriranje nepotrebnih vrstic (na primer po stolpcu Column2) in dviganje naslovov z gumbom Prvo vrstico uporabite kot glave (Uporabi prvo vrstico kot glave). Miza bo videti veliko bolje.

Da se stolpci iz različnih datotek kasneje samodejno prilegajo drug drugemu, morajo biti poimenovani enako. Tako množično preimenovanje lahko izvedete glede na predhodno ustvarjen imenik z eno vrstico M-kode. Ponovno pritisnemo na gumb f_x v vrstici s formulami in dodajte funkcijo za spremembo:

= Table.RenameColumns(#”Povišane glave”, Glave, MissingField.Ignore)

Ta funkcija vzame tabelo iz prejšnjega koraka Povišane glave in preimenuje vse stolpce v njem glede na ugnezdeni iskalni seznam Naslovi. Tretji argument MissingField.Ignore je potreben, da se pri tistih naslovih, ki so v imeniku, vendar niso v tabeli, ne pojavi napaka.

Pravzaprav je to vse.

Nazaj na zahtevo Poročila videli bomo popolnoma drugačno sliko – veliko lepšo od prejšnje:

• Kaj je Power Query, Power Pivot, Power BI in zakaj jih uporabnik Excela potrebuje
  
• Zbiranje podatkov iz vseh datotek v določeni mapi
• Zbiranje podatkov z vseh listov knjige v eno tabelo