Excel power pilot és power query használata

Power Query M fő funkciója

A Power Query M nyelv célja, hogy adatok betöltését, átalakítását és előkészítését automatizálja egy lekérdező motor segítségével. Az M nyelv deklaratív (leíró) jellegű, ami azt jelenti, hogy a felhasználó a kívánt eredményt határozza meg, nem pedig a műveletek végrehajtásának sorrendjét.

Hogyan valósítja meg ezt a Power Query?

1. Adatforrások csatlakoztatása

   • Különböző adatforrásokból olvas (Excel, SQL, Web API-k, SharePoint stb.).
   • Strukturált és félig strukturált adatokat képes feldolgozni.

2. Adatlekérdezés és transzformáció

   • Szűrés, csoportosítás, egyesítés és oszlopkezelés.
   • Képletek és kifejezések (pl. feltételes oszlopok létrehozása, dátumkezelés).

3. Automatikus újrahasználhatóság és dinamikusság

   • A lekérdezések automatikusan újrafuttathatók frissítéskor.
   • Paraméterezhető lekérdezések támogatása.

4. ETL (Extract, Transform, Load) végrehajtása

   • Extract (Kinyerés): Adatok beolvasása különböző forrásokból.
   • Transform (Átalakítás): Adattisztítás és formázás M nyelv segítségével.
   • Load (Betöltés): Adatok visszatöltése Power BI-ba, Excel-be vagy más célrendszerbe.

5. Függvények és bővíthetőség

   • Egyéni függvények és paraméterezhető lekérdezések létrehozása.
   • Külső adatkapcsolatok kezelése és API-hívások támogatása.

Power Pivot 

•  egy adatmodellező motor, amely xVelocity In-Memory alapú táblázatos motoron működik. Az In-Memory motor szupergyors válaszidőt biztosít a Power Pivot számára, a modellező motor pedig nagyszerű helyet biztosít a csillagséma, a számított mértékek és oszlopok felépítéséhez, entitásokon keresztüli kapcsolatok kiépítéséhez és így tovább.
•  a Data Analysis eXpression nyelvet (DAX) használja a mértékek és a számított oszlopok felépítéséhez. A DAX egy hatékony funkcionális nyelv, és ehhez rengeteg függvény található a könyvtárban. 

Alapértelmezetten az excel 2013-ban ki van kapcsolva a PowerPilot használati lehetőség.

A funkció bekapcsolása File -> Beállítások -> Bővítmények -> Com Bővítmények lista elem és ugrás gomb:

A megjelenő ablakban pipa a PowerPivot mellé és OK gomb:

Adatbázis adatok elérése 

 

SQL server elindítása :

•  Start -> SQL server Configuration 
•  SQLServer Service -> SQLServer(SQLEXPESS) -> Start

Kapcsolat beállítása :

•  Adatok -> Egyéb adatforrásból -> Az SQL Server

Tábla kiválasztása adatforrásként:

Csatlakozási karakterlánc mező tartalma :

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=kl;Data Source=KLAJOS-PC;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=KLAJOS-PC;Use Encryption for Data=False;Tag with column collation when possible=False

Tábla kapcsolat átírása SQL select parancsra:

•  Adatok -> Tulajdonságok

OK gombbal véglegesítjük az átállítást.