Sorok csoportosítása vagy összegzése
A Power Queryben a különböző sorok értékeit egyetlen értékre csoportosíthatja úgy, hogy a sorokat egy vagy több oszlop értékei szerint csoportosítja. Kétféle csoportosítási művelet közül választhat:
Oszlopcsoportok.
Sorcsoportok.
Ebben az oktatóanyagban a következő mintatáblát használja.
Képernyőkép egy táblázatról, amelyen az Év (2020), az Ország (USA, Panama vagy Kanada), a Termék (Póló vagy Rövidnadrág), az Értékesítési csatorna (Online vagy Viszonteladó) és az Egységek (55–7500 közötti különböző értékek) oszlopai láthatók.
A Csoportosítás gomb három helyen található:
A Kezdőlap lap Átalakítás csoportjában.
Az Átalakítás lap Táblázat csoportjában.
A helyi menüben kattintson a jobb gombbal az oszlopok kijelöléséhez.
Ebben a példában a cél az ország és az értékesítési csatorna szintjén értékesített összes egység összegzése. Az Ország és az Értékesítési csatorna oszlopokkal hajthatja végre a csoportot művelet szerint.
- Válassza a Csoportosítás gombot a Kezdőlap lapon.
- Válassza a Speciális lehetőséget, így több oszlopot is kijelölhet a csoportosításhoz.
- Válassza ki az Ország oszlopot.
- Válassza a Csoportosítás hozzáadása lehetőséget.
- Válassza ki a Sales Channel oszlopot.
- Az Új oszlop nevére írja be az Összes egység kifejezést, a Művelet területen válassza az Összeg lehetőséget, majd az Oszlop területen válassza az Egységek lehetőséget.
- Kattintson az OK gombra
Ez a művelet az alábbi táblázatot adja meg.
A Csoportosítás funkcióval az elérhető műveletek kétféleképpen kategorizálhatók:
- Sorszintű művelet
- Oszlopszintű művelet
Az alábbi táblázat ezeket a műveleteket ismerteti.
Művelet neve | Kategória | Leírás |
---|---|---|
Összeg | Oszlopművelet | Egy oszlop összes értékének összegzése |
Átlagos | Oszlopművelet | Egy oszlop átlagértékének kiszámítása |
Medián | Oszlopművelet | Kiszámítja a mediánt egy oszlopból |
Min | Oszlopművelet | Egy oszlop minimális értékének kiszámítása |
Max | Oszlopművelet | Egy oszlop maximális értékének kiszámítása |
Percentilis | Oszlopművelet | A percentilis kiszámítása 0 és 100 közötti bemeneti értékkel egy oszlopból |
Eltérő értékek megszámlálva | Oszlopművelet | Az oszloptól eltérő értékek számának kiszámítása |
Sorok megszámlálása | Sorművelet | Kiszámítja egy adott csoport sorainak teljes számát |
Különálló sorok megszámlálása | Sorművelet | Kiszámítja az adott csoporttól eltérő sorok számát |
Minden sor | Sorművelet | Egy táblaérték összes csoportosított sorát összesítés nélkül adja ki |
Megjegyzés
A különböző értékek és percentilis műveletek száma csak a Power Query Online-ban érhető el.
Az eredeti mintától kezdve ebben a példában egy olyan oszlopot hoz létre, amely tartalmazza a teljes egységeket és két másik oszlopot, amelyek az ország és az értékesítési csatorna szintjén összegzett, a legjobban teljesítő termék nevét és értékesített egységeit adják meg.
A következő oszlopokat használja csoportosítási oszlopként:
- Ország
- Értékesítési csatorna
Hozzon létre két új oszlopot az alábbi lépések végrehajtásával:
- Összesíti az Egységek oszlopot az Összeg művelettel. Nevezze el ezt az oszlopot Összes egységnek.
- Adjon hozzá egy új Termékek oszlopot a Minden sor művelettel.
A művelet befejezése után figyelje meg, hogy a Termékek oszlopban a [Table] értékek szerepelnek az egyes cellákban. Minden [Tábla] érték tartalmazza azokat a sorokat, amelyeket az eredeti tábla Ország és Értékesítési csatorna oszlopai csoportosítottak. A cellán belüli üres terület kiválasztásával megtekintheti a táblázat tartalmának előnézetét a párbeszédpanel alján.
Megjegyzés
Előfordulhat, hogy a részletek előnézete panel nem jeleníti meg a csoportosítási művelethez használt összes sort. A [Tábla] érték kiválasztásával megtekintheti a megfelelő csoportosítási művelethez tartozó összes sort.
Ezután ki kell nyernie azt a sort, amely a legmagasabb értéket tartalmazza az új Termékek oszlopban lévő táblák Egységek oszlopában, és ezt az új oszlopot kell meghívnia a legjobban teljesítő terméknek.
A [Tábla] értékeket tartalmazó új Termékek oszlopban új egyéni oszlopot hozhat létre a menüszalag Oszlop hozzáadása lapján, és az Általános csoport Egyéni oszlopa lehetőséget választva.
Nevezze el az új oszlop legjobban teljesítő termékét. Írja be a képletet Table.Max([Products], "Units" )
az Egyéni oszlop képlete mezőbe.
A képlet eredménye egy új oszlopot hoz létre [Rekord] értékekkel. Ezek a rekordértékek lényegében csak egy sorból állnak. Ezek a rekordok tartalmazzák a Termékek oszlopban lévő [Tábla] értékek Egység oszlopának maximális értékét tartalmazó sort.
Ezzel az új, [Rekord] értékeket tartalmazó legjobban teljesítő termékoszlopban kiválaszthatja a kibontás ikont, kiválaszthatja a Termék és egység mezőket, majd az OK gombot.
Miután eltávolította a Termékek oszlopot, és beállította mindkét újonnan kibontott oszlop adattípusát, az eredmény az alábbi képhez fog hasonlítni.
Megjegyzés
Az alábbi funkció csak a Power Query Online-ban érhető el.
A "homályos csoportosítás" módjának bemutatásához tekintse meg az alábbi képen látható mintatáblát.
A homályos csoportosítás célja egy olyan csoportosítási művelet végrehajtása, amely egy hozzávetőleges egyező algoritmust használ a szöveges sztringekhez. A Power Query a Jaccard-hasonlósági algoritmust használja a példánypárok közötti hasonlóság mérésére. Ezután agglomeratív hierarchikus fürtözést alkalmaz a csoportpéldányokra. Az alábbi képen a várt kimenet látható, ahol a táblázat a Person oszlop szerint van csoportosítva.
A homályos csoportosítás elvégzéséhez hajtsa végre a cikkben korábban ismertetett lépéseket. Az egyetlen különbség az, hogy ezúttal a Csoportosítás párbeszédpanelen bejelöli a Homályos csoportosítás használata jelölőnégyzetet.
A Power Query minden sorcsoport esetében a leggyakoribb példányt a "canonical" példányként választja ki. Ha több példány is előfordul ugyanazzal a gyakorisággal, a Power Query az elsőt választja ki. Miután a Csoportosítás párbeszédpanelen az OK gombot választja, megkapja a várt eredményt.
A Fuzzy csoportbeállítások kibontásával azonban jobban szabályozhatja a homályos csoportosítási műveletet.
A fuzzy csoportosításhoz a következő lehetőségek érhetők el:
- Hasonlóság küszöbértéke (nem kötelező):Ez a beállítás azt jelzi, hogy a két értéknek hogyan kell csoportosítania. A nulla (0) minimális beállítása miatt az összes érték csoportosítva lesz. Az 1 érték maximális beállítása csak a pontosan egyező értékek csoportosítását teszi lehetővé. Az alapértelmezett érték 0,8.
- Kis- és nagybetűk figyelmen kívül hagyása: Szövegsztringek összehasonlítása esetén a kis- és nagybetűk figyelmen kívül lesznek hagyva. A beállítás alapértelmezés szerint engedélyezett.
- Csoportosítás szövegrészek kombinálásával: Az algoritmus megpróbálja egyesíteni a szövegrészeket (például a Micro és a Soft egyesítését a Microsofttal) az értékek csoportosításához.
- Hasonlósági pontszámok megjelenítése: Hasonlósági pontszámok megjelenítése a bemeneti értékek és a számított reprezentatív értékek között a homályos csoportosítás után. Egy olyan művelet hozzáadását igényli, mint a Minden sor , hogy sorról sorra mutassa ezeket az információkat.
- Transzformációs táblázat (nem kötelező):Kiválaszthat egy olyan átalakítási táblát, amely az értékeket leképezi (például az MSFT-et a Microsofthoz rendeli), hogy csoportosítsa őket.
Ebben a példában egy transzformációs táblázat mutatja be, hogyan lehet az értékeket megfeleltetni. Az átalakítási táblázat két oszlopból áll:
- Feladó: A táblázatban keresni kívánt szöveges sztring.
- To: Az a szöveges sztring, amely a Feladó oszlopban lévő szövegsztring helyére lesz behelyettesíteni.
Az alábbi képen az ebben a példában használt transzformációs táblázat látható.
Fontos
Fontos, hogy az átalakítási táblázat ugyanazokat az oszlopokat és oszlopneveket tartalmazza, mint az előző képen (a "Feladó" és a "Címzett" címkével kell rendelkezniük). Ellenkező esetben a Power Query nem ismeri fel a táblát átalakítási táblaként.
Térjen vissza a Csoportosítás párbeszédpanelre, bontsa ki az Fuzzy csoport beállításait, módosítsa a műveletet a Darabszám sorról a Minden sorra, engedélyezze a Hasonlósági pontszámok megjelenítése lehetőséget, majd válassza az Átalakítás tábla legördülő menüt.
Az átalakítási tábla kiválasztása után kattintson az OK gombra. A művelet eredménye a következő információkat adja meg:
Ebben a példában engedélyezve volt a Kis- és nagybetűk figyelmen kívül hagyása beállítás, így az Átalakítás tábla From oszlopában lévő értékek a szöveges sztring keresésére szolgálnak anélkül, hogy figyelembe vennék a sztring esetét. Ez az átalakítási művelet először megtörténik, majd a rendszer végrehajtja a homályos csoportosítási műveletet.
A hasonlósági pontszám a személy oszlop melletti táblaértékben is megjelenik, amely pontosan tükrözi az értékek csoportosításának módját és a hozzájuk tartozó hasonlósági pontszámokat. Szükség esetén kibonthatja ezt az oszlopot, vagy használhatja az új gyakorisági oszlopok értékeit más típusú átalakításokhoz.
Megjegyzés
Ha több oszlop szerint csoportosít, az átalakítási tábla minden oszlopban végrehajtja a csereműveletet, ha az érték cseréje növeli a hasonlósági pontszámot.
Az átalakítási táblák működéséről az Átalakítás tábla parancsai című témakörben talál további információt.