Power Query-képletek írása az Excelben

A Power Query szerkesztőjével már végig Power Query-képleteket hoz létre. Nézzük meg, hogyan működik a Power Query a motorháztető alatt. A Power Query Szerkesztőt bekapcsolva megnézve megtudhatja, hogy miként frissítheti vagy használhatja a képleteket.  A Speciális szerkesztővel akár saját képleteket is begördíthet.           

A Power Query szerkesztő adatlekérdezéseket és -formázási Excel, amelyek segítségével számos adatforrásból származó adatokat alakíthat át. A Power Query Szerkesztő ablakának megjelenítéséhez importáljon adatokat külső adatforrásból egy Excel munkalapon, jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés > Szerkesztés gombra. Az alábbiakban összefoglaljuk a fő összetevőket.

A Lekérdezésszerkesztő részei

  1. The Power Query Editor ribbon that you use to shape your data

  2. Az adatforrások és táblák kereséséhez használt Lekérdezések ablaktábla

  3. A menüszalag parancsaihoz kényelmesen használható helyi menük

  4. The Data Preview that displays the results of the steps applied to the data

  5. The Query Gépház pane that lists properties and each step in the query

A színfalak mögött a lekérdezés minden lépése egy, a szerkesztőlécen látható képleten alapul.

Mintaképlet a Lekérdezésszerkesztőben

Lehetnek olyan időpontok, amikor képletet szeretne módosítani vagy létrehozni. A képletek a Power Query képletnyelvét használják, amellyel egyszerű és összetett kifejezéseket is felépíthet. A szintaxissal, argumentumokkal, megjegyzésekkel, függvényekkel és példákkal kapcsolatos további információkért lásd a Power Query M képletnyelvét.

Ha példaként egy focibajnokok listáját használja, a Power Queryvel a webhelyen talált nyers adatokat egy jól formázott táblázatba használhatja. Nézze meg, hogyan jön létre a lekérdezés lépései és a megfelelő képletek az egyes feladatokhoz a Lekérdezés Gépház munkaablak Alkalmazott lépések és a Szerkesztőléc alatt.

Ez a böngésző nem támogatja a videók lejátszását.

Eljárás

  1. Az adatok importáláshoz válassza az Adatok> weblapról lehetőséget,írja be a "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" címet az URL-cím mezőbe, majd válassza az OK gombot.

  2. A Kezelő párbeszédpanelen válassza az Eredmények [Szerkesztés] táblát a bal oldalon, majd válassza az Adatok átalakítása lehetőséget alul. Megjelenik a Power Query-szerkesztő.

  3. A lekérdezés alapértelmezett nevének módosítása érdekében a Lekérdezés Gépház tulajdonságok csoportban törölje az "Eredmények [Szerkesztés]" adatokat, majd írja be az "UEFA-bajnokok" nevet.

  4. 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 és > oszlopok eltávolítása lehetőséget.

  5. A nem kívánt értékek eltávolításához válassza az Oszlop1, a Kezdőlap >értékekcseréje lehetőséget, írja be a "részletek" értéket a Kereshető értékek mezőbe, majd válassza az OK gombot.

  6. Ha el szeretné távolítani a "Year" szót tartalmazó sorokat, válassza az Oszlop1szűrőnyilát, törölje a "Year" (Év) melletti jelölőnégyzet jelölését, majd kattintson az OK gombra.

  7. Az oszlopfejlécek átnevezéséhez kattintson duplán mindegyikre, majd módosítsa az "Oszlop1" nevet "Év", "Oszlop4" értékről "Nyertes" és "Oszlop5" szövegre a "Végleges pontszám" szövegre.

  8. A lekérdezés mentéséhez válassza a Kezdőlap>Bezárás & gombot.

Eredmény

Az útmutató eredménye – az első néhány sor

Az alábbi táblázat az egyes alkalmazott lépések és a megfelelő képletek összegzését tartalmazza.

A lekérdezés lépése és a 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 (amelyet a Power Query automatikusan tesz)

= 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}})

További oszlopok eltávolítása

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 cseréje a kijelölt oszlop értékeinek tisztításakor

= 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"))

Átnevezett oszlopok

Jelentéssel bíróra módosította az oszlopfejléceket

