- Võimalus ühendada, puhastada ja teisendada andmeid mitmest allikast automatiseeritud ja korduva töövoo abil.
- M-keele kasutamine täiustatud teisenduste tegemiseks, mis ületavad visuaalse liidese võimalusi.
- Jõudluse optimeerimine päringute voltimise ja tõhusa mäluhalduse abil.
- Täiustatud integratsioon Azure'i teenuste ja ettevõtte ETL-tööriistadega andmemahu skaleerimiseks.
Kui olete kunagi tundnud, et kulutate arvutustabelite korrastamisele rohkem aega kui nende analüüsimisele, siis pole te üksi. Enamik analüütikuid raiskab lugematuid tunde korduvatele ülesannetele, näiteks tühjade ridade kustutamisele või ebajärjekindlate kuupäevavormingutega maadlemisele. Siin tulebki mängu Power Query – Exceli ja Power BI-sse integreeritud tööriist, mis toimib sisuliselt nutika filtrina, valmistades teie andmed ette juba enne, kui need teie arvutustabelisse või aruandesse jõuavad.
Parim osa on see, et selle kasutamiseks ei pea olema kogenud programmeerija. Power Query võimaldab teil luua teisendustorustiku ; see tähendab, et salvestate tehtud sammud üks kord ja järgmisel korral, kui saate uusi andmeid, klõpsate lihtsalt värskendamisnupul. See on lihtsalt erinevus käsitsi iga esmaspäevase töö tegemise ja andmevoo automatiseerimise vahel, nii et süsteem teeb selle teie eest sekunditega ära.
Mis täpselt on Power Query ja milleks seda kasutatakse?
Põhimõtteliselt on see ekstraktimise, teisendamise ja laadimise (ETL) mootor, mis on loodud kasutajatele, kes ei soovi keerukat koodi kirjutada. See võimaldab teil importida andmeid enam kui 30 allikast , alates lihtsast CSV-failist või veebilehest kuni SQL Serveri andmebaaside või pilveteenusteni nagu Azure ja OneLake. Selle eesmärk on edastada andmeid teie analüüsiks ideaalses tabelina, kõrvaldades müra ja ebakõlad.
Kuigi see on uskumatult võimas, on sellel ka omad piirangud. Näiteks Excel piirdub umbes miljoni reaga ega paku reaalajas andmevärskendusi ega täiustatud meeskonnatöö funktsioone. Kui ettevõte kasvab ja peab haldama miljardeid kirjeid või nõuab selliste regulatsioonide nagu GDPR või HIPAA rangeid auditeid, on aeg liikuda spetsiaalsete ETL-platvormide, näiteks Integrate.io, poole, mis laiendab neid võimalusi ettevõtte tasemele.
Esimesed sammud: andmeallikate ühendamine
Alustamiseks minge lihtsalt Exceli vahekaardile Andmed ja otsige üles jaotis Andmete hankimine ja teisendamine . Sõltuvalt teie Exceli versioonist (2016 ja uuem) võib see juba integreeritud olla; väga vanades versioonides installiti see tasuta lisandmoodulina. Peamine on siin andmete eelvaade , mis võimaldab teil enne kogu teabemahu laadimist kontrollida, kas struktuur on õige.
- Üksikud failid: Ideaalne Exceli või CSV-töövihikute jaoks. Olge CSV-failidega ettevaatlik, kuna need sisaldavad mõnikord ebatavalisi eraldajaid, mis võivad tabelit rikkuda.
- Kaustade importimine: See on tõeline pärl. Kui salvestate iga kuu sama struktuuriga faili kausta, saab Power Query... ühenda kõik failid automaatselt, virnastades read üksteise alla.
- Veebiallikad ja andmebaasid: Saate tabeleid otse URL-ist ekstraheerida või luua ühenduse SQL-i, Oracle'i või PostgreSQL-iga, vähendades oluliselt käsitsi eksportimise vajadust.
Puhastamine ja ümberkujundamine: tööriista süda
Kui andmed on Power Query redaktoris, algab maagia. Liides on peamiselt jagatud kaheks vahekaardiks: „Teisendamine “, mis muudab praegust veergu, ja „Lisa veerg“ , mis loob olemasoleva põhjal uue. Näiteks kui soovite kuupäevast nädala numbri eraldada, muudab „Teisendamine“ kuupäeva nädalaks, samas kui „Lisa veerg“ säilitab algse kuupäeva ja lisab nädala numbri selle kõrvale.
„Müra” kõrvaldamiseks on kõige levinum lähenemisviis tühjade ridade (nullväärtuste) eemaldamine , ebavajalike veergude kõrvaldamine ja teksti standardiseerimine. Väga kasulik funktsioon on „Veerg näidetest ”, kus saate käsitsi kirjutada, kuidas soovite andmeid näha (näiteks ainult vedaja koodi eraldamine) ja Microsofti tehisintellekt tuvastab mustri, et täita ülejäänud veerg ilma, et peaksite kirjutama ühtegi valemit.
Ümberkujundamine: andmestruktuuri muutmine
Mõnikord on andmed "laias" vormingus (kuud veergudes), mis on küll inimloetav, aga analüüsi jaoks katastroof. Siin tulebki mängu funktsioon Unpivot Columns . See funktsioon teisendab need veerud ridadeks, muutes tabeli "pikaks" vorminguks, mis on PivotTable'ide ja Power BI diagrammide jaoks väga meeltmööda.
Samuti saate veerge eraldajate (nt koma või sidekriipsu) abil jagada või veerge kombineerida , et luua unikaalseid võtmeid. Rühmitamine võimaldab teil kirjeid piirkonna või kategooria järgi kokku võtta, muutes koondtabelite kiire loomise lihtsaks.
Tabelite ühendamine: ühendamine ja lisamine
Power Querys on andmete ühendamiseks kaks peamist viisi. Lisamine on sisuliselt ühe tabeli asetamine teise alla; see on nagu SQL-is UNION ALL. See sobib ideaalselt siis, kui teil on jaanuari, veebruari ja märtsi müügiandmed eraldi failides ja soovite ühte peamist loendit.
Ühendamisoperatsioon sarnaneb andmebaasi JOIN-iga. Siin ühendate kaks tabelit ühise veeru, näiteks kliendi ID, põhjal. Ühendusi on mitut tüüpi: vasakpoolne väline ühendus säilitab kõik esimese tabeli read ja ainult teise tabeli vastavad read, samas kui sisemine ühendus säilitab ainult read, mis eksisteerivad mõlemas tabelis. Kui teil on vaja leida ridu, mis EI SATTU, on anti-Join ühendused ideaalne tööriist.
Varjatud jõud: M-keel ja funktsioonid
Kuigi visuaalne liides katab 90% vajadustest, on iga klõpsu taga M-keeles kirjutatud kood . Seda näeb valemiribal või täpsemas redaktoris. M on funktsionaalne keel, mis võimaldab teha asju, mida nupud ei suuda, näiteks luua kohandatud funktsioone keerulise loogika kordamiseks mitmes tabelis.
Edasijõudnutele kasutajatele võimaldab M manipuleerida andmestruktuuridega, näiteks loendite ja kirjetega . Näiteks saate loendis navigeerida indeksite abil (alati nullist alustades) või tabelit dünaamiliste kriteeriumide alusel filtreerida. M-i valdamine eristab tavakasutajat tõelisest andme-ninjast.
Jõudlus ja optimeerimine: Exceli krahhi vältimine
Üks levinumaid vigu on sadade ebavajalike sammude loomine. Iga samm tarbib mälu ja kui teil on tuhandeid muutujaid, võib Power BI või Excel muutuda äärmiselt aeglaseks. Lahendus on sammude ühendamine või imporditud veergude arvu vähendamine nii kiiresti kui võimalik.
Kriitiline mõiste on päringu voltimine . See toimub siis, kui Power Query tõlgib teie sammud lähtekeelde (näiteks T-SQL). Kui voltimine toimib, teeb andmebaas raske töö ära ja saadab teie arvutisse ainult lõpptulemuse. Kui kasutate funktsiooni, mis ei toeta voltimist (näiteks mõned keerulised tekstiteisendused), peab Power Query kõik andmed töötlemiseks kohalikku mällu alla laadima, mis võib teie arvuti jõudlust tõsiselt mõjutada.
Täiustatud tehnikad ja analüüs
Power Queryt saab integreerida ka tehisintellekti teenustega. Näiteks API-de abil saate oma andmed ühendada Azure'i masinõppega , et ennustada tulemusi, või kasutada anomaaliadetektorit ebatavaliste tõusude leidmiseks aegridades. See muudab lihtsa andmete puhastamise tööriista ennustava analüüsi platvormiks.
Teine oluline aspekt on veakäsitlus . Selle asemel, et lasta ühel valesti kirjutatud kirjel kogu aruande värskendamine katkestada, saate luua erandpäringu . See hõlmab viitamist algsele tabelile ja ainult vigaste ridade väljafiltreerimist, luues aruandelehe, kus administraator saab täpselt näha, millised read nurjusid ja miks, ilma et see peataks üldist töövoogu.
Professionaalse töövoo parimad tavad
Selleks, et teie aruanded iga kord faili teisaldamise korral ei rikuks toimimist, peaksite järgima teatud reegleid. Esiteks kasutage stabiilseid teid ja vältige kaustade teisaldamist. Teiseks nimetage oma sammud selgelt; „1. samm” asemel kasutage „Filter_Müük_2023”. Kolmandaks keelake vahepäringute laadimine . Kui teil on päring, mis sisestab andmeid ainult teise, tühjendage märkeruut „Luba laadimine”, et see ei tarbiks andmemudelis ebavajalikku mälu.
Lõpuks, kui teil on käidelda tohutul hulgal andmeid, rakendage astmelist värskendamist . Selle asemel, et iga kord miljoneid ridu laadida, saate süsteemi konfigureerida nii, et see hangib uusi või muudetud andmeid ainult viimasest kuust, hoides eelmised aastad salvestusruumis külmutatult. See vähendab oluliselt ooteaegu ja pilveressursside tarbimist.
Selle tööriista valdamine tähendab liikumist lahtrite käsitsi redigeerimisest robustsete ja skaleeritavate andmesüsteemide loomisele. Automatiseeritud puhastamise, M-keele ja jõudluse optimeerimise integreerimise abil saab iga professionaal muuta kaootilised andmed strateegilisteks ressurssideks, mida saab värskendada ühe klõpsuga, kõrvaldades inimlikud vead ja vabastades aega sellele, mis on tõeliselt oluline: otsuste langetamine tegeliku teabe põhjal.