[Resolved] Power Query analitika tisztítás

Kedves Imre!

Az átláthatóság kedvéért célszerűbbnek érzem, hogy két részben írjam le a teljes folyamatot. Elsőként az alap állapotot és ehhez kapcsolódó feldolgozási lépéseket. Majd ennek fényében a jelenlegi Power Query-s megközelítést. A téma miatt nem tudom teljes mértékben kihagyni a számviteli részeket, de próbálom minimalizálni a használatát.

A feldolgozandó könyvelési analitikát a havi zárásokat követően kérjük le a vállalatirányítási rendszerből melyet jelenleg egy binárisként mentett excel fájlban tárolunk a formátum gyakorlati haszna miatt. Ezt még ki kell egészíteni két állománnyal, melyeket külön kell lekérnünk a programból, hogy összeálljon a kiindulási adathalmaz. A feldolgozáshoz használt „törzs adatokat” külön fájlban kezelem. Munkalaponként bontva a logikailag összetartozó elemeket.

A rendelkezésre álló adatok közül minden sor esetében a főkönyvi számla és az objektum az a két elsődleges jellemző ami vezérli a későbbi besorolási folyamatot.

Ezek közül a főkönyvi számlákat egy oszlopban tárolja a táblázat illetve a korábban említett külön lekért táblák megfelelő adatai is egy az egyben besorolhatók ide, melyek a leendő eredménykimutatást követő és néhány kiegészítő információkat tartalmazó horizontális struktúra alapjai lesznek.

Az SAP-s objektumokat, amik majd a kimutatás vertikumát adják azonban már fajta szerint külön-külön oszlopban hozza le a rendszer. Ezek a rendelések, költséghelyek és PST-elemek.
– A költségáramlási szabályok miatt a rendelésekben kezelt két alcsoport elemeit másodlagos főkönyveken (6-os számla osztály) elszámoljuk a hozzájuk beállított költséghelyekre záráskor. Így ezen elemek besorolásakor ezt az „utólagos” objektumot használhatjuk a további lépésekben.
– A vertikális besorolás első lépéseként, a számviteli törvényi szabályozások miatt a főkönyvi számokat felhasználva ki kell szűrni azokat a tételeket, amiket nem lehet közvetlenül a projektek értékelésénél figyelembe venni. Ezek a hozzájuk tartozó gyűjtő osztályba kerülnek függetlenül attól, hogy milyen objektum lett eredetileg hozzájuk rendelve.
– Következő lépésként egy nagyobb csoport felosztott költség besorolása következik a törzs adatokban kezelt, meghatározott szervezeti költséghely és főkönyvi szám kombinációjának a figyelembe vételével. A rendszer itt is másodlagos főkönyveket könyvel záráskor, de a kimutatáshoz meg kell határozni az „eredeti halmazt” is amit szétosztott.
– Ezután kerülnek a fennmaradó költséghelyeken lévő értékek osztályozásra. Majd a megmaradó rendelések és PST elemek is besorolásra kerülnek.

A folyamat végén egy kimutatás készül ahol már egy táblában (még mindig csak egy előzetes) láthatóak külön-külön a munkáink bevételei, költségei és az egyéb csoportosított eredmények.

Ezt követően a projektek értékeléséhez újabb, kiegészítő adatok rendelődnek a sorokhoz (szerződés, partner, terv bevétel…). Majd több, célirányos számítás következik újabb oszlopokban. Ezen kalkulációk egyes elemei tényleges könyvelést vonnak maguk után a munkákon. Mások csak az eredmények értékeléséhez kellenek.

A jelenlegi Power Query-s megközelítés:

A „törzs adatok” betöltését követően elsőként a két „kiegészítő” állományt veszem elő külön-külön. Mind a két esetben a szükséges oszlopokat meghagyva csoportosítást végzek oly módon, hogy az értékek összegzése mellé második műveletként a „Minden sor” lehetőséget is kiválasztom. Aminek hála az eredeti összes, oszlopot vissza tudom nyerni. Ezek után a kiszámított részösszegek közül kiszűröm a nulla egyenleggel rendelkező tételeket. Ezzel a módszerrel nem szükséges egy másik lekérdezést is használni a kihagyható sorok megállapításához.