= Table.RenameColumns(#"Filtered Rows",{{"Column1", "Year"}, {"Column4", "Winner"}, {"Column5", "Final Score"}})

Fontos    Legyen óvatos, amikorszerkeszti a Forrás,a Navigáció és a Típus módosítása lépést, mert azokat a Power Query hozta létre az adatforrás definiálása és   beállítása érdekében.

A szerkesztőléc megjelenítése vagy elrejtése

A szerkesztőléc alapértelmezés szerint látható, de ha nem látható, újra lejátszhatja.

  • Válassza a > elrendezés >szerkesztőlécen lehetőséget.

Edit a formula in the formula bar

  1. Lekérdezés megnyitásához keresse meg a Power Query szerkesztőből korábban betöltött lekérdezést, jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés és> gombra. További információt a Lekérdezés létrehozása,betöltése vagy szerkesztése a Excel.

  2. A Lekérdezés Gépház alkalmazott lépésekalatt válassza ki a szerkeszteni kívánt lépést.

  3. A szerkesztőlécen keresse meg és módosítsa a paraméterértékeket, majd válassza az Enter A Power Query szerkesztőlécének bal oldali Enter ikonja ikont, vagy nyomja le az Enter billentyűt. Módosítsa például ezt a képletet úgy, hogy az Oszlop2:

    Előtte: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
    utána:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

  4. Válassza az Enter A Power Query szerkesztőlécének bal oldali Enter ikonja ikont, vagy nyomja le az Enter billentyűt az új eredmények megjelenítéséhez az Adatbetekintőben.

  5. Ha meg szeretné látni az eredményt egy Excel, válassza a Kezdőlap> Bezárás & gombot.

Képlet létrehozása a szerkesztőlécen

Egy egyszerű képlet példájaként alakítsunk át egy szöveges értéket nagytűsikre a Text.Properfüggvény használatával.

  1. Ha üres lekérdezést nyit meg, a Lekérdezés Excel válassza az> Lekérdezés > Egyéb forrásokból és >lekérdezés lehetőséget. További információt a Lekérdezés létrehozása,betöltése vagy szerkesztése a Excel.

  2. A szerkesztőlécen írja be a=Text.Proper("text value"), majd válassza az Enter A Power Query szerkesztőlécének bal oldali Enter ikonja vagy nyomja le az Enter billentyűt.

    Az eredmények az Adatbetekintőben jelennek meg.

  3. Ha meg szeretné látni az eredményt egy Excel, válassza a Kezdőlap> Bezárás & gombot.

Eredmény:

Text.Proper

 Képlet létrehozásakor a Power Query ellenőrzi a képlet szintaxisát. Amikor azonban beszúr, átrendez vagy töröl egy köztes lépést egy lekérdezésben, az megszakíthatja a lekérdezést.  Mindig ellenőrizze az eredményeket az Adatbetekintőben.

Fontos    Legyen óvatos, amikorszerkeszti a Forrás,a Navigáció és a Típus módosítása lépést, mert azokat a Power Query hozta létre az adatforrás definiálása és   beállítása érdekében.

Képlet szerkesztése párbeszédpanel használatával

Ez a módszer a lépéstől függően változó párbeszédpaneleket használ. Nem szükséges ismeri a képlet szintaxisát.

  1. Lekérdezés megnyitásához keresse meg a Power Query szerkesztőből korábban betöltött lekérdezést, jelöljön ki egy cellát az adatokban, majd válassza a Lekérdezés és> gombra. További információt a Lekérdezés létrehozása,betöltése vagy szerkesztése a Excel.

  2. A Lekérdezés Gépház munkaablak Alkalmazott lépések ablaktábláján válassza a szerkeszteni kívánt lépés Gépház Beállítások ikon szerkesztése ikonját, vagy kattintson a jobb gombbal a lépésre, és válassza a Szerkesztés Gépház.

  3. A párbeszédpanelen írja be a kívánt módosításokat, majd válassza az OK gombot.

Lépés beszúrása

Miután befejezte az adatokat átalakító lekérdezési lépést, az aktuális lekérdezési lépés alá egy lépés lesz hozzáadva. Ha azonban a lépések közepén beszúr egy lekérdezési lépést, az azt követő lépésekben hiba léphet fel. A Power Query egy Lépés beszúrása figyelmeztetést jelenít meg, amikor Ön megpróbál beszúrni egy új lépést, és az új lépés módosítja a beszúrt lépést követő lépések bármelyikében használt mezőket, például oszlopneveket.

  1. A Lekérdezés Gépház alkalmazott lépések alatt válassza ki azt a lépést, amely közvetlenül az új lépés és a hozzá tartozó képlet előtt áll.

  2. Válassza a Lépés hozzáadása Függvény ikon ikont a szerkesztőléc bal felső részen. Másik lehetőségként kattintson a jobb gombbal egy lépésre, és válassza a Lépés beszúrása utána lehetőséget.Az új képlet a következő formátumban jön létre:

    = <nameOfTheStepToReference>, például =Production.WorkOrder.

  3. Írja be az új képletet a következő formátummal:

    =Class.Function(ReferenceStep[,otherparameters])

    Tegyük fel például, hogy van egy Nem oszlopot tartalmazó táblázata, és fel szeretne adni egy "Ms" értéket tartalmazó oszlopot. vagy "Mr.", a személy nemétől függően. A képlet a következő:

    =Table.AddColumn(<ReferencedStep>, "Prefix", each if [Gender] = "F" then "Ms." else "Mr.")

Példaképlet

Lépés átrendezés

  • A Lekérdezések Gépház alkalmazott lépésekalatt kattintson a jobb gombbal a lépésre, és válassza a Fel vagy a Le lehetőséget.

Lépés törlése

  • Válassza a Lépés törlése bal oldali Ikon törlése ikont, vagy kattintson a jobb gombbal a lépésre, és válassza a Törlés vagy a Törlés a végéig parancsot. A Lépés törlése törlés ikonja a szerkesztőléc bal felső részen is elérhető.

Ebben a példában a Speciális szerkesztőben képletek kombinációját használva alakítsunk át egy oszlopban lévő szöveget nagy szöveggé. 

Tegyük fel például, hogy van egy rendelések nevű Excel, és egy ProductName oszlopot szeretne nagy tetszetűre átalakítani. 

Előtte:

Előtte

Utána:

4. lépés - Eredmény

Amikor összetett lekérdezést hoz létre, a lekérdezési képlet lépéseit a "let" kifejezés alapján hozza létre. A "let" kifejezéssel neveket rendelhet hozzá, és kiszámíthatja azokat az értékeket, amelyekre az in záradék hivatkozik ( ez határozza meg 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" című szakaszban megadott eredményt.

let  
    Source = Text.Proper("hello world")
in  
    Source  

Láthatja, hogy minden lépés egy előző lépésre épül, ha név szerint hivatkozik egy lépésre. Emlékeztetőként a Power Query képletnyelve megkülönbözteti a kis- és nagybetűket.

1. fázis: A Speciális szerkesztő megnyitása

  1. A Excel lekérdezésben válassza az >adatok be > lekérdezés > lehetőséget. További információt a Lekérdezés létrehozása,betöltése vagy szerkesztése a Excel.

  2. A Power Query szerkesztőben válassza a Kezdőlap > Speciális szerkesztő lehetőséget, amely a "let" kifejezés sablonját tartalmazza.

Speciális szerkesztő2

2. fázis: Az adatforrás definiálása

  1. Hozza létre a "let" kifejezést a Excel. CurrentWorkbook függvény az alábbiak szerint:let


        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
    in
        Source

    1. lépés - Speciális szerkesztő

  2. A lekérdezés munkalapra való betöltéséhez válassza a Kész gombot, majd a Kezdőlap >Bezárás & a > Betöltés & gombra.

Eredmény:

1. lépés - Eredmény

3. fázis: Az első sor fejlécekként való előléptetve

  1. 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 és >gombra. További információt a Lekérdezés létrehozása, betöltése és szerkesztése a Power Queryben Excel ().

  2. A Power Query szerkesztőben válassza a Kezdőlap > Speciális szerkesztő lehetőséget, amely a 2. fázis:Adatforrás definiálása utasítással nyílik meg.

  3. A let kifejezésben adja hozzá a #"First Row as Header" é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"

  4. A lekérdezés munkalapra való betöltéséhez válassza a Kész gombot, majd a Kezdőlap >Bezárás & a > Betöltés & gombra.

Eredmény:

3. lépés - Eredmény

4. fázis: Egy oszlop minden értékének módosítása nagy- és kismértékre

  1. 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 és >gombra. További információt a Lekérdezés létrehozása,betöltése vagy szerkesztése a Excel.

  2. A Power Query szerkesztőben válassza a Kezdőlap > Speciális szerkesztő lehetőséget, amely a 3. fázis:Az első sor előléptethető fejlécekként utasítással nyílik meg.

  3. A let kifejezésben a Table.TransformColumns függvény használatával alakítsa át az egyes Terméknév oszlopértékeket megfelelő szöveggé, hivatkozva az "Első sor fejlécként" lekérdezési képletlépésre, adja hozzá az adatforráshoz a #"Capitalized Each Word" karakterláncot, majd rendelje hozzá a #"Capitalized Each 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"

  4. A lekérdezés munkalapra való betöltéséhez válassza a Kész gombot, majd a Kezdőlap >Bezárás & a > Betöltés & gombra.

Eredmény:

4. lépés - Eredmény

A Power Query Szerkesztő szerkesztőlécének viselkedését az összes munkafüzetben szabályozhatja.

A szerkesztőléc megjelenítése vagy elrejtése

  1. Válassza a Fájl> beállítások, majd a Gépház > lehetőséget.

  2. A bal oldali ablaktáblán a GLOBAL (GLOBÁLIS) csoportban válasszaa Power Query Editor (Power Query-szerkesztő) lehetőséget.

  3. A jobb oldali ablaktáblában, az Elrendezésalatt jelölje be a Szerkesztőléc megjelenítése jelölőnégyzetet, vagy törölje a jelölőnégyzetet.

Az M Intellisense be- és kikapcsolása

  1. Válassza a Fájl> beállítások, majd a Gépház > lehetőséget.

  2. A bal oldali ablaktáblán a GLOBAL (GLOBÁLIS) csoportban válasszaa Power Query Editor (Power Query-szerkesztő) lehetőséget.

  3. A jobb oldali ablaktáblában a Képlet csoportban jelölje be az M Intellisense engedélyezése a szerkesztőlécen, a Speciális szerkesztő és az Egyéni oszlop párbeszédpanelen jelölőnégyzetet,vagy törölje a jelölőnégyzetet.

Megjegyzés    A beállítás módosítása a Power Query Szerkesztő ablak legközelebbi megnyitásakor lép életbe.

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 (docs.com)

Egyéni függvények használata (docs.com)

Power Query M képletek (docs.com)

Hibák kezelése (docs.com)

További segítségre van szüksége?

Office-jártasság bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×