Telefonszámunk: 1-472-0679

[Resolved] Power Query analitika tisztítás

Kezdőlap Fórumok Power BI témák [Resolved] Power Query analitika tisztítás

Topic Resolution: Resolved
5 bejegyzés megtekintése - 1-5 / 5
  • Szerző
    Bejegyzés
  • #7212
    TPeter84
    Felhasználó

      Kedves Imre!

      Power Query-vel kapcsolatban lenne kérdésem.

      A havi rendszerességgel végzett projekt utókalkulációhoz is el kezdtem használni a PQ-t.

      Előre tudható, hogy az év vége felé közeledve a tény könyvelési analitikánk túl fogja lépni a maximális korlátot ami egy munkalapon kezelhető.

      Ennek megfelelően úgy kezdem el feldolgozni az adatforrásokat, hogy a táblák összefűzése előtt egy előszűrést végzek amivel a projekt kódok és költségnemek csoportosításával a tény összegeket megállapítom és megjelölöm a nulla egyenleggel rendelkezőket.

      Ezután új lekérdezést indítva, ismételten behívom a forrás táblát és az előzőekben megállapított, a végeredményre hatással nem lévő sorokat kiszűröm az adatok közül.

      Ez a fajta megközelítés működik ugyan, de meglehetősen idő igényes folyamat. Próbáltam a neten más megoldást keresni ami kevésbé megterhelő a rendszernek, de egyenlőre nem találtam.

      A gyakorlati tapasztalatok alapján milyen irányba lenne érdemes elmozdulnom?

      Segítségedet előre is Köszönöm!

      Üdvözlettel:
      Tóth Péter

      #7213
      horvimi
      Adminisztrátor

        Szia!

        Mondtam, hogy kérdezni fogok, mert a válaszhoz pontosan értenem kell amit szeretnél csinálni.
        Először is le kell szögezni itt is, hogy csodák nincsenek, sok adattal sokáig tarthat a feldolgozás.
        De azért érdemes megnézni, hogy mit is csinálsz tulajdonképpen.

        Kérdéseim:
        – Havi tábla összefűzésről van szó mappából? A teljes folyamat érdekel az elejétől a végéig.
        – Az összefűzött eredménytáblával utána mi a teendő? (Gondolom ebből havi szinten keletkezik valami riport. Pivotok, vagy egyéb elemzés.
        – Az előszűrés után betöltöd munkalapra, és a következő lekérdezést ezen alapítod? Vagy ezt felhasználva különbséget képzel?

        Meggondolandó
        Ha az eredeti gondolat szerint összefűzött táblával meghaladnád az Excel munkalap méretét, akkor töltheted az eredményt PowerPivot adatmodellbe is, így nem kell kétszer nekifutni.

        Imre

        #7231
        TPeter84
        Felhasználó

          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

          #7237
          horvimi
          Adminisztrátor

            Hűha!

            Ennyire alapos leírásra nem számítottam.
            Most az van, hogy a lépéseket értem, de az nem várható el az adatok és a feladat pontos szakmai ismerete nélkül, hogy alternatív megoldást adjak.

            Kérdések:
            Jól értem-e, hogy az eredménytábla sorainak száma az év során növekedve elérné az Excel max sorainak számát, és ezért vagy kénytelen plusz lépéseket, előválogatásokat tenni bele.
            Tehát, ha nem lenne korlát, akkor kevesebb lépés is elég lenne?

            A betöltött eredménytáblából készül egy PIVOT riport? Én nem látok ilyet a folyamat végén
            Ezt az előző hozzászólásban is megkérdeztem, de nem kaptam választ, vagy nem tudtam kibányászni az előző leírásból.

            Ha ez lenne, akkor meg lehetne próbálni kiiktatni a helytakarékosságra irányuló bonyolultabb összevonásokat, és inkább az eredményt PowerPivot adatmodellbe lehetne tölteni, és onnan készíteni PowerPivot táblákat.

            Ezt szintén javasoltam már, de nem reagáltál rá.

            A PowerQuery Merge (FKERES) műveletben egy lépésben csak kettő tábla tud részt venni.

            Imre

            #7238
            TPeter84
            Felhasználó

              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

            5 bejegyzés megtekintése - 1-5 / 5
            • Be kell jelentkezni a hozzászóláshoz.