Joskus tulee tilanteita, jolloin ei tiedetä etukäteen tarkasti, kuinka monta ja mitkä rivit pitää tuoda lähdetiedoista. Oletetaan, että meidän on ladattava tietoja tekstitiedostosta Power Queryyn, mikä ei ensi silmäyksellä aiheuta suurta ongelmaa. Vaikeus on, että tiedostoa päivitetään säännöllisesti, ja huomenna siinä voi olla eri määrä rivejä tietoja, kolmen, ei kahden rivin otsikko jne.:
Eli emme voi varmuudella sanoa etukäteen, mistä rivistä alkaen ja kuinka monta riviä tarkalleen tuodaan. Ja tämä on ongelma, koska nämä parametrit on koodattu pyynnön M-koodiin. Ja jos teet pyynnön ensimmäiselle tiedostolle (tuodat 5 riviä alkaen 4.), se ei enää toimi oikein toisen kanssa.
Olisi hienoa, jos kyselymme voisi itse määrittää "kelluvan" tekstilohkon alun ja lopun tuontia varten.
Ratkaisu, jota haluan ehdottaa, perustuu siihen ajatukseen, että tietomme sisältävät avainsanoja tai arvoja, joita voidaan käyttää tarvitsemamme tietolohkon alun ja lopun markkereina (ominaisuuksina). Esimerkissämme alku on rivi, joka alkaa sanalla SKU, ja lopussa on rivi sanalla Yhteensä. Tämä rivin validointi on helppo toteuttaa Power Queryssä käyttämällä ehdollista saraketta – funktion analogia IF (JOS) Microsoft Excelissä.
Katsotaan kuinka se tehdään.
Ensin ladataan tekstitiedostomme sisältö Power Queryyn tavallisella tavalla – komennon kautta Tiedot – Hae tiedot – Tiedostosta – Teksti-/CSV-tiedostosta (Data – Hae tiedot – Tiedostosta – Teksti-/CSV-tiedostosta). Jos Power Query on asennettu erillisenä apuohjelmana, vastaavat komennot ovat välilehdellä Tehokysely:
Kuten aina, tuonnissa voit valita sarakkeen erottimen (meissä tapauksessa tämä on sarkain), ja tuonnin jälkeen voit poistaa automaattisesti lisätyn vaiheen muokattu tyyppi (Muutettu tyyppi), koska on liian aikaista määrittää tietotyyppejä sarakkeille:
Nyt käskyllä Sarakkeen lisääminen – ehdollinen sarake (Lisää sarake — ehdollinen sarake)lisätään sarake, jossa tarkistetaan kaksi ehtoa – lohkon alussa ja lopussa – ja näytetään mahdolliset erilaiset arvot kussakin tapauksessa (esim. 1 и 2). Jos mikään ehdoista ei täyty, tulosta nolla:
Napsauttamisen jälkeen OK saamme seuraavan kuvan:
Siirrytään nyt välilehteen. Muutos ja valitse joukkue Täytä (Muunna – Täytä – Alas) – meidän ykköset venyvät alaspäin saraketta:
No, kuten arvata saattaa, voit yksinkertaisesti suodattaa ehdollisen sarakkeen yksiköt – ja tässä on haluttu tietomme:
Jäljelle jää vain nostaa ensimmäinen rivi otsikkoon komennolla Käytä ensimmäistä riviä otsikoina kieleke Koti (Etusivu – Käytä ensimmäistä riviä otsikoina) ja poista tarpeeton ehdollisempi sarake napsauttamalla hiiren oikealla painikkeella sen otsikkoa ja valitsemalla komennon Poista sarake (Poista sarake):
Ongelma ratkaistu. Nyt, kun lähdetekstitiedoston tietoja muutetaan, kysely määrittää nyt itsenäisesti tarvitsemamme tiedon "kelluvan" fragmentin alun ja lopun ja tuo joka kerta oikean määrän rivejä. Tietenkin tämä lähestymistapa toimii myös silloin, kun tuodaan XLSX, ei TXT-tiedostoja, samoin kuin tuodaan kaikki tiedostot kansiosta kerralla komennolla Tiedot – Hae tiedot – Tiedostosta – Kansiosta (Data — Hae tiedot — Tiedostosta — Kansiosta).
- Taulukoiden kokoaminen eri tiedostoista Power Queryllä
- Ristitaulukon uudelleensuunnittelu tasaiseksi makrojen ja Power Queryn avulla
- Hankkeen Gantt-kaavion rakentaminen Power Queryssä