Power Query: Tiedon täyttäminen, jakaminen, yhdistäminen ja muuntaminen

Power Query: Tiedon täyttäminen, jakaminen, yhdistäminen ja muuntaminen
<- Kyselyoppaan sisällysluettelo
<- Edellinen juttu

Kirjoitin aiemmin ohjeita sekä rivien ja sarakkeiden peruskäsittelystä että  niiden kääntämisestä, joten tässä jutussa on muutamia muita hyödyllisiä muokkaustoimintoja.

Tiedon täyttäminen

Tiedon täyttäminen on yksi kyselyeditorin hyödyllisimmistä toiminnoista, jos joudut tekemisiin sellaisten järjestelmien kanssa, joista dataa ei saa datana vaan erilaisina Exceliin tai tekstitiedostoihin muodostuvina raportteina. Voit täyttää sarakkeissa valitsemissasi sarakkeissa olevaa tietoa Transform > Fill (Muunna > Täyttö) –toiminnolla joko alas tai ylös.

täyttäminen

 

Sarakkeen monistaminen

Jos haluat säilyttää alkuperäisen sarakkeen koskemattomana, voit tehdä sarakkeesta kaksoiskappaleen (duplicate) ja ryhtyä muokkaamaan kopiota. Löydät kaksoiskappaleen luontikomennon sekä pikavalikosta että Add Column (Lisää sarake) -välilehdeltä.

Sarakkeen sisällön jakaminen

Voit jakaa sarakkeen sisällön useammaksi sarakkeeksi joko tietystä sijainnista kuten viidennen merkin kohdalta (by number of characters), tietyn erotinmerkin (esim. puolipiste) tai erotinmerkkien (esim. välilyönti ja sulkumerkki) kohdalta (By Delimiter).

Jakaminen löytyy sekä Home (Aloitus) -välilehdeltä että pikavalikosta.

split

Sarakkeiden yhdistäminen

Yhdistät sarakkeet yhdeksi merkkijonoksi valitsemalla ne ensin Ctrl-näppäimen avulla siinä järjestyksessä kuin haluat niiden yhdistyvän uuteen sarakkeeseen ja käyttämällä komentoa Add Column > Merge Columns (Lisää sarake > Yhdistä sarakkeet). Add Column -välilehdeltä valittu komento jättää aineistoon alkuperäiset sarakkeet ja lisää yhden yhdistetyn sarakkeen. Jos valitset saman komennon Transform (Muunna) -välilehdeltä, alkuperäiset sarakkeet poistetaan ja niiden tilalle tulee uusi muodostettu sarake. Voit valita sarakkeiden väliin lisättävän merkin (kuten välilyönnin) tai voit itse kirjoittaa tekstin, jonka haluat lisätä yhdistettävien tietojen väliin.

merge

Merkkien korvaaminen

Korvauskomento löytyy useammalta välilehdeltä ja pikavalikosta ja sen avulla voi korvata valitusta sarakkeesta merkkejä. Huomaa, että numeerisessa sarakkeessa ei voi suorittaa korvausta, vaan sinun pitää ensin muuntaa sarake Text-muotoon, tehdä korvaus ja palauttaa sen jälkeen sarake takaisin numeeriseksi.

replacereplace values 2

Merkkijonojen muuntaminen

Voit muuntaa Transform > Format (Muunna > Muotoilu) –toiminnolla tekstimuotoisen tiedon kirjainkokoa sekä esimerkiksi poistaa alusta tai lopusta turhat välilyönnit (trim). Clean (Tyhjennä) puolestaan poistaa soluissa olevat tulostumattomat merkit. Lisäksi voit lisätä merkkijonoon joko etuliitteen tai jälkiliitteen. Transform (Muunna) -komento löytyy myös pikavalikosta. Kun valitset vastaavan komennon Add Column -välilehdeltä, muodostat uuden sarakeen.

merkkijonojen-muuntaminen

Merkkijonojen pilkkominen sekä pituuden laskeminen

Voit muodostaa sarakkeen, jossa on valitun sarakkeen merkkijonojen pituus (length), poimia ensimmäisiä  tai  viimeisiä merkkejä tai halutusta kohdasta  halutun määrän merkkejä (Range). Komento löytyy sekä Add Column että Transform -välilehdiltä.

pilkkominen

Huomaa, että Range-komennolle annettava aloitusmerkki indeksoi merkit nollasta alkaen eteenpäin. Ensimmäinen merkki on siis nollas (0) merkki. Seuraava poimii kolmannesta (2) merkistä lukien 5 merkkiä.

range

Lukujen muuntaminen ilman erillistä kaavasaraketta

Voit muuntaa sarakkeellisen lukuja suorittamalla luvuille haluamasi aritmeettisen laskutoimituksen. Kun käytät komentoa Transform (Muunna) -välilehdeltä, alkuperäinen sarake korvataan uudella. Jos valitset vastaavan komennon Add Column (Lisää sarake) -välilehdeltä, alkuperäisen sarakkeen lisäksi muodostuu muunnettu sarake. Valittuasi komennon sinua pyydetään syöttämään millä luvulla lisäys, kertominen tms. laskutoimitus suoritetaan.

lukujen muuntaminen

Päivämäärien muuntaminen

Valitsemalla päivämäärä-muotoisen sarakkeen ja Transform > Date (Muunna > Päivämäärä) muunnat päivämäärien perusteella tiedot haluttuun muotoon. Vastaava Add Column (Lisää sarake) -välilehden komento muodostaa uuden sarakkeen. Huomaa, että voit tehdä vastaavia muunnoksia myös kellonajoille. (Edit 10/2016: Alla oleva kuva on vanhasta versiosta. Date-muunnoksia löytyy enemmän kuin kuvassa.)

Päivämäärien muuntaminen

<- Kyselyoppaan sisällysluettelo
<- Edellinen juttu

2 kommenttia artikkeliin ”Power Query: Tiedon täyttäminen, jakaminen, yhdistäminen ja muuntaminen

  1. Hei,

    saako tuota täyttöä toimimaan vain muutamalla rivillä sivusuuntaisesti? Täyttö-kohdassa vaihtoehdot ovat vain ylös tai alas.

    Tykkää

    • Hei Mia,

      Vaakasuuntaista täyttöä ei ole olemassa sisäänrakennettuna, joten pitää kikkailla.

      Kokeile seuraavaa:

      1) Muunna ne sarakkeet teksti-tietotyyppisiksi, joissa haluat suorittaa täytön.
      2) Korvaa kyseisten sarakkeiden null-soluihin jotain tekstiä, kuten ”tyhjä”.
      3) Suorita kyseisille sarakkeille unpivot-operaatio, jolloin muodostuu kaksi saraketta (attribute ja value) ja saat täytettävät tiedot value-sarakkeeseen allekkain. Unpivot-operaatio pudottaa null-arvot pois, mutta nyt ne säilyvät, kun null-arvojen tilalle on korvattu esim. ”tyhjä”.
      4) Suorita value-sarakkeessa ”tyhjä” arvojen korvaus takaisin null-arvoiksi.
      5) Suorita value-sarakkeessa fill down -operaatio.
      6) Valitse attribute ja value -sarake ja suorita niille takaisin pivot-operaatio siten, ettei suoriteta minkäänlaista laskutoimitusta (don’t aggregate).

      Olisipa tosiaan hienoa, jos Microsoft toteuttaisi fill left/right -tyyppiset operaatiot, jotta tämä olisi helpompi toteuttaa.

      Tykkää

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s