LuckyTemplates Power Query: eliminarea rândurilor duplicate

În acest blog, vă voi arăta cum să eliminați rândurile duplicate din LuckyTemplates Power Query în funcție de condiții. Puteți urmări videoclipul complet al acestui tutorial în partea de jos a acestui blog.

Acest exemplu este dintr-o întrebare care a fost ridicată pe . Membrul are date tranzacționale și dorește să elimine rândurile care se anulează reciproc.

Există două tipuri distincte de tranzacții, unul pentru intrare și unul pentru ieșire. Ea a adăugat și numărul de mișcări. Și pe baza asta, ea a formulat trei scenarii.

Scenariul unu se bazează pe două rânduri. Dacă se anulează reciproc, ambele rânduri ar trebui eliminate. Dacă nu este cazul, atunci ambele rânduri ar trebui păstrate.

Scenariile doi și trei se bazează pe o combinație de rânduri, anulându-se parțial reciproc. Aceste rânduri ar trebui eliminate în funcție de primul intrat, primul ieșit, astfel încât să fie păstrate numai cele mai recente tranzacții primite.

Practic, repetam aceeasi logica iar si iar. Ar trebui să putem proiecta o singură abordare care să se potrivească tuturor acestor scenarii.

Unele dintre lucrurile pe care le vom face sunt să adăugăm o coloană cu cantități absolute și să grupăm după „adăugați o coloană de sold de cantități” și un tabel imbricat folosind opțiunea „toate rândurile”. De asemenea, vom transforma acele tabele imbricate, vom sorta tipul de mișcare și data de postare, vom crea un total cumulat și vom păstra rândurile numai pe baza unei condiții.

Am evaluat acest lucru la un nivel intermediar, deoarece majoritatea transformărilor pe care le putem face aici sunt prin utilizarea interfeței cu utilizatorul. Acestea fiind spuse, să trecem la LuckyTemplates Power Query.

Cuprins

• Adăugarea unei coloane cu cantități absolute în Power Query
• Grupați după indicele ID și cantitatea absolută
• Transformarea tabelelor imbricate în LuckyTemplates Power Query
  • Sortarea tipului de mișcare și data înregistrării
  • Crearea unui total curent
  • Filtrarea rândurilor pe baza unei condiții
• Concluzie

Adăugarea unei coloane cu cantități absolute în Power Query

Vom începe prin a adăuga o coloană cu cantități absolute. Selectați coloana Cantitate , apoi pe panglica sau fila Adăugați coloană , mergeți la Științific , apoi faceți clic pe Valoare absolută .

Voi redenumi această coloană în bara de formule.

Grupați după indicele ID și cantitatea absolută

Acum putem grupa după Index ID și Cantitate Absolută. Selectați ID Index , apoi țineți apăsată tasta shift sau control pentru a selecta multiplă, apoi faceți clic pe Abs qty (cantitate absolută).

Veți găsi Grupare după pe fila Transformare.

Dar, îl puteți găsi și în fila Acasă.

Faceți clic pe el și acum vom adăuga o nouă coloană. Nu este un „număr”, dar va fi „suma” cantității. Deci, să-l redenumim în Cantitate (cantitate) Sold . Va fi o sumă a coloanei de cantitate. Și apoi, vom adăuga o altă agregare, dar va fi una specială, așa că vom selecta Toate rândurile . Să dăm și acestei coloane un nume ( AllRows ). Apoi, apăsați OK.

Dacă facem clic în spațiul alb din tabel, vedem o previzualizare a tabelului imbricat mai jos. Vedem că cantitățile pentru acest tabel imbricat se anulează reciproc, iar soldul cantității este zero.

Dacă ne uităm la următorul tabel imbricat (deci faceți clic pe lateral în spațiul alb al următorului rând), vedem că coloana de cantitate nu se anulează reciproc și un sold de cantitate este mai mare decât zero.

Să adăugăm o coloană personalizată, astfel încât să o putem folosi pentru transformările tabelelor noastre imbricate mai târziu. Deci, selectați Adăugați o coloană personalizată .

Să redenumim acea coloană în Transformări și vom adăuga zero (0) ca substituent. Apoi, faceți clic pe OK.

Transformarea tabelelor imbricate în LuckyTemplates Power Query

Acum suntem gata să creăm logica pentru transformarea acestor tabele imbricate. Dar nu vrem să scriem o grămadă de ele și nici nu avem nevoie.

Putem folosi interfața cu utilizatorul în cea mai mare parte. Dacă construim logica pentru acele transformări într-o interogare separată, cel mai elaborat scenariu pe care l-am avut a fost cel cu trei sau patru rânduri.

Acum, dacă dau clic pe partea dreaptă în ultimul rând, văd că acest tabel are patru rânduri. Deci, putem folosi asta pentru a ne construi transformările.

Faceți clic dreapta pe partea laterală în spațiul alb și selectați Adăugați ca interogare nouă .

Vedem că tabelul imbricat s-a extins și a fost adăugat ca o nouă interogare și îl putem folosi pentru a construi logica de care avem nevoie.

