„Power Query“ yra integruotas „Windows 365“ „Office 365“, „Excel 2016“, „Excel 2019“ versijose ir yra nemokamas atsisiųsti „Windows 2010“ ir „Excel 2013“ versijose. Šis įrankis skirtas išgauti, transformuoti ir įkelti duomenis į „Excel“ iš šaltinių įvairovė. Geriausia dalis: „Power Query“ prisimena jūsų veiksmus ir atkurs juos, kai norėsite atnaujinti duomenis. Tai reiškia, kad galite išvalyti 1 dienos duomenis 80% įprasto laiko, o 2–400 dienų duomenis galite išvalyti paprasčiausiai spustelėdami Atnaujinti.
Aš tai sakau apie daugybę naujų „Excel“ funkcijų, bet tai tikrai geriausia funkcija, pasiekianti „Excel“ per 20 metų.
Savo tiesioginiuose seminaruose pasakoju istoriją apie tai, kaip „Power Query“ buvo išrasta kaip ramentas SQL serverio analizės tarnybų klientams, kurie buvo priversti naudoti „Excel“, norėdami pasiekti „Power Pivot“. Tačiau „Power Query“ vis gerėjo, ir kiekvienas asmuo, naudojantis „Excel“, turėtų skirti laiko išmokti „Power Query“.
Gaukite maitinimo užklausą
Galbūt jau turite „Power Query“. Jis yra skirtuko Duomenys grupėje Gauti ir transformuoti.
Bet jei naudojate „Excel 2010“ arba „Excel 2013“, eikite į internetą ir ieškokite „Download Power Query“. Jūsų „Power Query“ komandos bus rodomos skirtuke „Power Query“ juostoje.
Išvalykite duomenis pirmą kartą atlikdami „Power Query“
Norėdami pateikti kai kurių „Power Query“ nuostabumo pavyzdžių, pasakykite, kad kiekvieną dieną gausite žemiau rodomą failą. A stulpelis neužpildytas. Ketvirčiai eina skersai, o ne žemyn.
Norėdami pradėti, įrašykite tą darbaknygę į kietąjį diską. Padėkite jį nuspėjamoje vietoje su pavadinimu, kurį naudosite tam failui kiekvieną dieną.
„Excel“ pasirinkite Gauti duomenis, iš failo, iš darbaknygės.
Naršykite po darbaknygę. Peržiūros srityje spustelėkite „Sheet1“. Užuot spustelėję Įkelti, spustelėkite Redaguoti. Darbaknygę dabar matote šiek tiek kitame tinklelyje - „Power Query“ tinklelyje.
Dabar jums reikia pataisyti visas tuščias langelius A stulpelyje. Jei tai padarėte „Excel“ vartotojo sąsajoje, nepatogi komandų seka yra „Pagrindinis“, „Rasti ir pasirinkti“, „Eiti į specialius“, „Blankai“, „Lygu“, „Rodyklė aukštyn“, „Ctrl“ + „Enter“ .
Dalyje „Power Query“ pasirinkite Transformuoti, Užpildyti, Žemyn.
Visos nulinės vertės pakeičiamos verte iš viršaus. Naudojant „Power Query“, reikia trijų, o ne septynių paspaudimų.
Kita problema: ketvirčiai eina skersai, o ne žemyn. Programoje „Excel“ galite tai išspręsti naudodami kelių konsolidavimo diapazono suvestinę lentelę. Tam reikia 12 žingsnių ir daugiau nei 23 paspaudimų.
Dalyje „Power Query“ pasirinkite du stulpelius, kurie nėra ketvirčiai. Skirtuke Transformavimas atidarykite išskleidžiamąjį stulpelį „Unpivot Columns“ ir pasirinkite Unpivot Other Columns, kaip parodyta žemiau.
Dešiniuoju pelės mygtuku spustelėkite naujai sukurtą stulpelį „Atributas“ ir pervadinkite jį vietoj „Atributas“ į ketvirtį. Dvidešimt plius paspaudimų programoje „Excel“ tampa penkiais paspaudimais „Power Query“.
Dabar, teisybės dėlei, ne kiekvienas „Power Query“ valymo žingsnis yra trumpesnis nei „Excel“. Stulpelio pašalinimas vis tiek reiškia, kad dešiniuoju pelės mygtuku spustelėkite stulpelį ir pasirenkate Pašalinti stulpelį. Bet jei atvirai, istorija pasakojama ne apie 1 dienos laiko taupymą.
Bet palaukite: „Power Query“ prisimena visus jūsų žingsnius
Pažvelkite į dešinę „Power Query“ lango pusę. Yra sąrašas pavadinimu Taikomi žingsniai. Tai greitas visų jūsų veiksmų audito pėdsakas. Spustelėkite bet kurią krumpliaračio piktogramą, kad pakeistumėte pasirinkimus tame žingsnyje ir pakeitimai būtų atliekami per būsimus veiksmus. Norėdami pamatyti, kaip duomenys atrodė prieš tą veiksmą, spustelėkite bet kurį veiksmą.
Baigę valyti duomenis, spustelėkite Uždaryti ir įkelti, kaip parodyta žemiau.
Patarimas
Jei jūsų duomenys yra daugiau nei 1 048 576 eilutės, naudodami išskleidžiamąjį meniu „Uždaryti ir įkelti“ galite įkelti duomenis tiesiai į „Power Pivot“ duomenų modelį, kuriame telpa 995 milijonai eilučių, jei mašinoje yra pakankamai atminties.
Po kelių sekundžių jūsų pakeisti duomenys bus rodomi „Excel“. Nuostabu.
Išmokėjimas: išvalykite duomenis rytoj vienu paspaudimu
Bet vėlgi, „Power Query“ istorija nėra apie laiko taupymą 1 dieną. Kai pasirenkate „Power Query“ grąžintus duomenis, dešinėje „Excel“ pusėje rodomas skydelis Užklausos ir jungtys, o jame yra mygtukas Atnaujinti. (Čia mums reikia mygtuko Redaguoti, bet kadangi jo nėra, turite dešiniuoju pelės mygtuku spustelėkite pradinę užklausą, kad galėtumėte peržiūrėti arba pakeisti pradinę užklausą).
Smagu valyti duomenis 1 dieną. Man patinka daryti kažką naujo. Bet kai mano vadovas pamato gautą ataskaitą ir sako „Gražu. Ar galite tai padaryti kiekvieną dieną? “ Greitai nekenčiu nuovargio valyti tuos pačius duomenų rinkinius kiekvieną dieną.
Taigi, norėdamas parodyti duomenų valymo 400 dieną, visiškai pakeičiau pradinį failą. Nauji produktai, nauji klientai, mažesnis skaičius, daugiau eilučių, kaip parodyta žemiau. Aš išsaugoju šią naują failo versiją tame pačiame kelyje ir tuo pačiu failo pavadinimu kaip ir pradinis failas.
Jei atidarysiu užklausos darbaknygę ir spustelėsiu Atnaujinti, per kelias sekundes „Power Query“ praneša apie 92, o ne 68 eilutes.
Duomenų valymas 2, 3, 4, 4,… 400,… Dienos ir begalybės dienomis dabar reikalauja dviejų paspaudimų.
Šis vienas pavyzdys tik subraižo „Power Query“ paviršių. Jei praleisite dvi valandas su knyga, M skirtas Ken Puls ir Miguel Escobar (Data) beždžionei, sužinosite apie kitas funkcijas, tokias kaip:
- Sujungti visus „Excel“ arba CSV failus iš aplanko į vieną „Excel“ tinklelį
- Ląstelės konvertavimas naudojant „Apple“; Bananas; Vyšnia; Krapai; Baklažanai į penkias „Excel“ eilutes
- Atliekant VLOOKUP į paieškos darbaknygę, kai įkeliate duomenis į „Power Query“
- Vienos užklausos pavertimas funkcija, kurią galima pritaikyti kiekvienoje „Excel“ eilutėje
Norėdami gauti išsamų „Power Query“ aprašą, apsilankykite Ken Puls ir Miguel Escobar „M Is for (Data) Monkey“. Iki 2019 m. Pabaigos bus prieinamas pakartotas antrasis leidimas „Master Your Data“.
Ačiū Migueliui Escobarui, Robui Garciai, Mike'ui Girvinui, Ray'ui Hauseriui ir Colinui Michaelui už „Power Query“ nominavimą.
