A Power Query az Office 365, Excel 2016, Excel 2019 Windows verzióiba van beépítve, és ingyenesen letölthető az Excel 2010 és az Excel 2013 Windows verzióiban. Az eszközt úgy tervezték, hogy adatokat nyerjen ki, alakítson át és töltsön be Excel-be egy sokféle forrás. A legjobb rész: A Power Query megjegyzi a lépéseit, és lejátszja őket, amikor frissíteni szeretné az adatokat. Ez azt jelenti, hogy az 1. napon az adatokat a normál idő 80% -ában tisztíthatja, a 2. – 400. Napon pedig egyszerűen a Frissítés gombra kattintva tisztíthatja az adatokat.
Sok új Excel-funkcióról mondom ezt, de ez valóban a legjobb tulajdonság az Excel elérésére 20 év alatt.
Élő szemináriumaim során elmesélek egy történetet arról, hogyan találták ki a Power Query-t mankóként azoknak az SQL Server Analysis Services ügyfeleknek, akik kénytelenek voltak az Excel használatát a Power Pivot eléréséhez. De a Power Query folyamatosan javult, és minden Excel-t használó embernek szánnia kell időt a Power Query megtanulására.
Teljesítmény lekérdezés
Lehet, hogy már van Power Query. Az Adatok lap Get & Transform csoportjában található.
De ha az Excel 2010 vagy az Excel 2013 programot használja, akkor lépjen az Internetre, és keresse meg a Letöltés teljesítménylekérdezést. A Power Query parancsai a szalag egy külön Power Query lapján jelennek meg.
Tisztítsa meg az adatokat a Power Query során
Ha példát szeretnénk mondani a Power Query néhány félelmetes tulajdonságára, mondjuk, hogy minden nap megkapja az alább látható fájlt. Az A oszlop nincs kitöltve. A negyedek keresztezik az oldalt lefelé.
Első lépésként mentse a munkafüzetet a merevlemezre. Tegye kiszámítható helyre egy névvel, amelyet minden nap használni fog az adott fájlhoz.
Az Excelben válassza az Adatok lekérése, fájlból, munkafüzetből lehetőséget.
Keresse meg a munkafüzetet. Az Előnézet panelen kattintson a Sheet1 elemre. A Betöltés gombra kattintás helyett kattintson a Szerkesztés gombra. Most látja a munkafüzetet egy kissé eltérő rácsban - a Power Query rácsban.
Most meg kell javítania az A oszlop összes üres celláját. Ha ezt az Excel felhasználói felületén tette volna, akkor a nehézkes parancssor: Home, Keresés és kijelölés, Ugrás speciálisra, Üresek, Egyenlő, Fel nyíl, Ctrl + Enter .
A Power Query alatt válassza az Átalakítás, Kitöltés, Le lehetőséget.
Az összes nullérték helyébe a fenti érték lép. A Power Query használatával hét helyett három kattintás szükséges.
Következő probléma: A negyedek lefelé haladnak. Az Excelben ezt kijavíthatja a Többszörös konszolidációs tartomány pivot táblával. Ehhez 12 lépés és 23+ kattintás szükséges.
A Power Query részben válassza ki a két oszlopot, amelyek nem negyedek. Nyissa meg az Oszlopok visszavonása legördülő listát az Átalakítás lapon, és válassza az Egyéb oszlopok visszavonása lehetőséget, az alábbiak szerint.
Kattintson a jobb gombbal az újonnan létrehozott Attribútum oszlopra, és nevezze át Negyedre az Attribútum helyett. Az Excel húsz plusz kattintása öt kattintássá válik a Power Query-ben.
Most, hogy igazságos legyek, nem minden tisztítási lépés rövidebb a Power Query-ben, mint az Excel-ben. Az oszlop eltávolítása továbbra is azt jelenti, hogy jobb gombbal kattint az oszlopra, és az Oszlop eltávolítása lehetőséget választja. De hogy őszinte legyek, a történet itt nem az 1. nap időmegtakarításáról szól.
De várjon: Az Power Query minden lépésére emlékezik
Nézze meg a Power Query ablak jobb oldalát. Van egy Alkalmazott lépések nevű lista. Ez azonnali ellenőrzési nyomvonal az összes lépésről. Kattintson bármelyik fogaskerék ikonra az adott lépés megváltoztatásához, és a változtatások lépcsőzetesek legyenek a következő lépéseken keresztül. Kattintson bármelyik lépésre annak megtekintéséhez, hogy az adatok hogyan néztek ki a lépés előtt.
Ha végzett az adatok tisztításával, kattintson a Bezárás és betöltés gombra az alábbiak szerint.
Tipp
Ha az adatai meghaladják az 1 048 576 sort, a Bezárás és betöltés legördülő menü segítségével az adatokat közvetlenül a Power Pivot adatmodellbe töltheti be, amely 995 millió sort képes befogadni, ha elegendő memória van telepítve a gépre.
Néhány másodperc múlva az átalakított adatok megjelennek az Excelben. Fantasztikus.
A fizetés: Tiszta adatok holnap, egyetlen kattintással
De a Power Query története nem az 1. nap időmegtakarításáról szól. Ha kiválasztja a Power Query által visszaadott adatokat, akkor az Excel jobb oldalán megjelenik egy Lekérdezések és kapcsolatok panel, amelyen egy Frissítés gomb található. (Szükségünk van itt egy Szerkesztés gombra, de mivel ilyen nincs, az eredeti lekérdezés megtekintéséhez vagy módosításához jobb egérgombbal kell kattintania az eredeti lekérdezésre).
Nagyon jó az adatok tisztítása az 1. napon. Szeretek valami újat csinálni. De amikor a menedzser meglátja a kapott jelentést, és azt mondja: „Gyönyörű. Meg tudod csinálni ezt minden nap? Gyorsan gyűlölöm azt az unalmat, hogy naponta ugyanazokat az adatsorokat tisztítom.
Az adatok tisztításának 400. napjának bemutatásához tehát teljesen megváltoztattam az eredeti fájlt. Új termékek, új vásárlók, kisebb számok, több sor, az alábbiak szerint. A fájl új verzióját ugyanabba az elérési útba és ugyanazzal a fájlnévvel mentem, mint az eredeti fájlt.
Ha megnyitom a lekérdezés munkafüzetét, és rákattintok a Frissítés gombra, néhány másodperc múlva a Power Query 68 sor helyett 92 sorról számol be.
Az adatok megtisztítása a 2., a 3., a 4., a…, a 400. napon … a Nap Infinity most két kattintást igényel.
Ez az egyetlen példa csak a Power Query felületét karcolja meg. Ha két órát tölt együtt a könyvvel, M Ken Puls és Miguel Escobar (Adat) Majomra vonatkozik, megismerhet más funkciókat, például ezeket:
- Az összes Excel vagy CSV fájl összevonása egy mappából egyetlen Excel rácsba
- Cella konvertálása Apple-vel; Banán; Cseresznye; Kapor; Padlizsán öt sorra az Excel-ben
- VLOOKUP készítése egy keresési munkafüzethez, amikor adatokat hoz a Power Querybe
- Egyetlen lekérdezés készítése az Excel minden sorára alkalmazható funkcióvá
A Power Query teljes leírásához nézze meg Ken Puls és Miguel Escobar M Is for (Data) majmját. 2019 végéig elérhető lesz az újrakiadott második kiadás, a Master Your Data.
Köszönet Miguel Escobarnak, Rob Garciának, Mike Girvinnek, Ray Hausernek és Colin Michaelnek a Power Query jelöléséért.
