LuckyTemplates Power Query: Ismétlődő sorok eltávolítása

Ebben a blogban megmutatom, hogyan távolíthatja el a LuckyTemplates Power Query ismétlődő sorait a feltételek alapján. Az oktatóanyag teljes videóját a blog alján tekintheti meg.

Ez a példa egy kérdésből származik, amelyet a. A tag tranzakciós adatokkal rendelkezik, és el akarja távolítani az egymást kizáró sorokat.

Két különböző tranzakciótípus létezik, az egyik a bejövő és a másik a kimenő tranzakciókra. A mozgásszámot is hozzáadta. És ez alapján három forgatókönyvet fogalmazott meg.

Az első forgatókönyv két soron alapul. Ha kioltják egymást, mindkét sort el kell távolítani. Ha ez nem így van, akkor mindkét sort meg kell tartani.

A második és harmadik forgatókönyv sorok kombinációján alapul, amelyek részben kioltják egymást. Ezeket a sorokat az „első az elsőben” alapon el kell távolítani, hogy csak a legutolsó bejövő tranzakciók maradjanak meg.

Alapvetően ugyanazt a logikát ismételgetjük újra és újra. Képesnek kell lennünk egyetlen megközelítés kidolgozására, amely illeszkedik ezekhez a forgatókönyvekhez.

Néhány dolog, amit meg fogunk tenni, az, hogy hozzáadunk egy oszlopot abszolút mennyiségekkel, és csoportosítjuk a „mennyiségegyenleg-oszlop hozzáadása” és egy beágyazott táblázatot az „összes sor” opció használatával. Ezenkívül átalakítjuk ezeket a beágyazott táblázatokat, rendezzük a mozgás típusát és a feladás dátumát, létrehozunk egy futó összeget, és csak feltétel alapján tartjuk meg a sorokat.

Ezt közepesre értékeltem, mert itt a legtöbb átalakítást a felhasználói felület használatával végezhetjük el. Ezt követően térjünk át a LuckyTemplates Power Queryre.

Tartalomjegyzék

• Oszlop hozzáadása abszolút mennyiségekkel a Power Queryben
• Csoportosítás azonosító index és abszolút mennyiség szerint
• Beágyazott táblák átalakítása a LuckyTemplates Power Queryben
  • Rendezési mozgás típusa és feladási dátuma
  • Futóösszeg létrehozása
  • Sorok szűrése Feltétel alapján
• Következtetés

Oszlop hozzáadása abszolút mennyiségekkel a Power Queryben

Kezdjük azzal, hogy hozzáadunk egy oszlopot abszolút mennyiségekkel. Válassza ki a Mennyiség oszlopot, majd az Oszlop hozzáadása szalagon vagy lapon lépjen a Tudományos elemre , majd kattintson az Abszolút érték elemre .

Át fogom nevezni ezt az oszlopot a képletsorban.

Csoportosítás azonosító index és abszolút mennyiség szerint

Most már csoportosíthatunk azonosítóindex és abszolút mennyiség szerint. Válassza ki az ID Index elemet , majd tartsa lenyomva a Shift vagy a Control billentyűt a többszörös kijelöléshez, majd kattintson az Abs qty (abszolút mennyiség) elemre.

A Csoportosítás lehetőséget az Átalakítás lapon találja .

De megtalálhatja a Kezdőlap lapon is.

Kattintson rá, és most hozzáadunk egy új oszlopot. Ez nem egy „számlálás”, hanem a mennyiség „összege” lesz. Tehát nevezzük át a Qty (quantity) Balance névre . Ez a mennyiség oszlop összege lesz. Ezután hozzáadunk egy másik összesítést, de az egy speciális lesz, ezért az Összes sor lehetőséget választjuk . Adjunk nevet ennek az oszlopnak is ( AllRows ). Ezután nyomja meg az OK gombot.

Ha a táblázatban a fehér mezőre kattintunk, lentebb láthatjuk a beágyazott táblázat előnézetét. Látjuk, hogy ennek a beágyazott táblának a mennyiségei kioltják egymást, és a mennyiségi egyenleg nulla.

Ha megnézzük a következő beágyazott táblázatot (tehát kattintson oldalra a következő sor fehér mezőjében), azt látjuk, hogy a mennyiség oszlop nem zárja ki egymást, és a mennyiségi egyenleg nagyobb, mint nulla.

Adjunk hozzá egy egyéni oszlopot, hogy a későbbiekben felhasználhassuk beágyazott táblázattranszformációinkhoz. Tehát válassza az Egyéni oszlop hozzáadása lehetőséget .

Nevezzük át az oszlopot erreés helyőrzőként nullát (0) adunk hozzá. Ezután kattintson az OK gombra.

Beágyazott táblák átalakítása a LuckyTemplates Power Queryben

Most készen állunk arra, hogy létrehozzuk a logikát a beágyazott táblák átalakításához. De nem akarunk egy csomót írni belőlük, és nem is kell.

A felhasználói felületet nagyrészt tudjuk használni. Ha ezeknek az átalakításoknak a logikáját külön lekérdezésben építjük fel, akkor a legkidolgozottabb forgatókönyv a három vagy négy soros forgatókönyv volt.

Most, ha az utolsó sorban a jobb oldalra kattintok, azt látom, hogy ennek a táblázatnak négy sora van. Tehát ezt felhasználhatjuk az átalakulásunk felépítésére.

Kattintson a jobb gombbal a fehér területen lévő oldalra, és válassza a Hozzáadás új lekérdezésként lehetőséget .