A korábban említett állapotot, hogy a rendszer objektum típusonként külön oszlopban adja meg az értékeket egy egyszerű oszlop egyesítéssel le is tudom.
További lépésként az általános leírásban említett két, költség elszámolással rendelkező csoport törzs adataival egyesítem a táblákat és az új oszlopokba kiíratom az utód objektumokat. Majd egy feltételes oszlopot használva összesítem az információkat úgy, hogy sorban veszem a lehetőségeket és amelyik sorban elszámolás van ott annak az oszlop értéket adja eredményül, ahol nincs ott az eredeti objektumot hagyja meg.

A kiegészítő táblákat csak eddig a lépésig készítem elő.

Az eredeti kérdés miatt a fő adatok tartalmazó táblázat adatait már most két fájlban kezelem. Külön a lezárt és külön a nyitott időszakra vonatkozó részeket. Annyi kis malőr van még benne, hogy a 2016-os excelben használatos PQ nem képes beolvasni a bináris állományt így ezeket xlsx kiterjesztéssel kell tárolni. A frissebb verziók nem tudom, hogy mentesek-e már ettől a hiányosságtól?!

Átgondolva a lehetséges lépéseket egy főkönyvi számla csoportonkénti előzetes felmérés után úgy döntöttem, hogy a feldolgozandó adathalmaz szűkítésére végett a két táblázatot tovább bontom egy csak másodlagos és egy elsődleges főkönyveket tartalmazó lekérdezésre. Természetesen ez azt jelenti, hogy négy külön lekérdezés készült úgy, hogy a lezárt időszak adataihoz már hozzáfűztem a nyitott időszak értékeit.

A kiegészítő tábláknál már leírt módszerrel ezen táblák esetében is csak az utód objektum megállapításáig és a zérus rész egyenlegek kiszűréséig végzem el a feldolgozást.

Az így előkészített lekérdezéseket összefűzöm egy új állományba és ott végzem el a további lépéseket.
Az általános leírásnál említett eredmény sorok megállapítás az ehhez tartozó „törzs állomány” egyesítésével készül. A vertikális besorolás szintén így történik, de a fent leírtaknak megfelelően ez már 5 „törzs” táblával való egyesítés, egy feltételes oszlopban ezek eredményeinek kiértékelése, majd a fölöslegessé vált oszlopok eltávolítása után záródik le.

Az így létrejött analitikát már kiíratom táblázatba is, de ez lesz az alapja a következő lépcsőfoknak szintén.

A kimutatáshoz használt lekérdezés első lépéseként csoportosítom a sorokat. Csak a leendő kimutatás előzőekben besorolt oszlopai és maguk az értékek kellenek. Az összegzett adatokra szintén teszek a biztonság kedvéért egy szűrőt ami az esetlegesen előforduló nulla egyenlegeket kiveszi.
Ezt követően elforgatom az eredmény sor oszlopot amivel már egy kimutatási táblázat formáját veszi fel a lekérdezés állapota. Ehhez az állapothoz rendelem hozzá a „törzs adatok” egyesítésével a projektekhez tartozó kiegészítő információkat. Majd az így elkészült lekérdezést szintén táblázatba íratom.

Az előzőekben említett célirányos számítások normál excel képleteit nem fordítottam még át a PQ nyelvezetére, de szeretném ezt is megtenni, hogy teljes legyen az automatizálási folyamat.

Mivel nem volt még időm elmerülni a PQ mély lélek tanában így tisztán csak ötletelni tudok, hogy hogyan lehetne egyszerűsíteni a folyamatot. Pl. Az egyedi képletezés meg engedi-e, hogy egyszerre több „törzs adat” lekérdezését bele vonjuk az „FKERES” modellezésbe, hogy kevesebb lépésből és segéd oszlopot használva tudjuk össze állítani a lekérdezést?

Válaszodat előre is Köszönöm!

Üdvözlettel:
Tóth Péter

Szia!

Az első kérdésedre IGEN a válasz. Már a májusi adatokkal együtt cirka 520 ezer sorról indultam. Ezért is kell ezeket az előszűréseket beiktatni.

Kimondottan PIVOT riport nem készült eddig. Ha sikerül belevarázsolni az említett, célirányos képletek alapján a számításokat akkor már egyértelműen PIVOT-ként fogom a táblákat kinyerni. Vagyis „hulladék időmben” erre kell fektetnem a hangsúlyt.

Köszönöm a Rám szánt idődet!

Péter