Csak a Power Query-szerkesztő használatával Power Query képleteket hozott létre. Lássuk, hogyan működik Power Query a motorháztető alatt. A Power Query-szerkesztő működés közbeni megtekintésével megtudhatja, hogyan frissíthet vagy adhat hozzá képleteket. A Speciális szerkesztő akár saját képleteket is forgathat.
A Power Query-szerkesztő adat lekérdezési és formázási felületet biztosít az Excelnek, amellyel számos adatforrásból származó adatokat alakíthat át. A Power Query-szerkesztő ablak megjelenítéséhez importáljon adatokat külső adatforrásokbólegy Excel-munkalapon, jelöljön ki egy cellát az adatok között, majd válassza a Lekérdezés > Szerkesztés lehetőséget. Az alábbiakban összefoglaljuk a fő összetevőket.
-
Az adatok formázásához használt Power Query-szerkesztő menüszalag
-
Az adatforrások és táblák megkereséséhez használt Lekérdezések panel
-
Helyi menük, amelyek a menüszalag parancsainak kényelmes billentyűparancsai
-
Az adatokra alkalmazott lépések eredményeit megjelenítő adatelőnézet
-
A Lekérdezés beállításai panel, amely felsorolja a tulajdonságokat és a lekérdezés egyes lépéseit
A színfalak mögött a lekérdezés minden lépése egy képleten alapul, amely látható a szerkesztőlécen.
Előfordulhat, hogy képletet szeretne módosítani vagy létrehozni. A képletek a Power Query képletnyelvet használják, amellyel egyszerű és összetett kifejezéseket is létrehozhat. A szintaxissal, argumentumokkal, megjegyzésekkel, függvényekkel és példákkal kapcsolatos további információkért lásd: Power Query M képletnyelv.
A futballbajnokságok listájának használata példaként a Power Query segítségével a webhelyen talált nyers adatok felhasználásával, majd egy jól formázott táblázattá alakítható. Tekintse meg, hogyan jönnek létre a lekérdezési lépések és a megfelelő képletek az egyes feladatokhoz a Lekérdezés beállításai panel Alkalmazott lépések területén és a Képletsávon.
Eljárás
-
Az adatok importálásához válassza az Adatok > a Webről lehetőséget, írja be a "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" kifejezést az URL-cím mezőbe, majd kattintson az OK gombra.
-
A Kezelő párbeszédpanelen válassza a bal oldali Eredmények [Szerkesztés] táblát, majd alul az Adatok átalakítása lehetőséget. Megjelenik a Power Query szerkesztő.
-
Az alapértelmezett lekérdezésnév módosításához a Lekérdezés beállításai panel Tulajdonságok területén törölje az "Eredmények [Szerkesztés]" elemet, majd írja be az "UEFA bajnokok" kifejezést.
-
A nem kívánt oszlopok eltávolításához jelölje ki az első, a negyedik és az ötödik oszlopot, majd válassza a Kezdőlap > Oszlop eltávolítása > Az egyéb oszlopok eltávolítása lehetőséget.
-
A nem kívánt értékek eltávolításához válassza az Oszlop1 lehetőséget, válassza a Kezdőlap > Értékek cseréje lehetőséget, írja be a "részleteket" a Keresendő értékek mezőbe, majd kattintson az OK gombra.
-
Ha el szeretné távolítani azokat a sorokat, amelyekben az "Év" szó szerepel, jelölje be a szűrőnyilat az Oszlop1 oszlopban, törölje a jelet az "Év" melletti jelölőnégyzetből, majd kattintson az OK gombra.
-
Az oszlopfejlécek átnevezéséhez kattintson duplán mindegyikre, majd módosítsa az "Oszlop1" értékét "Év", "Column4" értékre "Győztes" értékre, az "Oszlop5" értékét pedig "Végső pontszám" értékre.
-
A lekérdezés mentéséhez válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.
Eredmény
Az alábbi táblázat összefoglalja az egyes alkalmazott lépéseket és a megfelelő képletet.
Lekérdezési lépés és feladat |
Képlet |
---|---|
Forrás Kapcsolódás egy webes adatforráshoz |
= Web.Page(Web.Contents("http://en.wikipedia.org/wiki/UEFA_European_Football_Championship")) |
Navigáció A csatlakoztatni kívánt táblázat kijelölése |
=Source{2}[Data] |
Módosított típus Adattípusok módosítása (amely Power Query automatikusan megtörténik) |
= Table.TransformColumnTypes(Data2,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}}) |
Egyéb oszlopok eltávolítva A többi oszlop eltávolítása, hogy csak a szükségesek maradjanak meg |
= Table.SelectColumns(#"Changed Type",{"Column1", "Column4", "Column5"}) |
Lecserélt érték Értékek lecserélése a kijelölt oszlop értékeinek törléséhez |
= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,{"Column1"}) |
Szűrt sorok Szűrés végrehajtása az oszlop értékein |
= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year")) |
Oszlopok átnevezve Az oszlopfejlécek értelmesre módosultak |
= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}}) |
Fontos Körültekintően szerkessze a Forrás, a Navigáció és a Típus módosítása lépéseket, mert ezeket Power Query hozza létre az adatforrás definiálásához és beállításához.
A szerkesztőléc megjelenítése vagy elrejtése
A szerkesztőléc alapértelmezés szerint megjelenik, de ha nem látható, vissza is játszhatja.
-
Válassza a Nézet > Elrendezés > szerkesztőléc lehetőséget.
Képlet megjelenítése a szerkesztőlécen
-
Lekérdezés megnyitásához keresse meg a Power Query-szerkesztő korábban betöltöttet, jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
A Lekérdezés beállításai panel Alkalmazott lépések területén válassza ki a szerkeszteni kívánt lépést.
-
Keresse meg és módosítsa a paraméterértékeket a szerkesztőlécen, majd válassza az Enter ikont, vagy nyomja le az Enter billentyűt. Módosítsa például ezt a képletet úgy, hogy az Oszlop2: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"}) Utána:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})
Előtte: -
Válassza az Enter ikont, vagy nyomja le az Enter billentyűt az adatelőnézetben megjelenő új eredmények megtekintéséhez.
-
Ha egy Excel-munkalapon szeretné megtekinteni az eredményt, válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.
Képlet létrehozása a szerkesztőlécen
Egy egyszerű képletes példa esetén alakítsunk át egy szöveges értéket megfelelő kis- és nagybetűssé a Text.Properfüggvény használatával.
-
Üres lekérdezés megnyitásához az Excelben válassza az Adatok > Adatok lekérése > Más forrásokból > üres lekérdezés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
A szerkesztőlécen írja be a=Text.Proper("text value")kifejezést, majd válassza az Enter ikont, vagy nyomja le az Enter billentyűt. Az eredmények az Adatelőnézetben jelennek meg.
-
Ha egy Excel-munkalapon szeretné megtekinteni az eredményt, válassza a Kezdőlap > Bezárás & Betöltés lehetőséget.
Eredmény:
Képlet létrehozásakor Power Query ellenőrzi a képlet szintaxisát. Ha azonban beszúr, átrendez vagy töröl egy köztes lépést egy lekérdezésben, előfordulhat, hogy megszakít egy lekérdezést. Mindig ellenőrizze az eredményeket az Adatelőnézetben.
Fontos Körültekintően szerkessze a Forrás, a Navigáció és a Típus módosítása lépéseket, mert ezeket Power Query hozza létre az adatforrás definiálásához és beállításához.
Képlet szerkesztése párbeszédpanel használatával
Ez a metódus a lépéstől függően eltérő párbeszédpaneleket használ. Nem kell ismernie a képlet szintaxisát.
-
Lekérdezés megnyitásához keresse meg a Power Query-szerkesztő korábban betöltöttet, jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
A Lekérdezés beállításai panel Alkalmazott lépések területén válassza a szerkeszteni kívánt lépés Beállítások szerkesztése ikonját, vagy kattintson a jobb gombbal a lépésre, majd válassza a Beállítások szerkesztése parancsot.
-
A párbeszédpanelen végezze el a kívánt módosításokat, majd kattintson az OK gombra.
Lépés beszúrása
Miután végrehajtott egy lekérdezési lépést, amely újraformázta az adatokat, a rendszer hozzáad egy lekérdezési lépést az aktuális lekérdezési lépés alatt. de amikor a lépések közepén beszúr egy lekérdezési lépést, a következő lépésekben hiba léphet fel. Power Query egy Lépés beszúrása figyelmeztetést jelenít meg, amikor új lépést próbál beszúrni, és az új lépés módosítja a beszúrt lépést követő lépésekben használt mezőket, például az oszlopneveket.
-
A Lekérdezés beállításai panel Alkalmazott lépések területén válassza ki azt a lépést, amelyet közvetlenül az új lépés és a hozzá tartozó képlet elé szeretne helyezni.
-
A szerkesztőléc bal oldalán válassza a Lépés hozzáadása ikont. Másik lehetőségként kattintson a jobb gombbal egy lépésre, majd válassza a Lépés beszúrása utána lehetőséget. A következő formátumban jön létre egy új képlet:= <nameOfTheStepToReference>, például =Production.WorkOrder.
-
Írja be az új képletet a következő formátumban:=Class.Function(ReferenceStep[,otherparameters]) Tegyük fel például, hogy van egy nem oszlopot tartalmazó táblája, és hozzá szeretne adni egy "Ms" értékű oszlopot. vagy "Mr.", a személy nemétől függően. A képlet a következő lenne:=Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")
Lépés átrendezása
-
A Lekérdezések beállításai panel Alkalmazott lépések területén kattintson a jobb gombbal a lépésre, majd válassza a Feljebb vagy a Lejjebb lehetőséget.
Lépés törlése
-
Válassza a Törlés ikont a lépés bal oldalán, vagy kattintson a jobb gombbal a lépésre, majd válassza a Törlés vagy a Törlés a végéig lehetőséget. A törlése ikon a szerkesztőléc bal oldalán is elérhető.
Ebben a példában egy oszlop szövegét alakítsuk át megfelelő kis- és nagybetűkké a Speciális szerkesztő képletek kombinációjával.
Van például egy Rendelések nevű Excel-táblázata, amely egy ProductName oszlopot tartalmaz, amelyet megfelelő esetté szeretne átalakítani.
Előtte:
Utána:
Ha speciális lekérdezést hoz létre, lekérdezési képletlépések sorozatát hozza létre a let kifejezés alapján. A let kifejezéssel neveket rendelhet hozzá, és kiszámíthatja azokat az értékeket, amelyekre az in záradék hivatkozik, amely meghatározza a Lépést. Ez a példa ugyanazt az eredményt adja vissza, mint a "Képlet létrehozása a szerkesztőlécen" szakaszban szereplővel.
let Source = Text.Proper("hello world") in Source
Látni fogja, hogy minden lépés egy korábbi lépésre épül, egy név szerint megadott lépésre hivatkozva. Emlékeztetőül: a Power Query képletnyelv megkülönbözteti a kis- és nagybetűket.
1. fázis: Nyissa meg a Speciális szerkesztő
-
Az Excelben válassza az Adatok > Adatok lekérése > Egyéb források > üres lekérdezés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
A Power Query-szerkesztő válassza a Kezdőlap > Speciális szerkesztő lehetőséget, amely a let kifejezés sablonjával nyílik meg.
2. fázis: Az adatforrás meghatározása
-
Hozza létre a let kifejezést az Excel.CurrentWorkbook függvénnyel a következőképpen:let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]in Source#x4
-
A lekérdezés munkalapra való betöltéséhez válassza a Kész, majd a Kezdőlap > Bezárás & Betöltés >Bezárás & Betöltés lehetőséget.
Eredmény:
3. fázis: Az első sor előléptetése fejlécekként
-
A lekérdezés megnyitásához a munkalapon jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben (Power Query).
-
A Power Query-szerkesztő válassza a Kezdőlap > Speciális szerkesztő lehetőséget, amely a 2. fázis: Az adatforrás definiálása szakaszban létrehozott utasítással nyílik meg.
-
A let kifejezésben adja hozzá az #"Első sor fejlécként" és a Table.PromoteHeaders függvényt a következőképpen:let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source)#x3 #"First Row as Header"
-
A lekérdezés munkalapra való betöltéséhez válassza a Kész, majd a Kezdőlap > Bezárás & Betöltés >Bezárás & Betöltés lehetőséget.
Eredmény:
4. fázis: Egy oszlop minden értékének módosítása a megfelelő kis- és nagybetűkre
-
A lekérdezés megnyitásához a munkalapon jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.
-
A Power Query-szerkesztő válassza a Kezdőlap > Speciális szerkesztő lehetőséget, amely a 3. fázisban létrehozott utasítással nyílik meg: Az első sor előléptetése fejlécekként.
-
A Let kifejezésben konvertálja az egyes ProductName oszlopértékeket megfelelő szöveggé a Table.TransformColumns függvénnyel, hivatkozva az előző "Első sor fejlécként" lekérdezési képlet lépésre, hozzáadja a #"Nagybetűs minden Word" értéket az adatforráshoz, majd rendelje hozzá a #"Nagybetűs minden Word" értéket az eredményhez.let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source), #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})in #"Capitalized Each Word"
-
A lekérdezés munkalapra való betöltéséhez válassza a Kész, majd a Kezdőlap > Bezárás & Betöltés >Bezárás & Betöltés lehetőséget.
Eredmény:
A képletsáv viselkedését az Power Query-szerkesztő az összes munkafüzetében szabályozhatja.
A szerkesztőléc megjelenítése vagy elrejtése
-
Válassza a Fájl > Beállítások és beállítások > Lekérdezés beállításai lehetőséget.
-
A bal oldali panel GLOBÁLIS területén válassza a Power Query-szerkesztő lehetőséget.
-
A jobb oldali panel Elrendezés területén jelölje be a Szerkesztőléc megjelenítése jelölőnégyzetet, vagy törölje a jelölést.
Az M Intellisense be- vagy kikapcsolása
-
Válassza a Fájl > Beállítások és beállítások > Lekérdezési beállítások lehetőséget.
-
A bal oldali panel GLOBÁLIS területén válassza a Power Query-szerkesztő lehetőséget.
-
A jobb oldali panel Képlet területén jelölje be vagy törölje az M Intellisense engedélyezése lehetőséget a szerkesztőlécen, a speciális szerkesztőben és az egyéni oszlop párbeszédpanelen.
Megjegyzés A beállítás módosítása a Power Query-szerkesztő ablak következő megnyitásakor lép érvénybe.
Lásd még
Excelhez készült Microsoft Power Query – súgó
Egyéni függvény létrehozása és meghívása
Az Alkalmazott lépések lista használata (docs.com)
Egyéni függvények használata (docs.com)