Látjuk, hogy a beágyazott tábla most kibővült, és új lekérdezésként került hozzáadásra, és ezt felhasználhatjuk a szükséges logika felépítésére.

Rendezési mozgás típusa és feladási dátuma

Most először a Mozgás típusa szerint kell rendeznünk . Válasszuk a csökkenőt . Így a kimenő tranzakciók mindig a csúcson lesznek.

Ezután a Feladás dátumát növekvő sorrendben rendezzük , ügyelve arra, hogy ha kihagyunk sorokat, az mindig a FIFO elv szerint történik (first in first out).

Futóösszeg létrehozása

Az eltávolítandó sorok azonosításához hozzáadok egy futó összeget a letiltáshoz, és használhatjuk a List.FirstN-t ehhez a feladathoz. Ez a funkció egy listát hoz létre egy másik lista alapján, ahol a legfelső elemek egy adott szám vagy kritérium alapján kerülnek tárolásra.

Térjünk vissza a kérdésünkhöz. Két dologra van szükségünk a List.FirstN-hez. Először is szükségünk van arra a listára, és ez a lista a Mennyiség oszlopunk. Ha jobb gombbal kattintok a Mennyiség oszlop fejlécére, megkapjuk a Hozzáadás új lekérdezésként opciót .

És itt látjuk a kódot, amelyre szükségünk van a lista létrehozásához. Az alkalmazott lépésben láthatja, hogy az utolsó lépésre mutat, majd azonosítja a zárójelben lévő oszlopot.

A második rész, amelyre szükségünk van a List.FirstN számára, egy szám, amely meghatározza, hogy hány számot kell megtartani a listából. Ehhez használhatjuk az indexet, ezért ehhez a sorhoz hozzáadok egy indexoszlopot az egyikből (1).

Tehát az 1-es számnál a Mennyiség oszlop első sorában lévő szám marad, és így tovább. És ezt most összegezhetjük.

Adjunk hozzá egy egyéni oszlopot, és nevezzük azt Futó összegnek . Itt a List.FirstN-t használjuk, és láthatjuk, hogy az első paraméter ez a lista volt, a Mennyiség oszlop. Az utolsó lépésre kell mutatnunk, és ez az utolsó lépés a Hozzáadott index .

Meghatároztuk a kívánt oszlopot, ez pedig a Mennyiség oszlopunk. Számunk az Index oszlopban található.

Most egy listát ad vissza. Ha a fehér mezőben oldalra kattintunk, láthatjuk a lista tartalmát. Az első rekordnál csak a felső sort tartotta meg a listáról. A második rekordnál megtartotta az első két sort.

Most már csak összegezni kell ezeket a mennyiségeket, és ehhez a List.Sum segítségével tehetjük meg. Szóval, hozzáteszem a képletsávhoz. És felteszem a Típust is .

Sorok szűrése Feltétel alapján

Ezzel a futó összeggel azonosíthatjuk azokat a sorokat, amelyeket meg akarunk tartani. Csak a nullánál nagyobb sorokat szeretnénk megtartani, ezért adjunk hozzá egy szűrőfeltételt.

Most már eltávolíthatjuk segédoszlopainkat. Válassza az Index és a Futó összeget, majd kattintson az Oszlopok eltávolítása lehetőségre.

A Speciális szerkesztőben most már másolhatjuk az általunk létrehozott kódot. Megnyitom a -t, és láthatjuk, hogy ez a mi kettéosztott lépésünk. Tehát az alatta lévő mindent kiválaszthatunk és másolhatunk.

Most térjünk vissza az eredeti lekérdezésünkhöz. Nyissa meg ismét a Speciális szerkesztőt. A helyőrző helyett pedig térjünk át egy új sorra. A „ let ” kifejezést fogom használni, mert ez a változók közbenső számításaiból származó értékeket rögzíthet. Tehát „engedjük”, majd lépjen egy új sorba, és illessze be az átalakításaimat. A típust is megadom .

A fenti kiemelt kód a kibontott beágyazott táblázatunkra mutat, amely az Összes sor oszlopban található. Tehát erre fogok rámutatni azzal, hogy ezt a kiemelt kódot az AllRows- ra cserélem .

Így a transzformációs oszlopunkban most beágyazott táblázatok vannak. A lekérdezésünket az utolsó sor alapján építjük fel, igaz? És ez négy sort tartalmazott, amikor elkezdtük, és csak kettőt. Nálunk is nulla a mennyiség, igaz? És most van egy üres asztala.

Ezeket a Mennyiségi egyenleg oszlop szűrésével eltávolíthatjuk. Nem akarjuk, hogy a mennyiségi egyenleg nullával egyenlő legyen.

Ezután kiválasztom az átalakítási oszlopomat, és eltávolítom a többi oszlopot. Most már bővíthetem a transzformációs beágyazott tábláimat. Ne használja az eredeti oszlopnevet előtagként, és nyomja meg az OK gombot.

Az Átalakítás lapon válassza az Adattípus lehetőséget , és készen is vagyunk. És így tisztítja meg a tranzakciós adatokat.

Következtetés

Ebben az oktatóanyagban megmutattam, hogyan tisztíthatja meg a tranzakciós adatokat, különös tekintettel az ismétlődő sorok eltávolítására. Ez egy nagyszerű technika, amelyet a LuckyTemplates Power Query használatakor használhat.

Remélem, élvezted ezt. További részletekért tekintse meg az alábbi teljes videós bemutatót. Tekintse meg az alábbi linkeket a LuckyTemplates Power Query Editorral kapcsolatos további kapcsolódó tartalmakért.

Egészségére!

Méhfű