Elemi értékekre bontás – UNPIVOT, a nagy trükk

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

Gyakran találkozom azzal a problémával, hogy egy PIVOT jellegű elrendezésben lévő tartományt át kellene rendezni a következő ábra szerint. Ezt a műveletet UNPIVOT néven nevezi a világ. Magyarul elemi értékekre bontásnak nevezik.

Azt hiszem, hogy egyetérthetünk abban, hogy ezt manuális munkával elvégezni eléggé reménytelen vállalkozás. Főleg akkor, ha sok sor vagy/és sok oszlop van az eredeti táblában.

Erre a problémára makrót szoktak írni, vagy használható a PowerQuery egyik menüje is, de lehet találni fizetős UNPIVOT Add-in-t, azaz bővítményt is,  ami segít a megoldásban. Volt egy Fórum kérdés is még 2016-ban, ahol szintén a PowerQuery-s megoldás született.

A minap tanulmányoztam azt, hogy hogyan lehet több, azonos szerkezetű adatforrás konszolidálásával PIVOT táblát készíteni. Ez a lehetőség be van építve elég régóta az Excel PIVOT varázslóba. Újra megnéztem, és újra arra jutottam, hogy használhatatlan. Megerősödött az a véleményem, hogy a több adatforrást bármilyen eszközzel előbb egy közös adatforrásba kel tenni, és utána kell elkészíteni a riportot.

A megvilágosodás

Miközben a fentieket tanulmányoztam, elém tárult egy lehetőség, amit korábban nem ismertem, pedig eléggé beleástam magam a PIVOT táblák témájába. Eddig az UNPIVOT típusú feladatokra a Power Query-t használtam és ezt is tanítottam. Erre kiderül, hogy sima PIVOT tábla megoldással is megoldható ez a kérdés.

UNPIVOT lépések

Elsőként el kell indítani a PIVOT tábla varázslót. Itt beleütközünk abba a problémába, hogy a PIVOT varázsló nincs alapból fent egyik szalagon sem. De előcsalogathatjuk  a magyar Excelben az Alt->D->I billentyűk egymás utáni (nem egyszerre!!!) lenyomásával. Angol Excelben ugyanez az Alt->D->P.

Itt beállítjuk, hogy Több tartományt szeretnénk összesíteni. Ez nem igaz ugyan, de most ezt kell választani.

Második lépésben beállítjuk, hogy az oldalmezőket a felhasználó készíti

A harmadik lépésben megadjuk, hogy melyik tartományt akarjuk elemeire bontani. Kijelöljük a teljes tartományt, és a Hozzáadás gombbal érvényesítjük. Lent jóváhagyjuk, hogy nem kell oldal mező.

A negyedik lépésben a kimutatást egy új oldalra kérjük. Eddig még semmi nem történt, „csak” az eredeti tartományt tettük be egy PIVOT táblába.

Itt jön a meglepetés!

Ha kettőt kattintunk a végösszegen, akkor egy új munkalapon elénk tárul a csoda, az UNPIVOT végeredmény. Már csak annyi dolgunk marad, hogy átnevezzük a fejléc tételeit.

Lássuk mindezt videón