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

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.

1. Az adatok formázásához használt Power Query-szerkesztő menüszalag

2. Az adatforrások és táblák megkereséséhez használt Lekérdezések panel

3. Helyi menük, amelyek a menüszalag parancsainak kényelmes billentyűparancsai

4. Az adatokra alkalmazott lépések eredményeit megjelenítő adatelőnézet

5. 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

1. 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.

2. 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ő.

3. 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.

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 > Az egyéb 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 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.

6. 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.

7. 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.

8. 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

1. 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.

2. 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.

3. 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:
   
   Előtte: = Table.SelectColumns(#"Changed Type",{"Column4", "Column1", "Column5"})
   Utána:= Table.SelectColumns(#"Changed Type",{"Column2", "Column4", "Column1", "Column5"})

4. Válassza az Enter ikont, vagy nyomja le az Enter billentyűt az adatelőnézetben megjelenő új eredmények megtekintéséhez.

5. 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.

1. Ü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.

2. 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.

3. 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.

1. 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.

2. 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.

3. 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.

1. 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.

2. 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.

3. Í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ő

1. 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.

2. 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

1. 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

2. 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

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 > 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).

2. 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.

3. 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"

4. 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

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 > Szerkesztés lehetőséget. További információ: Lekérdezés létrehozása, betöltése vagy szerkesztése az Excelben.

2. 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.

3. 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"

4. 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

1. Válassza a Fájl > Beállítások és beállítások > Lekérdezés beállításai lehetőséget.

2. A bal oldali panel GLOBÁLIS területén válassza a Power Query-szerkesztő lehetőséget.

3. 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

1. Válassza a Fájl > Beállítások és beállítások > Lekérdezési beállítások lehetőséget.

2. A bal oldali panel GLOBÁLIS területén válassza a Power Query-szerkesztő lehetőséget.

3. 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.