Power Query ir iebūvēts Office 365, Excel 2016, Excel 2019 Windows versijās un ir pieejams kā bezmaksas lejupielāde Excel 2010 un Excel 2013 Windows versijās. Šis rīks ir paredzēts datu iegūšanai, pārveidošanai un ielādēšanai programmā Excel no avotu dažādība. Labākā daļa: Power Query atceras jūsu soļus un atskaņos tos, kad vēlaties atsvaidzināt datus. Tas nozīmē, ka jūs varat tīrīt datus 1. dienā 80% no parastā laika, un jūs varat tīrīt datus no 2. līdz 400. dienai, vienkārši noklikšķinot uz Atsvaidzināt.
Es to saku par daudzām jaunām Excel funkcijām, taču šī patiešām ir labākā iezīme, lai sasniegtu Excel 20 gadu laikā.
Savos tiešraides semināros es stāstu par to, kā Power Query tika izgudrots kā kruķis SQL Server Analysis Services klientiem, kuri bija spiesti izmantot Excel, lai piekļūtu Power Pivot. Bet Power Query turpināja uzlaboties, un katrai personai, kas izmanto programmu Excel, vajadzētu veltīt laiku, lai apgūtu Power Query.
Saņemt Power Query
Iespējams, jums jau ir Power Query. Tas ir cilnes Dati grupā Iegūt un pārveidot.
Bet, ja izmantojat programmu Excel 2010 vai Excel 2013, dodieties uz internetu un meklējiet Download Power Query. Jūsu Power Query komandas parādīsies īpašā Power Query cilnē lentē.
Notīriet datus pirmo reizi Power Query
Lai sniegtu jums Power Query lielisko piemēru, sakiet, ka katru dienu saņemat zemāk redzamo failu. A kolonna nav aizpildīta. Ceturtdaļas iet pāri, nevis pa lapu.
Lai sāktu, saglabājiet šo darbgrāmatu savā cietajā diskā. Novietojiet to paredzamā vietā ar nosaukumu, kuru katru dienu izmantosit šim failam.
Programmā Excel atlasiet Iegūt datus, no faila, no darbgrāmatas.
Pārlūkojiet līdz darbgrāmatai. Priekšskatījuma rūtī noklikšķiniet uz Sheet1. Noklikšķiniet uz Ielādēt, nevis uz Rediģēt. Darbgrāmatu tagad redzat nedaudz citā režģī - Power Query režģī.
Tagad jums ir jānovērš visas tukšās šūnas A slejā. Ja tas būtu jādara Excel lietotāja saskarnē, apgrūtinošā komandu secība ir Sākums, Atrast un atlasīt, Iet uz īpašo, Blanks, Vienāds, Bultiņa augšup, Ctrl + Enter .
Sadaļā Power Query atlasiet Pārveidot, Aizpildīt, Uz leju.
Visas nulles vērtības tiek aizstātas ar vērtību no augšas. Izmantojot Power Query, tas prasa trīs klikšķus, nevis septiņus.
Nākamā problēma: kvartāli iet pāri, nevis uz leju. Programmā Excel to var novērst, izmantojot vairāku konsolidācijas diapazona rakurstabulu. Tam nepieciešami 12 soļi un vairāk nekā 23 klikšķi.
Programmā Power Query atlasiet divas kolonnas, kas nav ceturtdaļas. Cilnē Pārveidot atveriet nolaižamo izvēlni Unpivot Columns un izvēlieties Unpivot Other Columns, kā parādīts zemāk.
Ar peles labo pogu noklikšķiniet uz jaunizveidotās kolonnas Atribūts un pārdēvējiet to Kvartāls, nevis Atribūts. Divdesmit plus klikšķi programmā Excel kļūst par pieciem klikšķiem programmā Power Query.
Tagad, godīgi sakot, ne visi tīrīšanas soļi programmā Power Query ir īsāki nekā programmā Excel. Kolonnas noņemšana joprojām nozīmē peles labo pogu noklikšķiniet uz kolonnas un izvēlieties Noņemt kolonnu. Bet, godīgi sakot, šeit stāsts nav par laika ietaupījumu 1. dienā.
Bet pagaidiet: Power Query atceras visus jūsu soļus
Paskaties loga Power Query labajā pusē. Ir saraksts ar nosaukumu Applied Steps. Tā ir tūlītēja visu jūsu darbību revīzijas liecība. Noklikšķiniet uz jebkuras zobrata ikonas, lai mainītu izvēli šajā solī un lai izmaiņas tiktu ietvertas turpmākajās darbībās. Noklikšķiniet uz jebkura soļa, lai skatītu, kā dati izskatījās pirms šīs darbības.
Kad esat pabeidzis datu tīrīšanu, noklikšķiniet uz Aizvērt un ielādēt, kā parādīts zemāk.
Padoms
Ja jūsu dati pārsniedz 1 048 576 rindas, varat izmantot nolaižamo izvēlni Aizvērt un ielādēt, lai datus ielādētu tieši Power Pivot datu modelī, kurā var ievietot 995 miljonus rindu, ja mašīnā ir pietiekami daudz atmiņas.
Pēc dažām sekundēm jūsu pārveidotie dati tiek parādīti programmā Excel. Satriecošs.
Izmaksa: tīri dati rīt ar vienu klikšķi
Bet atkal Power Power vaicājuma stāsts nav par laika ietaupījumu 1. dienā. Atlasot Power Query atgrieztos datus, Excel labajā pusē parādās panelis Queries & Connections, un tajā ir poga Refresh. (Mums šeit ir nepieciešama poga Rediģēt, taču, tā kā tādas nav, jums ar peles labo pogu noklikšķiniet uz sākotnējā vaicājuma, lai skatītu vai veiktu izmaiņas sākotnējā vaicājumā).
Ir jautri tīrīt datus 1. dienā. Man patīk darīt kaut ko jaunu. Bet, kad mans menedžeris redz iegūto ziņojumu un saka: “Skaisti. Vai jūs to varat darīt katru dienu? ” Es ātri pieaugu, ienīstot nogurumu katru dienu tīrīt to pašu datu kopu.
Tātad, lai parādītu datu tīrīšanas 400. dienu, esmu pilnībā mainījis sākotnējo failu. Jauni produkti, jauni klienti, mazāks skaits, vairāk rindu, kā parādīts zemāk. Es saglabāju šo jauno faila versiju tajā pašā ceļā un ar tādu pašu faila nosaukumu kā sākotnējais fails.
Ja atveru vaicājuma darbgrāmatu un pēc dažām sekundēm noklikšķiniet uz Atsvaidzināt, Power Query ziņo par 92, nevis 68 rindām.
Datu tīrīšana 2. dienā, 3. dienā, 4. dienā,… dienā 400. dienā … Dienas bezgalība tagad prasa divus klikšķus.
Šis viens piemērs tikai saskrāpē Power Query virsmu. Ja pavadāt divas stundas kopā ar grāmatu, M ir paredzēts Ken Puls un Miguel Escobar (Data) Monkey, jūs uzzināsiet par citām funkcijām, piemēram:
- Apvienojot visus Excel vai CSV failus no mapes vienā Excel režģī
- Šūnas konvertēšana ar Apple; Banāns; Ķirsis; Dilles; Baklažāni programmā Excel piecās rindās
- Veicot VLOOKUP uzmeklēšanas darbgrāmatu, ieviešot datus Power Query
- Atsevišķa vaicājuma izveidošana par funkciju, kuru var pielietot katrā Excel rindā
Lai iegūtu pilnīgu Power Query aprakstu, skatiet Ken Is Puls un Miguel Escobar M Is for (Data) Monkey. Līdz 2019. gada beigām būs pieejams pārstrādātais otrais izdevums Master Your Data.
Paldies Migelam Eskobaram, Robam Garsijai, Maikam Girvinam, Rejam Hauseram un Kolinam Maiklam par Power Query izvirzīšanu.