Sortarea tipului de mișcare și data înregistrării

Acum, primul lucru pe care trebuie să-l facem este să sortăm pe tipul de mișcare . Să alegem coborât . În acest fel, tranzacțiile de ieșire vor fi întotdeauna în top.

În continuare, vom sorta Data Postării ca crescător , asigurându-ne că, dacă omitem rânduri, se va face întotdeauna conform principiului FIFO (primul intrat, primul ieşit).

Crearea unui total curent

Pentru a identifica rândurile pe care le vom elimina, voi adăuga un total cumulat pentru a le dezactiva și putem folosi List.FirstN pentru acea sarcină. Această funcție creează o listă bazată pe o altă listă, unde elementele de top sunt păstrate pe baza unui anumit număr sau a unui criteriu.

Să revenim la interogarea noastră. Avem nevoie de două lucruri pentru List.FirstN. În primul rând, avem nevoie de acea listă, iar acea listă este coloana noastră Cantitate . Dacă dau clic dreapta pe antetul coloanei Cantitate, obținem opțiunea Add as NewQuery .

Și aici, vedem codul de care avem nevoie pentru a genera acea listă. În pasul aplicat, puteți vedea că indică către ultimul pas și apoi identifică coloana din acele paranteze.

A doua parte de care avem nevoie pentru List.FirstN este un număr pentru a identifica câte numere să păstrăm din acea listă. Putem folosi indexul pentru asta, așa că voi adăuga o coloană index dintr-una (1) pentru acest rând.

Deci, pentru numărul 1, va păstra numărul de pe primul rând din coloana Cantitate și așa mai departe. Și acum putem rezuma asta.

Să adăugăm o coloană personalizată și să o numim Total total . Folosim List.FirstN aici și putem vedea că primul parametru a fost acea listă, coloana Cantitate. Trebuie să indicăm ultimul pas, iar ultimul pas este Indexul adăugat .

Am identificat coloana pe care o dorim și aceasta este coloana noastră Cantitate. Numărul nostru este în coloana Index.

Acum returnează o listă. Dacă facem clic pe lateral în spațiul alb, putem vedea conținutul acelei liste. Pentru prima înregistrare, a păstrat doar rândul de sus din acea listă. Pentru al doilea record, a păstrat primele două rânduri.

Acum tot ce trebuie să facem este să însumăm aceste cantități și putem folosi Lists.Sum pentru a face asta. Deci, voi adăuga asta în bara de formule. Și o să pun și Type .

Filtrarea rândurilor pe baza unei condiții

Cu acest total curent, putem identifica rândurile pe care dorim să le păstrăm. Vrem doar să păstrăm rândurile care sunt mai mari decât zero, așa că să adăugăm o condiție de filtru.

Acum putem elimina coloanele noastre de ajutor. Selectați Index și Total, apoi faceți clic pe Eliminare coloane.

În Editorul avansat, acum putem copia codul pe care l-am creat. Voi deschide Editorul avansat și putem vedea că acesta este pasul nostru împărțit. Deci, putem selecta și copia totul de mai jos.

Acum să revenim la interogarea noastră inițială. Deschideți din nou Editorul avansat. Și în loc de substituent, să trecem la o nouă linie. Voi folosi expresia „ let ” deoarece aceasta poate capta valori din calcule intermediare în variabile. Deci, „lasă” și apoi mergi la o nouă linie și lipește transformările mele. De asemenea, voi declara Type .

Codul evidențiat de mai sus indică tabelul nostru imbricat extins, care sunt plasate în coloana Toate rândurile. Așadar, voi indica acest lucru, înlocuind acest cod evidențiat cu AllRows .

Deci, coloana noastră de transformare are acum tabele imbricate. Ne construim interogarea pe baza ultimului rând, nu? Și care conținea patru rânduri când am început și conținea doar două. Avem și acele cantități echilibrate cu zero, nu? Și acum are o masă goală.

Le putem elimina prin filtrare pe coloana Sold Cantitate. Nu vrem ca soldul cantitativ să fie egal cu zero.

Și apoi, voi selecta coloana mea de transformare și voi elimina alte coloane. Acum îmi pot extinde tabelele imbricate de transformare. Nu folosiți numele coloanei originale ca prefix și apăsați OK.

În fila Transformare, selectați Tip de date și am terminat. Și așa curățați datele tranzacționale.

Elemente de bază pentru anularea pivotării și pivotarea în LuckyTemplates – Editorul de interogări Examinați
sfaturile mele de practică atunci când utilizați Editorul avansat LuckyTemplates
Transformări avansate în LuckyTemplates

Concluzie

În acest tutorial, v-am arătat cum să curățați datele tranzacționale, în special eliminând rândurile duplicate. Aceasta este o tehnică excelentă pe care o puteți folosi atunci când utilizați LuckyTemplates Power Query.

Sper că ți-a plăcut asta. Urmăriți tutorialul video complet de mai jos pentru mai multe detalii. Consultați și linkurile de mai jos pentru mai multe conținuturi legate de LuckyTemplates Power Query Editor.

Noroc!

Melissa