Hyvä Enniii! Tästä se vasta alkaa.

Kyllä urheiluspektaakkeli aina yhden vertailutaulukon ansaitsee. Lopputulos ei ole mikään huikea spektaakkeli, mutta  sen avulla voi seurata miten paljon kunkin maan mitalisaalis eroaa Vancouverissa saavutetusta mitalisaaliista.

Kerron tässä jutussa, miten mitalitiedot ladataan Exceliin Sotshin olympialaisten virallisilta sivuilta ja miten eri olympialaisten tiedot yhdistetään yhteen luetteloon. Tiedot voisi ladata myös Power Pivotiin ja yhdistää siellä, mutta nyt homma hoidettaan pelkällä Power Queryllä. Kyselyiden tekeminen ja yhdistäminen on helppo operaatio, mutta sisältää useita vaiheita. Käytän taas runsaasti kuvia, jotta saat hyvän kuvan asiasta vaikkei sinulla olisi Power Queryä.

Jos et ole käyttänyt Power Queryä aikaisemmin, lue myös aiempi artikkelini: Power Query tutuksi + aineiston kääntäminen.

Sotshi–kyselyn luonti

1. Valitse Power Query > From Web ja määritä osoite (http://www.sochi2014.com/en/medal-standings), josta tiedot noudetaan.
2. Napsauta Navigator-ikkunassa oleva hierarkia auki ja kuljeta hiirtä taulukoiden päällä, jotta näet esikatselun kustakin taulukosta. Valitse mitalitaulukko napsauttamalla (tässä tapauksessa Table 0).
3. Käynnistä kyselyeditori Navigatorin alareunassa olevalla Edit-painikkeella.
4. Poista sekä Rank– että Total-sarakkeet pikavalikon Remove-komennolla.
5. Käännä mitalisarakkeet valitsemalla sarakkeet ja valitsemalla pikavalikosta Unpivot.
6. Vaihda uusien muodostuneiden sarakkeiden nimiksi Medal ja Sochi count pikavalikon Rename-komennolla:
7. Anna kyselylle Navigator-ikkunan avulla nimeksi Sochi medal count ja valitse ikkunan alaosasta ettei kyselyn tulosta ladata mihinkään. (Lataat Exceliin vasta lopullisen kokonaisuuden, jossa on myös Vancouverin mitalit mukana.)
8. Suorita kysely Apply & Close –toiminnolla ja näet kyselyn Excelissä Workbook Queries –ikkunassa. Tuo ikkuna tarvittaessa esiin valitsemalla Power Query > Workbook.

Vancouver-kyselyn muodostaminen

Vancouverin mitalitiedot haetaan täsmälleen samalla tavalla sivulta, jossa on samanlainen rakenne kuin Sotshin sivulla, joten voimme tehdä Sochi medal count -kyselystä kopion ja muokata kyselyä hieman.

1. Tee Sochi-kyselystä kopio Duplicate-komennolla.
2. Siirry muokkaamaan kopiota.
3. Vaihda kyselyn nimi ja käy muuttamassa Source-vaiheen asetuksista nettisivun osoite (http://www.sochi2014.com/en/medal-history?year=2010). Tämä on mahdollista, sillä molemmat sivut ja taulukot ovat rakenteeltaan samanlaiset.
4. Napsauta Navigation-ikkunan viimeistä vaihetta ja muuta kaavarivillä näkyvää kaavaa. Kirjoita sarakkeen otsikoksi Vancouver count.

Kyselyiden yhdistäminen yhdeksi luetteloksi

Lopputuloksena on kaksi kyselyä, joista kumpaakaan ei vielä ole ladattu Exceliin, joten yhdistetään kyselyiden tiedot yhteen luetteloon ja ladataan lopputulos Exceliin.

1. Valitse Workbook Queries –ikkunasta Sochi-kyselyn pikavalikosta komento Merge.
2. Valitse ikkunan alapuoliseen listaan Vancouver-kysely ja valitse molemmista listoista ne sarakkeet, joissa olevien tietojen perusteella luettelot yhdistetään. Molemmissa pitää olla sekä sama maa että mitali. Tätä vaihetta voisi verrata Excelin VLOOKUP-funktioon, sillä erotuksella että molemmista taulukoista voi valita useita sarakkeita, joiden pitää olla yhteneviä. Lisäksi jos Vancouver-taulukosta löytyisi esim. useita Norway-Gold -rivejä, kaikista muodostuisi oma rivinsä uuteen taulukkoon. Mainiota!
3. Hyväksy OK:lla.
4. Power Query näyttää yhdessä sarakkeessa, että se on yhdistänyt Sochi-aineistoon toisen taulun. Voit valita mitä tietoja toisesta taulusta näytetään napsauttamalla lopputuloksena muodostuneen NewColumn-sarakkeen nuolisymbolista. Valitse että haluat näyttää Vancouver count –sarakkeen tiedot.
5. Vaihda sarakkeen nimeksi Vancouver count.
6. Lisää kyselyyn uusi sarake, johon lasket Sochin ja Vancouverin mitalimäärien erotuksen:
   
7. Vaihda uuden sarakkeen nimeksi Difference.
8. Anna kyselylle nimeksi Sochi & Vancouver ja valitse että kyselyn tulos ladataan Exceliin ja suorita kysely.

Päivitys

Nyt sinulla on Excelissä luettelo, joka on muodostettu kahdesta eri kyselystä. Kun valitset tämän luettelon päältä pikavalikosta Refresh, Power Query suorittaa tuoreiden tietojen hakemisen, muokkaamisen ja yhdistämisen ja sinulla on uudet päivittyneet tiedot käytössäsi!

Listasta voi sellaisenaan tehdä vertailuita. Minä tein aineistosta myös Pivot-taulukon, johon lisäsin osittajaksi (slicer) mitalivaihtoehdot, Erotus-kenttään ehdollisen palkkimuotoilun (conditional formatting) ja lajittelin pivotin Sotshin mitalimäärän perusteella laskevaan järjestykseen, jotta mitalikuningas on listalla ensimmäisenä. Alla oleva kuva on 9.2 päivitetystä aineistosta.

Ladattava alkuperäinen versio: Mitalivertailu Sochi & Vancouver.

Ja eikun uusia mitaleita HUUTAMAAAAN!

Alla on vielä vertailu upotettuna, jotta voit itse suodattaa ja osittaa haluamasi mitalit esiin. Upotettu versio ei toimi kaikilla selaimilla eikä mobiilissa. Lupaan päivittää raportin päivittäin.